Concept: Modeled Sheet Building

Modeled sheets take input from planners and automatically model the results of that input. Modeled sheets are best for modeling data based on records. The most common uses for modeled sheets are:

Capital planning so that you can list each asset and relevant information about the asset.

Personnel planning so that you can list employees or jobs.

How Modeled Sheets Work

A modeled sheet contains rows of records with columns of data. You enter data or select data from the columns for a record. You do not enter data for the accounts. Instead, the sheet calculates the account values based on the formulas you define in the account settings. The formulas in modeled sheet accounts evaluate:

The data you enter in the columns.

The lookups you add to the columns.

The assumptions you create for the sheet.

Formulas you add to the calculated accounts.

Options for Building Modeled Sheets

Choose from the different ways to build modeled sheets:

Use the Capital
, Personnel
, or Sales
templates to create a modeled sheet with built in logic and calculations. You can then edit the sheet.

Upload a modeled sheet. You must first download an existing sheet. See Upload Modeled Sheets.

Clone modeled sheets. You must have a modeled sheet to clone.

Start from scratch and build your own sheet.

Modeled Sheet Column Types

Data entry columns
enable users to enter data into cells when they open the sheet. Each data entry column has a code. You can reference this code in the sheet's formula-driven accounts. You can also add spread or value lookups to columns that affect the calculation when formulas reference the column. You cannot reference data entry columns from outside the modeled sheet.

Custom Dimension
and Attribute
columns place existing custom dimensions and attributes on modeled sheets. Users can use these to filter the sheet or as part of their input data. Custom dimensions can also contain lookups that affect data calculations.

Display columns
are read-only displays of values that the sheet calculates.

Levels
are a column by default. Because levels are required, you can't remove them. Choose the levels you want available on the sheet and then manage the settings.

Modeled Sheet Account Types

Calculated accounts
use formulas to calculate their own value based on the column data and lookups.

Assumption accounts
are periodic accounts that drive data. Only admins can enter the data in the assumption accounts from the Sheet Summary page.

Initial balance accounts
are automatically created if you add an

Initial Balance

data entry column. Initial balances seed cumulative accounts.

Timespan
is automatically created if you add a Timespan data entry column to the sheet. Timespans enable users to enter data across the periods of your model. Example: Jan 2023, Feb 2023, and so on.

Modeled Sheet Lookups

You add lookups to the data entry columns that are text selectors, or drop-downs and dimension columns. Lookups provide logic based on the selections you make from the drop-downs in the sheet when you're entering data. You can then create formulas for the calculated accounts that reference those columns.

Types of lookups:

Value lookups translate a selection from a row into time-based values. Exanple: You can associate benefits choices (HMO, PPO, and so on) with appropriate percentages. Next, you can multiple the percentages by salaries to calculate benefits expense. The benefits choices and their percentages reside in a value lookup.

Spread lookups spread the values in one account over subsequent time periods of another account. A selection from a row translates into values spread over time. Example: Use a depreciation method to spread values into the depreciation expense account, based on a capital spending account.

Maintaining Modeled Sheets

To change or update the structure of modeled sheets, go to the

Modeling

area. From there, you can get to the

Sheet Summary

when you edit the sheet. The

Sheet Summary

displays when you first start creating sheets and when you edit sheets. The summary provides these links:

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.

Data Validation Rules

: Set up requirements for user-entered data on sheet cells.

Accessibility

(user-assigned sheets only): Add or remove users who can view and edit the sheet.

These additional links display when they exist on the sheet already:

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 Add Spread Lookups to Modeled Sheets.

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 Add Value Lookups to Modeled Sheets.

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.

Managing the Usability and Performance

Modeled sheets can hold many records and columns, which can feel cumbersome to your endusers. Adaptive Planning provides several options to help you improve the usability and the performance. See:

Check Sheet Metrics.

Reference: Performance and Usability of Modeled Sheets.

How Modeled Sheets Look in the Sheets Area

Option	Description
1	Add Row and Delete Row buttons display in addition to, or instead of, the add, delete, rename splits buttons.
2	Labeled columns exist across the top and records list down the rows. The column types determine how you enter data. You can type in fields, or select from drop-downs. There are no row headers, only column headers.

You can enter new information into existing rows or add new rows. Your admin can set a maximum number of rows a modeled sheet can display at once. If the sheet has a maximum, a message states how many rows of the total you can see. Use the filters to uncover different sets of data.