The modeling lessons in this article come from more than a decade of building institutional financial models. They were learned and reinforced with hard-earned trial and error.
Every dealmaker needs to know their way around simple analytics. Of course, you can hire people to do the complicated work, but a good understanding of the basics will drive greater success.
Show Your Work
Think back to your middle school math class. Regardless of how elegantly you solved a problem, your teacher always wanted to see how you arrived at that solution. This was always a challenge for me. I like clean, scribble-free worksheets.
Fast-forward to now, and you may be surprised to see that advice at the top of my list. However, it’s probably not what you think. I’m not suggesting you include a worksheet full of your thoughts. Rather, I encourage you to use comments liberally and separate complex solutions into multiple lines, especially where logical operators are concerned.
The value of showing your work comes in the transportability of the file. You want the workbook to be passed from one analysis to the next with very little lost in translation. More importantly, though, you can learn a lot from your past self if you come across a rarely used solution. Good notes will save you hours of rebuilding.
Keep It Simple
Simplicity is the key to all success. Complicated solutions work well for intermediate problem solving, but the beginning and end should always be easy to follow and comprehend.
Most challenging problems start with a simple first step. However, our nature is often to solve the problem three steps ahead of the starting point. That will only lead to frustration.
Think about the next step at every turn. So long as you keep moving in that direction, you won’t find yourself stuck trying to crack an unsolvable problem.
Design with the End User in Mind
I always say that good analysis is 20% technical and 80% design. You may produce the best technical analysis in the world, but it’s worthless if your consumer can’t understand.
Before you begin, consider who will be consuming your model. Think about what functionality they need and how you will provide that. This perspective is essential to ensure your model doesn’t just sit on a shelf.
I like to use industry-standard formatting – blue inputs and green links. For outputs, make sure that your summary pages flow with the story you want your user to read.
Being user friendly is one of those modeling lessons that actually works well in life, generally. Every communication has the potential to influence. Think about how it will be received before committing fully.
Use Smarter Lookups
Spreadsheets can get clunky very easily. Your ability to build flexibility and efficiency depends on the way you access information from one place to the next.
A spreadsheet allows you to access information in fixed or dynamic ways. In my opinion, there are very few reasons to access information with a fixed link. Most financial models need to access information from a range of cells for summarizing or inputting data.
My two favorite lookup functions are:
- INDEX-MATCH – lookup a dynamic range across rows and columns using matching criteria
- SUMIF – sum across a range of cells based on predefined conditions
Spend some time learning about these functions and playing with different use cases.
Use MVC
The MVC architecture unifies most of these modeling lessons into a single framework. It stands for Model-View-Controller, and it’s the foundation for most modern software.
This architecture separates the three functional areas of a piece of software into a siloed and protected area. Let’s look at each very briefly.
- Model – the brains of the system. This takes inputs from the controller, manipulates it logically, and outputs to the view.
- View – the output from the system. This takes results from the model along with any other data to make a user-friendly output.
- Controller – the input to the system. This takes inputs from the user and feeds it into the model.
There is some overlap and double duty for each of these, but most cases keep them well separated. For example, sometimes the controller manipulates inputs before feeding them into the model to streamline the user interface. Similarly, to skip duplication, the view may take data from the controller directly.
So many of my modeling lessons came from looking at the financial model as a piece of software. This mental model shift holds so much potential for you to create scalable and reusable tools. It’s well worth spending the time to dig deeper if you expect to make a living in building great financial models.