Ramping Up Your Financial Modeling Skills

When I started my CFO and controllership practice in 2001, I lacked a financial modeling application that I felt good about. And I’m not referring to the dumbed-down versions that are on the market today (and back then). I wanted horsepower. But it didn’t exist yet for the price I was willing to pay.

So, I used the spreadsheet, which probably 98 percent of all CFO consultants still use today. But 20-plus years ago, I graduated into a bigger and more important financial mindset by opening the purse strings for a robust modeling system. 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.

Around 2006, I ran into the following three applications:

• Alight Planning (loved it)
• Whitebirch (loved it too)
• Quantrix (my current tool of choice)

I no longer use Alight or Whitebirch as both focus on industry verticals. The price points are not geared for small businesses 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.

The bottom line is that 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 its automation and structure. I can move far faster using a system designed for financial modeling. Plus, mistakes are easier to make and hard to spot 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 span three years into the future. Why not just one? How about two?

There’s no reason to settle for default periods. If you need two years, then roll 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 refresh the model monthly.

Don’t forget to select your periods as well. Most financial modelers always forecast in monthly buckets. However, 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 account level? As a business person, I think about activities, not account numbers. I want my model to reproduce the way I think about business planning as best as possible. Every business needs to find customers, sell to customers, and deliver to customers. A well-designed model should quantify that flow.

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 that exclude non-financial data (like the count of the widgets your clients sell).

I can’t stress how foundational this is. Consider the image below, where the first three columns are actual units, followed by the average selling price and then the total. The next three columns represent the plan (units, average selling price, and total).

Then, I have two sets of variance columns. I include a simple variance analysis, which is straight arithmetic. Then, the Unit, Rate, and Total variances round out the table using simple algebra.

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, and it allows you to forecast and stress test assumptions over 12 to 36 months. But you forgot one thing. How will actuals be integrated into 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. However, 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 only 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.

For a $50 million eCommerce business, I’ve reduced my critical (key) drivers from 12 to 7 with no detrimental impact on the quality of the model. Getting too detailed with the model only adds incremental improvements.

Finally, don’t predict the future. Create the future. Use your model as the map that numerically and economically quantifies your business objectives. Then go and make it happen.