For this assignment you will attempt to refine the solution to the aggregate planning problem presented in Commentary 1. Start with the Excel workbook linked below, which currently reflects the "chase demand" solution from the Commentary 1 video. Your task is to attempt to minimize the total cost of the plan, while ensuring that your plan is feasible.

Aggregate Planning Workbook Chase Demand (linked workbook)

You have two options, (1) change the production schedule so that the resulting workload pattern fits within available capacity, and (2) change the capacityto match the workload implied by the production schedule. The production schedule is changed in rows 12 - 15 of the spreadsheet, while capacity is varied by changing the number of shifts in rows 35. You can only schedule integer values (1, 2, or 3) of shifts. Your solution could involve a combination of these two approaches.

Your goal is to minimize the total cost reported in cell A86. Recall that the spreadsheet reflects inventory carrying costs (which increase as we try to level the workload through forward production), capacity costs (which are higher when capacity is higher), capacity change costs (which we incur whenever we change capacity), as well as the costs of lost sales that result if the schedule fails to satisfy demand.

Remember, however, that your production schedule must be feasible, meaning the workload must fit within available capacity during every period. The spreadsheets represent "infinite capacity" scheduling (they let us enter any production values we want, even if capacity is exceeded) which means it is up to you to check the workload vs. capacity charts, to be sure none of the "blue bars" extend above any of the "red lines".

COST SECTION COST