cost calculation for salesa with variable transaction cost, different cost monthwise

[nsimha]

I am building a small summary sheet to workout costing for an item sold... Following is the summary

There are two type of equipment sold - Car and Bike in different quantities each month
There is a transaction cost per sale for every car & bike sold.. The transaction cost varies between months
There are three employees handling the sales and i have a small table showing allocation of cost of these three employees to every sale

I am able to determine (using vlookup) the total cost of an item sold based on quantity sold as long as the transaction cost and allocation cost has to be derived from only one month
I am unable to arrive at a formula that can be copied across cells.

I need a formula which will lookup a) item b) month & c) allocation together to determine cost for any item in any month.. ..

I have given below sample data for just two products and three employees.. The actual data that I work on consists of atleast 15 products and cost of 12 employees in various proportions to the 15 products.

can somebody help me ? I will be glad to provide any information you need Fixed Cost Emp cost Apr May Jun Jul Aug Sep Oct Nov Dec Jan peter 80000 88000 96800 106480 117128 128840.8 141724.9 155897.4 171487.1 188635.8 thomas 90000 99000 108900 119790 131769 144945.9 159440.5 175384.5 192923 212215.3 jack 65000 71500 78650 86515 95166.5 104683.2 115151.5 126666.6 139333.3 153266.6 <colgroup><col><col span="10"></colgroup><tbody> </tbody>

<colgroup><col><col span="10"></colgroup><tbody>
</tbody>

Fixed Transaction cost / unit
	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan
bike	10	11	12	13	14	15	16	17	18	19
car	12	13	14	15	16	17	18	19	20	21

<colgroup><col><col span="10"></colgroup><tbody>
</tbody>

Fixed Cost allocation
Type	peter	thomas	jack
bike	80%	10%	40%
car	20%	90%	60%

<colgroup><col><col span="3"></colgroup><tbody>
</tbody>

Item sold	Aug	Sep
Bike	10	20
Car	20	30
Bike cost	99100	109120		Can this be done by vlookup??
Car cost	136240	149990		Can this be done by vlookup??

<colgroup><col span="8"></colgroup><tbody>
</tbody>

 

[gaz_chops]

What is the formula you have already?

 

[gaz_chops]

I think a few things appear wrong, The 99100 & 109120 should (I think) be 76250 & 83985, you are using 80% 10% 40% in your calc, that = 130%!
Should prob be 61.5% 7.7% 30.8% = 100% (that is 80/130 etc). Also these figures are for Apr/May i assume (not Aug/Sep?).

Is this correct?

 

[gaz_chops]

Ignore my last post! Think I got my head round your calcs.

 

[nsimha]

Thanks but can you help me with a solution...

 

[gaz_chops]

Is all of the data on one sheet?

 

[nsimha]

yes it is on a single sheet

 

[gaz_chops]

In above your data starts in A1

Change the layout of "Fixed Cost Allocation" to

Fixed Cost allocation
Type bike car
peter
thomas
jack

Next to "Bike Cost" enter
=SUMPRODUCT(B3:B5,$B$14:$B$16)+(B9*B20)
Next to "Car Cost" enter
=SUMPRODUCT(B3:B5,$C$14:$C$16)+(B10*B21)

Drag both across columns

 

[nsimha]

Fantastic.. it worked... thank you

However is there a way to workout the same if the names and allocation are in different order.. that is can we validate the formula by adding a lookup or if statement to ensure that that the formula picks up thomas cost in column B3 and then multiplies with thomas allocation in B9

Emp cost	Apr
peter	80000
thomas	90000
jack	65000

<colgroup><col><col></colgroup><tbody>
</tbody>

Type	bike	car
peter	80%	20%
jack	40%	60%
thomas	10%	90%

<colgroup><col><col span="2"></colgroup><tbody>
</tbody>

Thanks again

In above your data starts in A1

Change the layout of "Fixed Cost Allocation" to

Fixed Cost allocation
Type bike car
peter
thomas
jack

Next to "Bike Cost" enter
=SUMPRODUCT(B3:B5,$B$14:$B$16)+(B9*B20)
Next to "Car Cost" enter
=SUMPRODUCT(B3:B5,$C$14:$C$16)+(B10*B21)

Drag both across columns

 

[gaz_chops]

Yes, I was trying to keep it simple.