Question

Hello, I need help with the following problem:

1. Multi-period production problem.

1. Formulate the production and inventory problem at XXI Company using Excel. See narrative below
2. Solve the problem in Excel

XX1 Company Production and Inventory Problem Narrative

A company named XX1 has contracted to produce products A and B, over the months of June, July and August. The total production capacity (expressed in hours) varies monthly. The following table provides the basic data for the situation.

	June	July	August
Demand for A (units)	500	5000	750
Demand for B (units)	1000	1200	1200
Capacity (hours)	3000	3600	3000

The production rates in units per hour are 1.25 and 1 for products A and B, respectively (Hint: from these data you can easily compute the hours needed to manufacture a unit of each product). All demand must be met. However demand for a later month may be filled from the production in an earlier one. For any carryover from one month to the next, holding costs of $0.90 and $0.75 per unit per month are charged for A and B, respectively. The unit production costs of the two products are $30 and $28for A and B, respectively.

a. Formulate a linear program (LP) to determine the optimum production schedule for the two products.

Decision Variables:

Objective function:

Constraints. Write all constraints regarding capacity, balance flow constraints (i.e. inventory-demand constraints) and any others (if existing) for each month.

Sign constraints:

b. Solve the XX1 company problem with Excel Solver.

Answer 1

a) Linear Programming model is following:

Decision variables:

A1, A2, A3 = production of A in month June, July and August resp.

B1, B2, B3 = production of B in month June, July and August resp.

VA1, VA2, VA3 = ending inventory of A in month June, July and August resp.

VB1, VB2, VB3 = ending inventory of B in month June, July and August resp.

Objective function:

Minimize 30A1+30A2+30A3+28B1+28B2+28B3+.9VA1+.9VA2+.9VA3+.75VB1+.75VB2+.75VB3

Constraints:

(1/1.25)A1+1B1 <= 3000

(1/1.25)A2+1B2 <= 3600

(1/1.25)A3+1B3 <= 3000

A1-VA1 = 500

A2-VA2+VA1 = 5000

A3-VA3+VA2 = 750

B1-VB1 = 1000

B2-VB2+VB1 = 1200

B3-VB3+VB2 = 1200

A1, A2, A3, B1, B2, B3, VA1, VA2, VA3, VB1, VB2, VB3 >= 0

b) Solution using EXCEL SOLVER is following:

N3 Q f SUMPRODUCT(B3:M3,$B$15:$M$15) S Solver Parameters 3 Cost: 30 30 30 282828 0.9 0.9 0.9 0.75 0.75 0.75 Set Objective: To: By Changing Variable Cells SNS3 Max O Min O Value Of: O 5 Demand-A11 6 Demand-A2 7 Demand-A3 8 Demand-B1 9 Demand-B2 10 Demand-B3 11 Capacity-1 0.8 12 Capacity-2 13 Capacity-3 14 15 Result: 16 17 18 19 500 500 5000 5000 750 750 10001000 1200 1200 1200 1200 3000 3600 3000 SBS15:SMS15 Subject to the Constraints SNS5:SNS10-SPS5:SPS10 SNS11:SNS13SPS11:SPS13 Add 3000 0.8 0.8 1800 Delete 2500 3000 750 1000 1200 1200 2000 0 0 0 00 Reset All Make Unconstrained Variables Non-Negative 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 21 Solve Close 25 26

EXCEL FORMULAS:

N3 =SUMPRODUCT(B3:M3,$B$15:$M$15) copy to N5:N13

Optimal production schedule:

	June	July	August
Demand for A (units)	2500	3000	750
Demand for B (units)	1000	1200	1200

Total cost = $ 284,500