I want to calculate approx shipping costs, with minimal losses across
most situations. Shipping costs are dependent on weight and
destination continent. I want to know which formulas best fit my data,
while (if possible) having one formula per shipping method, to
minimize losses on shipping when automated quoting to customers. I
have 4 tables, one for each shipping method available. rows has weight
ranges, in grams. columns of data are 5 per table, one for each
continent. Our average package weight is 2500g and we most often ship
to Groups III and IV.

You can download the XLS file from http://tinyurl.com/an8ca

Thank you.

You may have to adjust some of the shipping rates, if you want a
simple linear formula. For example, for Mercadoria Economica, group I,
the formula grpI = 45.5 + 0.006 peso  gives the rates currently in use
for weights above 5000 grams.  It is too high by 0.5 for all other
weight classes except 1500g, and it is too high by 2.5 for the case of
1500 grams. Similar approximate linear formulae can be found in most
other cases. They always give shipping rates that are of the form
123.0 or 234.5 and may differ from your current rates, sometimes by up
to 5.  The biggest difference usually occurs for the case of 1500
grams, where your published rate is always a bit higher than the rate
that would be produced by a linear formula.

Is that what you want?

Regards

hpe

hi hpe

I dont think a linear formula is necessary, as i intend to plug it
into computer code if i can find a good one. Also, note that weight in
these tables is by range, meaning the 2500 weight row is for
2000-2999.

I've did a regression myself on GRPIxMercadoriaEconomica and found similar formula:
GRP I = 45.6265037593985+5.98984962406015E-03*Peso(g)

What i would like to have is the least amount of formulas to deal
with. If I can't find a good small set, i will just do a table lookup
on the actual rates.

thanks,

EB

I don't think you need a formula at all. I mean, if it is true that
the weight rows are by range there couldn't be any formula because the
price does not change according to the weight, but according to the
range. Indeed even though your formula for GRP I is correct for the
values in the table (except for 1500..), if you have a mercadoria of
750 g, the formula's not applicable to the case-as well as all the
others not in the table. Moreover you better check your data because
it seems that the ranges for mercadoria economica and for EMS
mercadoria are not the same.
What I think you need is a simple program in which you specify the
different criteria: weight, GRP #, if it is a mercadoria or a
documento, and if it is economica or EMS, and the program checks which
range the weight is in and, according the datas previously put in it,
it comes out with the price.

Regards, pizzy

yes pizzy thats what i figured out i'd need to do.

thanks to all

PS: EMS and mercadoria are different shipping methods, and do need different values.