Information

Price: $95.50

Views: 1475

Rating:
  • Resold modules appear on your website. You earn syndication share from each purchase.

    Contact Coggno to learn more on how to embed your own Portable Webshop in your website.

  • What is syndication?
  • Syndication FAQs

FAST Financial Modelling: Level 1 Certification

Preview    

Faster, Better, Cheaper:

Do you want to be more proficient and efficient in Excel?  FAST Modeling Certification is an online course that covers all the design and construction skills you’ll need as a foundation for financial modeling.  Our training follows the methods endorsed by FAST-Standard.org, a non-profit industry standards body.  Instructional videos presented by John Richter, author of the FAST Standard and Stanford University visiting instructor,  take you step-by-step, as we cover such topics as Excel setup, the Model Build Process, Balances, and more. 

Upon passing the assessments you will be sent a certificate stating that you are FAST Financial Modelling: Level 1 Certified.

What is FAST Modeling?

The FAST Modelling Standard is a set of rules providing guidance on the structure and design of efficient spreadsheets for financial models. FAST Modeling provides three core components:

  1. Effecient and error-reducing model construction techniques
  2. A clear route to good model design for the individual modeller; and
  3. A common style platform enabling modellers and reviewers to work efficiently when passing models amongst themselves.

Components 2 and 3 are further detailed in the FAST Modeling Standard, as document available for download at FAST-Standard.org

FREE Sample Tutorial!

Enter your email address below to get instant access to our Structured Scenario Control tutorial

Email:

 

Leading Companies Trust Our Training

Lets get started!

Whether you are a professional wanting to improve you and your teams skills or just looking to build your resume; this Certification is for you.

$95.50
:

Included Modules

Lesson 1: Initial Set-up


The FAST Standard


Excel 2003 Setup


Excel 2007 Setup


Calculation Options


Lesson 1: Exam


Lesson 2: Modelling Skills & Drills


Worksheet Layout

Key rules:

  • Each column should have a specific purpose
  • (the reciprocal to first rule) Each purpose should have a specific column

If you like, it’s the same as your grandmother’s old saying “a place for everything and everything in its place.”

  • Column E is a wide column that’s for row labels. It’s really important to spend time on row labels and make them descriptive and make them very clear and make sure they describe accurately what’s in the row.
  • Column F is for constants. These are values that don’t change overtime.  These values don’t get any bigger or any smaller over time; it is a fixed number. It, therefore, belongs in Column F which is specifically reserved for constants. Constants will sometimes be inputs, but, may also be calculations.
  • Column G is reserved for unit labels. Each line item has a description of what kind of a number it is. Again, make that clear.
  • Column H is for row totals which we will cover later.
  • We’ll also come back to the purpose of Column I later in the course.
  • Column J onwards are our time-based columns. Using Control+right-arrow you can skip to the end of that series.

You want to get rid of those columns that lie outside the time axis of our model. They are just additional columns and we’re never going to use them so we’re just going to get rid of them.

Here’s how we do that:

  • Control+Shift+Right arrow from the 1st unused column – that will take us to the end of the timeline
  • Control+Zero will hide up those unused columns

Note: this is the only time we will be hiding anything in our model at all.

Once we’ve done that, whenever we use Control+Right arrow we’re going to get to the end of the model which helps us to move around more easily in the model.

Another principle we want to bear in mind is that all worksheets with the same time axis should align.  By that we mean the date represented in column J should represent the same date in all worksheets with the same time axis.


Constructing Calculations

All of what you do when you’re modelling will be writing formulae and copying them across so worth spending time getting good at it.

Writing a formula

Generally speaking, when we are writing a timed series based formula we will be starting in column J and then copying it across and all the other columns through to the end of the time line.

One thing that the FAST standard recommends (which may be a new addition for you) is the use of spaces between the elements of your formulae.

One of our key design considerations in modelling is making it as easy as possible for other people to read our model and this is simple way of doing that. We strongly recommend that you get into that habit of putting spaces into your formula as it greatly aids the readability of your model.

“Copying it across”

When we’re ready to copy our formula across the time sheet, we follow the same steps every time (remember – muscle memory!)

  • Control+C: and that copies the formula into the clipboard.
  • Control+Shift+Right Arrow: Select the other columns that we want to paste the formula into.  We quite often see modellers first of all hitting the right arrow to start the selection from column K. This is an unnecessary step and we recommend you drop it. You can stay where you are in column J and select up the remaining columns from there.
  • Enter to paste down the formula: Why Enter and not Control+V? Control V pastes what’s in the clipboard but leaves it in the clipboard; Enter pastes what’s in the clipboard and clears the clipboard which makes it a safer operation.
  • Home: To take us back to the left hand side of our model – the intersection of our freeze panes. This ensures that we return to a consistent starting point.
  • Shift F9: to perform a local recalculation (i.e. a calculation just of this sheet). We’re going to talk in another lesson about different Excel calculation methods and why we recommend being in manual recalculation mode.

We recommend that you learn this one single keyboard sequence that you practice until you are very fluid and confident with it.

  • Don’t just do a bit of it – this will stop you from ever getting good.
  • Get into a habit of doing it the same way each time – this is the best way to get good!

If you perform this sequence the same way every time, it will go into muscle memory and it will become a very effective,  reliable set of key strokes.

Formula complexity

Simply put: long formulas greatly degrade the readability of your model.

A core recommendation of the FAST standard is for you to strive to keep your formula as simple and as short as possible.  As we progress through this course we will show you some specific and very effective things that you can do to keep your formulas from getting too long.

When is a formula too long? Remember the rule of thumb – make your formula no longer than your thumb.  While that’s fairly glib, it’s a useful benchmark in a world where lots of modellers think that long formulas mean that they are being very sophisticated and clever.

Remember Da Vinci: Simplicity is the ultimate sophistication. And it’s actually harder to write a model that’s simple for others to understand, but that’s what we are going to teach you.


Links and Calculations Blocks

So, what’s the alternative to long formulas? Well, we saw that in business communication we’re taught to break things out into bullet points and to keep our communication short and simple; and that’s what we’re going to do in our models also.

The temptation is just to write the formula and point to all the components from elsewhere on the sheet. If we do that, it will suffer from a whole host of problems. When somebody comes along to read this formula, they’ve got to go hunting for the ingredients.

What we want to look at today is an alternative way of constructing this calculation that greatly aids readability. The key to readability is to move away from thinking of calculations as something we do in single lines to thinking of them as something that we do in blocks. The calculation block is a core element of the FAST standard approach to modelling.

With a calculation block, we can lay out each of the ingredients to the calculation next to the calculation itself. Instead of including the remote elements in the formula, we fist link to each ingredient in turn.

In order to display each of the ingredients, we create links, also known as “call ups”.

The sequence to create a link or call up is:

  1. Start from column E.
  2. Type = into the column E cell.
  3. Using the arrow keys, go and point to the row label of the calculation that you want to link to i.e. we are linking from Column E to Column E always; by linking to the row label we ensure that the naming is consistent and we don’t have to retype the label each time.
  4. Give two taps on F4 to row anchor the link (we’ll come in a couple of days to why this is important)
  5. Copy across (using the same copy across technique as we learned earlier – Control+c, Shift+Control+Right Arrow, Enter, Home, Shift+F9).

When we do that for each ingredient to the formula, we can then put the formula at the bottom of the block.
Now, everything we need to see to understand the calculation is right next to the calculation. There are a few other benefits to the calculation block structure, but we will cover those in another lesson.

There are some rules about calculation blocks that are important to bear in mind:

  1. There should only be one calculation per block
  2. The calculation is the last item in the block
  3. The calculation should only refer to ingredients in the local block


Re-using Calculation Blocks

Another benefit of the calculation block structure is that it’s easy to copy calculations and then just replace ingredients. For example, if you’ve spent some time modelling Tranche A of debt and Tranche B has the same structure, you can just copy the calculation blocks for Tranche A and replace the ingredients.

The steps we need to go through when copying a calculation block are:

  1. Select up the whole block (holding down shift and tapping the down arrow to select the rows and then Shift+Spacebar to select the row)
  2. Control+C
  3. Find where you want to put the block
  4. Select the row at the destination (again with Shift+Spacebar)
  5. Enter to paste it down

Note: If the destination area does not have enough space to take the whole block and you are worried that you might overwrite code when you paste down the block, use Control and + together. (On most QWERTY keyboard, + is on the shift position of = so you have to type Control + Shift + =. That will insert the rows that you have in the clipboard and move the other rows down at the same time).

You can then replace the ingredients simply by overwriting the existing links with new links.

Remember to change the label on the calculation.


Lesson 2: Exam


Lesson 3: Advanced Setup & Techniques


Inputs & Model Build Process


FAST Format Macros

We have two objectives in formatting our models:

  1. Make the formats we use consistent between modellers and between models.
  2. Make the process of applying formatting easy, so that we can do it as we go along and don’t have to come back to it later.

Before we go any further, download the FAST format macro file and print out the shortcut sheet that you find in the downloads section of this page.

The PDF tells you all the shortcuts that are going to work when you have FAST format macro file loaded.

So… when you loaded the file you enabled the macros and wondering why nothing happened. Don’t worry, it did happen! This file is set up so that it loads in a hidden state.  It’s running in the background. When it loads, it sets up a whole bunch of new key strokes that are not native to Excel that allows us to do our formatting quickly and easily.  The PDF sheet that we sent you to download and print lists out what those key strokes are.

We suggest that you play around with these keystrokes for a while on your Drills model to get used to what they do. You quickly see how useful they are – our teams all rely on them when we’re building models.

How to set up excel to autoload your format macros

You’ll want to know where to put this file on you system so that it loads automatically every time you open Excel.

In the video tutorial we run through where to save this based on a machine running Windows 7.

You’re looking for a folder called XLSTART. Whatever you saved into that folder, Excel will launch whenever you run Excel

The set up may be different on your machine but on most PCs you will find that folder in:

C drive: Program files > Microsoft office > Office11 or  Office12 (depending on which version you have installed; Office11 is Excel 2003; Office12 is Excel 2007) > XLSTART

Because the macro file is saved in the hidden state, you will never actually see it loading, it will just happen in the background each time you load Excel. When it loads your FAST format macros will just reliably work all the time – you won’t have to think about it again.

How cool is that!


Customizing the Default Chart

This video walks through through customising the default chart in Excel.

What we don’t cover is “what makes a good chart” – this is a whole discipline in itself, but a fascinating one. We’ve included a few links below to get you started in creating more effective and impactful charts. (Hint – 3D charts are neither impressive nor impactful).

A great place to start is with the work of Edward Tufte. He’s written several books on the topic including the ‘classic’ Visual Display of Quantitative Information. This book is a visual feast – beautifully put together and highly informative.

We also highly recommend the work of Stephen Few, especially his books “Show Me The Numbers: Designing Tables and Graphs to Enlighten” and “Now You See It: Simple Visualisation Techniques for Quantitative Analysis“. Stephen’s blog, Perceptual Edge is worth following as well. He has some great example of good and bad chart design.


Paste Link

Up to now, when we’ve been creating links, we’ve been where we want the link to be. We’ve typed = into column E and then gone to point at the item we want to link to.

But what if you’re starting from the thing you want to link to rather than the place you want to put the link?

What we’d have to do is a kind of a round trip:

Control+Page Up / Down to get to the sheet where we want the link to be.

Type = in a column E cell and Control+Page Up / Down to point to the item we were looking at in the first place.

The purpose of this video is to demonstrate an alternative way of creating a link that avoids that round trip. It’s called the paste-link operation.

Here are the steps:

Starting on the item that you want to link to (rather then where you want the link to be):

  1. Control+c on the label
  2. Control+Page Up/Down to get yourself to where you want to put this link
  3. Excel 2003: Alt+E+S for Paste Special and then L for paste link
  4. Excel 2007: Alt+H+V+N for paste link
  5. F2, F4, Enter to correct the anchoring state (we want row anchored only)

And now you can copy that across and that’s exactly the same as if we’ve created it the other way. The only difference is where we started.

As our model gets bigger, this technique starts to become more useful because it means that we don’t need to make that round trip when we’re creating links.

Why format cross work sheet links?

  1. The complexity of the average financial model goes beyond what any one person can keep track of in their head (even if one person trying to track it in their head was a good idea!)
  2. When we collaborate with others, we want to make it easy for each other to understand our models; it helps if we put as much ‘intelligence’ as possible into the model itself. Showing clearly how each of the sheets in the model is interacting with other sheets contributes to that “distributed intelligence”.
  3. Cross worksheet links can be copied anywhere in the model – local links can only be copied on the same sheet.
  4. Marking exports is important because: a. they tend to be significant line items on the sheet and the immediate visual identification of that is useful; and b. deletion of these line items is going to cause repercussions elsewhere in the model that will have to be managed.

Imported links: a link to a line item on another worksheet – use blue font (Control+Shift+M)

Exported link: a line item that is being linked to from another sheet (Control+Shift+X)

Whenever we jump to the source of an imported link using Control+[ or Control+Shift+J, we should land on an item marked as an export.

Formatting Counter-flows

You may be asking yourself “What is a counter-flow?”

Models should be built so that the worksheets and the calculations on those worksheets flows from left to right and top to bottom like a book.  A counter-flow is simply a link to a calculation that is completed ‘later’ in your model.  These should be avoided whenever possible because they can cause your model to become circular which is not allowed in a FAST Standard model.

We are going to format counter-flows by following all previous link formatting rules, plus shade the whole row (Shift+ Spacebar) light grey with our FAST Format Macros shortcut keystroke Ctrl + Shift + O.

So if your model does become circular you will easily be able to track down the likely cause.


Lesson 3: Exam


Lesson 4: FAST Implementation


Modeling with Placeholders

Placeholders allow us to mark temporary code in our model and move on. In this video you’ll learn:

  • Why it’s important to have a standardised way of marking placeholders
  • What the 3 stages of creating a placeholder are
  • What keystrokes are used to create placeholders

The reality of financial modelling is that the model building is not linear. It’s not the case that you get all the information that you need on day one, you’re given all the inputs you require, and you neatly build them up into a model.

What happens is you don’t always get all the data at once and so you don’t always have all the information that you need for a given section that you are working on.

We need to have a coherent plan for what we’re going to do in that situation.

What we need is a structured, reliable way of indicating that something is temporary in our model. It needs to be clear to other people on our team that a particular line item is temporary code and we also need to ensure that we won’t forget about those temporary items.

Today, therefore, we’re going to teach you the technique of creating “placeholders” that will allow us to indicate clearly that something is temporary code, something perhaps we just hacked in so that we could make progress in a particular area.

There are 3 things we are going to do to make any temporary line item:

1. Visual marking.

Mark the whole line item in bright yellow.

Select the row (Shift+Spacebar) and apply bright yellow shading (Control+Shift+Y)

This gives us a visual indication to anybody looking at this sheet that this item is temporary. Here again we can see why standardisation is important. It’s going to greatly increase effectiveness if we all use the same formatting convention.

2. Square brackets

Secondly, we’re going to borrow a technique from the lawyers. Anytime a provision in a contract is temporary or yet to be agreed, lawyers mark it with square brackets. We’re going to do the same thing.  We have a shortcut in our Format macros: Control+Shift+ t will add square brackets, Control+Shift+t will also remove existing square brackets.

This gives us a structural indication that that’s a temporary item in addition to our visual indication. This means that, before we send this model out, we can search the model for items with square brackets. We can just easily search on square brackets, and we will find all those items that we told ourselves we would come back and fix later.

3. Add a comment

In three weeks’ time when this model is finished, we’ll run a search on square brackets and we will have a list of items that we told ourselves we would come back and fix. For some of these items we’ll have no idea why we put square brackets around them because it was three weeks ago and may as well have been a different person who did it!

And so, we should leave ourselves a little reminder as to why we’ve marked it and what we intend to do about it. This is also very helpful for other people who pick up your model.

Shift+F2 inserts a cell comment. Once you’ve written the comment, hit escape twice.

We apply these three markings to all temporary line items, but, that means that we can be confident in leaving temporary code in our model while we’re in the model build phase.


Timing Flags

For every line item that we model, we have to be able to answer two fundamental questions:

1. How much?

2. When?

Simple right. Most of what we’ve been doing in the course so far has been focused on answering the “how much” type questions.  Now is the time to focus on “when?”.

Whenever we’re asking the “When” question were going to answer that using a flag. Combining the “when” and the “how much” logic in the same formula creates a horribly complicated calculation.  FAST approach is to separate the timing element logic into a flag. The flag is then the ingredient within the calculation block that deals with the “when” question.

This whole issue of time is what separates just your average “spreadsheet” from a financial projection model; the whole treatment of time and the intelligence around time.

For examples of timing flags, review the ‘time’ worksheet found in the Input Sample Model you downloaded during the last lesson.


Sign Convention Design Overview

For an example of the logic setup of a sign switch, review the ‘Tmp’ worksheet in the Input Sample Model you downloaded during the last lesson.


Sign Convention FAST Implementation


Modeling Balances

There are two basic types of line items in our model:

  • flows – an amount of “value” passing in and out of the business within a period
  • balances – the amount of value present at a particular time

All balances have the same 3 essential properties:

  • upward flow – the flow that makes the balance go up
  • download flow – the flow that makes the balance go down
  • initial balance – at a particular point in time

In the context of working capital these properties would be:

  • revenue recognised (invoices issued) – upward flow
  • cash received (invoices paid) – downward flow
  • initial balance of accounts receivable on the balance sheet

Since all of the balances are essentially the same, we can use the same modelling structure to model all of them. This standard structure is called a “corkscrew” and the same structure should be used for modelling all balances.

For an example of the logic setup for a corkscrew that includes an initial balance, review the ‘Tmp’ worksheet in the Input Sample Model you downloaded during the last lesson.


Tmp Sheet


Lesson 4: Exam



Attachments

Excel 2003 Shortcut Keys

54.84 KB

Excel 2007 / 2010 Shortcut Keys

55.32 KB

Drills Sheet

62 KB

FAST Format Macros Shortcut Keys

24.08 KB

FAST Format Macros

202.5 KB

Input Sample Model

235.5 KB