Help with Optimization and Decision Support Modeling for Business HW2

profileRedhand
M3-2_PracticeProblems_Ch5_Solution.xlsx
Home>Business & Finance homework help>Operations Management homework help>Help with Optimization and Decision Support Modeling for Business HW2

Prob1

Big M Company Distribution Problem
Shipping Cost
(per Lathe) Customer 1 Customer 2 Customer 3 Range Name Cells
Factory 1 $700 $900 $800 OrderSize C15:E15
Factory 2 $800 $900 $700 Output H11:H12
ShippingCost C5:E6
Total TotalCost H15
Shipped TotalShippedOut F11:F12
Units Shipped Customer 1 Customer 2 Customer 3 Out Output TotalToCustomer C13:E13
Factory 1 10 2 0 12 = 12 UnitsShipped C11:E12
Factory 2 0 6 9 15 = 15
Total To Customer 10 8 9
= = = Total Cost
Order Size 10 8 9 $20,500

Prob1 Sens. Report

Variable Cells
Final Reduced Objective Allowable Allowable
Cell Name Value Cost Coefficient Increase Decrease
$C$11 Factory 1 Customer 1 10 0 700.0000000007 100.0000000208 1E+30
$D$11 Factory 1 Customer 2 2 0 900.0000000015 100.000000015 100.0000000206
$E$11 Factory 1 Customer 3 0 100.000000015 800.0000000175 1E+30 100.000000015
$C$12 Factory 2 Customer 1 0 100.0000000206 800.0000000175 1E+30 100.0000000206
$D$12 Factory 2 Customer 2 6 0 900.0000000015 100.0000000209 100.0000000153
$E$12 Factory 2 Customer 3 9 0 700.0000000011 100.0000000152 1E+30
Constraints
Final Shadow Constraint Allowable Allowable
Cell Name Value Price R.H. Side Increase Decrease
$F$11 Factory 1 Out 12 0 12 0 1E+30
$F$12 Factory 2 Out 15 0 15 2 0
$C$13 Total To Customer Customer 1 10 700.0000000011 10 0 10
$D$13 Total To Customer Customer 2 8 900.0000000036 8 0 2
$E$13 Total To Customer Customer 3 9 700.000000004 9 0 2

Prob3

Super Grain Corp. Advertising-Mix Problem
TV Spots Magazine Ads SS Ads Range Name Cells
Exposures per Ad 1,300 600 500 BudgetAvailable H7:H8
(thousands) BudgetSpent F7:F8
Cost per Ad ($thousands) Budget Spent Budget Available CostPerAd C7:E8
Ad Budget 300 150 100 3,775 <= 4,000 CouponRedemptionPerAd C15:E15
Planning Budget 90 30 40 1,000 <= 1,000 ExposuresPerAd C4:E4
MaxTVSpots C21
Number Reached per Ad (millions) Total Reached Minimum Acceptable MinimumAcceptable H11:H12
Young Children 1.2 0.1 0 5 >= 5 NumberOfAds C19:E19
Parents of Young Children 0.5 0.2 0.2 5.85 >= 5 NumberReachedPerAd C11:E12
RequiredAmount H15
TV Spots Magazine Ads SS Ads Total Redeemed Required Amount TotalExposures H19
Coupon Redemption per Ad 0 40 120 1,490 = 1,490 TotalReached F11:F12
($thousands) TotalRedeemed F15
Total Exposures TVSpots C19
TV Spots Magazine Ads SS Ads (thousands)
Number of Ads 3 14 7.75 16,175
<=
Maximum TV Spots 5

Prob3 Sens. Report

Variable Cells
Final Reduced Objective Allowable Allowable
Cell Name Value Cost Coefficient Increase Decrease
$C$19 TVSpots 3 0 1299.9999999981 1040.0000000008 1E+30
$D$19 Number of Ads Magazine Ads 14 0 600.0000000001 1E+30 192.59
$E$19 Number of Ads SS Ads 7.75 0 500.0000000009 577.78 1E+30
Constraints
Final Shadow Constraint Allowable Allowable
Cell Name Value Price R.H. Side Increase Decrease
$F$7 Ad Budget Budget Spent 3,775 0 4000 1E+30 225
$F$8 Planning Budget Budget Spent 1,000 35 1000 22.5 85.0000000002
$F$15 TotalRedeemed 1,490 -8 1490 385.0000000004 90.0000000001
$F$11 Young Children Total Reached 5 -1575.76 5 1.32 0.45
$F$12 Parents of Young Children Total Reached 5.85 0 5 0.85 1E+30

Prob4

Wyndor Glass Co. Product-Mix Problem
Doors Windows Range Name Cells
Unit Profit $300 $500 DoorsProduced C12
Hours Hours HoursAvailable G7:G9
Hours Used Per Unit Produced Used Available HoursUsed E7:E9
Plant 1 1 0 1.3846153846 <= 4 HoursUsedPerUnitProduced C7:D9
Plant 2 0 2 12 <= 12 TotalProfit G12
Plant 3 3.25 2.25 18 <= 18 UnitProfit C4:D4
UnitProfitPerDoor C4
Doors Windows Total Profit UnitProfitPerWindow D4
Units Produced 1.385 6 $3,415 UnitsProduced C12:D12
WindowsProduced D12