Financial Modeling: Part 2 – From Whiteboard to Spreadsheet

This post is part of the series on Business Development Essentials. These posts provide an overview of the fundamental areas of business management. Feel free to contact me directly should you have any questions or need help in any of these areas!

In this second part we will discuss how to get started with our model by taking it from an idea stage to a well-planned structure. Personally I find the beginning to be one of the toughest parts: you are staring at an empty spreadsheet and have no idea where to begin. There is, however, a relatively simple solution: stop staring at your screen and go back to pen and paper for now.

Everyone Loves a Wish List

A great way to start is with the list of functions you want your model to have. Here is an example for a real estate investment model:

  • Has to be able to project rental income, operating expenses (e.g. property management), general overhead costs (e.g. legal and accounting costs) and financing costs (interest, bank fees etc.)
  • Needs to let me change growth rates for both income and costs
  • Has to have the option to let empty space to new tenants
  • Should offer the possibility to change debt financing terms in future (in case you want to re-finance your property)
  • Would ideally also show sales proceeds and tax expenses

When you look at the above list you will notice that I started with the absolute ‘must haves’ before continuing with the more optional functions. Prioritising functions in this way will keep you focused when building your model in the face of time or budge constraints and make sure you do not get sidetracked building rather optional parts first.

Now that you know what you want your model to do, it is time to move to the next stage: structural design. I know this probably does not sound very exciting, but it really is vital: starting without a solid structural concept in place will most likely come to bite you later on – you will see why in a minute. We will first have to translate our list of requirements into a list of model components to get the different building parts of our financial model. In our example it could look like this:

  • Input sheet: for all growth assumptions, cost assumptions; this sheet drives the model
  • Rent roll: shows all lease contract data (start and end dates, monthly rent amounts, etc.) to calculate rental income, including new lettings
  • Calculations sheet: performs all calculations; gets inputs from input sheet and drives output sheets
  • Output sheets: profit and loss statement, balance sheet and cash flow statement; maybe a one page summary sheet with some graphs to provide a quick performance overview

Why You Really Want to do This / The First Iron Principle of Model Design

Obviously this list represents a very basic model structure and you may think that it is not even necessary to go through all this planning effort. However, for bigger projects you will find it an indispensable part of the process, and especially for beginners it is useful to make it a habit from the beginning. If you are still in doubt then you will be cured once you try doing a more complex project. Imagine you have to write hundreds or thousands of formulas in order to deliver a large number of output metrics, and you started building your model without first determining which parts should be drivers, and which should be just output sheets, and the relationships between the different sheets – it will become a nightmare!

You can now use this structure to establish the relationships between the different parts: where is data entered, how do the inputs link into the calculations, how do you design your calculation sheet efficiently so it can handle many different scenarios, and how does that impact the design of your output sheets?

We will look at the details later, but there is one principle I would like to introduce at this point because it is so important: thou shalt have no more than one input sheet! All input variables should be on the same sheet, and this sheet should drive the entire model. No other sheets should contain any variable inputs (note: this does not include static data inputs, in our case lease contract data – something that does not change can be on a different sheet than the input sheet). There are two primary reasons for following this practice: Having all inputs on one sheet helps you remember to make all the necessary changes when updating your assumptions. Also, it is easier for other users to understand your model if they don’t have to go hunting for other input variables across all your sheets (thinking of more complex models again).

Using Flow Diagrams in Model Designs

For more complex models it can be useful to map out the structure of the model graphically; you can do this for example with a flow diagram to show the interaction between the different sheets and how data is processed from input to output page.
Today we have taken an idea from which we derived a list of desired functions, which we then turned into a structural design for your model. Now we are ready to get started with actually building our model. Next we will look at formatting practices and I will show you how poor formatting can render a model useless – and how great formatting can make your life incredibly easy. Check out the next post in the seriesAll About the Looks.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s