Question

Assignment requirements: Hand in both a summary sheet with your answers, and the needed computer backup work.    Put your name on all of the work; the computer output should have your name printed by the computer. (Include both a printout of your spreadsheet and a printout of the ANSWER report generated by SOLVER. Also if you could upload the excel sheet as well will be very appreciated. thanks

Problem 4 Sailco Corporation must determine how many sailboats to produce during each of the next four quarters. The demand during each of the next four quarters is in the table below. Quarter 1 Quarter 2 Quarter 3 Quarter 4 Demand 35 65 70 30 At the beginning of the first quarter, Sailco has an inventory of 12 sailboats. At the beginning of each quarter, Sailco must decide how many sailboats to produce during the quarter. For simplicity, we assume that sailboats manufactured in that quarter can be used to meet demand for that quarter. During each quarter, Sailco can produce up to 40 sailboats with regular-time labor at a total cost of $400 per sailboat. By having employees work overtime during a quarter, Sailco can produce additional sailboats with overtime labor at $450 per sailboat. At the end of each quarter (after production has occurred and the current quarter's demand has been satisfied), a holding cost of $20 per sailboat is incurred. Determine a production schedule to minimize the sum of production and inventory holding costs during the next four quarters a. Formulate a linear programming model for this problem (A Spreadsheet approach is easier.) b. Solve the model using SOLVER. c. Suppose the holding cost is not $20 but some other number. For what levels of holding cost per quarter is the optimal solution in "b" still optimal?

Answer 1

a)

Linear Programming model in spreadsheet is following:

C fx C =SUM(H4:H8) D 2 3 65 70 E Solver Parameters 1 2 3 4 30 F G H Total Unit CostCategory Cost 200 H 10 @ A B Quarter 1 Demand 35 Production schedule Regular Overtime Inventory Beginning 12 Ending | -23 o 0 400 450 Set Objective: SH$10 To: O Max Min value of: 0 By Changing Variable Cells: SBS4:SES5 Subject to the Constraints: $B$4:SE$4 < = 40 SB$8:SE$8 >= 0 6 Add -23 -88 -88 -158 -158 -188 Change -457 20 -9,140 Delete 8 9 10 11 12 Total Cost = ---9,140 Reset All Make Unconstrained variables Non-Negative 13 Select a Solving Method Simplex LP Options Solving Method Select the IPOPT Nonlinear engine for Solver Problems that are smooth nonlinear.Select the LP Simplex engine for linear Solver Problems. 17 18 | Solve Close 19

EXCEL FORMULAS:

Quarter	1	2	3	4	Total	Unit Cost	Category Cost
Demand	35	65	70	30	=SUM(B2:E2)
Production schedule
Regular					=SUM(B4:E4)	400	=F4*G4
Overtime					=SUM(B5:E5)	450	=F5*G5
Inventory
Beginning	12	=B8	=C8	=D8
Ending	=-B2+B4+B5+B7	=-C2+C4+C5+C7	=-D2+D4+D5+D7	=-E2+E4+E5+E7	=SUM(B8:E8)	20	=F8*G8
						Total Cost =	=SUM(H4:H8)

b)

Click Solve button on Solver Parameters window to generate the optimal solution

C H Category Cost I J K L M N S Solver Results Solver found a solution. All constraints and optimality conditions are satisfied. H10 @ fx =SUM(H4:48) A A B C D E F G 1 Quarter 1 2 3 4 Total Unit Cost 2 Demand 35 65 70 30 200 3 Production schedule 4. Regular 40 40 40 30 150 400 Overtime 08 30038 450 6 Inventory Beginning 12 17 0 0 8 Ending 17 0 0 0 17 20 9 Total Cost = Reports 60,000 17,100 Keep Solver Solution Answer Sensitivity Limits 7 O Restore Original Values 340 Return to Solver Parameters Dialog 10 77,440 OK Cancel 12 Solver found a solution. All constraints and optimality conditions are satisfied. When Simplex LP is used, this means Solver has found a global optimal solution.

Optimal production schedule is following:

Quarter	1	2	3	4
Demand	35	65	70	30
Production schedule
Regular	40	40	40	30
Overtime	0	8	30	0

Total Cost = $ 77,440

c)

On the Solver Results window, check Sensitivity to generate the sensitivity report

@ fx C =SUM(H4:H8) D 2 3 65 70 E G Unit Cost H Category Cost I a Solver Result J K L M F Total 200 N 1 35 4 30 H10 A A B 1 Quarter 2 Demand 3 Production schedule Regular 5 Overtime 0 6 Inventory Beginning Ending 4 40 40 40 30 30 0 150 38 400 450 60,000 17,100 8 Solver found a solution. All constraints and optimality conditions are satisfied. Reports Answer O Keep Solver Solution Sensitivity Limits O Restore Original Values 1. 12 17 0 0 17 00 017 20 340 Return to Solver Parameters Dialog Total Cost = 77,440 OK Cancel Reports Create the type of report that you specify, and place each report on a separate sheet in the workbook 18

Sensitivity report,

Variable Cells Final Reduced Value Cost 40 40 0 0 30 20 50 Cell Name $B$4 Regular $C$4 Regular $D$4 Regular $E$4 Regular $B$5 Overtime $C$5 Overtime $D$5 Overtime $E$5 Overtime 40 Objective Allowable Allowable Coefficient Increase Decrease 480 301E+30 460 1E+30 440 50 1E+30 420 50 420 5301E+30 20 510 20 490 20 470 1E+30 0 30 50 Constraints Final Cell Name Value $B$8 Ending 17 $C$8 Ending 0 $D$8 Ending 0 $E$8 Ending0 Shadow Constraint Allowable Allowable Price R.H. Side Increase Decrease 0 0 17 1E+30 2 0 0 30 8 70 30 4200 10 0

c) Refer sensitivity report, reduced cost of holding cost is 30. So, the upper limit of holding cost = 20+30 = $ 50, beyond which the optimal solution will change.

If holding cost is >= $ 50, then the optimal solution will change.