Elizabeth Burke wants to develop a model so 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 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 production 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 significant 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 optimization model. Here are the highlights from the presentation: Variables: X, = planned production in period t I, = inventory held at the end of period t L, = number of lost sales incurred in period t O, = amount of overtime scheduled in period t U, = amount of undertime scheduled in period + R, = increase in production rate from period t - 1 to period t D, = decrease in production rate from period 1 - 1 to period t Material balance constraint: X, + 1-1 - 1, + L, = demand in month t Overtime/undertime constraint: O, - U, = X, - normal production capacity Production rate-change constraint: X, - X,-1 = R, - Dt Ms. Burke also provided the following data and estimates for the next year: unit production cost = $70.00; inventory holding cost = $1.40 per unit per month; lost sales cost = $200 per unit; overtime cost = $6.50 per unit; undertime cost = $3.00 per unit; and production-rate-change cost = $5.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 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. 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. EXCEL SOLUTION PLEASE!!!

Month	NA	SA	Europe	Pacific	China	World
Ene-14	6000	200	720	100	0	7020
Feb-14	7950	220	990	120	0	9280
Mar-14	8100	250	1320	110	0	9780
Abr-14	9050	280	1650	120	0	11100
May-14	9900	310	1590	130	0	11930
Jun-14	10200	300	1620	120	0	12240
Jul-14	8730	280	1590	140	0	10740
Ago-14	8140	250	1560	130	0	10080
Set-14	6480	230	1590	130	0	8430
Oct-14	5990	220	1320	120	0	7650
Nov-14	5320	210	990	130	0	6650
Dic-14	4640	180	660	140	0	5620
Ene-15	5980	210	690	140	0	7020
Feb-15	7620	240	1020	150	0	9030
Mar-15	8370	250	1290	140	0	10050
Abr-15	8830	290	1620	150	0	10890
May-15	9310	330	1650	130	0	11420
Jun-15	10230	310	1590	140	0	12270
Jul-15	8720	290	1560	150	0	10720
Ago-15	7710	270	1530	140	0	9650
Set-15	6320	250	1590	150	0	8310
Oct-15	5840	250	1260	160	0	7510
Nov-15	4960	240	900	150	0	6250
Dic-15	4350	210	660	150	0	5370
Ene-16	6020	220	570	160	0	6970
Feb-16	7920	250	840	150	0	9160
Mar-16	8430	270	1110	160	0	9970
Abr-16	9040	310	1500	170	0	11020
May-16	9820	360	1440	160	0	11780
Jun-16	10370	330	1410	170	0	12280
Jul-16	9050	310	1440	160	0	10960
Ago-16	7620	300	1410	170	0	9500
Set-16	6420	280	1350	180	0	8230
Oct-16	5890	270	1080	180	0	7420
Nov-16	5340	260	840	190	0	6630
Dic-16	4430	230	510	180	0	5350
Ene-17	6100	250	480	200	0	7030
Feb-17	8010	270	750	190	0	9220
Mar-17	8430	280	1140	200	0	10050
Abr-17	9110	320	1410	210	0	11050
May-17	9730	380	1340	190	0	11640
Jun-17	10120	360	1360	200	0	12040
Jul-17	9080	320	1410	200	0	11010
Ago-17	7820	310	1490	210	0	9830
Set-17	6540	300	1310	220	0	8370
Oct-17	6010	290	980	210	0	7490
Nov-17	5270	270	770	220	0	6530
Dic-17	5380	260	430	230	0	6300
Ene-18	6210	270	400	200	0	7080
Feb-18	8030	280	750	190	0	9250
Mar-18	8540	300	970	210	5	10025
Abr-18	9120	340	1310	220	16	11006
May-18	9570	390	1260	200	22	11442
Jun-18	10230	380	1240	210	26	12086
Jul-18	9580	350	1300	230	14	11474
Ago-18	7680	340	1250	220	15	9505
Set-18	6870	320	1210	220	11	8631
Oct-18	5930	310	970	230	3	7443
Nov-18	5260	300	650	240	1	6451
Dic-18	4830	290	300	230	0	5650