The Hessian Company makes basketballs and wants to plan out how many units to produce in each of the next 6 months. The company can use a given month's production to help meet the demand for that month, and it can carry inventory from one month to the next to help meet future demand. The selling price for basketballs is not relevant to production decisions because the company is committed to meeting all demand each month. Therefore, Hessian wants to determine the production schedule that minimizes total production and inventory holding costs.

Demand forecasts for the next 6 months are shown in the worksheet.

The plant has an absolute production limit of 7,000 basketballs per month. In addition, because of the difficulty of ramping up or down, the production level in each month after the first cannot be less than 50% or more than 150% of the preceding month's production level (assume the first-month production level is unrestricted).

The company currently has 1,000 basketballs in inventory. There is enough inventory storage capacity to hold up to 2,500 basketballs at the end of each month and they want to hold a safety stock of at least 250 basketballs at the end of each month. In addition, the company wants the last month of the planning period to end with at least 1,000 units in stock to start the next planning cycle.

Unit production cost is $10.00 for the first 3 months but is expected to jump up to $15.00 for months 4-6, because of seasonal supply-chain issues.

The cost of holding inventory is estimated as $3.50 times the average of beginning and ending inventory levels each month (this includes the cost of storage and also the cost of money tied up in inventory).

Use a good spreadsheet modeling technique to create an LP model in Excel and find the optimal production and inventory plan for each month using Solver. (Above is all the information)
 

Demand Forecasts Month 1 3,000 Month 2 3,000 Month 3 7,000 Month 4 8,500 Month 5 6,000 Month 6 2,000