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.

Performance Analysis task

Elizabeth Burke wants to develop a model to 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.

In discussing this with her, she explained that she could change the production rate by using planned overtime or undertime (producing 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. Consequently, 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.

TASK ONE

One of PLE’s manufacturing facilities produces metal engine housings from sheet metal for both mowers and tractors. Production of each product consists of five steps: stamping, drilling, assembly, painting, and packaging to ship to its final assembly plant. The production rates in hours per unit and the number of production hours available in each department are given in the following table:

DEPARTMENT     MOWER HOUSINGS     TRACTOR HOUSINGS          PRODUCTION HOURS AVAILABLE

Stamping                      0.03                          0.07                                                     200

Drilling                         0.09                          0.06                                                      300

Assembly                      0.15                           0.10                                                    300

Painting                       0.04                           0.06                                                     220

Packaging                    0.02                           0.04                                                     100

In addition, mower housings require 1.2 square feet of sheet metal per unit and tractor housings require 1.8 square feet per unit, and 2,500 square feet of sheet metal is available. The company would like to maximize the total number of housings they can produce during the planning period. Formulate and solve a linear optimization model using Solver and recommend a production plan. Illustrate the results visually to help explain them in a presentation to Ms. Burke. In addition, conduct whatever what-if analyses (e.g., run different scenarios and apply parameter analysis) you feel are appropriate to include in your presentation. Summarize your results in a well-written report.

 

TASK TWO

Elizabeth Burke wants to develop a model to 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.

In discussing this with her, she explained that she could change the production rate by using planned overtime or undertime (producing 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. Consequently, 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:

Xt= planned production in period t

It= inventory held at the end of period t

Lt= number of lost sales incurred in period t

Ot= amount of overtime scheduled in period t

Ut= amount of undertime scheduled in period t

Rt= increase in production rate from period t−1 to period t

Dt= decrease in production rate from period t−1 to period t

Material balance constraint:

Xt+It−1−It+Lt=demand in month t

Overtime/undertime constraint:

Ot−Ut=Xt−normal production capacity

Production rate-change constraint:

Xt−Xt−1=Rt−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 December 2012 was 9,100 units. She believes that 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 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

Last Completed Projects

topic title academic level Writer delivered
© 2020 EssayQuoll.com. All Rights Reserved. | Disclaimer: For assistance purposes only. These custom papers should be used with proper reference.