Spreadsheet

Pigskin Company produces footballs. Pigskin must decide how many footballs to produce each month. The company has decided to use a six$-$month planning horizon. The forecasted monthly demands for the next six months are $10,000,15,000,30,000,35,000,25,000,$ and $10,000.$ Pigskin wants to meet these demands on time, knowing that it currently has $5000$ footballs in inventory and that it can use a given month's production to help meet the demand for that month. $($For simplicity, we assume that production occurs during the month, and demand occurs at the end of the 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 occurred. The forecasted production costs per football for the next six months are $$12\mathrm{.}50,$$$12\mathrm{.}55,$$$12\mathrm{.}70,$$$12\mathrm{.}80,$$$12\mathrm{.}85,$ and $$12\mathrm{.}95,$ respectively. The holding cost per football held in inventory at the end of any month is figured at $5\%$ of the production cost for that month. $($This cost includes the cost of storage and the cost of money tied up in inventory.$)$ The selling price for footballs is not considered relevant to the production decision because Pigskin will satisfy all customer demand exactly when it occurs$-$at whatever the selling price is$.$ In other words, total revenue for the planning horizon is fixed, regardless of production decisions.

In one modification of the Pigskin model, the maximum storage constraint and the holding cost are based on the average inventory $($not ending inventory$)$ for a given month, where the average inventory is defined as the sum of beginning inventory and ending inventory, divided by $2,$ and beginning inventory is before production or demand. Modify the Pigskin model with this new assumption, and use Solver to find the optimal solution. Use the given data file P$12\_21.$xlsx$.$ How does this change the optimal production schedule? Round your answers to nearest whole number.

$\backslash$table$[[$Month$,$Original Schedule,Modified Schedule$],[1,62500,],[2,,],[3,,],[4,,],[5,,],[6,,]]$

How does it change the optimal total cost? Round your answer to two decimal places. Enter your answer as a positive number.

The holding cost is slightly $$ than for the original model with the difference of $ $$