Flexibility – three basic guidelines
We all want our models to remain flexible so that we can continue to update them and use them going forward. Lack of flexibility is a major cause of frustration and excessive maintenance of the models and is the primary reason a model needs to be rebuilt after some time.
Here are three basic tips for keeping your models nice and flexible:
- Never hard-code numbers (put a number in a formula/calculation that can be referenced instead)
- Keep assumptions separate and well organized
- Spend time up front thinking about what you might want to be able to change later
-For example: number of years, market growth, market share
How to build a model
It’s important to think about a model as a process rather than an outcome. There are four key steps to building high quality models that address the problem at hand and remain error free and easy for team-mates to understand
1. Determine the necessary output. If you don’t know what you want to do with the results or how they are going to be used then you should ask clarifying questions upfront.
2. Work backwards from the required outputs to your input assumptions. Work out what will be calculated by the model and what is an assumption, then spend time thinking about where you are going to get the support backup for your assumptions – and don’t forget to document these sources.
3. Build model on paper. Before you open excel you should diagram out the model on paper. What are the important sections? What merits it’s own tab? How are you going to lay out the tabs? It’s a good idea to get feedback from others who will be using the results or providing input at this stage to avoid having to redesign later
4. Build model on computer. If you have done 1-3 above this should be relatively easy, I’ll cover some of the tips and techniques for doing this in a future posting
I outline more details about what you need to do in each of these steps in my ebook
String functions
String functions are an extremely powerful and useful technique that can often save you hours of work if you are dealing with large database-type models that are frequently updated. They are much faster than recording macros or using visual basic to manipulate data. They also make your work less error prone as if you are repeating a manual process to import new data and get it in the right format you will often make mistakes.
Here are some of the most useful:
- LEFT (text, num_chars): returns the first character or characters in a text string, based on the number of characters you specify Example: LEFT(“Test”,2) = “Te”
- RIGHT (text, num_chars): returns the last character or characters in a text string, based on the number of characters you specify Example: RIGHT(“Test”,2) = “st”
- MID (text,start_num,num_chars): returns a specific number of characters from a text string, starting at the position you specify, based on the number of characters you specify Example: MID(“Test”,2,2) = “es”
- LEN (text): returns the number of characters in a text string Example: LEN(“Test”) = 4
- FIND (find_text,within_text,[start_num]): finds one text string (find_text) within another text string (within_text), and returns the number of the starting position of find_text, from the first character of within_text. [Note that FIND is case sensitive. SEARCH is an identical function that is not case sensitive.] Example: FIND(“s”,”Test”) = 3
- TRIM (text): removes all spaces from text except for single spaces between words. Example: TRIM(“ Test ”) = “Test”
- PROPER (text): capitalizes the first letter in a text string and any other letters in text that follow any character other than a letter. Converts all other letters to lowercase letters. Example: PROPER(“tEsT”) = “Test”
For more useful functions and worked examples see my ebook.
Types of spreadsheet models
When you start building a spreadsheet, it’s important to start with the end in mind and know what you are trying to use the spreadsheet for. There are three types of spreadsheet, which I’ll discuss in this post, and it’s important to be clear about which type of spreadsheet you are building when you start so as you don’t get confused.
1. Database-oriented
This is a common spreadsheet type, where you have a large number of input variables spread across a large number of records. It’s important with this type to keep all the input data on dedicated tabs so that you don’t confuse with calculations or other assumptions and so that it’s easy to keep updated without changing the data or introducing errors because you forgot to update a calculation or pasted data over the top.
2. Calculation-oriented
With this type you have far fewer inputs and the bulk of the work goes into the calculations. It’s important to clearly put your assumptions on a dedicated sheet, and ensure that every assumption goes into it’s own dedicated cell – never put an assumption inside a formula as then it won’t be transparent to either you or someone else and you might end up with the same assumption in multiple places and errors when you try to update. Constants, like days in a year or hours in a day are OK to include in the formula.
3. Scratch-pad
This is a very typical model where you want to do a back of the envelope type calculation to get at a quick answer. Structure and layout matter less as you will often discard the results afterwards. The important thing to remember about this type of model is that if at any stage you decide to build a more detailed model of one of the other types you should start from a blank sheet – many errors get introduced from starting with a scratch pad model then hacking it into one of the other types.
Shortcut key for paste-values
One of the features in excel which we all use a lot is ‘Paste-Values’. This is a very useful feature, so its surprising that there isn’t a quick way to do this with a direct keyboard shortcut.
There may not be a direct shortcut for this function, but you can still do with with just a few key presses and as you will use this very often it will come naturally to you. The exact key sequence varies depending on which version of excel you are using, but luckerly the ‘old’ shortcut keys still work in more recent versions of the software. Here is the reference:
Alt E-S-V (Microsoft office pre 2007)
Alt H-V-V (Microsoft office 2007)
Ctrl-Alt-V (Microsoft Office 2010 – so it looks like they did get there in the end after all!!)
Hello World
Hello, and welcome to Excel for Pro’s. This new blog will focus on addressing advanced questions on how to use Microsoft Excel, in particular including processes for creating top quality models.
