Question

SMM is a Saskatoon based producer of emergency vehicles. SMM equips its vehicles with medical equipment based on the received orders. The following table presents SMM’s orders of two of its popular products for the next five months. SMM has five model A, and 5 model B vehicles in its inventory in the beginning of month 1 and is planning to have five extra vehicles at the end of the planning horizon to keep its production plan flexible.

	Model A	Model B
Initial inventory	5	5
Month 1	12	11
Month 2	5	4
Month 3	7	8
Month 4	7	13
Month 5	9	5
Desired inventory at the end of month 5	5	5

SMM can produce six model A and five model B vehicles each month. If required, SMM can produce five more model A and four more model B vehicles per month in overtime. The production costs and inventory holding costs are given below. The costs are in thousands of dollars.

	Model A	Model B
Production cost (regular working hours)	5	8
Production cost (overtime)	7	11
Inventory holding cost (per unit, per month)	3	3

Based on the given data, solve a model using Excel Solver to find the best production plan.

Answer 1

I have calculated Inventory holding basis the ending inventory. In case same needs to be calculated basis average inventory, kindly let me know.

Below is the screenshot of the production schedule -

S 2020 - Excel File Home Insert Page Layout Formulas Data Review View Help Tell me what you wan my Cut LE Copy Format Painter Calibri V11 VA A BIU - B A Fonto === === aby Wrap Text Merge & Center - Paste General $ - % Num Clipboard Alignment A1 x fc Month G Total H Cost Per Unit Total Cost 5 B C D E F 1 Month Month 1 Month 2 Month 3 Month 4 Month 5 2 Demand Model A 12 5 7 7 4 Model B 11 4 8 13 5 Regular Production 6 Model A Model B 8 Overtime Production 9 Model A Model B 11 Inventory Model A Beginning 5 -7 -12 -26 Ending -7 -12 -19 -26 -35 14 Inventory Model B Beginning 5 -6 -10 -18 -31 Ending -6 -10 -18 -31 -36 17 -19 13 -297 -101 -303 -600 Overall Cost

Below is the screenshot of the formula applied -

2020 - Excel Abhishek Rai ESSE File Home Insert Page Layout Formulas Data Review View Help * Insert AutoSum Recently Financial Logical Text Date & Lookup & Math & More Name Function - - Time Reference Trigº Functions Manager Function Library fx F ? A .l le - Calculate Now Tell me what you want to do Define Name Fi Use in Formula Create from Selection Defined Names - Trace Precedents 5 Show Formulas Trace Dependents ! Error Checking Remove Arrows - Evaluate Formula Formula Auditing Watch Window Calculation Calculate Sheet Options Calculation B5 A Month 1 month 3 month 2 Month 4 months I Total Month 2 Month 3 Month 4 Month 5 Total Cost Per Unit Total Cost =SUM(B6:F6) =SUM(B7:F7) =H6*G6 =H7*G7 B 1 Month Month 1 2 Demand Model A12 Model B 11 5 Regular Production 6 Model A Model B 8 Overtime Production 9 Model A 10 Model B 11 Inventory Model A Beginning 5 13 Ending =B12+B9+B6-B3 14 Inventory Model B Beginning 5 Ending =B15+B10+B7-B4 =SUM(B9:F9) =SUM(B10:F10) =H9*G9 =H10*G10 =C13 ED13 =B13 =C12+C9+C6-C3 =D12+D9+D6-D3 =E12+E9+E6-E3 =F12+F9+F6-F3 =SUM(B13:F13) =H13*G13 =B16 =C15+C10+C7-C4 =C16 =D15+D10+D7-D4 =D16 =E15+E10+E7-E4 =E16 =F15+F10+F7-F4 ESUM(B16:F16) 13 =H16*G16 =SUM(16:116) Overall Cost

Below is the screenshot of the solver -

2020 - Excel ES File Home Solver Parameters Insert Page Layout Formulas Data Review View Help Tell me what you wan Set Objective: SI517 Get External Data Show Queries From Table New Query - Le Recent Sources Get & Transform Connections Properties Refresh All - Edit Links Connections Stocks Geography = Filter Sort To: O Max Min O Value of: Data Types Sort & Filter By Changing Variable Cells: $B$6:$F$7,$B$9:$F$10 x fc Month G Total H Cost Per Unit Total Cost Add 3 Change Subject to the Constraints: $B$10:SF$10 <= 4 $B$13:SF$13 >= 0 $B$16:$F$16 >= 0 SBS6:SFS6 <= 6 SBS6:SFS7 = integer SB$7:$F$7 <= 5 SBS9: SFS10 = integer $B$9:$F$9 <= 5 SFS13 = 5 F$16 = 5 Delete 0 5 Reset All B C D E F 1 Month Month 1 Month 2 Month 3 Month 4 Month 5 2 Demand Model A 12 5 7 7 4 Model B 11 4 8 13 5 Regular Production 6 Model A Model B 8 Overtime Production 9 Model A 10 Model B 11 Inventory Model A Beginning 5 -7 -12 -26 Ending -7 -12 -19 -26 -35 14 Inventory Model B Beginning 5 -6 -10 -18 -31 Ending -6 -10 -18 -31 -36 Load/Save Make Unconstrained variables Non-Negative Select a Solving Simplex LP Method: Options -19 -297 Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth. -101 -303 -600 Overall Cost Help I solve Close

Below is the screenshot of the result-

2020 - Excel Sao File Home Insert Page Layout Formulas Data Show Queries Connections From Table Properties Get External New Refresh Data Query - Le Recent Sources All Edit Links Get & Transform Connections Review View Help Th E Stocks Geography Tell me what you want 21 Y and Sort Filter Data Types Sort & Filter X for Month G Total H Cost Per Unit Total Cost 3 6 6295 145 200 | А B C D E F 1 Month Month 1 Month 2 Month 3 Month 4 Month 5 2 Demand Model A 12 5 7 7 4 Model B 11 4 | 8 | 13 5 Regular Production 6 Model A 5 Model B . 5 5 8 Overtime Production Model A 1 Model B 1 11 Inventory Model A Beginning Beginning 5 0 0 0 Ending 14 Inventory Model B 15 Beginning) Ending 0 4 9 10 16 77 176 13 0 wo IN 5 5 45 667 Overall Cost