When I started my CFO and controllership practice in 2001, I was lacking a critical tool–a financial modeling application that I could feel good about. And not the dumbed-down versions that are on the market today. I wanted horsepower. But it didn’t exist yet for the price I was willing to pay.
So I used what probably 98 percent of all CFO consultants still use today–the spreadsheet. But around 10 years ago, I graduated and opened the purse strings. I got the horsepower I was searching for.
Your Tool of Choice is a Personal One
When I started my part-time CFO journey I messed around with Cashe, a tool I used in the ’90s in my W-2 role. Meh.
The first tool I bought as a consultant was Profit Mentor by Moss Adams. I didn’t like it either. I don’t think they did either as they eventually retired it.
I’m not name dropping the next two I acquired. Both are well-known in small CPA firm circles. But I hated both. See a pattern here?
Then I ran into the following three:
- Alight Planning (loved it)
- Whitebirch (loved it too)
- Quantrix (my current tool of choice)
I no longer use Alight or Whitebirch as both are now focused on industry verticals. The price points are not geared for solopreneurs either.
And that means Quantrix fits the bill. It’s powerful, but there is a learning curve. However, the investment is worth the time spent learning this robust tool.
Bottom line, the software you choose is a personal one. If you want to use spreadsheets, that’s your prerogative. I prefer the modeling platform due to automation and structure. I can move far faster using a system designed for financial modeling. Plus, mistakes are easier to make in the spreadsheet world.
Having Great Software is 20% of the Solution
Like losing weight, eating right is more than half the battle, not working out.
Accordingly, I view having a great financial modeling system as similar to working out. Adhering to a financial modeling framework with guiding principles is similar to eating right. Without the framework, the tool is worthless.
For the rest of this discussion, I’ll focus on the guiding principles of financial modeling. These principles can be used for any financial modeling tool. Even though my screenshots will be from Quantrix, you can still use Excel or any other tool you choose.
Know Your Most Important Question
Don’t start modeling until you answer this question first–what answers do I want from this financial model? For instance:
- When do we run out of cash?
- Can we afford this scaling-up process?
- What happens when our margins start dropping over the next 12 months?
- What happens to cash when we add the eastern and western territory sales managers?
- Can I afford these 3 overseas closeout shipments?
- Can I pull $1 million off the table (remove from equity)?
Knowing your key questions first keeps the model from becoming a going-through-the-motions activity. Don’t let that happen.
What is Your Timeframe?
Most models I review are three years. Why not just one? How about two?
There’s no reason to settle for default periods. If you need two years, then role with two years. If one, forget the other two years.
It took me about 20 years to figure this out. For example, specialty contractors and IT firms with large contracts are the hardest businesses to model. That’s because their sales funnels are lumpy and not all contract periods are the same. That’s why I sometimes just forecast out 6 months and keep refreshing the model every month.
Don’t forget to select your periods as well. Most financial modelers always forecast in monthly buckets. But I like forecasting in weekly buckets for cash-based businesses like QSRs.
Vineyards are another good case in point. Do you really need to forecast in months? I forecast using seasons like pruning, sprouting, thinning, and harvesting. Be creative.
Level of Detail
I’ve seen way too many models where the architect of the model planned at the general ledger level.
If you were starting a company, would you really plan at the general ledger level? As a business person, I think about activities, not account numbers. I want to plan by the way I think.
Determine well ahead of time how many levels of detail you will include in the model. Then ask yourself, does this minutia add value?
Don’t Just Focus on Dollars But Units Too
This is critical. I can’t begin to count the number of models from savvy financial professionals where there was no inclusion of units (like the widgets your clients sell).
I can’t stress how foundational this is. Consider the graphic below where the first three columns are actual units, the average selling price, and the total. The next three columns represent the plan (units, average selling price, and total).
Then I have two sets of variance columns. There’s simple variance which is straight arithmetic. And then the Unit, Rate, and Total variances round out the table using some simple algebra–you know, the kind of analysis we cried about in cost accounting.
A – This is the plan data that you and your client project.
B – These are actuals which will be discussed below.
C – Simple variances are calculated here. Essentially this is B less A.
D – These three columns calculate the dollar impact of simple variances.
Integrated Actuals
This is the perfect segue for mentioning the integration of actual results.
So you’ve built the financial model of a lifetime allowing you to forecast and stress test assumptions over 36 months. But you forgot one thing. How will actuals be integrated in the model?
In the image above, the first three sets of columns are for actuals. You have to have those, otherwise how do you compare yourself against plan, the targets, or budget?
It sounds easy. It seems obvious. But the model builder has to figure out the most efficient way to integrate actual results. You need to nail down this principle no matter how obvious it sounds.
Cash, Cash, Cash
I’ve lost track of how many models focus on the P&L. The balance sheet takes as much time as projecting the P&L.
And why project the balance sheet? We need to see what’s going to happen to cash.
Stress Testing
More important than creating scenarios, set up your model where you can quickly stress key parts of your model on the fly.
We can’t project black swans, but we can ask questions like, “What happens if churn changes from 5 customers per month to 10. The goal is to create a model that’s as fluid as possible.
What Are Your Guiding Principles in Financial Modeling?
Above all, keep it simple. Most of us are working with smaller businesses.
Even in one of my larger clients doing around $50 million in sales per year, I’ve reduced my critical (key) drivers down from 12 to 7 with no detrimental impact on the quality of the model.
Getting too detailed with the model only adds incremental improvements. Remember your purpose.
Finally, don’t predict the future. Create the future. Use your model as the map that numerically and economically quantifies your client’s business objectives. Then go and make it happen.
