Elizabeth Burke wants to develop a model to more effec$-$

tively 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 the previous year. However, looking at the

unit sales figures for the previous year, she observed that

the demand for mowers has a seasonal fluctuation, so with

this "level" production strategy, there is overproduction in

some months, resulting in excess inventory buildup, and

underproduction in others, which may result in lost sales

during peak demand periods.

Ms$.$ Burke explained that she could change the pro$-$

duction rate by using planned overtime or undertime $($pro$-$

ducing 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. Con$-$

sequently, she believes that the company can save a signif$-$

icant amount of money by optimizing the production plan.

Ms$.$ Burke saw a presentation at a conference about a

similar model that another company used but didn't fully

understand the approach. The PowerPoint notes didn't

have all the details, but they did explain the variables and

the types of constraints used in the model. She thought

they would be helpful to you in implementing an optimiza$-$

tion model. Here are the highlights from the presentation:

Variables:

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

D$\_($t$)=$ decrease in production rate from period t$-1$

to period t

Material balance constraint:

X$\_($t$)+$I$\_($t$-1)-$I$\_($t$)+$L$\_($t$)=$ demand in month t

Overtime$/$undertime constraint:

O$\_($t$)-$U$\_($t$)=$X$\_($t$)-$ normal production capacity

Production rate$-$change constraint:

X$\_($t$)-$X$\_($t$-1)=$R$\_($t$)-$D$\_($t$)$

Ms$.$ Burke also provided the following data

and estimates for the next year: unit production

cost $=$$$70\mathrm{.}00$; inventory$-$holding cost $=$$$1\mathrm{.}40$ per unit

per month; lost sales cost $=$$$200$ per unit; overtime

cost $=$$$6\mathrm{.}50$ per unit; undertime cost $=$$$3\mathrm{.}00$ per unit;

and production$-$rate$-$change cost $=$$$5\mathrm{.}00$ per unit, which

applies to any increase or decrease in the production rate

from the previous month. Initially, $900$ units are expected

to be in inventory at the beginning of January, and the pro$-$

duction rate for the past December was $9,100$ units. She

believes that monthly demand will not change substan$-$

tially 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.

Your task is to 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. Interpret the Sensitivity Report

and conduct an appropriate study of how the solution will

be affected by changing the assumption of the lost sales

costs. Summarize all your results in a report to Ms$.$ Burke. The demand of each month are January$-71,553$; February$-94,679$;March$-104,555$;April$-115,849$; May$-120,840$; June$-126,000$; July$-116,165,$ August$-103,864$;September$-91,448$;October$-80,540$; November$-68,734$;December$-57,825.$