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;

See Also

Other Resources

Choose a rule type