Your task $isto$ 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 $toMs.$ Burke.

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

$costof$ lost sales $orof$ 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$

Overtim$\frac{e}{u}$ndertime 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

$tobein$ 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 $isto$ 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 $toMs.$ Burke. January $7080$

February $9250$

March $10025$

April $11006$

May $11442$

June $12086$

July $11474$

August $9505$

September $8631$

Ottober $7443$

November $6451$

December $5650$