Results 1 to 10 of 10
Thread: What if Analysis One way table

20120507, 12:10 #1
 Join Date
 Feb 2008
 Posts
 975
 Thanks
 63
 Thanked 2 Times in 2 Posts
What if Analysis One way table
I have a spreadsheet containing the following
HTML Code:Part Weight 14 Material Cost 1.25 Production volume 250,000 Total Cost 21.36
Attached please find sample data. It would be appreciated if someone could assist me

Subscribe to our Windows Secrets Newsletter  It's Free!
Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!
+ Get this BONUS — free!
Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual  Subscribe and download Chapter 1 for free!

20120507, 14:40 #2
 Join Date
 Mar 2002
 Location
 Newcastle, UK
 Posts
 1,474
 Thanks
 22
 Thanked 167 Times in 163 Posts
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

The Following User Says Thank You to zeddy For This Useful Post:
HowardC (20120507)

20120507, 21:37 #3
 Join Date
 Feb 2008
 Posts
 975
 Thanks
 63
 Thanked 2 Times in 2 Posts
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

20120508, 11:14 #4
 Join Date
 Mar 2002
 Location
 Newcastle, UK
 Posts
 1,474
 Thanks
 22
 Thanked 167 Times in 163 Posts
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

20120508, 12:04 #5
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,196
 Thanks
 14
 Thanked 326 Times in 320 Posts
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

20120508, 12:36 #6
 Join Date
 Mar 2002
 Location
 Newcastle, UK
 Posts
 1,474
 Thanks
 22
 Thanked 167 Times in 163 Posts
Hi Steve
Checked your figures  you are spot on!
zeddy

20120508, 14:49 #7
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,196
 Thanks
 14
 Thanked 326 Times in 320 Posts
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.

20120508, 20:29 #8
 Join Date
 Feb 2008
 Posts
 975
 Thanks
 63
 Thanked 2 Times in 2 Posts
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

20120509, 06:50 #9
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,196
 Thanks
 14
 Thanked 326 Times in 320 Posts
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

The Following User Says Thank You to sdckapr For This Useful Post:
HowardC (20120509)

20120509, 12:03 #10
 Join Date
 Feb 2008
 Posts
 975
 Thanks
 63
 Thanked 2 Times in 2 Posts
Hi Steve
Thanks for the time taken to explain the concept. It makes far better sense now
Regards
Howard