Please answer this using EXEL AND SOLVER ADD IN. Include and identify decision variables, objective, and constraints. Please also upload what the info in solver you added was!!!! It is summertime and your winter coat factory is trying to decide its production schedule and staffing level for the next season. You are forecasting demand of 5000 coats in October, 8000 coats in November, 8000 coats in December, 12000 coats in January, 12000 coats in February and 9000 coats in March. You want to manufacture these coats at the lowest cost possible during your production window (October-March). The first decision you need to make is how many people to hire. Each employee will cost $2500 a month. You don’t want to hire or fire any employees mid-season in order to minimize disruptions in your factory floor. You can have employees idle if there isn’t sufficient work for them to do. Employees still need to be paid even if they are idle. An employee works 20 business days a month and takes half a day to produce one coat. The employees can also be asked to work in overtime, in which case you need to pay them $70 per coat they produce. You don’t need to assume there is any capacity constraint on the number of overtime hours. Coats can be produced ahead of time, but holding them in inventory costs $10 a month per coat due to warehousing and financing costs. What’s your ideal staffing level and production schedule?

Practical Management Science

6th Edition

ISBN:9781337406659

Author:WINSTON, Wayne L.

Publisher:WINSTON, Wayne L.

Chapter2: Introduction To Spreadsheet Modeling

Section: Chapter Questions

Problem 20P: Julie James is opening a lemonade stand. She believes the fixed cost per week of running the stand...

See similar textbooks

Related questions

Question

Please answer this using EXEL AND SOLVER ADD IN. Include and identify decision variables, objective, and constraints. Please also upload what the info in solver you added was!!!!

It is summertime and your winter coat factory is trying to decide its production schedule and staffing level for the next season. You are forecasting demand of 5000 coats in October, 8000 coats in November, 8000 coats in December, 12000 coats in January, 12000 coats in February and 9000 coats in March. You want to manufacture these coats at the lowest cost possible during your production window (October-March). The first decision you need to make is how many people to hire. Each employee will cost $2500 a month. You don’t want to hire or fire any employees mid-season in order to minimize disruptions in your factory floor. You can have employees idle if there isn’t sufficient work for them to do. Employees still need to be paid even if they are idle. An employee works 20 business days a month and takes half a day to produce one coat. The employees can also be asked to work in overtime, in which case you need to pay them $70 per coat they produce. You don’t need to assume there is any capacity constraint on the number of overtime hours. Coats can be produced ahead of time, but holding them in inventory costs $10 a month per coat due to warehousing and financing costs. What’s your ideal staffing level and production schedule?

Expert Solution