Financial Modelling Manual

Aasha Shamsuddin

Description

A comprehensive but succinct step-by-step guide to building a financial forecast model in Excel.All the steps are fully explained with screenshots from a case model which can be downloaded online and used to practice as you work through the manual. The manual contains additional chapters focussing on circularity in models and how to troubleshoot related problems. The manual also includes a guide on to how to thoroughly check a model for errors and how to resolve them, and an introduction to the key Excel keyboard shortcuts and functions that will ensure fast, accurate modelling.To download the accompanying excel model for this manual please go to https://bgmanual.learnupon.com - See more at: http://www.firstywork.com/BGConsulting/index.htm?/Financial+Modelling+Manual/&bid=9780954449698#sthash.voWBn6Dv.dpuf

Sujets

• Economie gestion
• Corporate finance

Informations

• Publié par: BG Consulting
• Date de parution: 19 octobre 2015
• Nombre de lectures: 2
• EAN13: 9780954449698
• Langue: English
• Poids de l'ouvrage: 7 Mo

Extrait

Contents
Introduction
Modelling step by step
The 10 golden rules of modelling
Step 1. Objectives, structure, shell
Step 2. Input historical numbers
Step 3. Income statement assumptions and forecast (except interest)
Step 4. Balance sheet assumptions and forecast (except cash and debt)
Step 5. Cash flow statement forecast
Step 6. Plug the balance sheet cash
Step 7. Detailed debt schedule
Step 8. Link short-term and long-term debt into the balance sheet
Step 9. Calculate interest and link into the income statement
Step 10. Turn on iterations in Excel and the model’s circularity switch
Step 11. Complete ratios, analysis and final checks
CIRCULARITY IN FORECAST MODELS
HOW TO CHECK A MODEL – STEP BY STEP
EXCEL ESSENTIALS FOR MODELLERS
Introduction
What is a financial forecast model?
A model takes a company’s 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 company’s 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.

Objectives, structure, shell

Input historical numbers

Income statement assumptions and forecast (except interest)

Balance sheet assumptions and forecast (except cash and debt)

Cash flow statement forecast

Plug the balance sheet cash

Detailed debt schedule

Link short-term and long-term debt into the balance sheet

Calculate interest and link into the income statement

Turn on iterations in Excel and the model’s circularity switch

Complete ratios, analysis and final checks
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
1. 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.
2. 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.
3. Always model with iterations in Excel turned off, and the model’s circularity control switch turned off, if there is one.
This is so that you will see Excel’s 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.
4. 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.
5. 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.
6. 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.
7. Always build subtotals for the historic years.
This gives you an extra check that the numbers you input for the historic years are correct. This is especially important in the income statement where you are likely to have cleaned some of the historic numbers.
8. When building formulas, put the assumption first for easier formula checking
The trace precedents shortcut, CTRL + F5, will only go cross-sheet to the first reference in the formula. See the error checking chapter for more information.
9. Ideally build only the year one forecast for the entire model and copy right only once the model balances
People often correct errors in the first forecast year and then forget to copy right the corrections across the remaining years.
10. Avoid uncopiable formulas as far as possible.
People often recopy formulas right in a model as they are checking and changing assumptions, which could result in errors where formulas were not intended to be copied right.
Step 1. Objectives, structure, shell
Objectives
Financial models help companies to make financial decisions and they can be prepared for many reasons. Below are some of them:
• Budgeting / forecasting
• Valuation
• Credit
• Company restructuring
• Debt restructuring
• Investments / divestments (investing in new assets, selling/part-disposal of assets, buying a new company, selling a company etc.)
Before you begin you need to know why you are preparing a financial model, who is going to use it and what result it should present. Make sure that you know the answers to these questions before you begin.
Structure
Model structures can vary widely so before you start working you need to decide which type of model you will create:
• Tower model – all of the information is shown on one spreadsheet . These models are generally easy to navigate as everything is one place but if they become too long, they then become much harder to manage.
• Book model – each main financial statement and detailed schedule is presented on a separate spreadsheet . These models might end up with a lot of tabs but they can be easier to manoeuvre around than equivalent tower style models. Checking formulas can be a bit trickier with this style of model, especially where they are ‘cross-sheet’ i.e. include cell references from different tabs but there are ways of dealing with this which we will look at later.
The model used for illustration in this manual is a book style of model.
Shell
If you are not using an existing model shell, then you will need to build one yourself.
This means:
• Set up the Excel file and make sure that iterations in Excel options are turned off . This will help in identifying and preventing circular errors as you are inputting numbers. For more on this, see the error section.
Whilst circularity is the existence of a circular formula in a model (a formula which includes itself in the answer), iterations are an Excel tool for solving solvable circular formulas. Interest in a model is deliberately built as a circular formula, but as circular formulas can de-stabilise models.
See the Circularity in Forecast Models chapter for more on circularity and iterations .
• Design a control sheet with key information about the company being modelled, including currency, units, year-end, details of the model builder and any switches if used, making sure they are initially set to off. If you are using a circularity switch, then you can range name it at this stage, to make it easier to reference in formulas later.
For help on how to range name cells, see the Excel Essentials for Modellers chapter .
• Decide which other worksheets to include – in a book style model you will need separate sheets for income statement, balance sheet, cash flow statement, debt schedule, detailed calculations such as property, plant and equipment (PPE), and ratios as a minimum.
• Decide where to show the model assumptions – historic and projected growth rates and ratios
In a book style model you could choose to show all assumptions on a separate sheet or you could show them threaded throughout the model –balance sheet assumptions on the balance sheet pa