Construction of Consistent Forecasted Financial Statements (ConstruccióN De Estados Financieros Proyectados Y Consistentes)

Abstract:     
In order to value a firm or a project, it is necessary to construct estimated financial statements and free cash flows. In this teaching note we will present a spreadsheet model - EXCEL© - for forecasting financial statements in a consistent way and without using what is known as plugs. The simplest and coarse form of a plug in forecasting financial statements is to match the financial statements (in particular the Balance Sheet) creating a line or item to account for any difference that arises between total assets and liabilities plus debt in order to make the Balance Sheet to check. If total assets are greater than total liabilities plus equity, then the plug is a new line in the liabilities side. If lower, the plug is a line in the assets side. This is no only inappropriate, but it can hide mistakes when working with complex models such as those used by valuation analysts.

We illustrate the procedure with an example that has some complexities. These complexities are explained in Section One. The purpose of this note is that the reader, following the instructions, could complete the example. Then, the readers are encouraged to read actively by constructing the financial statements for themselves on a spreadsheet. The relevant financial statements are: the Balance Sheet (BS), the Income statement (IS) and the Cash Budget (CB). The construction of the financial statements starts from policies and/or targets (i.e. accounts receivable policy or target) and input data that is specific for the firm or from the macroeconomic environment. With these targets or policies and data we can construct the financial statements. For valuation purposes, the balance sheet and the income statements are important but may be insufficient if we wish to construct the cash flow using the direct method. For that reason we construct the CB.

From the table of parameters we construct tables that will be used in the construction of the main financial statements. In the main text we describe some complexities such as price-demand elasticity, the effect of book value leverage on the real growth and on accounts payable policy. We introduce the effect of accounts receivable policy on growth as well.

We reproduce Excel© spreadsheet and in the last two columns we include the formulation than the reader should replicate and copy for the forecasting period. There are a few exceptions, but they will be announced.

Note: Downloadable document is in Spanish.

Number of Pages in PDF File: 27

Keywords: Financial statements, forecasting, net present value (NPV), firm valuation, equity valuation, cost of capital, break even analysis, sensitivity analysis, scenario analysis, cash flow valuation

JEL Classification: G12, G31, M41

working papers series