Excel: Control Accounts

The concept of "control accounts" is to break down multiple changes to a model variable instead of including every change in one huge and incomprehensible formula.

In principle, the development of an account within one period is shown line by line. You start out with a value at the beginning of the period ("bop") and add every line and end up with the value at the end of the period ("eop"):

Excel: Control Accounts
Excel: Control Accounts

A classical example for a control account is the fixed assets account in the balance sheet:

Excel: Fixed Assets Control Account
Excel: Fixed Assets Control Account

In this example, the value of the fixed assets at the beginning of each period is the same as at the end of the last period. In the first period, we link to an empty cell (grayed out) to illustrate that model users should be aware of potential inconsistencies when adjusting something here. Each period, asset investments are added on top, and asset disposals as well as depreciations are deducted. Each of those calculation steps can either be done in that row directly, or link to another sheet: For asset investments and disposals, you will normally receive input data by management and should store these in a separate sheet. The calculation of the depreciation per period can be tricky, considering differing ages and depreciation timeframes for different fixed assets, so this can also be done in a different sheet to ensure the user keeps an overview.

One Reply to “Excel: Control Accounts”

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.