Skip to main content
Adaptive Insights
Knowledge and Support - Adaptive Insights

Creating Modeled Sheets

Modeled sheets are designed to take input from planners and contain logic for automatically modeling the results of that input. Planners use the sheets to enter forecasted revenue and expense drivers, such as headcount, capital spending, and sales. These drivers can be tagged with dimensions such as job code, product line, benefits choice, revenue recognition method, or invoicing timing.

Modeled sheets appear with other sheets on Sheets > Overview, or on Modeling > Model Management > Assumptions if the modeled sheet is a user assigned sheet. 

The following topics describe how to manually create a new modeled sheet. You can also create a new modeled sheet by uploading a previously-downloaded modeled sheet. Refer to Upload a New Modeled Sheet.

Start a New Modeled Sheet

To start a new modeled sheet: 

  1. Navigate to: Modeling > Model Management.
  2. Choose one of the following options. 
    • Level Assigned Sheets: Create a level-assigned sheet.
    • User Assigned Sheets: Create a user-assigned sheet. The users you assign will have access to all levels available in the sheet, including access to sensitive information they might not typically see (for example, personnel data). 
  3. Click New Sheet
  4. Enter a Sheet name. The sheet name must be unique for your installation.
  5. Select Create a new sheet > Modeled.
  6. Pick a template for the sheet: Capital, Personnel, Sales, or Blank modeled sheet.  To help you create a new modeled sheet, Adaptive Planning templates for different sheet types. These can be used "as is" or modified to better fit your needs. Refer to Using Sheet Builder Templates for more information.
  7. Enter an Account code prefix.
  8. Click Next.

Add Columns and Levels

This topic describes how add columns and levels to modeled sheets. You can also define column properties, such as renaming the title and setting display options. The Modeled Sheet Builder includes a three-pane interface for defining columns and levels in your modeled sheet. 

  • Elements: (left pane)  Select data entry columns, custom dimensions, and display columns and add them to the sheet.
  • Sheet Canvas: (center pane) Shows columns added to the sheet. In the builder canvas, columns added to the sheet are shown as rows.
  • Properties: (right pane) Set properties for the currently-selected column. For details, refer to Modeled Sheet Builder: Sheet Elements and Column Properties.

Add Data Entry Columns

Data entry columns are data entry points for users working on sheets. For example, Employee ID is typical data entry column for a personnel model sheet. Typically, each data entry column appears as a single column on the sheet. The exception is the timespan element.  If added,timespan must be the last element listed in the sheet builder and is displayed in the Sheet Viewer as a column for each month in the current version  A modeled sheet can contain only one timespan element.

To add data entry columns:

  1. From the Sheet Summary page for a modeled sheet,click Columns and Levels.
  2. In the Elements pane, click Data Entry Columns. A list of data entry types appears: Text, Number, Date, and so on. For more information, refer to Modeled Sheet Element: Data Entry Columns.
  3. Drag the column type you want to add to the center pane. 
  4. In the Properties panes, enter properties for the column.  For details, refer to Modeled Sheet Builder: Sheet Elements and Column Properties.
  5. Click Save .

Add Custom Dimensions

Custom dimension columns are used for selecting a value from a custom dimension. For example, Job Function can be a custom dimension on a personnel sheet, where users can associate a job function to an employee by picking a value. You can add many custom dimensions to a sheet, but cannot duplicate or add the same dimension element twice.   

To add a custom dimension:

  1. From the Sheet Summary page for a modeled sheet,click Columns and Levels.
  2. In the Elements pane, click Dimensions. A list of dimensions appears.
  3. Drag the dimension you want to add to the center pane.  
  4. In the right pane, set General Properties and Dimension Properties for the dimension. For details, refer to Modeled Sheet Builder: Sheet Elements and Column Properties.
  5. Click Save .

Add Attributes

Attributes are logical groupings with lists of possible values. You can use them to tag accounts, dimensions, and levels with those values. For example, an attribute called SEO Reporting might have values of Include and Exclude, and accounts included or excluded in SEO reporting could be tagged appropriately. Use Attribute columns to display dimension attribute values.

To add an attribute:

  1. From the Sheet Summary page for a modeled sheet,click Columns and Levels.
  2. In the Elements pane, click Attributes. A list of attributes appears. You can navigate to find an attribute or Search for attributes.
  3. Drag the attribute you want to add to the center pane. Adding an attribute automatically adds that attribute's dimension if it is not already present.
  4. In the right pane, set attribute Properties. Select Enable as filter for dimension to make this attribute available in filters.
  5. Click Save .

If you add level attributes as columns in a modeled sheet, end-users can filter the modeled sheet rows based on the level attribute they're interested in. For example, an end-user could see the total of all salaries for the Northern Region, which is a level attribute value. 

Add Display Columns

Display columns show a single calculated value for information purposes (for example, personnel start month or total headcount). A display column element is always read-only. You can place multiple display columns on a sheet, with different or identical settings. 

To add a display column:

  1. From the Sheet Summary page for a modeled sheet,click Columns and Levels.
  2. In the Elements pane, click Display Columns. A list of display columns appears. 

  3. Drag the display column you want to add to the center pane. 

  4. In the right pane, set General Properties and Display Properties for the display column. For details, refer to Modeled Sheet Builder: Sheet Elements and Column Properties.
  1. Click Save .

Manage Modeled Sheet Elements and Set Sheet Properties

To make changes to a modeled sheet, you can:  

  • Use the Up/Down arrow keys  to rearrange elements on the modeled sheet. 
  • To delete an element from the sheet, select the element and click Delete .
  • To set sheet properties, click the gear icon .  Properties you can set include:
    • Change the sheet Name and Description.
    • Restrict access to salary information. Only allow users with Salary Detail permission to view salary data. 
    • Set the number of columns to freeze: Specifies the number columns to the left that cannot scroll horizontally.
    • Select column to set as the row key (advanced): Specifies the text columns that will display as the label for rows when individual rows are viewed in reports or in the Cell Explorer. Multiple options can be selected. On a personnel sheet, use this option to prevent access to row-level salary detail in formulas. 
      • Allow splits: Allows the rows to have splits. Once enabled, the individual columns on the sheet can be marked as allowing splits, which allows end users to create allocation "splits" of a row on a sheet and enter different values for columns that can be split.
      • Allow editing when viewing rollup levels: Allows users to make changes to the sheet to view rollup levels.
      • Make sheet available in Actuals version: Makes the modeled sheet available in the Actuals version. 
      • Recalculate on demand: Holds all calculations until requested. This setting is useful for large spreadsheets with long calculation times that can interrupt work-in-progress. 

Create a Modeled Sheet Account

Like cube sheets, modeled sheets can have accounts associated with them. The types of account associated with modeled sheets are:  

  • Calculated Accounts: Calculated accounts hold a formula you use to run calculations on data from elsewhere in your model. For example, you may use a calculated account to spread the values of another account over a period of time. The user does not directly enter any values. 
  • Assumption Accounts: Assumption (Modeled) accounts hold the values calculated by the sheet. These accounts are associated with a timespan, initial values, and calculated accounts. Assumption accounts can be referenced in formulas within the modeled sheet. They are also available to users building formulas outside of the modeled sheet. (This is how modeled accounts are linked to GL accounts.)

Create a new Calculated account

A calculated account does not allow data entry by users; it either evaluates a formula in each time slot or spreads another account in the model. Calculated accounts are computed by the model on a row-by-row basis and are summarized when they are referenced by formulas outside of the model. Calculated accounts must contain a modeled sheet formula.

  1. From the Sheet Summary page for a modeled sheet,click Modeled Accounts to open the account builder.
  2. Click the Calculated Account icon  on the toolbar. 

  3. In <New Account>  > Account Details (on the right) enter a Code and Name for the account. Short Name and Description are optional.

  4. Select an account Type. Options include:

  • Periodic: The amount entered in each month represents activity in the month.

  • Cumulative: The amount entered in each month represents the balance in the account at the end of the month.

  • Spread: The amounts in the source account (chosen below) are spread across multiple time periods in the calculated account. For spread accounts, also specify:

    • Source Account: the account to serve as the source for the spread account.

    • Spread using: Select a spread lookup to define how the spread account will distribute values from the source account.  

  1. In Account Attributes, select attributes, if needed.  

  2. In Data Type, specify the following: 

  • Display As: (Periodic and Cumulative only) Select Number, Currency or Percent to control how you want the account to be displayed on sheets and reports.

  • Decimal Places and Exchange Rate: How many decimal places the account’s values should have on sheets and reports. For currency, choose an Exchange Rate.

  • Formula: (Periodic and Cumulative only). Enter a formula to run calculations for the account. You can use the Formula Assistant to help you create Modeled Sheet Formulas

  1. Set Data Privacy for the account. 

  2. Click Save .

Create New Assumption Accounts

Assumption (Modeled) accounts hold the values calculated by the sheet. These accounts are associated with a timespan, initial values, and calculated accounts. Assumption accounts can be referenced in formulas within the modeled sheet. They are also available to users building formulas outside of the modeled sheet. You create and name a separate account for each assumption. For example: HourlyPersonnel.RaisePct, HourlyPersonnel.OvertimePayMultiplier, and so on.

  1. From the Sheet Summary page for a modeled sheet,click Modeled Accounts to open the account builder.
  2. Click the Assumption Account   on the toolbar.

  3. In <New Account>  > Account Details (on the right) enter a Code  and Name for the account. Short Name and Description are optional.

  4. In the Data Type accordion, select to Display As number or percent.

  5. In Data Type, specify the following: 

    • Display As: Select Number, or Percent to control how you want the account to be displayed on sheets and reports.

    • Decimal Places: How many decimal places the account’s values should have on sheets and reports.

  6. Click Save .

Once you create an assumption account, an Assumptions link appears on the Sheet Summary. You're ready to create assumptions for the sheet.

Create Assumptions Used in the Modeled Sheet

Sheet assumptions are not computed by the model on a row-by-row basis. Assumptions in a modeled sheet behave just like global assumptions, but can only be used from within the modeled sheet. They cannot be referenced outside of the sheet and are not displayed on the Modeling > Model Management > Assumptions. An example of a sheet assumption is a base "raise percentage" on a personnel sheet. 

  1. From Sheet Summary for a modeled sheet, click Assumptions. This link is only visible if you created an assumptions account for this modeled sheet.
  2. A sheet appears with a list of assumptions. Click in a cell to enter a value or formula. Use the sheet assumptions toolbar to make changes as desired.

  3. When you've finished entering values and formulas, click Save .

Sheet Assumptions Toolbar

Icon Effect
Launch the Formula Assistant
Save the assumptions
Click to copy formulas or values
Set the viewable time range and column display options
Launch the cell explorer to drill into the data.
Download a printable, Microsoft Excel spreadsheet of the assumptions.
Clear all changes since the last Save
Refresh the sheet with the latest updates.

Add Value Lookups and Spread Lookups

You can add lookups to custom Dimensions and Text Selectors.  Lookups include:

  • Value lookups: These lookups translate a selection from a row of the sheet into time-based values. For example, benefits choices (HMO, PPO, and so on) may be associated with appropriate percentages. These percentages can then be multiplied by salaries to calculate benefits expense. The benefits choices and their percentages reside in a value lookup. 
  • Spread lookups: These lookups spread the values in one account over subsequent time periods of another account. Translates a selection from a row of the sheet into values spread over time. You can then use these spread values to generate values into one account based on another account. For example, you may use a depreciation method to spread values into the depreciation expense account, based on a capital spending account.  

To add a lookup:

1. From Sheet Summary,  select a custom Dimension or Text Selector (center pane).  

2. In the Properties pane, open the expander for the type of lookup you want to add:

3. Click Add new lookup

4. Enter a Name for the lookup and click OK

5. Click Save . You must save the sheet before you can add values to the new lookup. 

6. Click Edit .

7. Enter the lookup values. Refer to Editing Value Lookups  and Editing Spread Lookups for more information. 

8. Click Save .

To view or edit lookups: 

  1. From Sheet Summary for a modeled sheet, click the type of lookup you want to view. These links are only visible if you added a custom Dimension or Text Selector that supports lookups. 
    • Spread Lookups
    • Value Lookups
  2. Make changes to the lookup as desired. Refer to Editing Value Lookups  and Editing Spread Lookups for more information. 
  3. Click Save .

Upload a New Modeled Sheet

If you previously downloaded a modeled sheet you can upload it as an entirely new sheet if the sheet does not already exist. This process is useful for importing a modeled sheet from a sandbox or development instance without having to manually recreate it.

  1. Navigate to: Modeling > Model Management
  2. Click Level Assigned Sheets or User Assigned Sheets.
  3. Click Upload New Modeled Sheet.
  4. Browse to the XML file of a modeled sheet you previously downloaded to your computer.
  5. Click Upload.
  • Was this article helpful?