. Formulate Example as a linear program

A food company makes small puddings in a plant. It sells the puddings in cases

(a case contains 48 pudding cups). The plant has 15 parallel production lines.

The regular operating hours is 8 hours a day, 5 days a week, 13 weeks a quarter.

Each line is automated but needs 6 workers to operate it and can produce

200 units of output (each unit = 1,000 cases) per quarter. Currently, only 11

production lines are used, for a total quarterly output of 2,200 units. The sales

department has forecasted demand for puddings for the next five quarters:

2,000, 2,200, 2,500, 2,700, and 2,200 units. Currently, there are no puddings

in the warehouse. The company can change production by changing workforce

level but needs to hire or lay off a group of six workers at a time, i.e., by opening

or shutting down one production line. Hiring or layoff of one worker costs

approximately $1,000 each. Each worker is paid an average of $19.23 per hour.

Overtime costs 1.5 times regular-time wages, and is limited to the maximum

of 20 percent of regular-time production in any quarter. Holding inventory of a

unit for a quarter will cost $50 (charged on the average level of inventory in the

quarter). Backorder per unit per quarter is estimated to cost $200.

Question: . Formulate Example as a linear program in Excel and solve it using Excel’s Solver. There is no need to consider the use of overtime.