Developing a model to more effectively plan production for the next year:

Currently, PLE has a planned capacity of producing 9,100 mowers each month, which is approximately the average monthly demand over previous years. However, looking at the unit sales figures for the previous years, Elizabeth observes a seasonal fluctuation resulting in overproduction in some months. In subsequent months, inventory buildup and underproduction occur which may result in lost sales during peak demand periods. Elizabeth explains that she could change the production rate using planned overtime and downtime (producing more or less than the average monthly demand), but this incurs additional costs, although it may offset the cost of lost sales or of maintaining excess inventory.

Yet, She strongly believes that by optimizing the production plan PLE can save a significant amount of money. Elizabeth has provided with data, variables, constraints, and estimates for the next year:

- Condition

Unit production cost = $70

Inventory-Holding Cost = $1.40/unit per month

Lost Sales Cost = $200/unit

Overtime Cost = $6.50/unit

Undertime Cost = $3.00/unit

Production-Rate-Change Cost = $5.00/unit (this applies to any increase or decrease in the production

- Mower Unit Sales

Month Demand

January 7,080

February 9,250

March 10,020

April 10,995

May 11,436

June 12,082

July 11,486

August 9,504

September 8,635

October 7,451

November 6,453

December 5,651

Xt = planned production in period t

It = inventory held at the end of period t

Lt = number of lost sales incurred in period t

Ot = amount of overtime scheduled in period t

Ut = amount of undertime scheduled in period t

Rt = increase in production rate from period t1t1 to period t

Dt = decrease in production rate from period t1t1 to period t

Material balance constraint:

(Xt+It)1(It+Lt) = demand in montht

Overtime/undertime constraint:

OtUt = Xtnormal production capacity

Production rate-change constraint:

XtXt1 = RtDt

Elizabeth shares with you that 900 units are expected to be in inventory at the beginning of January, and the production rate for December was 9,100 units.

Please express your work in the Excel Solver