Modeled sheets take input from planners and automatically model the results of that input.
1 Add/Delete Rows buttons in addition to, or instead of, the add/delete/rename splits buttons.
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
How Modeled Sheets Work
When you look at a modeled sheet, you see rows of records with columns that contain data. You enter data or select data from the columns for that record. You do not enter data for the accounts. Instead, the sheet calculates the account values based on the formulas defined in the account settings. The formulas in modeled sheet accounts evaulate the data you enter in the columns, the lookups you added to the columns, and assumptions you created for the sheet.
Type of Modeled Sheet Columns
- 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. Data entry columns cannot be referenced from outside the modeled sheet.
- Custom dimension and attribute columns place already-existing custom dimensions and attributes on modeled sheets. Users can use these to filter the sheet or as part of their input data. Dimensions can also contain lookups that affect data calculations.
- Display columns are read-only displays of values calculated by the sheet.
- 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.
Types of Modeled Sheet Accounts
- Calculated accounts use formulas to calculate their value based on the column data and lookups.
- Assumption accounts are periodic accounts that drive data. Only admins can enter the data in the assumptions accounts from the Sheet Summary page.
- Initial balance accounts are automatically created if you add an Initial Balance column in the previous step.
- Timespan are automatically created if you add timespan column in the previous step.
Modeled Sheet Lookups
Lookups are added to the dimension columns and data entry columns that are text selectors, or dropdowns. Lookups provide logic based on the selections you make from the dropdowns in the sheet when you're entering data. The logic then drives the calculations of the calculated accounts that reference those columns.Types of lookups:
- 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.