What if Analysis One way table

**HowardC** · 2012-05-07 12:10

I have a spreadsheet containing the following

HTML Code:

        Part Weight
   14       Material Cost    1.25
       Production volume    250,000       
   
       Total Cost
   21.36

I have tried to set up a one one table to compute what the total cost would be based on various production volumes, but cannot get it to work

Attached please find sample data. It would be appreciated if someone could assist me

**zeddy** · 2012-05-07 14:40

Hi Howard

I don't understand the question really.
If you are saying that the Total Cost is A, for a production volume of B
..then for any other production volume C, the cost would be (A/B)*C
so in cell [G2] of your file, the formula would be
=($B$5/$B$3)*F2

..from your file, I don't see where Material Cost or Part Weight is relevant.

zeddy

**HowardC** · 2012-05-07 21:37

Hi Zeddy

Thanks for the help. I got this example from this Website http://msl1.mit.edu/rdn/d_table.pdf. It did not make much sense to me as well

Regards

Howard

**zeddy** · 2012-05-08 11:14

Hi Howard

The source example explains that the 'Total Cost is dependent on the other three parameters (Part weight, Production volume, and Material Cost) through a series of interrelationships (i.e. formulas) embedded in the spreadsheet'.
But it doesn't tell you what these interrelationships (i.e. formulas) are.
If it did, then you could use the Excel functions as described for sensitivity analysis.
So you can't do a sensitivity analysis unless you have the relationships between the variables defined.

zeddy

**sdckapr** · 2012-05-08 12:04

What is indicated in the pdf link, but not detailed is that the value in Cell B6 is not a number but a formula. Based on the data it calculates, B6 has a formula that seems to be:
=965077/B3+B1*B2

It seems that you are calculating a cost per each part. You have the material cost (B1*B2 = weight *Material Cost) but another factor based on the volume: the more volume produced the lower the variable production cost that get added to each part (economy of scale)

Steve

**zeddy** · 2012-05-08 12:36

Hi Steve

Checked your figures - you are spot on!

zeddy

**sdckapr** · 2012-05-08 14:49

zeddy,
Thanks for verifying my calculations.

Steve
PS. I thought it a little weird not to provide this data in the article, though perhaps their spreadsheet was more simulation and I just used their numbers (from the simulation) to create a model equation. I have no idea of what the basis of the fixed cost of 965,077 to run the plant (for whatever time frame [it seems an odd choice of value for an example, why not just use 1,000,000? --Perhaps it is actually 1,000,000 and has some random factor included...]. I don't expect any updates since the article was written for Lotus123 almost 15 years ago) whether it is 1 part or more (and at what prodn volume the fixed costs would have to increase), but that is what seems to me to come from the numbers generated.

**HowardC** · 2012-05-08 20:29

Hi Steve & Zeddy

Thanks for your input. I was trying to establish the logic, but using the formula =965077/B3+B1*B2 in B6 gives me the desired results

Regards

Howard

**sdckapr** · 2012-05-09 06:50

The logic I see is that the part weight is 14 (no units, so assume kg). The material cost is 1.25 and I will assume $1.25 / kg. Therefore the total material cost for each part is (14 kg)*($1.25/kg) = $17.50. But in addition to the material cost, there is a cost to manufacture each part. From the data in the article it seems to cost 965,077 to run the plant for a given production run which I will assume to be dollars / day. If you wake 250,000 parts / day, your manufacturing cost per part each shares in that manufacturing cost so each part needed ($965,077/day)/(250,000 parts/day) = $3.86/part. The total cost/part is the share of mfg cost + the material cost ($3.86+17.50) = $21.36

If less parts are made (eg 100,000) in the day, you still spend the $965,077 (cost of overhead, salaries, etc) but that means each part must share in that so each part is charged $9.65 for manufacturing and with the $17.50 in the material cost, the total is $27.15.

The simulation can help then look at what price they must charge to be profitable given a variable production amount. ONe must be able to not only recover the material cost per part, but the fixed costs per day.

Steve

**HowardC** · 2012-05-09 12:03

Hi Steve

Thanks for the time taken to explain the concept. It makes far better sense now

Regards

Howard

Thread: What if Analysis One way table

Thread Tools