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 profit 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:
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 found 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:
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:
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.