Modeled sheets take input from planners and automatically model the results of that input.
1 Add Row and Delete Row buttons in addition to, or instead of, the add, delete, rename splits buttons. See Add Splits and Rows.
2 Labeled columns across the top and records listed down the rows. The column types determine how data is entered: either by typing in fields, or selecting from drop-downs. There are no row headers, only column headers.
Enter new information into existing rows or add new rows. Your admin may set a maximum number of rows a modeled sheet can display at once. If this is the case, you see a message stating how many rows you're seeing of the total. Use the filters, to uncover different sets of data.
Build Modeled Sheets
Choose from the different ways to build a modeled sheet:
- Modeled Sheet Templates. 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.
- Clone modeled sheets.
- Start from scratch and build your own sheet.
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.
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. Some columns have spread or value lookups 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.
- Timespan is automatically created if you add a Timespan data entry column to the sheet.
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. For example, 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. For example, use a depreciation method to spread values into the depreciation expense account, based on a capital spending account.