Rock Financial Modelling Book a Course Now Enquire Re Consulting

Financial Modelling

Summary

We start with assumptions/inputs for a global manufacturing company with operations in six different countries, (thus six different currencies) and with a construction and an operations phase. You will then create a model “from scratch”, containing key outputs (shown quarterly) to arrive at a valuation of the business, and then in a few keystrokes you will create key outputs (shown annually), and a report with impressive presentation, and then look at sensitivities (and the best Excel tools to use for such). We will be employing some advanced Excel formulae, which we will cover initially on Day 1.

Part A: Financial Modelling 101

  • How is Financial Modelling different from Advanced Excel?
  • What makes a good financial model?
  • What makes a good financial modeller?

Part B: Advanced Excel

  • Functions: Concatenate, Count, Dates, Index, Indirect, Logic (If and AND), Lookups, Match, Offset, PMT ( and PPMT and IPMT), Rand, Rank, Rounding, Sumif, SumProduct, (and “triple sumproduct”), Vlookup, XNPV & XIRR. We also look at the additional benefit from combining functions i.e.: Match & Vlookup, Match & Index, Rank & Sumif, etc.
  • Commands: Custom lists, Data validation, , Goal seek, Macros (and creating “buttons and shortcuts for a Macro) , Rapid formulae copy Watch Window,
  • Charts: creating, adding data, adding a 2nd axis, adding a regression line, “floating” charts
  • Tables: Instant tables (F11), shortcuts to create tables, data tables, pivot tables, scenario manager
  • Other: Custom Numbers, Conditional Formatting

Part C: Building the Model

In this course we will be constructing a model with not too many variants, however in real life businesses are complex i.e. you could be asked to forecast ( i.e. model), the annual quantity of petrol required for 2,000 trucks, which have an option of 38 different routes, depending on weight of goods transported, destination, number of drivers available, weather conditions, and 20 other inputs …thus it will make your job easier if you can adhere to the tenets of best practice modelling- which we will teach in this course.

Starting with a list of inputs, we teach how to build a financial model from a blank Excel sheet:

  • Planning/ logic flow/ model set-up / design rules for good layout
  • Standard elements that every model should have: Uniform headers/ key data and error checks showing at top of every sheet/ Cover sheet/ Model map with hyperlinks and formulae inside shapes)/ Error check panel/ Log of changes/ “shadow” model check
  • Creation of error checks as we go along : the more checks we have the less chance of error.
  • Dates: get them right, as the model hinges on dates – particularly when there are different phases i.e. construction vs. commissioning vs. operations vs. Major overhaul every x years.
  • Assumptions: Entry of, and complete validation of input source
  • Outputs (Quarterly): Construction versus Operations phases / Creating the calculations to arrive at business cashflow / Working Capital/ EBITDA/ Depreciation/EBIT/ Tax/ NPAT/ Breakeven Analysis.
  • Valuation: Using DCF and Transaction Multiples we will arrive at a value of the business.  We will explain these different valuation approaches, as well as when you should use one versus the other. We will also explain “terminal value”,  and the “Gordon growth model”, explaining how these also impact valuation.
  • Outputs (Annual): the same outputs as described above but shown annually- and how to do this in just a few keystrokes (Issues to look out for: balances versus flows, % outputs, tax schedule).

Part D: Impressive Presentations

  •  You learnt them on day 1 – now we put them into practice, creating the following for various data sets in the model : Instant charts, Floating charts, Bubble charts, Spider charts, Pivot tables, Data tables, Conditional Formatting.

Part E: Get it right: Perform your own self-audit

Think you got it right? Before we go on to performing sensitivities (below) we need to check we got the base case correct. In addition to the error checks we have built in we cover the following:

  • Common errors
  • Audit tools: Inbuilt ones in Excel : Go To Special (search for constants, a particular format, differences between rows or columns, last cell)
  • Audit tools: commercially available software-our top picks
  • Perform a sanity check to ensure logic is flowing thru model correctly i.e. Historical versus forecast- does it make sense? Instant charting of key lines to look for “spikes”
  • Try and “break” the model – not physically! ..Rather, via changing inputs to deliberately high/low values and see if the model can “handle” it. i.e. change the inflation rate on Opex and see the effect on EBITDA -if there is a dramatic change you may have miscalculated the inflation factors..or maybe it is right- you need to understand the results.
  • Create a “shadow” model to replicate results.

Part F: What if Analysis/Sensitivities

Now that we’ve created the “base case” above, we look at sensitivities:

  • We will create various scenarios-and look at the options in Excel to do this i.e. Scenario Manager or Data tables- what’s the difference? Which is better? Which is quicker?
  • Conditional formatting applied to data tables to provide great visuals that will keep your audience focussed — this could be key if you are presenting to a bored boardroom
  • Understand not just how to create sensitivities, but be able to confidently explain them as well, particularly when results go in the opposite direction to that anticipated- this may not be wrong- there could be a good reason!