A company is planning production for the next 4 quarters and wants to minimize the cost of production. The production cost, demand, and production capacity vary from quarter to quarter as shown in Table 1 below. The maximum amount of inventory that can be held is 100 units, and management wants to keep at least 50 units on hand at all times. The quarterly per unit inventory holding cost is 4% of the unit production cost for the same quarter. There are currently 50 units in inventory, and the company wants to produce at no less than one half of its maximum capacity in any quarter.

(Quarter on top row i.e., 1, 2, 3, 4)

Table 1. Input Data for Production Planning Problem

Let Xi = number of units produced in quarter i, i = 1, ?, 4 and

Yi = beginning inventory for quarter i, i = 1, ?, 4

Write the algebraic formulation of this optimization problem, such that total production costs are minimized. Be sure to include the objective function and all necessary constraints. (Hint: You should have 12 constraints in addition to the non-negativity constraints.)

Create a spreadsheet model for this scenario and use Solver to find the optimal solution. Be sure to highlight the number of units produced and the total production cost.

Quarter Unit Production Cost Units Demanded Maximum Production Holding Cost 1 $55 100 150 $2.20 2 $50 150 150 $2.00 3 $50 180 160 $2.00 4 $45 120 130 $1.80