Question

In: Economics

Elizabeth Burke wants to develop a model to more effectively plan production for the next year....

Elizabeth Burke wants to develop a model to more effectively plan production for the next year. Currently, PLE has a planned capacity of producing 9100 mowers each month, which is approximately the average monthly demand over the previous year. However, looking at the unit sales figure for the previous year, she observed that the demand for mowers has a seasonal fluctuation, so with this level production strategy there is over-production in some months, resulting in excess inventory buildup and underproduction in others, which may result in lost sales during peak demand periods. In discussing this with her, she explained that she could change the production rate by using planned overtime and under time (producing more or less than the average monthly demand), but this incurs additional cost, although it may offset the cost of lost sales or of maintaining excess inventory. Consequently, she believes that the company can save a significant amount 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 power point notes didn’t have all the details but they did explain the variables and types of constraints used in the model. she thought they would be helpful to you in implementing an optimization model. Here are the highlights of the presentation.

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 under time 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 in balance constraint: (X^t) + (I^t) – (I^t) + (L^t) =demand of the month

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 cost production = $70: inventory holding cost= $1.40 per unit per month; lost sales cost=$200 per unit; overtime cost =$6.50 per unit; undertime cost is $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 December 2012 was 9100 units. she believed that the monthly demand will not change substantially from last year so, the sales figures for last year in the PLE database should be used for the monthly demand forecast.

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 the solution with the level of production strategy of producing 9100 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 cost.

Data:

Month NA SA Europe Pacific China World
Jan-18 6210 270 400 200 0 7080
Feb-18 8030 280 750 190 0 9250
Mar-18 8540 300 970 210 5 10025
Apr-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
Aug-18 7680 340 1250 220 15 9505
Sep-18 6870 320 1210 220 11 8631
Oct-18 5930 310 970 230 3 7443
Nov-18 5260 300 650 240 1 6451
Dec-18 4830 290 300 230 0 5650

If possible could you show the excel step by step calculation for i^t, x^t etc.

Solutions

Expert Solution


Related Solutions

Ina works in a juice firm and has to develop the production plan for the lemon...
Ina works in a juice firm and has to develop the production plan for the lemon and the orange juice concentrates. The fruits (lemon and orange) that she needs to make the juice are not the bottleneck but Ina is concerned about the other main ingredients that go into making the juice: a water-based solution, sugar, and a vitamin mix. Checking with the ERP system tells her that she has 10.3 metric tons of the water solution, 2.92 metric tons...
A real estate agent wants to develop a model to predict the selling price of a...
A real estate agent wants to develop a model to predict the selling price of a home. The agent believes that the most important variables in determining the price of a house are its: Size of the house (square footage) Number of bedrooms Lot size where the house is built (also in square feet). He collects from the market the data on a number of houses, which include for every house the information listed above as well as the house...
A teacher wants to develop a model to predict a student’s grade on the final exam...
A teacher wants to develop a model to predict a student’s grade on the final exam from the number of hours spent studying for the final exam and the student’s GPA at the university. The data (for 22 students) follows below.       PREDICTOR               COEF           STDEV               P-VALUE       Constant                      -1.30             1.429                0.405       Hours                           .0793            .0759                0.344       GPA                             1.11             .7543                0.202       ANOVA       SOURCE                                SS                     DF                 MS                F                                        Regression                             5.0040         ...
A teacher wants to develop a model to predict a student’s grade on the final exam...
A teacher wants to develop a model to predict a student’s grade on the final exam from the number of hours spent studying for the final exam and the student’s GPA at the university. The data (for 22 students) follows below.        PREDICTOR                 COEF             STDEV                       P-VALUE        Constant                         -1.30               1.429                          0.405        Hours                              .0793              .0759                          0.344        GPA                                1.11               .7543                          0.202        ANOVA        SOURCE                                    SS                               DF                   MS                  F                                                      Regression                                 5.0040                                                                     Error                                            1.1548                    TOTAL     (a) What is the student’s expected grade if she has a 2.7 GPA and she studies 12 hours for this test? (b) Interpret the slope coefficient for the variable...
Final Project: Due Week 8 (NEXT WEEK): Risk Management Plan Develop a Risk Management Plan. This...
Final Project: Due Week 8 (NEXT WEEK): Risk Management Plan Develop a Risk Management Plan. This is a document which details the risk management plan for your project. (Don't confuse this with the Risk Register or Risk Response Planning). Rubric Task Point Value (out of 100) Proper APA format, spelling, grammar, citation, and organization of writing 10 points All required sections included in the RMP 50 points Clearly understood plan, regardless of the persons background in risk or project management...
Plan for a solution by drawing a flowchart EFG Co wants you to develop a C++...
Plan for a solution by drawing a flowchart EFG Co wants you to develop a C++ program to calculate the total invoice based on the user inputs item price and the quantity of the item purchased, taking into consideration the discount given for each category as follow: Less than 10 Item: No discount Between 10 and 20 items: 10 % off the item price More than 20 items: 20% off the item price.
The production of wine is a​ multibillion-dollar worldwide industry. In an attempt to develop a model...
The production of wine is a​ multibillion-dollar worldwide industry. In an attempt to develop a model of wine quality as judged by wine​ experts, data was collected from red wine variants. A sample of 20 wines is provided in the accompanying table. Develop a multiple linear regression model to predict wine​ quality, measured on a scale from 0​ (very bad) to 10​ (excellent) based on alcohol content​ (%) and the amount of chlorides. Complete parts a through g below. Quality  ...
The production of wine is a​ multibillion-dollar worldwide industry. In an attempt to develop a model...
The production of wine is a​ multibillion-dollar worldwide industry. In an attempt to develop a model of wine quality as judged by wine​ experts, data was collected from red wine variants. A sample of 20 wines is provided in the accompanying table. Develop a multiple linear regression model to predict wine​ quality, measured on a scale from 0​ (very bad) to 10​ (excellent) based on alcohol content​ (%) and the amount of chlorides. Complete parts a through g below. Quality  ...
The production of wine is a​ multibillion-dollar worldwide industry. In an attempt to develop a model...
The production of wine is a​ multibillion-dollar worldwide industry. In an attempt to develop a model of wine quality as judged by wine​ experts, data was collected from red wine variants. A sample of 20 wines is provided in the accompanying table. Develop a multiple linear regression model to predict wine​ quality, measured on a scale from 0​ (very bad) to 10​ (excellent) based on alcohol content​ (%) and the amount of chlorides. Complete parts a through g below. LOADING......
Develop a production plan and calculate the annual cost for a firm whose demand forecast is...
Develop a production plan and calculate the annual cost for a firm whose demand forecast is fall, 10,300; winter, 8,200; spring, 6,800; summer, 12,300. Inventory at the beginning of fall is 515 units. At the beginning of fall you currently have 30 workers, but you plan to hire temporary workers at the beginning of summer and lay them off at the end of summer. In addition, you have negotiated with the union an option to use the regular workforce on...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT