Question

please solve excel solver and share or send in excel file form please

MULTI-PERIOD PRODUCTION-INVENTORY MODEL Example 2.4.3 on page 64 of Taha's book Acme Manufacturing Company has contracted to deliver home windows over the next 6 months. نما 4 Month Demand Production Cost per window 100 $50 250 $45 190 $55 140 $48 5 220 $52 6 110 $50 To take advantage of the fluctuations in manufacturing cost, Acme may select to produce more than needed in a given month and hold the excess units for delivery in later months. This, however, will incur inventory holding costs at the rate of $8 per window per month incurred on the end-of-month inventory level. At the beginning of the first month, there are no units available in stock. 24

Answer 1

Set up Excel model as follows:

A B C D E 1 Month Demand Production cost/window Production Inventory 2 0 3 1 100 50 =E2-B3+D3 4 2 250 45 =E3-B4+D4 5 3 190 55 =E4-B5+D5 6 4 140 48 =E5-B6+D6 7 5 220 52 =E6-B7+D7 8 6 110 50 =E7-B8+D8 9 10 11 Total Cost =SUMPRODUCT(D3:08,03:08)+SUM(E3:E8)*8 12 13 14

Set up Solver Parameters as follows:

Solver Parameters Х Set Objective: $E$11 To: Max Min Value Of: 0 By Changing Variable Cells: $D$3:$D$8 Subject to the Constraints: $E$3:$E$8 >= 0 > Add Change Delete Reset All Load/Save Make Unconstrained variables Non-Negative Select a Solving Method: Simplex LP Options 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. Help Solve Close

Click Solve to generate the solution.

After that, values appear automatically in variable (yellow colored cells)

А E F G - H B с D Production Demand cost/window Production Month 1 2 3 1 4 N 3 100 250 190 140 220 110 5 6 Inventory 0 0 190 0 0 0 0 50 45 55 48 52 50 100 440 0 140 220 110 4 7 Сл 8 LO Total Cost 49980 Solver Results X 9 10 11 12 13 14 15 16 17 18 19 20 Solver found a solution. All Constraints and optimality conditions are satisfied. Reports Answer Sensitivity Limits Keep Solver Solution Restore Original Values 21 22 Return to Solver Parameters Dialog Outline Reports OK Cancel Save Scenario... 23 24 25 26 27 28 29 30 Solver found a solution. All constraints and optimality conditions are satisfied. When the GRG engine is used, Solver has found at least a local optimal solution. When Simplex LP is used, this means Solver has found a global optimal solution. Ready

Optimal production schedule is:

Month	Production
1	100
2	440
3	0
4	140
5	220
6	110

Total cost = $ 49,980