How To Input Historical Financial Statements In A Financial Model

How To Input Historical Financial Statements In A Financial Model

An integrated financial model will normally contain 3 to 5 years of historical income statement and balance sheet information, with approximately the same line items as the available historical financial statements of the business to be analyzed.

However some historical line items in the income statement and balance sheet of the financial model needs to be treated differently from that reflected in the historical financial statements, and a good financial analyst should know how to differ this to ensure accuracy in financial model calculations and projections.

Historical Income Statements

The income statement of a financial model should have approximately the same line items as the income statement of the business’s published financial reports. However, there are a number of accounts that should be forecast separately and should therefore have their own line item. These accounts are normally:

  • Depreciation
  • Amortization
  • Interest Income
  • Interest Expense

Depreciation

Unless stated otherwise, the depreciation expense in a company’s published income statement will normally be included in the cost of sales account and overhead expense accounts such as SG&A. If the business is a manufacturing company, the majority of the depreciation will be in cost of sales. If the company is a service oriented business, the majority of the depreciation will be in the overhead accounts.

When modeling an income statement of a business, you must make an informed decision as to where the majority of the depreciation is located, based on the rule stated above. You must then remove the depreciation from this account and put it on its own line item in your financial model. The process of removing the depreciation from an account is referred to as “cleaning” the account.

When cleaning an account, a financial analyst should give as much information as necessary to a potential user of your financial model as to how you cleaned the account. For example, if a manufacturing company’s cost of goods sold was 1,000 and depreciation expense was 90, in one of the historical years, it’s clean cost of goods sold would be 910. Rather than simply typing 910 into the clean cost of goods sold year, a financial analyst would write a simple formula as follows:

=1000 – 90

This will indicate to a potential user what you have done with the cost of goods sold line when they are comparing your financial model to the published financial statements.

You will find the depreciation charge for the year in the operating section of the cash flow or the fixed asset footnote in the company’s published financial reports.

Amortization

Unless stated otherwise, amortization will normally be included in one of the overhead cost accounts on a company’s published income statement. Amortization expense can be found in the published cash flow statement or the intangibles footnote.

Similar to depreciation, amortization on the modeled income statement should be forecast separately in its own line item and the cost account originally containing amortization should be cleaned. As with depreciation, good financial modeling practice would include formulae in the cost line item to make this as clear as possible.

Interest Income and Interest Expense

The interest line item on a company’s published income statement will normally be interest expense net of interest income. However, sometimes you will find that interest expense will be disclosed separately on the face of the published income statement and interest income will be included in another income or other expense account.

A good financial analyst would forecast interest expense and interest income separately in a financial model. They should therefore have their own line items. Once again, when cleaning the various accounts to derive interest income and expense, a good financial analyst would make calculations explicit by using formulae in the various line items.

Historical Balance Sheets

The balance sheet should have approximately the same line items as the company’s published balance sheet. Depending on the purpose of the model, you will sometimes consolidate some of the published line items into other assets or other liability accounts. When doing this, a good financial analyst will remember to include formulae and cell comments so that future users of the financial model can see what has been done before.

You should also input capital expenditure historical values into your financial model. To do this, set up a fixed asset (PP&E) schedule in the calculations section of the financial model and input the capital expenditure values in the appropriate cells. Historical capital expenditure values can be found in the cash flow statement of the published financial materials. You will be able to complete the remaining parts of the fixed asset (PP&E) schedule when forecasting the income statement (profit and loss account).


Visit Finance 3.0 - don't miss out on this high quality financial learning opportunity

Comment on this article