Skip to main content
Adaptive Insights
Knowledge and Support - Adaptive Insights

Creating a Capital Sheet

Describes how to use a modeled sheet template to create a Capital sheet.

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 automati­cally 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.

Create a New Capital Sheet 

The sheet builder for 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 sheet builder 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:

  • ValueROW.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 val­ues.

To create a new capital sheet:

  1. Navigate to: Modeling > Model Management, then click Level Assigned Sheets.

  2. Click New Sheet.

  3. Enter a Sheet name.

  4. Create a new sheet. Select Modeled (for the sheet type) and Capital (for the sheet template).

  5. Add an Account code prefix, then click Next. A Sheet Summary appears. You are ready to define your modeled Capital sheet.

  6. Click Columns and Levels to view the sheet elements.

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 differ­ent depreciation life. There are two asset classes set up for you already: Furniture & Fix­tures 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 selec­tor.

To create a new asset class:

  1. From the sheet builder, select the Asset Class column (in the center pane).

  2. In the Text selector expander (right pane), type the name of a new asset class in the Enter new value field and click 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 for asset classes. 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:

  1. Expand the Spread Lookups for the Asset Class column (right pane).

  2. Edit  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.

  1. 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 clicking save 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 propor­tions 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 val­ues.

  1. When you've finished adding the Spread (Months) and Spread Expression for the asset class, click Save .

Set Sheet Properties

To set sheet properties:

  1. On the Sheet Summary for your Capital sheet, click Columns and Levels.

  2. Click Properties  (in the center pane toolbar) to set sheet properties.

  3. Define sheet Details, Security, and Settings properties as desired. Refer to Set Sheet Properties.

  4. Click OK to exit Model Sheet Properties.

  5. 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:

  1. Navigate to: Formulas.

  2. For each depreciation account (there are likely to be several, one for each appropri­ate department), set a formula to display the results of the Capital sheet deprecia­tion calculation. 

  3. Select a depreciation account in the Account drop-down menu.

  4. Select all Levels. 

  5. 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 exam­ple: (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.

  1. Click Save .

You can also link the capital asset balance sheet accounts with formulas to the calculated accounts from this sheet. For more information, refer to Formulas.

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 cap­ital spending values.

To modify the sheet:

  1. Navigate to Modeling > Model Management and click Level Assigned Sheets.

  2. Find the Capital sheet you created and click Edit.

  3. On the Sheet Summary, click Columns and Levels.

  4. Delete  the Cost column. When a warning message appears, click Proceed

  5. Select the Capital Quantity column and rename it to Capital Spending (or something sim­ilar) in General Properties (right pane).

  6. Click Save .

  7. Return to the Sheet Summary and click Modeled Accounts.

  8. Modify the formula in the Value calculated account. The formula is located in the Data Type expander.
    ROW.CapitalSpending

  9. Click Save .

  • Was this article helpful?