Problem Statement

Suppose that you are tasked with developing a manufacturing schedule for the next 5 months for a particular facility. This facility can produce up to 500 units a month, but there is a significant setup cost for each month that the facility is producing. Your task is to decide in which months the facility will operate, and how many units are produced in each month. To make matters interesting, the amount of units demanded changes from month to month, and you must pay a holding cost for any units produced prior to the month in which they will be sold to a customer. In other words, you must pay the holding cost for every unit held in inventory for every month it is held in inventory.

Develop a what-if model using the provided template to find the optimal production schedule.

To help you validate your model, enter a solution of producing exactly the demand in each policy (i.e. producing the minimum amount possible in each month while still meeting demand) and producing all the units you would need in Month 1 (so producing 1270 units in Month 1 and 0 in Months 2-5). These are two different solutions, so consider them separately; they correspond to Total costs of $3,970 and $5,290 respectively.

Once you are satisfied that your what-if model is working as intended, construct an optimization model in Solver to determine the optimal production plan. Dont forget to include integer and/or binary constraints as well! Take a screenshot of Solver and paste it onto your spreadsheet, and run the model to find that optimal production plan.

PLEASE INCLUDE ALL FORMULAS, CONSTRAINTS AND SOLVER SCREENSHOTS! THANK YOU SO MUCH!

Monetary inputs Setup cost Variable cost Holding cost $250.00 $2 $1 Beginning inventory 0 Month 5 Demand Production Capacity Month 1 220 500 Month 2 280 500 Month 3 360 500 Month 4 140 500 270 500 Model Months to produce (1 if production is positive, if not) Month 1 Month 2 Month 3 Month 4 Month 5 Month 1 Month 2 Month 3 Month 4 Month 5 Production plan Production level Max Production Allowed Ending inventory Meet demand on time Total setup cost Total variable cost Total holding cost Total cost