メインコンテンツまでスキップ
Adaptive Insights
Knowledge @ Adaptive Insights LLC, a Workday Company

How To Create A Cube Sheet For Sensitivity Analysis

Question

How can I use a Cube Sheet for Sensitivity Analysis?

Answer

The purpose of this article is to give an example of how to use a Cube Sheet for high-level sensitivity analysis.

Step 1: Create a New Cube Sheet

  1. Navigate to Modeling > Sheets
    →Note: The sheet can be created as sheet available on levels or a user-assigned sheet. Because this sheet is being used for sensitivity analysis, you may wish to create this sheet as a user-assigned sheet. User-assigned sheets are found on the Assumptions tab and are accessible only to specific users as defined by an administrator. In this example, the sheet will be created as a user-assigned sheet.
  2. Select New Sheet.
  3. Select Cube from the drop-down selector next to Create new sheet.
  4. Populate the Sheet name: field with the name of your new sheet, Sensitivity Analysis, and the Account group name: with Sensitivity.
    →Note: The Account group name is used when referring to this sheet in formulas.
  5. Select Next.

Step 2: Create Standard Cube Accounts to Represent the % Change for each P&L Account

  1. From the Summary of Sensitivity Analysis screen, select Edit next to Cube Accounts.
  2. Select New Group.
  3. Populate the Name field with Account Modifiers (% change) and then select Save.
  4. Select the newly created group, Account Modifiers (% change), and then select New Account.
  5. Populate the Account Details for each P&L GL account. Below is an example of how to create the Revenue Account Modifier account.
    →Helpful Hint: After completing Step 3, you may wish to go back and adjust the Time rollup setting to weight the account by it's corresponding calculated account. 
  6. Repeat steps 4 & 5 for each P&L account.

Step 3: Create Calculated Cube Accounts that Mirror the P&L GL Accounts

  1. From the Summary of Sensitivity Analysis screen, select Edit next to Cube Accounts.
  2. Select New Group.
  3. Populate the Name field with Income Statement Accounts and then select Save.
  4. Select the newly created group, Income Statement Accounts, and then select New Account.
  5. Populate the Account Details for each Income Statement GL account. Below is an example of how to create the Revenue cube account.
    • Type - Set to Calculation.
    • Formula – The formula should multiply each P&L account by one plus the corresponding modifier.
      →Note: There are unique restrictions for the formulas that can be created in a Cube Calculation Account. The formulas for a Cube Calculation Accounts are required to always evaluate to zero at a particular location in the cube if all of the Cube's input accounts are empty at that location. If a user tries to enter a formula which would not satisfy that rule, the attempt to save the cube calculation will fail with an error: "Cube Calculation Formulas must evaluate to zero when the cube's other accounts are zero.” The workaround for this restriction would be to condition the Cube Calculation account based on another account in the current cube sheet. For example, you could create a formula that looks something like: 
      if(isblank(ACCT.Sensitivity.Revenue),0,(1+ACCT.Sensitivity.Rev_Modifier)*ACCT.Income[level=this(+)]) 
    • Click here for more information about this restriction. 

       
  6. Repeat step 4 & 5 for each Income Statement account.

Step 4: Create Cube Calculation Accounts for Gross Margin, Gross Margin % and Net Income

You will need to create additional accounts to perform accurate Gross Margin, Gross Margin % and Net Income calculations based on the Cube P&L accounts.

  1. Select New Account and set the Type to Calculation.
  2. Set the Formula accordingly for each account. Here is an example of what each formula should be:
    • Gross Margin - ACCT.Sensitivity.Revenue-ACCT.Sensitivity.COS
    • Gross Margin % - divf(ACCT.Sensitivity.GM,ACCT.Sensitivity.Revenue)
    • Net Income - ACCT.Sensitivity.GM-ACCT.Sensitivity.Total_OpExp-ACCT.Sensitivity.NonOp

Step 5: Create a Dimension for the Different Scenarios

  1. Go to Admin and select Define Dimensions.
  2. Select New Dimension and name it Scenario.
  3. Click Save.
  4. With the newly created Scenario dimension selected, click New Value.
  5. Enter each Scenario you would like to analyze and save. For example, you might create Current Version, Best Case and Worst Case Dimension Values.

Step 6: Make the Sheet Available on a Level, Add the Newly Created Scenario Dimension to the Sensitivity Analysis Cube Sheet  and set Sheet Properties

  1. Go to Admin and select Manage User Sheets - Restricted by User.
  2. Select Edit next to the Sensitivity Analysis sheet.
  3. Select Edit next to Dimensions and Levels.
  4. Under Organization select the Top Level in your Organizational Structure.
  5. From the Custom Dimensions list, drag the Scenario dimension to the right of Time. You may wish to unselect the All and Unategorized Scenario values.
  6. Click the Sheet Properties icon.
  7. From the Initial Settings tab set the Horizontal dropdown to Scenario and the Vertical dropdown to Account.
  8. From the Sheet Accessibility tab, check each user that should have access to the sheet.
  9. Click OK.
  10. Click Save.

Step 7: Populate the % Change Account Modifiers created in Step 2 for their Corresponding Scenario Dimensions

    1. Go to Home and select the newly created Sensitivity Analysis sheet from the Assumptions tab.
    2. Populate data in the % Change Account Modifier accounts.
      →Helpful Hint: To make data entry easier, select the Change Dimensions icon and set the Horizontal dropdown to Time and the Vertical dropdown to Accounts and Scenario. You can use the Copy Forward function to quickly populate the grid.

Your Cube Sheet should now display a high-level sensitivity analysis of your P&L.

  • この記事は役に立ちましたか?