Question

EZ-Windows, Inc., manufactures replacement windows for the home remodeling business. In January, the company produced 15,000 windows and ended the month with 10,000 windows in inventory. EZ-Windows’ management team would like to develop a production schedule for the next three months. A smooth production schedule is obviously desirable because it maintains the current workforce and provides a similar month-to-month operation. However, given the sales forecasts, the production capacities, and the storage capabilities as shown, the management team does not think a smooth production schedule with the same production quantity each month is possible.

	February	March	April
Sales forecast	15,000	15,500	20,500
Production capacity	13,000	15,000	17,000
Storage capacity	6,000	6,000	6,000

The company’s cost accounting department estimates that increasing production by one window from one month to the next will increase total costs by $1.00 for each unit increase in the production level. In addition, decreasing production by one unit from one month to the next will increase total costs by $0.65 for each unit decrease in the production level. Ignoring production and inventory carrying costs, formulate and solve a linear programming model that will minimize the cost of changing production levels while still satisfying the monthly sales forecasts. If required, round your answers to two decimal places. If an amount is zero, enter "0".

Let:

F = number of windows manufactured in February

M = number of windows manufactured in March

A = number of windows manufactured in April

I_m = increase in production level necessary during month m

D_m = decrease in production level necessary during month m

s_m = ending inventory in month m

Min	I1 + I2 + I3 + .65D1 + .65D2 + .65D3
s.t.
(1)	F - s1 = 5000	February Demand
(2)	s1 + M - s2 = 15500	March Demand
(3)	s2 + A - s3 = 20500	April Demand
(4)	F - I1 + D1 = 15000	Change in February Production
(5)	M - F - I2 + D2 = 0	Change in March Production
(6)	A - M - I3 + D3 = 0	Change in April Production
(7)	F ≤ 13000	February Production Capacity
(8)	M ≤ 15000	March Production Capacity
(9)	A ≤ 17000	April Production Capacity
(10)	s1 ≤ 6000	February Storage Capacity
(11)	s2 ≤ 6000	March Storage Capacity
(12)	s3 ≤ 6000	April Storage Capacity

If required, round your answers to the nearest dollar.

Cost: $ ____

If required, round your answers to the nearest whole number. If an amount is zero, enter "0"

	February	March	April
Production Level		15000	15000
Increase in Production	0		0
Decrease in Production		0	0
Ending Inventory	6000	5500	0

Answer 1

Answer

I use excel solver to solve this equation for minimization, The formulas are shown as below. The 1's are just initial Numbers that will change when we use sover. The formula used to connect each are also shown below.

Z = 1*b1 + 1*b2 + 1*b3 + 0.65*c1 + 0.65*c2 + 0.65*c3

	February	March	April
	1	2	3
Actual Production(a)	1	1	1
Increase in Production Level(b)	1	1	1
Decrease in Production Level(c)	1	1	1
Month end Inventory(d)	1	1	1
Equation No. 1,2,3	a1-d1	d1+a2-d2	d2+a3-d3
Equation No. 4,5,6	a1-b1+c1	a2-b2+c2	a3-b3+c3

Z = 4.95

	February	March	April
	1	2	3
Actual Production(a)	1	1	1
Increase in Production Level(b)	1	1	1
Decrease in Production Level(c)	1	1	1
Month end Inventory(d)	1	1	1
Equation No. 1,2,3	0	1	1
Equation No. 4,5,6	1	1	1

Solution after applying solver in excel

	February	March	April
Actual Production	12000	15000	15000
Increase in Production Level	0	3000	0
Decrease in Production Level	3000	0	0
Month end Inventory	6000	5500	0
Equation No. 1,2,3	6000	15500	20500
Equation No. 4,5,6	15000	12000	15000

Z=4950
So, Cost = $4,950

Im attaching the sreenshot of the solver used in excel.

XUS New Microsoft Excel Worksheet (3).xlsx - Excel (Product Activation Failed) ? FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW DEVELOPER Sign in FREE AL 2 RE #E HE A ? Solver Connections Properties AU From From From From Other Access Web Text Sources Clear To Reapply X Refresh Z Sort Filter Existing Connections Solver Parameters All Edit Links Advanced Text Colum Get External Data Connections Sort & Filter nalysis A Set Objective: SBS3 B3 : X To: O Max O Min O Value of: 0 A B с D D L M 1 By Changing Variable Cells: $B$5:$D$8 2 3 4 Zi4.95 February March 1 April 1 1 Add 1 1 1 1 1 1 Change 5 Actual Production 6 Increase in Production Level 7 Decrease in Production Level 8 Month end Inventory 9 Equation No. 1,2,3 10 Equation No.4,5,6 11 1 1 1 Subject to the Constraints: $B$10 = 15000 $B$5 <= 13000 $B$8 <= 6000 SBS9 = 6000 SCS10 - $B$5 SC$5 <= 15000 $C$8 <= 5500 $C$9 = 15500 SDS10 = $C$5 SD$5 <= 17000 SD$8 <= 5000 SD$9 = 20500 Delete 0 1 1 1 1 1 Reset All 12 Load/Save 13 ✓ Make Unconstrained Variables Non-Negative 14 15 Select a Solving Method: GRG Nonlinear Options 16 17 18 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. 19 20 21 22 Help Solve Close 23 Sheet12 Sheet13 Sheet14 Sheet15 Sheet16 Sheet17 Sheet18 Sheet19 Sheet20 POINT 100% 1 EM х 10:44 PM 25-09-2020