Modeled sheets are designed to take input from planners and contain logic for automatically modeling the results of that input. Some common uses of modeled sheets are:
- Personnel sheet: Planning headcount details and calculating resulting monthly salaries and related benefits expenses.
- Capital sheet: Planning capital purchases and calculating resulting monthly depreciation expense.
- Sales sheet: Planning projected sales and calculating resulting revenue, COGS, invoicing, and accounts receivable.
Modeled sheets are created and configured by administrators. 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.
Behind the scenes, the sheet uses these inputs and the associated dimensions to automatically calculate revenue, expenses, and balance sheet activity, which can be spread
out over future time periods (for example, revenue recognition, depreciation, and invoicing). These values can be calculated based on lookups.
For example, you may tag headcount with benefits choices. Each benefits choice is associated with a percentage of salaries. Benefits expense is calculated based on the choices, the lookup of percentages, and salary values. The automatically calculated values are placed in modeled accounts. These calculated values are stored in the database, but are not yet included on the P&L or Balance Sheet. Administrators link these modeled accounts to the appropriate financial planning (GL) accounts, such as salaries, which roll up to the forecasted financial statements.
Sheet designers can create modeled sheets with any number and type of columns, but cannot specify rows. Users can add and delete entire rows from the sheet, and each row represents an item being modeled. Rows can also be imported. Modeled sheets can then use the lists of items users have entered to generate values using formulas created by sheet designers. Those values appear as "accounts" which can then be used in formulas, or placed in reports or on standard sheets.
Modeled sheets appear along with other sheets on Sheets > Overview or as a user-assigned sheet on Modeling > Accounts > Assumptions.
Personnel planning is commonly-done with a modeled sheet. In this GCF example, you won’t see accounts or dimensions in the rows. Instead, each row is an individual component of data entry, also referred to as splits. So budgeters are adding rows, or reviewing, or perhaps modifying rows that have previously-been imported. In a modeled sheet design, the column types determine how data is entered. In this example, the Level column allows users to make selections from the levels which are active in the sheet.
Last Name, First Name, and Job Title are all text elements, so users type directly into these fields. Medical Benefits and Dental Insurance are both dimensions which allow users to select from the defined values. Dimension values can be created during data entry in both modeled or cube sheets if the user and sheet have been configured to allow for it. The remaining fields in this Personnel example (Start Date, End Date, and so on) are all data entry using data and numeric column types.
In modeled sheets, the end result of populating an account with a value is performed through a series of calculations that are not visible to the budgeter. However, row details can be viewed to explore how an individual data entry row breaks down into its individual modeled accounts.
Both modeled sheets and cube sheets work with dimensions. Here are some considerations for picking which type of sheet to use for your designs:
- Modeled sheets allow for individual rows of data entry, such as employee or fixed asset purchases. And they allow for multiple rows with the same selection of dimension values.
- When entering data in a cube sheet however, a selected dimension value only relates to a single coordinate of data.
- Cube sheets also offer the ability to restrict specific combinations of dimension values, so users can only budget to valid selections.
- Modeled sheets can be designed with calculated patterns which are essential when planning for depreciation expense or revenue recognition.