DATA:https://docs.google.com/spreadsheets/d/1AwbWgj09keDs6S5N-limjOXSthXOPkAc/edit?usp=sharing&ouid=115011566029800565379&rtpof=true&sd=true

Use your knowledge and skills learned in this unit, complete the case analysis requirements for Performance Lawn Equipment.

Elizabeth Burke wants to develop a model to optimize production for the next year.

Here are the constraints:

1. Material balance constraint: X_t + I_t-1 - I_t + L_t = demand in month t
2. Overtime/undertime constraint: O_t - U_t = X_t - normal production capacity
3. Production rate-change constraint: X_t - X_t-1 = R_t - D_t

Where

• X_t = planned production in period t
• I_t = inventory held at the end of period t
• L_t = number of lost sales incurred in period t
• O_t = amount of overtime scheduled in period t
• U_t = amount of undertime scheduled in period t
• R_t = increase in production rate from period t-1 to period t

Ms. Burke also provided the following data:

1. Unit production cost $70.00
2. Inventory holding cost $1.40 per unit per month
3. Lost sales cost $200 per unit
4. Overtime cost $6.50 per unit
5. Undertime cost $3.00 per unit
6. Production rate change cost $5.00 per unit, which applies to any increase or decrease in the production rate from the previous month
7. Initially, 900 units are expected to be in inventory at the beginning of January, and the production rate for the past December was 9,100 units. She believes that monthly demand will not change substantially from last year, so the mower unit sales figures for the last year in the Performance Lawn Equipment Database should be used for the monthly demand forecasts

Design a spreadsheet that provides detailed information on monthly production, inventory, lost sales, and the different cost categories and solve a linear optimization model for minimizing the total cost of meeting demand over the next year. Compare your solution with the level production strategy of producing 9,100 units each month. What are the key insights?