Sales Cost Calculator - lots of variables!! Help Please!

[Jamie]

Dear All,

I am trying to make a basic sales cost calulator for my sales team. It
consists of lots of variables, of which I have managed to solve a few. The
main problem I have is the following:
I have 4 types of variables (more if I can use them, but 4 minimum) which
affect the price
1) Destination (6 options e.g New York)
2) Mode of transport (3 options e.g.Sea Freight )
3) Freight rate (4 options e.g $45 per cuft)
4) Destination rate (4 options e.g. full unpacking, not just curbside
deliery )

I have the above as options using Data - Validation drop down screens.
What I want to do is to get, for an example the cost for a job with X [units
of measurement] and the following responses:
1 - 4
2 - 2
3 - 1
4 - 4

All of the rates are on a seperate worksheet on the workbook, so I'll need
to be able to have a solution that is able to use a different worksheet
(unless I transfer everything on to the main one which is a possibility, but
not clean.

Can anybody exaplain, as basically as possible how I can do this please?

Any responses gratefully received.

Jamie

 

[galimi]

Jamie,

It seems like you want to do some basic permuations, which can be
accomplished with a couple of for...next loops in VBA. Provide further
details.

 

[Eduardo]

Hi Jamie,
Let's assume you have the destination information/rates in Sheet 2 and you
are in Sheet1
Besides the destination name enter this formula

=VLOOKUP(A3,Sheet2!A:B,2,FALSE),
It will look up for the destination which is in Sheet 2 column A , and will
bring the value of column B (2), if the value is in another column count it
and change the 2

 

[Jamie]

Dear Eduardo and Galimi,

Thanks for the responses. I think I need to be more clear. The values in
each possibility will be different and I need to multiply each of those
values by the volume of goods, then add everything up, eg. as follows:
I have 100 cuft for a client who wants a comparisson rate for air and sea to
France
but with a full door delvery, so I need to calculate 1) the packing (which I
have solved) then 2 options (air and sea) for the destination.
100 cuft x by
1 Destination (6 possible tarriffs
then add the multiplication of cuft for:
2 Mode of transport (both air and sea different rates)
then add the multiplication of cuft for:
3 additional costs for transport (air and sea have differnt add-on costs)
then add the multiplication of cuft for:
4 Destination services (full unpacking versus curbside which have different
rates)

I need a calculation that thinks 'ok, I have 100 cuft, where's it going? ok,
then how is it going there, then what are associated costsinvolved and what
service at the end?)

Sorry if this is unclear, I don't know how to explain to a non-freight
industry person!
Thanks for your efforts
Jamie

Hi Jamie,
Let's assume you have the destination information/rates in Sheet 2 and you
are in Sheet1
Besides the destination name enter this formula

=VLOOKUP(A3,Sheet2!A:B,2,FALSE),
It will look up for the destination which is in Sheet 2 column A , and will
bring the value of column B (2), if the value is in another column count it
and change the 2

Dear All,

I am trying to make a basic sales cost calulator for my sales team. It
consists of lots of variables, of which I have managed to solve a few. The
main problem I have is the following:
I have 4 types of variables (more if I can use them, but 4 minimum) which
affect the price
1) Destination (6 options e.g New York)
2) Mode of transport (3 options e.g.Sea Freight )
3) Freight rate (4 options e.g $45 per cuft)
4) Destination rate (4 options e.g. full unpacking, not just curbside
deliery )

I have the above as options using Data - Validation drop down screens.
What I want to do is to get, for an example the cost for a job with X [units
of measurement] and the following responses:
1 - 4
2 - 2
3 - 1
4 - 4

All of the rates are on a seperate worksheet on the workbook, so I'll need
to be able to have a solution that is able to use a different worksheet
(unless I transfer everything on to the main one which is a possibility, but
not clean.

Can anybody exaplain, as basically as possible how I can do this please?

Any responses gratefully received.

Jamie