Aasha Shamsuddin - Financial Modelling Manual A comprehensive but succinct step-by-step guide to building a financial forecast model in Excel

Contents

Introduction

What is a financial forecast model?

A model takes a companys key financial statements and forecasts them into the future, usually for a five to ten year period. It is rare to forecast beyond this, as longer periods are harder to predict and the numbers become more uncertain.

A model is typically set up in an Excel file which will have separate areas for the financial statements themselves, forecast assumptions and workings. Excel formulas link the forecast numbers to forecast assumptions. The assumptions are commonly based on estimated future growth rates and margins for each line item in the companys income statement and balance sheet, and so estimating assumptions is a key part of the modelling process. Poor assumptions will result in a poor quality forecast. One way to check the quality of the forecast is to look at the trends in projected margins and growth rates based on the estimated numbers produced in the model and so most models will also have a complex ratio and analysis section.

Once the basic forecast has been built, the model can be used for further types of analysis, and additional worksheets such as discounted cash flow valuation or leveraged buyout valuation can be added.

Financial models are widely used in the banking environment and by corporates. Models can help in making important financial decisions such as new investments, divestments and restructuring.

How to use this Manual

This manual will guide you through the process of building a model step by step. We have provided a model which you can build as you work through the manual. The template and solution files for this model, referred to as the Case Model, can be found on our BG Portal (BG Library)

Note that the model template provided already contains historical numbers and forecast assumptions.

If you are new to Excel and modelling then make sure you read the Excel Essentials for Modellers chapter first and work through the practice exercises in the practice file which is also available at our website.

There are also additional chapters on Circularity in Forecast Models which includes a detailed explanation of circularity in models and how to deal with it and How to Check a Model which covers how to find and fix typical errors.

Modelling step by step

Building a financial model should be done in an organised way. This is essential in helping you to avoid mistakes and will make your model easier to follow. We recommend that you follow the steps below in order, to achieve the best results.

Each step is explained further in the following chapters.

It is recommended that you download the practice model from our website and build the model as you work through this manual.

The 10 golden rules of modelling

Keep your model structure easy to follow and user-friendly.

It is important that your model has a good cover or control sheet (see later). Consistent styles and formatting will ensure that your model looks professional. Comment cells to give more information to the user on where the numbers are sourced from in the Annual Report or why an adjustment is being made.

Do not have any other Excel workbooks or models open as you build the model.

It is easy to accidentally flip to the wrong workbook and link cells in error. In addition, a circularity in one open workbook can cause circular error messages in another open workbook. See the circularity chapter for more information.

Always model with iterations in Excel turned off, and the models circularity control switch turned off, if there is one.

This is so that you will see Excels circularity indicators as soon as you build a circular formula so that you can avoid errors which might destabilize your model and cause it to blow up. See the circularity chapter for more information.

Never repeat an assumption on another page. Always link to one original assumption cell.

A new user of the model would never know that the same assumption needs changing in more than one location.

No hard-coded numbers in formulas.

A new user of the model would have no idea of where the hard numbers are and therefore not be able to change them.

Never rebuild a formula

If the formula has already been built once in the model and is copyable, it is a waste of time to rebuild it from scratch and you are more likely to introduce a new error in doing so. Better to copy it from one location to another, if needed elsewhere. Remember to double check that your cell references are copied correctly.