Question

Carlton Goods Co. makes household appliances at a single manufacturing facility. The expected demand for one of these appliances during the next four months is shown in the table below along with the expected production costs and the expected capacity for producing these items.

		Month
	1	2                            3	4
Demand Production Cost Production Capacity	420 $49 500	580                   310 $46                   $45 520                   480	540 $47 550

                               

Carlton estimates that it costs $2.50 per month for each unit of this appliance carried in inventory, estimated by averaging the beginning and ending inventory levels for each month. For instance, if the beginning inventory of one month is 180 and the ending inventory is 220, the “average” inventory for that month will be (180+220)/2 = 200 and therefore inventory cost will be 2.5 × 200 = 500.

Currently, Carlton has 120 units in inventory on hand for this product. To maintain a level workforce, Carlton wants to produce at least 350 units in each month. It also wants to maintain a safety stock of at least 60 units at the end of each month. Carlton wants to know how many appliances to manufacture during each of the next 4 months to meet expected demand at the lowest cost.  

a)    Formulate the problem as a linear program. Clearly define your decision variables and write the objective function and all constraints in algebraic form.

b)    Create a spreadsheet model for this problem in Excel and solve it with Solver. Attach three snapshots of your setup:  Final view, Formula view, and Solver setup.

c)     What is the optimal solution? What is the total cost of this production plan?

Answer 1

a) Linear program is following

Decision variables: Let P1, P2, P3, P4 be the number of units to manufacture in each of the 4 months and V1, V2, V3, V4 be the amount of ending inventory of each of the 4 months

Objective function: Min 49P1+46P2+45P3+47P4+2.5*((120+V1)/2+(V1+V2)/2+(V2+V3)/2+(V3+V4)/2)

Constraints:

P1-V1 = 420-120

P2+V1-V2 = 580

P3+V3-V2 = 310

P4+V4-V3 = 540

P1, P2, P3, P4 >= 350

V1, V2, V3, V4 >= 60

P1 <= 500

P2 <= 520

P3 <= 480

P4 <= 550

All variables >= 0

b) Spreadsheet model and its solution using Solver is following

Final View and Solver Setup

Formulas:

J3 =SUMPRODUCT(B3:I3,$B$24:$I$24)   copy to J3:J6, J8:J11, J13:J16, J18:J22

c) Optimal solution:

Production in

Month 1 = 420

Month 2 = 520

Month 3 = 350

Month 4 = 500

Total cost of this production plan = $ 84,525