Using excel for integrated representation of financial statements and financial simulations

 

Abstract

With a few simple rules and a spread sheet, even those who are not familiar with accounting procedures can process data and make predictions, and produce the “classic” financial statements tables: balance sheet, income statement and cash flow statement.

This article describes how to produce a calculation model for spread sheets that will result in an integrated representation of the financial statements.

The model shows how the figures in the financial statements are interconnected, and it does so in a transparent manner, making the matter understandable even to those not familiar with accounting.

This matrix is a veritable “engine” for use in the preparation of economic simulations and financial  forecasts; it provides the users with a “financial statements calculator”, which, thanks to the flexibility of the spread sheet, can be adapted to the reality of any company, no matter how complex.

 

Introduction

Many technicians, engineers, mathematicians and the like, find it hard to understand the information given in the financial statements of a company.

To explain a subject which, after all, is simple enough, all it takes is changing the point of view of the observer, e.g., looking at it from the standpoint of the classic spread sheet table.

By applying a few simple rules, we describe an effective method, based on a knowledge of accounting systems, which can be used when training company financial analysts and anyone looking to learn how easy it can be to understand financial statements and perform financial simulations and numerical forecasts.

 

 

Using excel to understand the financial statements of a company

The financial statements are composed of a series of tables and the explanatory notes.

Laypersons find it particularly difficult to understand the mutual correlations between the opening balance sheet, the income statement, the cash flow statement and the year-end balance sheet.

It is not easy, in fact, to describe the criteria adopted in entering the values and the connections between the various sections of the financial statements.

Such difficulties can be overcome by representing the values entered in the financial statements in “matrix” form: in other words, by exploiting the functional features of the “spread sheet”.

In this manner, we obtain an integrated representation of the values entered in the financial statements, which reveals how the values are interconnected with one another.

Essentially, the model described below makes it possible to attain two basic results.

The first result is an understanding of how company figures “move”: how does changing one or more values affect the entries in the various  financial statements?

The other, equally important result, is using the model to generate highly effective economic/financial forecasts of a dynamic nature, making possible to construct flexible “home-made” excel models, which can be quite complex and detailed, where the “financial statement matrix” is the “engine” powering (or powered by) the other spread sheets.

Let us begin by describing the basic use of the matrix: obtaining an integrated representation of the financial statements of a company.

It should be noted that since our aim is to make the concepts easier to understand, the examples provided are simple and intended for an intuitive understanding.

Once they have gained an understanding of how the matrix works, the users can elaborate it as they please.

The financial statements

Financial statements basically consist of four tables:

• Opening balance sheet
• Income statement
• Cash flow statement
• Year-end balance sheet.

The tables refer to a period of time delimited by a start date and an end date, which generally coincide with 31 January and 31 December.

Balance sheet tables show the values of the company’s assets and liabilities on two exact dates:  1 January (opening balance sheet) and 31 December (year-end balance sheet); they show stock quantities;

Income statement and cash flow statement tables show the dynamic of the values during the period from 1 January through 31 December; they are measurements of flow reflecting variations in value between the start date and the end date (see Figure 1).

In their “traditional” form, the financial statements of a company are as follows (see Figure 2):

Compiling the financial statements with excel

Laypersons find it hard to grasp the interaction between the values expressed in the traditional form.

They may find it easier if values were represented in a different manner (see Figure 3).

The “matrix” presented above preserves all the information of the classical form as well as the interactions between the values.

The matrix is built and should be used as follows.

The four columns represent the opening balance sheet, the income statement, the cash flow statement and the year-end balance sheet, respectively.

To help the user control the figures more effectively, the total of the algebraic sum of the columns is taken to be zero (whereas, as we will see, for the rows, the “final” value corresponds to the algebraic sum of opening balance sheet, income statement, and cash flow statement).

The opening balance sheet and year-end balance sheet columns show the values by assigning a “+” sign to the assets and a “-“ sign to the liabilities and equity: the values are recorded in such a way that their algebraic sum is zero (see Figure 4).

In the income statement columns, income entries are preceded by a “+” sign, and expenditure items by a “-“ sign; when entering the values in the model, where, for control purposes, all totals must be zero, the positive result for the year appears preceded by a “-“ sign (see Figure 5).

Unfortunately, in some instances, due to model construction rules, the attribution of signs is not “intuitive”: the income statement example seen above shows that profit is entered with a “-“ sign, and a loss would be entered with a “+” sign: this is necessary due to the way the matrix works.

This aspect is even more pronounced in the “cash flow statement” column, where cash receipts are shown with a “-“ sign and payments with a “+”: however, the sign attributed to “net cash flow” value is intuitive: it is a plus if the overall inflow is positive (cash receipts are higher than cash payments) and a minus if it is not (see Figure 6).

The usefulness of the convention of assigning algebraic signs to balance sheet entries becomes apparent when we read the model along the rows.

By convention, the “year-end balance sheet” value is given by the algebraic sum of the values on the left side.

The “customers” row (see Figure 7) shows that the starting value of accounts receivable was 7,800; during the course of the year, total sales came to 25,000 (and receivables increased accordingly), and cash receipts amounted to 26,636 (with a corresponding decrease in receivables). At year end, accounts receivable was 6,164 Euros.

 

The other rows are read in the same manner (see Figure 8).

The columns are always four, whereas the number of rows changes depending on the level of analysis deemed necessary by the creator of the model.

In order to understand how the matrix can be used with excel (or other spread sheet software) as a working tool, it is indispensable to lay down two more basic rules.

In the “income statement” column, profit/loss for the year is necessarily equal to the algebraic sum of income and expenditures items, preceded by the appropriate plus or minus sign (see Figure 9).

 

In the “cash flow statement” column, net (generated or used) cash flow is given by the algebraic sum of cash receipts and cash payments, preceded by the appropriate plus or minus sign (see Figure 10).

Let us now examine how the matrix works with the spread sheet.

The first step consists of understanding what happens when a figure contained in the matrix is changed.

The example below (see Figure 11) shows what happens if the value of sales increases from 25,000 to 30,000: a yellow background highlights the variable that changes, and a green background highlights the values that are modified by the change.

Due to the effect of the formulas applied, a change to a value determines in cascade form the relative changes in receivables, profit and equity.

The sequence presented above is a mathematical calculation of little consequence: everyone realises that if the sales figure changes, my purchase figures will have to increase too. We use this example to make the matter understandable through successive approximations.

However, the usefulness of the model becomes apparent when additional correlations between the values are made: let us assume that the correlation between purchase and sales amounts is 60% (15,000/25,000=60%) and taxation comes to 27% profit before taxes. Moreover, let us assume that collection time for payables from customers is 90 days and payments to suppliers are made at 80 days.

These variables are introduced into the excel spread sheet by adjusting the rules for the income statement column (expenditure is given by sales * 60%; taxes correspond to 27% profit before taxes) and the rules for the receivables from customers rows (the cash inflow for the period will be given by the sum of initial receivables plus a (365-90)/365) fraction of the value of sales for the year).

The result of the simulation (see Figure 12) now becomes much more detailed (changed values are highlighted in green; the “inventory” effect is disregarded for the sake of simplicity)

It should be noted that the sales figure may be changed as many times as we want, and the system recalculates the financial statements entries in an integrated manner, revealing the potential of the matrix to serve as the “engine” of simulations. The values are then taken from the matrix and entered in the “traditional” tables (see Figure 13).

Savvy users can also parameterise the various correlations, changing them, and the same can be done with receivables and payables adjustment periods: simulation possibilities are countless.

In conclusion, we should underscore the continuity of the values: the year-end balance sheet for an operating year is always the same as the opening balance sheet for the following year (see Figure 14).

Thus, it is apparent that the matrix may be used to link the simulations that a user wants to produce, without any time limits, obtaining integrated results for the balance sheet, the income statement and the cash flow statement tables of the financial statements.

 

Conclusions

• Laypersons may find difficult to produce thorough analysis of Financial Statements and carry out integrated assessment of its intrinsic dynamics;
• An Excel “Matrix” tool, able to synthesize the financial statement complexity and key figures interplay was presented;
• The Matrix allows accurate and integrated representation of a company financial situation and provides the analyst with the possibility for easy and complete financial simulations;
• Using this Matrix spread sheet tool should provide laypersons a higher degree of control of financial statement’s behaviours, hence a company core financial situation.