Love to Excel: A Financial Modeling Masterclass for the Analyst in You

By Jules Nkansah

Starting out your career as an analyst or budding entrepreneur? Looking to upgrade your skills and give yourself a boost? Building your aptitude in financial modelling with this series will give you the edge you need. Financial modelling holds the key to decision-making in business and everyday life, be it through business case and budget development, portfolio analysis or market sizing. In this series, we take you on an uncharted path to building your financial modelling expertise, in an easy to follow, step-by-step case study approach.

• Language: English
• Publisher: Partridge Publishing Singapore
• Release date: Aug 22, 2017
• ISBN: 9781543741193

Jules Nkansah

Jules began his professional career as an Investment Banking analyst at Citigroup, New York. He moved into the telecoms industry at Vodafone Ghana, working through varied marketing and strategy roles across consumer and B2B spaces. He has vast experience driving the decision-making of businesses through financial modelling and analysis. An INSEAD MBA, Jules is passionate about developing the skills of the next generation of super analysts and business leaders.

Book preview

Copyright © 2017 Jules Nkansah. All rights reserved.

ISBN

978-1-5437-4118-6 (sc)

978-1-5437-4119-3 (e)

All rights reserved. No part of this book may be used or reproduced by any means, graphic, electronic, or mechanical, including photocopying, recording, taping or by any information storage retrieval system without the written permission of the publisher except in the case of brief quotations embodied in critical articles and reviews.

Because of the dynamic nature of the Internet, any web addresses or links contained in this book may have changed since publication and may no longer be valid. The views expressed in this work are solely those of the author and do not necessarily reflect the views of the publisher, and the publisher hereby disclaims any responsibility for them.

www.partridgepublishing.com/india

10/19/2017

17030.png

Contents

Prologue

Chapter 1 – Getting Started

Our Code, The Cardinal Rules and Getting Set-Up

Chapter Highlights

Chapter 2 – AWWI Case

Part A: Starting from Cell A1’

Part B: Formatting Your Model

Chapter Highlights

Chapter 3 – Outlier University Case

Part A: It’s All About Your Drivers!

Part B: Flexing Your Assumptions

Part C: Key Financial Summaries of Your Model

Chapter Highlights

Chapter 4 – Sensitivity Analysis

How Sensitive Is Your Model?

Chapter Highlights

Chapter 5 – Afrobeats Online Streaming – A Market Sizing Case’

Part A: How Big Is the Addressable Market?

Part B: How Much Money Is There to Be Made?

Part C: What Do We Have to Spend to Make This Money?

Chapter Highlights

Chapter 6 – Exporting Your Model to the World

Chapter Highlights

Chapter 7 – Charting Your Model to Success: Letting Pictures Speak for You

Chapter Highlights

Chapter 8 – In Conclusion

To the women in my life…Andrea, Sandra, Victoria and Virginia

My fountains of strength and inspiration

Prologue

Financial modelling is the go or no-go of decision-making in business and in everyday life.

Recently, my wife and I were looking to buy a house, which we intended to finance by taking out a mortgage loan. We found one we both liked, although it seemed a bit pricey. It wasn’t until I built a two-year model of the implications of making a down payment on that house and servicing the monthly interest payments along with our other monthly obligations and commitments that I realized that we would indeed be overstretching ourselves.

I didn’t need a financial advisor to state that to me. By building a fairly simple financial model, as we shall do in this series, I was able to arrive at that decision for myself.

In the business world, we encounter financial modelling in similar action. Even the best marketing ideas have to go through a business case – essentially a financial model – to ascertain whether they will pay off as hoped. On Wall Street, every transaction, from a few millions through billions of dollars in value, is driven by a cold hard financial model that analysts poured their heart and soul into for weeks to derive expected returns. The outputs of these models in both cases are reviewed by business stakeholders – including managers, board members, and principals – to determine whether to go ahead or not with these ideas or transactions.

Financial modelling is an indispensable tool at work and in everyday life. In essence, a financial model is a numerical representation of a real-life business or personal situation. It allows the financial modeller to arrive at conclusions.

In this series, we will focus on how to build a financial model the right way. Whether you’re a budding or aspiring analyst looking to sharpen your skills or a marketing executive constantly building budgets and analysing your portfolio – or perhaps an entrepreneur looking to build out your own business projections to support your business plan – this series is tailored to you.

Love to Excel is a multipart series that will take you from complete novice to financial-modelling demigod. This first volume aims to build the fundamentals. Whether or not you were familiar with financial modelling in Excel prior to this, we recommend immersing and grounding yourself in our distinctive approach to financial modelling in this introductory volume.

Requirements for This Series

You will need a PC/laptop with Microsoft Excel 2003 or later installed. (Note: This series is primarily written for a Windows operating system, and thus some instructions may not work the same way to other operating systems.)

Our instructions are based on an English (UK or US) keyboard, and you will need to be familiar with typing on these keyboard formats. Your natural typing speed will translate into how expediently you can carry out our keyboard instructions.

Finally, you will need an Internet connection to visit our online portal, www.lovetoexcel.com, to find resources that complement this series.

Style of This Series

We shall employ case studies – starting from basic ones and moving on to more involving cases – to help you put your skills into action real-time. Each case study is worked through step by step. Starting from a blank spreadsheet, you will be able to build a model and see it blossom before your very eyes!

Our Goal

It is our hope that this series inspires a movement of financial modellers who model anything that moves for fun! We hope that beyond the professional sphere, you’re able to derive the everyday benefits of loving to Excel.

Chapter 1 – Getting Started

Our Code, The Cardinal Rules and Getting Set-Up

There are three main pillars upon which financial modelling is built: flexibility, control, and clarity. We shall coin a term out of these three: flexcoclar. These principles should absolutely govern your approach to building models.

Flexibility simply means that your model should not be rigid. A good model can easily be updated or adapted to different outcomes and purposes, whereas a rigid model is unwieldy, difficult to manipulate by an outsider who didn’t build the original model, and quite static, in that it may not be easily used for other purposes or scenarios.

Control is essential in ensuring that your model does what you want it to do. Many times I’ve encountered analysts who have produced some output from a financial model that looked dubious. Upon querying the authenticity of the results, I often get a shrug and a ‘That’s what’s in the model.’ Time out: You build the model, and thus you control the model, not the other way around. Build the model such that the output you get is consistent with the output you might expect and can reasonably defend.

Clarity as a pillar is often neglected. A financial model in Excel should read like a book. Just because it involves numbers doesn’t mean you can arrange them in any way, make the model look unkempt, have random data all over your spreadsheet, and just focus on the output. In truth, you never really build a financial model for just yourself. You build it so that all mankind, present and future, can look at your model, make sense of it, and use it. Financial modelling, you see, is a way of business analytical procreation. Each model you birth should be wonderfully and fearfully clear.

The Seven Cardinal Rules

To achieve flexcoclar in your model, there are a few rules you should adhere to: seven to be exact. We term these our seven cardinal rules.

Rule 1: Thou shall banish the mouse.

In all of this series, never use your mouse – either an external mouse, the trackpad on your laptop keyboard, a remote-controlled mouse, or any kind other of mouse – unless explicitly directed to do so. Using the mouse makes you slow and kills your control of the model. You need to be able to move around with speed and dexterity, which you can’t do when you’re pointing and clicking the mouse like a dinosaur. If you doubt that the use of the mouse slows you down, take our finger-stretching exercise later in this chapter.

How in the world are you supposed to get around in Excel then? We shall be teaching you to use the keyboard!

Rule 2: Thou shall not hardcode.

Hardcoding is the definition for having formulas in Excel where raw numbers are entered into the calculation as part of the formula. This makes it impossible to audit your model in the future, as these hardcoded numbers cannot be interpreted by future generations. Besides that, it’s difficult to know where each instance of that assumption occurs in the model, in case we wanted to change the value of the assumption. Hardcoding kills all of flexibility, control, and clarity in flexcoclar.

Rule 3: Thou shall label every number.

I’ve seen many an Excel model with numbers hanging in random cells with no label next to them. How in the world will future users of the model know what these numbers were meant to represent? Aside from that, it’s easy for someone to delete these numbers, assuming that they are not in use in the model. Label every number or set of numbers that appears in your model. Failure to do so kills the clarity in flexcoclar.

Rule 4: Thou shall not duplicate.

Duplication is one of those basic yet criminal sins. This occurs when, in the same model, we have more than one occurrence of the same assumption or set of assumptions. This could lead to parts of the model being driven off different values for the same variable or assumption. This kills the control factor in your model as it loses its integrity. All unique assumptions or inputs in your model should have one and only one source within your model.

Rule 5: Thou shall save multiple versions of your work.

The analyst’s curse is losing all one’s work minutes before it is due. This shall not happen to you, because you’ll periodically save incremental upgrades or changes on your model as a new version of your model. This not only prevents you from losing everything at once, but also allows you to roll back in case you need to revert to a certain point in your model before things went crazy. Saving different versions increases control.

Rule 6: Thou shall check your work, check, and check again.

Just because you entered a formula and it returned a number does not mean it is correct. You need to consistently check new formulas and sections of your model as you move along to ensure that the formulas are indeed doing what you intend for them to do. This increases control and ensures integrity of your model, as mistakes are caught early and corrected as you go along.

Rule 7: Thou shall not produce ugly output.

Your model may end up in a CEO’s boardroom or a team presentation. Format it so that it looks good and appealing to the eye. Just because it’s numbers doesn’t mean the model has to look ugly. An ugly presentation can diminish the clarity of your work.

We shall reinforce these seven cardinal rules in subsequent chapters as we encounter situations that evoke them.

The Magic Keys of Financial Modelling in Excel

Since we’ll be issuing 99 per cent of our Excel instructions using the keyboard, per Cardinal Rule 1, take a minute to identify the following keys on your keyboard that we shall be using throughout this series:

• Control key, identified on your keyboard as Ctrl

• Shift key, identified on your keyboard as Shift or an upward-pointing block arrow key. Usually there will be a shift key on either side of your keyboard. Identify the one on your left, as this will be the one you’ll most often use.

• Alternate key, identified by Alt. You may have two of these on either side of your space bar key; identify the one on your left

• Escape key, identified by Esc, usually located on the top leftmost corner of your keyboard

• F2 key, the second of the function keys, usually numbered from F1 to F12, on the topmost row of your keyboard

• F4 key, the fourth of the function keys

• Enter key, identified by the word or a left-pointing arrow, usually located on the right side of your keyboard

• Page Up and Page Down keys, identified by PgUp and PgDn respectively, usually located on the right of your keyboard

• Space Bar key, usually the widest key on your keyboard, located in the centre of the first row from the bottom

• Arrow Keys, a set of four directional arrows on the right side of the keyboard. Starting from the lowest row, they are the Right Arrow, Down Arrow, Left Arrow, and Up Arrow. These help you move left, up, right, or down in your sheet.

In this series, we shall make reference to these special keys by their short forms or keyboard names as captured above. And of course, you will use all the lettered keys from A to Z for special commands or building your model.

Holding versus Tapping Keys

In this series, we shall visually depict the keyboard instructions needed to carry out various commands in your model. These keyboard commands are often known as shortcuts. There are two ways in which the shortcut keys on the keyboard can be used: by holding or by tapping. The holding option involves depressing the key and keeping it depressed. This is similar to taking a pulse and keeping your finger in place for a few seconds. In tapping, you only need to hit the key once without holding on to it. This is similar to the normal motion you would go through if you were typing a letter on a keyboard.

We shall distinguish between holding and tapping when we visually depict the keyboard instructions. A hold key in an instruction sequence, shall be depicted as a shaded-in box.

44890.png

A tapped key in an instruction sequence shall be depicted as an unshaded box.

44955.png

Over time, we shall no longer visually depict the instructions as you become familiar with what’s held versus tapped.

So we would visually depict the sequence to create a new Excel spreadsheet, Ctrl-N, as follows:

45027.png

This means that while Excel is open, you hold the Ctrl key on your keyboard and tap the letter N on your keyboard to complete the sequence.

You do not ever have to type two keys simultaneously. In an instruction that involves multiple keys, you can tap or hold one key after the other. Note that some shortcuts do require you to hold down more than one key at a time; however, to get to those two keys, you can hold down one first and then the next. In instruction sets that only involve tapping, just type them as you would normally type a word in Excel. For example, executing the following instruction sequence doesn’t have to be done any slower than you would type the word name.

45094.jpg

Try as much as possible to carry out these instructions at your normal typing speed. The faster you type, the more fluid these instructions will be to you.

Just a side note: The number 0 will not be used in any of our shortcuts. You can always assume that O in a shortcut sequence represents the letter O and not the number 0.

Getting Set Up

Let’s get started. Power up Excel – or if you already had Excel open, close all open files. If you powered up Excel from scratch, you should have in front of you a screen like this:

Image376.jpg

If you don’t have the screen that shows the iconic Excel gridlines – A, B, C, D lettering on the horizontal axis and 1, 2, 3, 4, 5 numbering on the vertical axis – you may instead have something that looks like this:

Image383.jpg

In that case, simply type Ctrl-N to create a new Excel file:

45164.png

Just a bit of nomenclature to get us all on the same page: What you have in front of you now is known as a spreadsheet. Go ahead and save it and make it an Excel file. (Remember Cardinal Rule 5.) To save a new Excel file or a new version of an Excel file, use the ‘Save As’ feature, which is executed by the shortcut Alt-F-A.

45229.png

Navigate to your desired folder on your laptop, give the file a name (e.g., Starters_120716_v1), and hit Enter to save it. There’s a method to our naming of a file:

1. The first part speaks to what the file is about. Just saving something as ‘Doc1’ or ‘File’ is not very intuitive, as you will have no idea what this file contains a month from now. Use the first part of the file name to give yourself an indication of what the file contains.

2. The second part of the file name is the date. Think of it as putting a timestamp on when you first created the file. This is useful when you’re sifting through files months or years later and want to make sure you’re pulling the right file from the right time frame.

3. The last past is for version control. As you develop your model, you will need to save newer versions of your model to capture significant changes.

When you save the file, make sure you choose a location that’s intuitive. Saving files on the desktop is not a good idea as you’ll end up with a cluttered desktop. Instead, after you’ve typed Alt-F-A, locate your ‘Documents’ folder. Next, click on ‘New Folder’ on the pane just below the address bar. As the text ‘New Folder’ is highlighted, replace it by typing a new name (e.g., Love to Excel). This can be the folder where you save all your Excel files that you will have to use in this series.

Now let’s delve into the spreadsheet proper.

Image415.jpg

Essentially, an Excel spreadsheet is laid out as a grid. There are vertical lanes and horizontal lanes. The vertical lanes are named after letters of the alphabet – starting from A, B, C. These are known as columns (shaded yellow above). The horizontal lanes are numbered starting from 1 and are known as rows (shaded dark blue above).

The intersection of a row and a column is known a cell. Three such cells are shaded grey in the shot above. Each cell has its own address, which is made up of the column name and row number. So the first cell in an Excel spreadsheet is A1. Try naming the grey shaded cells in the shot above.

Your answers should be K8, I10, and L13.

Another important element is what we shall refer to as the cell highlighter or cell selector. This is the cell that’s currently selected within your spreadsheet at any point in time. The selection is marked by a box in bold around the cell. At the start of a new spreadsheet, your cell highlighter is always on A1; in the shot above, we’ve moved the cell highlighter to M7.

The grid you see in front of you is called a sheet. Each Excel file comes with at least one sheet, also known as a tab. In older versions of Excel, we are presented with three sheets to begin with. However, we always have the option to add more sheets or tabs to our model. Remember the good old days of having folders and coloured tabs dividing each section of the folder? The tabs in Excel serve almost the same purpose. Sheet 1 contains all the relevant elements of your model that you believe need to go together in one section. Sheet 2 can be a different section of your model, and so on.

Later on, we will come back to these sheets and what you can do with them. For now, just remember that they exist to help you organize your model clearly so that everything is not jammed up into one complex model. (Remember clarity in flexcoclar?)

The Finger-Stretching Exercise

Now that we’ve got our spreadsheet laid before us, let’s get warmed with the finger-stretching exercise. For this, you will need a stopwatch feature on your phone.

In cell A1 of your model, type the letter a and hit