Reference no: EM131898615

The Pigskin Company produces footballs. Pigskin must decide how many footballs to produce each month. The company has decided to use a 6-month planning horizon. The forecasted demands for the next 6 months are in the following table:

Month	1	2	3	4	5	6
Demand	10,000	15,000	30,000	35,000	25,000	10,000

Pigskin wants to meet these demands on time, knowing that it currently has 5,000 footballs in inventory and that it can use a given month's production to help meet the demand for that month.

(For simplicity, assume that production occurs during the month, and demand occurs at the end of month.) During each month, there is enough production capacity to produce up to 30,000 footballs, and there is enough storage capacity to store up to 10,000 footballs at the end of the month, after demand has occurs. The forecasted production costs per football for the next 6 months are given below:

Month	1	2	3	4	5	6
Unit Production Cost	$12.50	$12.55	$12.70	$12.80	$12.85	$12.95

The holding cost per football held in inventory at the end of any month is figured at 5% of the unit production cost for that month. (This cost includes the cost of storage and also the cost of money tied up in inventory.)

The selling price for footballs is not considered relevant to the production decision because Pigskin plans to satisfy all customer demand exactly when it occurs - at whatever the selling price is. Therefore, Pigskin wants to determine the production schedule that minimizes the total production and inventory holding costs.

An important feature of this type of production scheduling problem is that there is a so-called balance constraint for each month j of the planning horizon in form of

(inventoy held at the end of the month j-1) + (production quantity in month j) = (forecasted demand for month j) + (inventory held at the end of month j)

Questions:

1. Develop a linear programming spreadsheet model, where the decision variables are the footballs produced and the inventory held in each month, to solve the Pigskin's football production problem. Interpret the optimal solution that is solved by Excel Solver: What are the optimal production plan and inventory holding plan for the coming 6-months? What is the minimum total cost for production and holding inventory?

2. Would more ending inventory be carried if the holding cost percentage were lower? Or would even less be carried if it were higher? Check this with the one-way SolverTable output. The output we track should be the maximum ending inventory ever held, which can be calculated with the formula:

 "=MAX(ending_inventory_row)"

3. In reality, the company will probably implement the model's recommendation only for the first month. Then at the beginning of the second month, it will gather new forecasts for the next 6 months, months 2 through 7, solve a new 6-month model, and again implement the model's recommendation for the first of these months, month 2.

If the company continues in this manner, it is using a 6-month rolling planning horizon. The question, then, is whether the assumed demands (really, forecasts) toward the end of the planning horizon have much effect on the optimal production quantity in month 1.

We hope not, because these forecasts could be quite inaccurate.

Use the two-way SolverTable to show how the optimal month 1 production quantity varies with the forecasted demands in months 5 and 6.