Using Nested Functions

One planner has created a demand forecast. It is the job of a second planner to arrive at a final forecast adjustment figure. The second planner has a data view containing monthly forecast adjustment figures from two departments, say Marketing & Production. In the example, row 1 represents the production adjustments, row 2 the marketing adjustments, and row 3 the final forecast adjustments. He wants to take a conservative estimate for the final forecast adjustments and smooth this figure at the same time. By using this macro he does the following:

1. Calculates rolling quarterly figures for each month in both rows
2. Calculates a mean of the quarterly figure in each month
3. In each month, writes the greater of the two values to the final forecast adjustment figure

Example: Demand Manager's Data View

 Jan 2002 Feb 2002 Mar 2002 Apr 2002 May 2002 Production Adjustments 10 15 20 85 45 Marketing Adjustments 20 25 30 35 40 Final Forecast Adjustments 25 40 50

In the example above, the first iteration would be:

1. Production Adjustments Row: 10 + 15 + 20 = 45
Marketing Adjustments Row: 20 + 25 + 30 = 75
2. Monthly mean for February: Production Adjustments Row
= 45/3
= 15
Monthly mean for February: Marketing Adjustments Row
= 75/3
= 25
3. 15 < 25 Final Forecast Adjustment for February = 25.