The Capital sheet template creates a sheet on which users choose an asset class (for example, Furniture) from a drop-down list, enter asset cost, and enter units in the time period columns. Based on the asset class selection, the sheet automatically associates each row with the appropriate depreciation timing. You can also assign other identifiers, such as region or project, to each row.
This topic walks you through setting up an example Capital sheet. You start with a modeled sheet template for Capital and then customize it. There are several tasks required to create and customize the sheet. Refer to the Table of Contents for details.
Before You Begin
Required permission: Model Management Access > Model.
How You Get There
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.
Create a New Capital Sheet
The template for Capital is populated with columns. You can edit, reorder, or delete these columns. The columns and column types are:
- Asset Class (Text Selector)
- Label (Text)
- Level (Required column)
- Cost (Number)
- Capital Quantity (Timespan)
The template is populated with calculated accounts. You can modify or delete these accounts, but it is best to leave them alone except for the following suggested changes.
The accounts are:
ROW.cost * ROW.Quantity
This formula is used to calculate the value of the capital purchase.
- Depreciation: Spread of account Value using spread lookup Depr_Spread
This calculated account holds the result of the depreciation spread of the capital values.
To create a new capital sheet:
From the sheet list, select the New Sheet button.
Enter a Sheet name.
Select the Create a new sheet radio button, and choose Modeled and Capital from the drop-down.
Add an Account code prefix, then select Next.
A Summary for the sheet appears. You are ready to define your modeled Capital sheet.
Create an Asset Class
This Capital sheet is designed to have different asset classes (for example, Furniture, Office Equipment, Computers, Leasehold Improvements). Each class can have a different depreciation life. There are two asset classes set up for you already: Furniture & Fixtures and Leasehold Improvements. They are already created for you, in the Asset Classes column.
You can rename these asset classes and add others to this Text Selector column. This makes the asset classes available in a drop-down menu for users to choose from when they work on a sheet.
If the asset classes need to be referenced in formulas outside of this sheet, use a custom dimension column for asset classes instead of a text selector.
To create a new asset class:
From the Summary, select Columns and Levels to view the sheet elements.
From the center panel, select the Asset Class column (in the center pane).
In the Text selector expander (right pane), type the name of a new asset class in the Enter new value field and select Add. After you add the new asset class, the next step is to define a depreciation lookup for the asset classes.
You can Edit, Delete, or Reorder text selector values using the icons. The first choice in the list is the default when users add a new row to the Capital sheet.
Edit a Depreciation Lookup
The depreciation pattern for each asset class is defined in the Depr_Spread spread lookup. A spread lookup translates entered text or a selected dimension into values spread over time.
To add depreciation information for asset classes:
Expand the Spread Lookups for the Asset Class column (right pane).
Click the edit icon next to the Depr_Spread lookup. You'll see a list of spread lookups for the asset classes.
You can also edit Spread Lookups from the Sheet Summary.
Select an asset class (left pane) and define the Spread (Months) and Spread Expression for the depreciation.
Spread (Months): The number of months over which the depreciation is spread. For example, if Furniture & Fixtures has a life of three years, enter 36. Entering only a Spread (Months) number and saving automatically creates an even spread expression across the number of months you enter.
Spread Expression: A range and a fraction like 1:36=1/36. This sets the proportions for each month in the remaining cells for each asset class’s row. Spread expressions follow the pattern A:B=n/d,D:C=n/d where A and C are start month numbers, B and D are end month numbers. The spread proportion is n/d and can be a fraction like 1/40, or a number like .025.
Separate each range by the locale specific separator - either comma (,) or semicolon (;) depending on your locale. Only one range is required. Spread Expression does not accept thousands separators in values.
- When you've finished adding the Spread (Months) and Spread Expression for the asset class, save.
Set Sheet Properties
On the Sheet Summary for your Capital sheet, select Columns and Levels.
From the toolbar, click the gear button.
Define sheet Details, Security, and Settings properties as desired. Refer to Set Sheet Properties.
Select OK to exit Model Sheet Properties.
Click Save to save all changes you made to the sheet.
Link GL Accounts to Modeled Accounts
The values in the calculated accounts of the Capital sheet you created will not roll up to the appropriate GL accounts until you link the two.
To link your Capital sheet and GL accounts:
From the nav menu, select Formulas.
For each depreciation account (there are likely to be several, one for each appropriate department), set a formula to display the results of the Capital sheet depreciation calculation.
Select a depreciation account in the Account drop-down menu.
Select all Levels.
- Enter a formula (in the right pane) to link the account to the appropriate modeled account from your Capital sheet. Use the Formula Assistant if you want. For example:
(ACCT. Depreciation[Asset Class=Computers] + ACCT. Depreciation[Asset Class=LHI]+ ACCT. Depreciation[Asset Class=Furniture])
You can only reference the asset class here if it exists as a custom dimension column, not a text selector column.
You can also link the capital asset balance sheet accounts with formulas to the calculated accounts from this sheet. See Formula Overview for more information.
Capital Spending Value - Another Method
The Capital sheet template is designed so that users can enter an asset price and an asset quantity, and the sheet will automatically calculate the capital spending as "price x quantity." If you want to use a different planning method, you can leave the sheet "as is" but instruct users to enter a price of 1 and enter the capital spending forecast in the Capital Quantity column. Leaving the sheet "as is" enables users to choose whether to use the "price x quantity" planning method, or not.
If you don't want to use the "price x quantity" method, modify the sheet so users enter capital spending values.
To modify the sheet:
Find the Capital sheet you created and select Edit.
On the Sheet Summary, select Columns and Levels.
C;lick the trash button in the Cost column. When a warning message appears, select Proceed.
Select the Capital Quantity column and rename it to Capital Spending (or something similar) in General Properties (right pane).
Return to the Sheet Summary and select Modeled Accounts.
Modify the formula in the Value calculated account. The formula is located in the Data Type expander.