Skip to main content

logoCorrectSize.png

Adaptive Insights LLC.

Build Modeled Sheets from Scratch

Explains how to build a modeled sheet from scratch, including guidance on the columns, accounts, dimensions, and lookups.

Once you start a modeled sheet, you build and edit its components from the Sheet Summary. To get to the Sheet Summary, select the Edit link next to any modeled sheet in the sheet list.

The Sheet Summary shows links. Go to each link to finish building or editing the sheet:

  • Modeled Accounts: Create, edit, and delete assumption and calculated accounts, including settings, formulas, names, and codes.
  • Columns and Levels: Manage the columns visible on the sheet, levels assigned to the sheet, lookups per column, and the sheet settings.
  • Accessibility (user-assigned sheets only): Add or remove users who can view and edit the sheet.

Additional links appear after you're finished building the sheet if relevant to the sheet:

  • Spread Lookups: Visible after you add spread lookups to dimension columns or text selector columns. Select this link to edit the expression and the spread. See Spread Lookups.
  • Value Lookups: Visible after you add value lookups to dimension columns or text selector columns. Select this link to enter or edit the values in a lookup sheet. See Value Lookups.
  • Assumptions: Visible after you create assumption accounts. Select this link to opens a sheet with all the assumption accounts. Edit or enter the values for the assumption accounts per time period. See the section Enter Assumption Account Data below.

Before You Begin

  • Required permission: Model Management Access > Model.
  • Create any dimensions, attributes, and levels you plan to add to the sheet.
  • New to modeled sheets? See Modeled Sheet Overview to get started.

Building modeled sheets from scratch is one way to build a modeled sheet. You can also clone modeled sheets, upload modeled sheets, or use the modeled sheet templates

How You Get There

Compass.png From the nav menu, select Modeling. In the Sheets menu, select User Assigned Sheets or Level Assigned Sheets to view the list of sheets in your model.

Basic Steps to Build Modeled Sheets from Scratch

  1. Start a new sheet. 
  2. Assign users.
    Required for user-assigned sheets. 
  3. Assign levels to the sheet. 
    Required for both user-assigned sheets and level-assigned sheets.
  4. Add data entry columns.
    You can also add the columns for dimensions and attributes.
  5. Create calculated and assumption accounts.
  6. Optional. Add display columns.
  7. Update the Sheet Settings.
  8. Save. 

Depending on the sheet components you created, when you are finished building the sheet:

  • Enter data in the assumptions sheet.
  • Define the expression of spread lookups. 
  • Enter values in the value lookup sheets.

Start a New Sheet

  1. From the Sheets list, select New Sheet
  2. Enter a unique sheet name.
  3. Select the Create new sheet radio button and choose Modeled from the drop-down. 
  4. From the second drop-down, select Blank modeled sheet (advanced) 
  5. Enter an account code prefix to identify the sheet. Every account code on the sheet automatically includes this prefix.
  6. Select Next.

Assign Users

For user-assigned sheets only. Skip to the next section if you're building a level-assigned sheet. 

  1. From the sheet summary, select Accessibility
  2. From the Available Users box, select users.
  3. Select the forward arrow to add them to the Selected Users box. 
  4. Save.

Users assigned to sheets can edit all the data and dimensions, regardless of their level ownership. See Assign Level Owners for more information.

To open the sheet, users require the Access Salary Detail permission if the salary detail sheet setting is active. See the Update Sheet Properties section in this article. 

Assign Levels

If you're building a level-assigned sheet, this step assigns the levels to the sheet, which controls access to the sheets and the sheet's data.  If you're building a user-assigned sheet, this step adds the relevant levels to the sheet for data entry at each level. Users assigned to user-assigned sheets can see and edit all the data at all the levels on the sheet.

See Add or Edit Modeled Sheet Columns to get familiar with the interface and types of columns.

  1. From the sheet summary, select Columns and Levels
  2. From the canvas, select the Level row. When you build modeled sheets from scratch, Level is the only row in the canvas.
  3. In the settings:
    • If you want the sheet to display a different label for the level column, change the name in the Name field.
    • If you are going to import data into the sheet, make the column read-only to prevent users from changing the levels.
  4. Select the checkboxes next to the levels you want to add to the sheet. 
  5. Select Save from the canvas toolbar. 

Add Data Entry Columns

Columns are what you see on the sheet. There are several kinds of columns: Data Entry Columns, Dimensions, Attributes, and Display Columns. Add the data entry columns before you create the accounts because you need to reference the column codes in the formulas. You can also add the dimension and attribute columns in this step.

To add a data entry column:

  1. From the sheet summary, select Columns and Levels
  2. Expand Data Entry Columns.
  3. Choose the type of data entry column you want from the list and drag and drop it into the canvas. Each type has a different function on the sheet. See Add or Edit Modeled Sheet Columns 
  4. Edit the properties. See Modeled Sheet Column Settings.
  5. Save.

Create Modeled Accounts

To create calculated and assumption accounts:

  1. From the sheet summary, select Modeled Accounts
  2. Select  for calculated accounts, and  for assumption accounts. 
  3. Enter a code and name for the account. 
  4. For calculated accounts, complete these settings:
    1. Select a Type:
      • 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 of the selected source account are spread across multiple time periods in the calculated account. If you select Spread, these drop-downs appear:
      • Source Account: Select one of the modeled accounts to serve as the source.
      • Spread using: Select a spread lookup to define how the spread account will distribute values from the source account. You created the spreads in the data entry columns. 
    2. Periodic and Cumulative only. Enter a Formula. You can use the Formula Assistant to help you create Modeled Sheet Formulas
  5. For Display As, select Number, Currency or Percent to control how you want the account to display on sheets and reports. Spread accounts always display as currency.
  6. Select an option for Data privacy. 
  7. Save.

See Accounts for more information. 

Add Display Columns

Display columns include the level currency and all the accounts you created. These columns display read-only data when you open the sheet. 

  1. From the sheet summary, select Columns and Levels.
  2. Expand Display Columns to see the list of the accounts you created.
  3. Drag and drop them into the canvas. See Add or Edit Modeled Sheet Columns.
  4. Manage the properties. See Modeled Sheet Column Settings.
  5. Save.

Update Modeled Sheet Properties

To set sheet properties:

  1. From the sheet summary, select Columns and Levels
  2. From the canvas toolbar, select the gear icon 
  3. Select the tabs to review and edit the settings. Select OK. See Setting Options below.
  4. Select Save from the canvas toolbar.

Setting Options

Options Description

Details Tab

 
Name Rename the sheet.
Description Add  a sheet description.
Code Prefix Information only. The sheet code which is the prefix for all the account codes on the sheet.

Security Tab

 

Restricts access to salary information. If selected, only users with Access Salary Detail permission can open the sheet. 

Settings Tab

 
Set the number of columns to freeze Specifies the number of columns to the left that cannot scroll horizontally.
Select column to set as the row key (advanced) Applies text columns only. Select the text columns to display as the records for rows.  You can select multiple options. 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, you can flag the individual columns on the sheet as allowing splits.  Users can create allocation "splits" of a row and enter different values for columns that can be split.
Allow editing when viewing rollup levels Allows users to add records at the rollup levels.
Make sheet available in Actuals version Makes the modeled sheet available in the Actuals version. 
Recalculate on demand Available with the Manual Recalculation capability. Holds all calculations until requested. This setting is useful for large spreadsheets with long calculation times that can interrupt work-in-progress. See Manual Recalculation
Select the time stratum associated with this modeled sheet Available if the sheet has a timespan column. Select the time periods the timespan displays. Typical options include, month, quarter, and year, depending on your calendar setup.

Enter Assumption Account Data

Assumption accounts appear as read-only cells on the modeled sheet. Their data drives other data in the calculated accounts. To enter data into the assumption accounts: 

  1. From the sheet summary, select the Assumptions link. You only see this link if the sheet has assumption accounts. 
  2. The sheet lists all the assumption accounts down the rows and time periods across the column. 
  3. Enter data and save the sheet. 

Create Value Lookups and Spread Lookups

You can add lookups to Dimension columns and Text Selector data entry columns. To add a lookup:

  1. From the sheet summary, select Columns and Levels.  
  2. Select a custom dimension or a text selector from the canvas. Refer to the Type column to see what kind of data entry column it is.
  3. From the general properties, select Value Lookups or Spread Lookups
  4. Select Add new lookup
  5. Enter a Name for the lookup and select OK
  6. Save the sheet before you can add values or spread information. 
  7. Return to the column with the new lookup.
  8. Next to the name of the lookup, select Edit .
    • For spread lookups, enter the number of months and the spread expression.
    • For value lookups, you see a sheet that lists the text selector or dimension options down the rows. Enter the values for each time period and option. Save the sheet. 

Once you add lookups to the columns, you can access them directly from the sheet summary for edits.

  • Was this article helpful?