Export (0) Print
Expand All

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

Show:
© 2014 Microsoft