You have two options, (1) change the production schedule so that the resulting workload pattern fits within available capacity, and (2) change the capacity
to 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".
For this assignment you may simply post the Excel workbook reflecting
your solution directly to the dropbox. There is no need to copy your
results into a Word document.