Excel models aren’t just for investment bankers. From entrepreneurs to product managers to students writing a thesis, a good model can save you time and let you shine in front of a supervisor.
But turning a blank spreadsheet into a killer model can be a daunting task. Poorly built models are a nightmare, to say the least. Never fear! Five easy steps will have you modeling with the best of them:
1. Concept First, then Spreadsheet
Tempted though you may be to open Excel and get started, building a model has a lot in common with building a house: you need blueprints before you can lay the first stone.
Start by taking a blank piece of paper and writing down what you’re trying to solve for. For an entrepreneur or product manager, it could be “calculating the projected profit of your small business or product;” for students, maybe it’s “estimating the number of female voters in the next election.”
Next, write down, as a simple algebraic formula, what it would take to get to that number. For example:
Projected profit of my small business = projected revenue – projected cost
Great! This gives you the most basic structure around which to build the model, and a logical way to lay it out in Excel.
2. Get to the Basics: Identify your Inputs
Now take it to the next level:
Projected revenue of my small business = (projected number of widgets sold x projected price of widgets) – (projected fixed costs + projected variable costs)
And keep going:
Projected fixed costs = rent + utilities
Projected variable costs = (cost of materials per widget + cost of shipping per widget) * widgets sold
As you get down to the details, you’ll start to get a sense of the raw inputs you’ll need, be they true facts (e.g., your monthly rent specified in your lease, or number of people who live in your county) or just best-guess assumptions (e.g., how many of those people would buy your widgets).
Now you can start building. Use your logical structure to make your model intuitive: in this case, put revenues on one side and costs on the other. Organize revenue into “number of widgets” and “price;” organize costs into “fixed costs” and “variable costs;” organize “fixed costs” into “rent” and “utilities.”
Make it easy to see what your raw inputs are. Widgets sold, price, rent, utilities—an input is anything you’re assuming, rather than calculating. Highlight these cells in a different color or use a different color font. Just be consistent! In a large model, you might consider creating a separate “inputs” tab to keep all your assumptions in the same place.
3. Never Hard-Code Anything
Hard-coding is the deadly sin of modeling. It means writing in numbers where you should be writing in formulas.
There are two types of numbers in an Excel model: inputs and calculations. Inputs, as described above, are typed in by you, with either a source or an assumption to back them up. Calculations are formulas you type, which produce a number by doing math on other cells.
Let’s go through an example:
Taking the same scenario as before, your inputs are price ($4, in cell D5) and number of widgets sold (100,000 units, in cell D6). The revenue is $400,000, shown in cell D7.
The question is: what do you actually type in cell D5? You could have typed:
- “400,000” – this would make it a hard-coded input (which is incorrect: you’re calculating it)
- “=4 * 100,000” – this would make it a hard-coded calculation
- “=D5 * D6” – this is a true calcuation
Why use calculations? Imagine in two weeks you decide the right price is actually $5. The logical thing to do is change “Price” in cell D5 from $4 to $5. If your revenue is set up as a calculation, it will automatically update to $500,000—which is what you want. If you had hard-coded the revenue cell, by typing in “400,000” or “=4 x 100,000,” you would need to remember to update it manually to “500,000” or “=5 x 100,000” when you change the price. When your model is small, you might remember to do so every time. But as it grows, the likelihood of errors increases exponentially.
In fact, as the supervisor of many first-time Excel modelers, the majority of mistakes I’ve find are due to hard-coding. If you take only one piece of advice away from this article: don’t hard-code!
4. Design for Someone Else
When I build models, I like to pretend that the minute it is complete, I will need to hand it off to a stranger who won’t be able to ask me any questions, but will need to use it. Why? It forces me to get everything from my head onto paper—er, spreadsheet.
An intuitive logic set up is the first step, but I also make sure to include any information that might be asked of me, if someone glanced through the model for the first time:
- Explicitly write down any assumptions I made and the rationale behind them. Assumptions should always be grounded in facts, so that they can be justified to a skeptical third party
- Note any any sources I used
- Specify units of measurement (“Hey Alex, was that number in dollars or euros? Oh, Canadian dollars in 2006? I wouldn’t have guessed that…”)
- Add a “Notes” or “Intro” tab if the model is large enough. Think of this as a guide to the model, explaining what can be found where
5. Remember that Looks Still Matter
Being an analytical genius may get you pretty far, but most colleagues and supervisors will still be swayed with the aesthetics of your model. A couple quick tips for building a “beautiful” models:
- Be consistent throughout the model with whatever formatting choices you make. Yellow shading and blue shading should not mean the same thing. And yellow shading on one tab should mean the same thing as yellow shading everywhere else in the model.
- Fill all of your cells with a white background to make your model look sleeker
- Bold headings, and use 1-2 accent colors (it’s always a nice touch to use company colors, where relevant)
All make sense in theory but not sure what it looks like in practice? Download this Before & After example of a model to see the difference! Once you get the basics, you’ll be empowered to use Excel to get ahead in work or school.