Scribd Logo
Upload

Welcome to Scribd!

  • 28 views

    Group Work Practice

    Uploaded by

    Anuj Popli
    This sensitivity report summarizes the adjustable cells, constraints, and other key inputs and outputs of a production optimization model in Microsoft Excel. It shows the optimal production quantities and costs across 3 production facilities to meet product demands at minimum cost while satisfying material requirements and production capacity limits. Cash flow projections are provided that factor in production costs, revenues, loans taken and repaid, and interest payments over a 12 month period. Finally, interest rates and bad debt proportions are specified for different loan categories.

    Copyright:

    © All Rights Reserved
    Download as XLSX, PDF, TXT or read online from Scribd

    Group Work Practice

    Uploaded by

    Anuj Popli
    28 views7 pages
    This sensitivity report summarizes the adjustable cells, constraints, and other key inputs and outputs of a production optimization model in Microsoft Excel. It shows the optimal production quantities and costs across 3 production facilities to meet product demands at minimum cost while satisfying material requirements and production capacity limits. Cash flow projections are provided that factor in production costs, revenues, loans taken and repaid, and interest payments over a 12 month period. Finally, interest rates and bad debt proportions are specified for different loan categories.

    Original Title

    Group work practice.xlsx

    Copyright

    © © All Rights Reserved

    Available Formats

    XLSX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This sensitivity report summarizes the adjustable cells, constraints, and other key inputs and outputs of a production optimization model in Microsoft Excel. It shows the optimal production quantities and costs across 3 production facilities to meet product demands at minimum cost while satisfying material requirements and production capacity limits. Cash flow projections are provided that factor in production costs, revenues, loans taken and repaid, and interest payments over a 12 month period. Finally, interest rates and bad debt proportions are specified for different loan categories.

    Copyright:

    © All Rights Reserved
    Download as XLSX, PDF, TXT or read online from Scribd
    Download as xlsx, pdf, or txt
    28 views7 pages

    Group Work Practice

    Uploaded by

    Anuj Popli
    This sensitivity report summarizes the adjustable cells, constraints, and other key inputs and outputs of a production optimization model in Microsoft Excel. It shows the optimal production quantities and costs across 3 production facilities to meet product demands at minimum cost while satisfying material requirements and production capacity limits. Cash flow projections are provided that factor in production costs, revenues, loans taken and repaid, and interest payments over a 12 month period. Finally, interest rates and bad debt proportions are specified for different loan categories.

    Copyright:

    © All Rights Reserved
    Download as XLSX, PDF, TXT or read online from Scribd
    Download as xlsx, pdf, or txt
    You are on page 1of 7

    Microsoft Excel 12.

    0 Sensitivity Report
    Worksheet: [Book2]Sheet1
    Report Created: 23-11-2016 21:48:02

    Adjustable Cells
    Final Reduced Objective Allowable Allowable
    Cell Name Value Cost Coefficient Increase Decrease
    $C$6 qty Chocolate 0 -0.375 10 0.375 1E+030
    $D$6 qty Vanilla 300 0 9 0.5 0.125
    $E$6 qty Banana 75 0 9.5 0.214285714 0.5

    Constraints
    Final Shadow Constraint Allowable Allowable
    Cell Name Value Price R.H. Side Increase Decrease
    $G$2 Milk requirement 180 0 200 1E+030 20
    $G$3 Sugar requirement 150 18.75 150 10 30
    $G$4 Cream requirement 60 10 60 15 3.75
    Chocolate Vanilla Banana Available requirement
    Milk 0.45 0.5 0.4 200 180
    Sugar 0.5 0.4 0.4 150 150
    Cream 0.1 0.15 0.2 57 60
    Unit profit 10 9 9.5 3412.5
    qty 0 300 75
    ProductionCapacity
    F1 5 300
    F2 6 600
    F3 4 500

    C1 C2 C3 C4 Capacity production cost


    F1 3 4 3 5 300 5
    F2 4 2 3 3 600 6
    F3 1 3 2 4 500 4
    Sales Price 13 14 11 12
    Demand 200 350 450 250

    C1 C2 C3 C4
    F1 0 0 150 0 150
    F2 0 350 0 250 600
    F3 200 0 300 0 500
    200 350 450 250

    total cost 2700 6350 9050


    Total reve 15450
    6400
    production cost
    beginningcashflow long term loan takeninterest paid on long term loaLong term loan repaid
    January 6500 -12000 30234.6413795537
    February 24833.58 -10000 -302.3464137955
    March 14589.36 -8000 -302.3464137955
    April 6312.16 -10000 -302.3464137955
    May 5020 -4000 -302.3464137955
    June 5020 5000 -302.3464137955
    July 5020 -7000 -302.3464137955
    August 5020 -2000 -302.3464137955
    September 5020 15000 -302.3464137955
    October 5020 12000 -302.3464137955
    November 12302.35 -7000 -302.3464137955
    December 5020 45000 -302.3464137955
    January 49916.52 -302.3464137955 -30234.6413795537
    short term loan taken interest paid on shoshort termcash positiinterest eafinal cash
    0 24734.64 98.93857 24833.58
    0 0 0 14531.23 58 14589
    0 0 0 6287.012 25 6312
    8990.1863139832 0 0 5000 20 5020
    13407.3855224884 -134.8527947097 -8990.19 5000 20 5020
    8890.8427191213 -201.1107828373 -13407.4 5000 20 5020
    16306.5517737037 -133.3626407868 -8890.84 5000 20 5020
    18833.4964641047 -244.5982766056 -16306.6 5000 20 5020
    4398.3453248619 -282.5024469616 -18833.5 5000 20 5020
    0 -65.9751798729 -4398.35 12253.33 49 12302
    0 0 0 5000 20 5020
    0 0 0 49717.65 199 49917
    0 0 19379.54 0

    total interest paid 4388.213


    Interest % Bad Debt proportionnet interesbad debt
    Personal 0.14 0.015 30 4.2 0.45
    Business 0.13 0.0125 0 0 0
    Car 0.12 0.012 15 1.8 0.18
    Education 0.11 0.01 45 4.95 0.45
    Agriculture 0.1 0.013 0 0 0
    Home 0.08 0.009 10 0.8 0.09
    100 11.75 1.17

    Social Sect 55
    car 15
    home 10
    personal + 30
    bad debt 1.17

    You might also like