Assignment example: Creating a forecast
This topic shows three examples in which fact data is used to drive a forecast. The examples are based on the Alpine Ski House sample application that is included with Microsoft Office PerformancePoint Server 2007.
Forecast sales based on an assumption
Forecast operating expenses based on actual expense
Forecast interest expense based on actual expense
Forecast sales based on an assumption
This example shows how a forecast might use a Revenue Assumptions model to create forecasts. This example presents tables that show assumption data and a hypothetical forecast. Then, the example presents business rules written in PerformancePoint Expression Language that perform a sales revenue forecast of resort rooms for a set time.
In this example, the data for room rates and availability is associated with the Revenue Assumptions model, not the operational model. The Revenue Assumption model links to an operational model.
Table 1 : Revenue Assumptions
The following table shows revenue assumptions for this scenario. The assumptions create a price and availability list.
Product Resort Entity Price/Unit Units Standard room
Tahoe
346
211
View room
Tahoe
406
196
Premium room
Tahoe
450
140
Table 2 - Revenue Model Values
The following table shows a sales forecast based on the assumptions in Table 1. The business driver calculation in the Forecast_Sales cells would resemble this pseudo-formula:
Forecast_Sales = ((Units * Occ_Rate) * Price/Unit)* 30 days.
Product Resort Entity Month Occ_Rate Forecast_Sales Standard room
Tahoe
Jan
90%
65,705
View room
Tahoe
Jan
87%
69,231
Premium room
Tahoe
Jan
80%
50,400
Standard room
Tahoe
Feb
92%
67,166
View room
Tahoe
Feb
87%
69,231
Premium room
Tahoe
Feb
77%
49,510
Standard room
Tahoe
Mar
87%
63,515
View room
Tahoe
Mar
83%
66,048
Premium room
Tahoe
Mar
75%
47,250
The following example shows the business rule that calculates the sales forecast.
// Scope specifies members of dimensions for the model SCOPE ( [Account].[Corporate].[Revenue], [BusinessDriver].[All Members].[NONE], [BusinessProcess].[Standard].[INPUT], [Currency].[All Members].[USD], [Entity].[ResortOps].[Tahoe], // LeafMembers function returns all members at same level [Product].[ResortProducts].[Single Room].LeafMembers, [Scenario].[All Members].[Forecast], [Time].[Monthly].[Month 4 Year 2004]:[Time].[Monthly].[Month 6 Year // // Link the Revenue Assumptions model for room rates etc THIS = ( MODEL([Revenue Assumptions]), [Account].[Corporate].[Room Units] ) * ( [Product].[ResortProducts].[Single Room], [Account].[Corporate].[Occupancy Rate] ) * ( MODEL([Revenue Assumptions]), [Account].[Corporate].[Price/Unit] ) * 30; END SCOPE;
Forecast operating expenses based on actual expense
The following example shows a rule that forecasts operating expenses for a single subsidiary, specified in the Entity dimension. The rule forecasts the last three months of the year based on the average of the actual operating expenses for the first nine months of the year.
SCOPE (
[Account].[Corporate].[Operating Expense].LeafMembers,
[BusinessDriver].[All Members].[NONE],
[BusinessProcess].[Standard].[Input],
[Currency].[All Members].[USD],
[Entity].[SubsidiaryOperation].[MySubsidiary],
[ParentEntity].[All Members].[None],
[Product].[SubsidiaryProducts].[NONE],
[Scenario].[All Members].[Forecast],
[Time].[Monthly].[Month 10 Year 2004]:[Time].[Monthly].[Month 12 Year 2004],
[Version].[All Members].[NONE]
);
/* Avg function calculates average actual operating expenses over 9 months */
THIS = Avg(
[Time].[Monthly].[Month 1 Year 2004]:[Time].[Monthly].[Month 9 Year 2004],
[Scenario].[All Members].[Actual]
);
END SCOPE;
Forecast interest expense based on actual expense
The next example shows a rule that forecasts the interest expense for a single resort, and is very similar to the previous rule. The only difference is that the Interest Expense member is specified for the Account dimension. The rule forecasts the last three months of the year based on the average of the actual interest expense for the first nine months of the year.
// Scope Statement
SCOPE (
// Account dimension specifies Interest Expense
[Account].[Corporate].[Interest Expense],
[BusinessDriver].[All Members].[NONE],
[BusinessProcess].[Standard].[Input],
[Currency].[All Members].[USD],
[Entity].[ResortOps].[Tahoe],
[ParentEntity].[All Members].[None],
[Product].[ResortProducts].[NONE],
[Scenario].[All Members].[Forecast],
[Time].[Monthly].[Month 10 Year 2004]:[Time].[Monthly].[Month 12 Year 2004],
[Version].[All Members].[NONE]
);
/* Avg function computes average actual expense over 9 months of previous year. */
THIS = Avg(
[Time].[Monthly].[Month 1 Year 2004]:[Time].[Monthly].[Month 9 Year 2004],
[Scenario].[All Members].[Actual]
);
END SCOPE;