Scribd Logo
Upload

Welcome to Scribd!

  • 67 views

    Assignment 3.1

    Uploaded by

    anitscit
    This document contains a sensitivity report for a spreadsheet model that optimizes production levels of two vehicle models to maximize profit. It evaluates multiple scenarios, adjusting production levels and labor hours to determine an optimal solution. The best performing scenario increases production of both models, increases labor hours and generates $376,000 more in profit compared to the original solution.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as XLSX, PDF, TXT or read online from Scribd

    Assignment 3.1

    Uploaded by

    anitscit
    67 views11 pages
    This document contains a sensitivity report for a spreadsheet model that optimizes production levels of two vehicle models to maximize profit. It evaluates multiple scenarios, adjusting production levels and labor hours to determine an optimal solution. The best performing scenario increases production of both models, increases labor hours and generates $376,000 more in profit compared to the original solution.

    Copyright

    © Attribution Non-Commercial (BY-NC)

    Available Formats

    XLSX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document contains a sensitivity report for a spreadsheet model that optimizes production levels of two vehicle models to maximize profit. It evaluates multiple scenarios, adjusting production levels and labor hours to determine an optimal solution. The best performing scenario increases production of both models, increases labor hours and generates $376,000 more in profit compared to the original solution.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as XLSX, PDF, TXT or read online from Scribd
    Download as xlsx, pdf, or txt
    67 views11 pages

    Assignment 3.1

    Uploaded by

    anitscit
    This document contains a sensitivity report for a spreadsheet model that optimizes production levels of two vehicle models to maximize profit. It evaluates multiple scenarios, adjusting production levels and labor hours to determine an optimal solution. The best performing scenario increases production of both models, increases labor hours and generates $376,000 more in profit compared to the original solution.

    Copyright:

    Attribution Non-Commercial (BY-NC)
    Download as XLSX, PDF, TXT or read online from Scribd
    Download as xlsx, pdf, or txt
    You are on page 1of 11

    Microsoft Excel 12.

    0 Sensitivity Report
    Worksheet: [Assignment 3.1.xlsx]3.1 a
    Report Created: 2/27/2011 11:30:30 AM

    Adjustable Cells
    Final Reduced Objective Allowable Allowable
    Cell Name Value Cost Coefficient Increase Decrease
    $B$13 Units Family Thrillseeker 3800 0 3600 7200 514.28571427
    $C$13 Units Classy Cruiser 2400 0 5400 900 3600

    Constraints
    Final Shadow Constraint Allowable Allowable
    Cell Name Value Price R.H. Side Increase Decrease
    $E$10 Doors Total 20000 180 20000 12000 5500
    $E$11 Labour hour Total 48000 480 48000 8250 18000
    Doors profit Labour hour

    Family Thrillseeker 4 3600


    Classy Cruiser 2 5400

    Family Thrillseeker Classy Cruiser Total


    Doors 4 2 20000 <= 20000
    Labour hour 6 10.5 48000 <= 48000
    Profit 3600 5400
    Units 3800.00000020452 2400
    <=
    3500
    Profit individual 13680000.0007363 12960000
    Profit 26640000.0006277
    Doors profit Labour hour

    Family Thrillseeker 4 3600


    Classy Cruiser 2 5400

    Family Thrillseeker Classy Cruiser Total


    Doors 4 2 20000 <= 20000
    Labour hour 6 10.5 48000 <= 48000
    Profit 3600 5400
    Units 3799.99999999989 2400
    <=
    4200
    Profit individual 13679999.9999996 12960000
    Profit 26640000.0000355

    The campaign should not be taken as profit remains same so no point in shelling out $500000 extra
    Doors profit Labour hour

    Family Thrillseeker 4 3600


    Classy Cruiser 2 5400

    Family Thrillseeker Classy Cruiser Total


    Doors 4 2 20000 <= 20000
    Labour hour 6 10.5 56250 <= 60000
    Profit 3600 5400
    Units 3250.00000000063 3500
    <=
    3500
    Profit individual 11700000.0000023 18900000
    Profit 30600000.0000023
    Doors profit Labour hour

    Family Thrillseeker 4 3600


    Classy Cruiser 2 5400

    Family Thrillseeker Classy Cruiser Total


    Doors 4 2 20000 <= 20000
    Labour hour 6 10.5 56250 <= 60000
    Profit 3600 5400
    Units 3250.00000000063 3500
    <=
    3500
    Profit individual 11700000.0000023 18900000
    Profit 30600000.0000023

    Previous profit 26640000

    Extra amt to labour 3960000.00000228


    Doors profit Labour hour

    Family Thrillseeker 4 3600


    Classy Cruiser 2 5400

    Family Thrillseeker Classy Cruiser Total


    Doors 4 2 20000 <= 20000
    Labour hour 6 10.5 60000 <= 60000
    Profit 3600 5400
    Units 3000.0000000024 4000
    <=
    4200
    Profit individual 10800000.0000087 21100000
    Profit 32400000.0000366

    Previous profit 26640000

    Extra amt to labour 5760000.0000366


    In 'e' part
    Profit Extra amt to labour Net profit
    31900000.0000366 1600000 30300000

    In 'a' part 26640000.0006277

    Solution or decision taken in 'e' part is good as compared to 'a' part.


    As it if fetching an extra profit of 3659999.9994089
    Doors profit Labour hour

    Family Thrillseeker 4 3600


    Classy Cruiser 2 5400

    Family Thrillseeker Classy Cruiser Total


    Doors 4 2 14500 <= 20000
    Labour hour 6 10.5 48000 <= 48000
    Profit 2800 5400
    Units 1875.00000000828 3500
    <=
    3500
    Profit individual 5250000.00002318 18900000
    Profit 24150000.0000232
    Doors profit Labour hour

    Family Thrillseeker 4 3600


    Classy Cruiser 2 5400

    Family Thrillseeker Classy Cruiser Total


    Doors 4 2 13000 <= 20000
    Labour hour 7.5 10.5 48000 <= 48000
    Profit 2800 5400
    Units 1500.00000000307 3500
    <=
    3500
    Profit individual 4200000.0000086 18900000
    Profit 23100000.0000086
    Doors profit Labour hour

    Family Thrillseeker 4 3600


    Classy Cruiser 2 5400

    Family Thrillseeker Classy Cruiser Total


    Doors 4 2 14500 <= 20000
    Labour hour 6 10.5 48000 <= 48000
    Profit 2800 5400
    Units 1875.00000000828 3500
    <=
    3500
    Profit individual 5250000.00002318 18900000
    Profit 24150000.0000232
    Profit in 'a' part 26640000.0006277
    Profit difference from 'a' part 2490000.0006

    Exceptable diff 2000000


    Doors profit Labour hour

    Family Thrillseeker 4 3600


    Classy Cruiser 2 5400

    Family Thrillseeker Classy Cruiser Total


    Doors 4 2 16880 <= 20000
    Labour hour 7.5 10.5 60000 <= 60000
    Profit 2800 5400
    Units 2120.00000000368 4200
    <=
    4200
    Profit individual 5936000.00001029 22180000
    Profit 27016000.0000103

    Previous profit 'a' part 26640000

    Profit difference 376000.000010289

    After applying new considerations in 'f', 'g' and 'h' part, we et more profit from 'a' part i.e. $376000
    So we can go by applying all these conditions

    You might also like