A Step by Step Guide to Construct a Financial Model Without Plugs and Without Circularity for Valuation Purposes

Abstract

In this teaching note the reader finds a simplified financial model. In reality, financial models are huge and cumbersome. This is a very simplified model compared with what is found in practice.

We present some basic principles for constructing the financial statements needed for valuation. The reader is encouraged to construct the financial statements for herself 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). With these targets or policies we can construct the financial statements.

The first table to be constructed is the table of parameters. This table organizes all of the relevant information. The subsequent tables are linked to the table of parameters via formulas. We construct other supplementary tables that will be used in the construction of the main financial statements. We indicate the formulas that have to be utilized in the construction of the financial model. In the first line and in the first column the reader finds the letters and numbers corresponding to the Excel spreadsheet in order to make it easier the localization and the construction of the formulas. In the last two columns we have written those formulas. Usually they correspond to the year 0 and/or year 1. When necessary, we show the formulas for other years and we indicate it. Shaded cells are for the input data. If the reader wishes to construct the model exactly as we did, she will be able to do that step by step.

The contribution of this work is double: one is to show that we can construct financial statements without the use of plugs and circularity and the second is that we can use a very simple approach to construct cash flows and to value them.

The model shown has two parts. One is the proper financial statements forecast. The second one is a simple cash flow calculation and valuation exercise using the Capital Cash Flow and assuming the risk of the tax savings equal to Ku, the cost of unlevered equity.

Keywords: Accounting, Forecasting Financial Statements, Decision Making, plugs, Planning and control, double entry principle, unbalancing problem, cash flows, firm valuation, cost of unlevered equity

JEL Classification: G12, E47, G31, H43, M40