Aggregate planning in Union brewery

Instructions:

In Union brewery they are facing a common problem of matching their production capacities with seasonal demand. They have decided to tackle the problem by determining the best way to align their production levels with the changing demand. Therefore they are looking for a production strategy, which would result in the lowest costs. As demand forecast is one of the main inputs to the aggregate planning process, they have first tackled this problem.

1) Analyze the past sales data given in Table 1 and try to come up with the monthly demand forecast for year 2014. ). When discussing the possible aggregate planning strategies they have determined that the viable strategies for the company are the ones where they can influence the number of workers and use the inventories to smooth out the variability in demand. To get a general feeling for which aggregate planning strategy would work best in their case they have elected to first test the two characteristic strategies: chase and level strategy.

2) Based on your demand forecast for year 2014 and the production parameters given in Table 2 determine the costs assoB?G?ed with the two characteristic strategies: chose strategy (production capacity is aligned with the demand by changing the number of workers and using only regular work in this case) and level strategy (production capacity will be approximately equal to average monthly demand and you will be using a strategy of prebuilding inventory to cope with high the season). Within both strategies do not forget to hold adequate safety stocks, which will enable you to cope with the demand uncertainty. Derive the table with cost calculations for both options2 and also present the strategy in a graphical form, where you will plot both the demand and production levels through the year 2014.

As they are uncertain about their accuracy of assessing the inventory holding cost, the company also wants to do the sensitivity analysis. They want to see at what inventory holding cost the costs of a chase strategy is equal to the cost of a level strategy.

3) Copy the sheet with your results from question 2 to a new sheet and find out at which level of inventory holding costs the two strategies have the same costs. Do this by varying inventory holding cost per unit (starting value is 2 EUR/unit/month). Try it with either Goal Seek or Solver tool within Excel. If you have trouble using either of the tools you can also do it by hand (trying out different values).

Now the company wants to find the optimal mixed strategy, which would use both options: varying the number of workers as well as inventories at the same time.

4) Derive a table where you can use both options in your aggregate planning strategy. Setup the problem as a linear programming problem with Solver and use it to determine the optimal mixed strategy. Obviously you will try to find the lowest total costs and you will have to enforce some constraints (inventories/production levels in each period will have to be sufficient to cover the demand and safety stock, assume integer values for the number of workers.)