Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,023
    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
    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
    Attached Files Attached Files

  2. #2
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,520
    Thanks
    32
    Thanked 178 Times in 173 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

  3. The Following User Says Thank You to zeddy For This Useful Post:

    HowardC (2012-05-07)

  4. #3
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,023
    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

  5. #4
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,520
    Thanks
    32
    Thanked 178 Times in 173 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

  6. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 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

  7. #6
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,520
    Thanks
    32
    Thanked 178 Times in 173 Posts
    Hi Steve

    Checked your figures - you are spot on!

    zeddy

  8. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 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.

  9. #8
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,023
    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

  10. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 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

  11. The Following User Says Thank You to sdckapr For This Useful Post:

    HowardC (2012-05-09)

  12. #10
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,023
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •