Assignment example: Creating a forecast in Planning Business Modeler

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

In this topic

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 Microsoft Office 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;

Top of Page

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;

Top of Page

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;

Top of Page

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×