Question $1(14$ points$)$

Aggregate Planning

Given the projected demands for the next six months, prepare an aggregate plan that uses inventory, regular time,

overtime, subcontract and backorders. Regular time is limited to $150$ units per month $($Cost per Unit $=$$$50.$ Overtime

is limited to a maximum of $20$ units per month $($Cost per Unit $=$$$75.$ Units purchased from the subcontractor $($Cost per

Unit $=$$$90)$ cannot exceed $70$ per month and the total purchases from the subcontractor over the $6$ month period

cannot be over $220$ units. Backorders cannot exceed $40$ units in any given month $($Cost per Unit $=$$$1)$ and must be no

more than $20$ in Period $6.$ Average Inventory Holding cost per Unit $=$$$4.$ Forecasted Demand as well as Beginning and

desired Ending Inventory are listed in the table below.

Required:

Find the Minimum Cost Production Plan by Creating a Spreadsheet in Excel. Use Solver to find the Minimum Cost

Solution. Leave a copy of your Spreadsheet in the DropBox.

Total Cost Month $1=$

$,10050$

Total Cost Month $2=$

$,15400$

Total Cost Month $3=$

Total Cost Month $4=$

Total Cost Month $5=$

Hint: Range

Total Cost Month $6=$

Total Cost All Periods $=$

$,72420)$

Answer Format: No Dollar $($$$)$ signs or commas $---$ Answers should be whole numbers.