Financial Modeling 102: 4 major types of consulting models

Today we continue our series on financial modeling (or financial modelling for you internationals) for consulting. Financial modeling scared the pants off of me until I figured out how useful it was, and I was even more motivated to be good at it when I realized how awesome it made me look as an analyst. I was purely motivated by competitive drive – what can I say?

In our earlier Financial Modeling 101 post, we covered 6 features of every great consulting financial model. Today we take it to the next level, highlighting the different types of financial models and different case types for which you’ll use financial models.

One of the earliest things you’ll use Excel for is market sizing—it’s an easy work stream to assign to the newbie—but there are 3 other types of models you should be aware of as well. Most of the spreadsheets you build in consulting will be based on 1 of 4 models.

4 Consulting Models:

1. Market sizing
2. Profitability
3. Market study
4. M&A

Next I’ll say a few things about each model and how you’ll build them using Excel.

Model 1: Market Sizing

Market sizing is one of the simpler versions of the type of models you will build. You’ll use Excel to look at where the market—a demand for services or delivery of a product—is heading. One of the key pieces you’ll build into your financial model is the ability to input assumptions that will impact the sensitivity of the final solution.

Market sizing questions are fairly straightforward. You’re measuring the existing market of an item in total units or total sales ($).  You’re trying to answer questions like:

• How many of X exist in a market?
• How fast is the market for X growing?
• What is the $$ opportunity if the client introduces X into the market?

Your Excel spreadsheet will be structured to calculate these numbers based on a set of assumptions.

The keys to market sizing models? Flexibility and data tracking. Someone will undoubtedly question every single number you propose for your first few months on the job. Therefore, in the spreadsheet keep your assumptions clearly linked throughout the document, and make sure you track your sources and link to them.

Model 2: Profitability For The Client 

With profitability, you will move beyond what you practiced in cases. On the job, profitability is more complicated than just standard revenue and cost. Maybe you’ll need to do a deep dive on product level profitability or total allocated cost profitability, profitability by product line or profitability by month, operating profitability or net profitability.

Remember in case practice, when you list out 4 options for why profitability may have declined? Well, model iterations 1, 2 and 3 might actually be an exploration of alternative hypotheses. You rarely get to the exact issue on the first try in the real world of consulting.

In fact, you could look at profitability 8 different ways, all within the same project.  To do so effectively, you will require a well-developed and highly accurate spreadsheet in Excel.

In a profitability model, you’re trying to find ways to optimize profit for the client.  You will build different scenarios in Excel to test solutions in order to identify the source (or sources) of declining profitability, or find areas of potential growth. Questions you’ll be answering include:

• How can our client increase profitability?
• Why is profit declining?
• What should the client do about it?

Some of the things you could be testing are:

1. Pricing and elasticity
2. Sales commission/channel analysis
3. Product mix analysis
4. Customer segment profitability (by demographic, needs, etc.)
5. Fully loaded cost

This is where some of your core accounting terms are going to come in.

What if you’re working on government projects or for a not-for-profit organization?  Will you still be expected to know and apply the profitability model? Yes! Even if it’s not financial profitability—it could be people served or donations received—all organizations have metrics by which they measure their performance. You’ll tweak the profitability model to the client’s particular problem, but the concepts are the same.

Model 3: Market Studies Focusing On Real Questions 

This is where it gets fun! You’re looking at real problems and real questions that your clients are dealing with, like:

• Should we enter the market or not?
• What’s our approach?
• How can we increase our revenues?
• Why is our market share declining and what can we do about it?

With killer Excel skills, you can actually come up with solutions.  There are 3 primary questions you’re looking at to tackle market study problems in Excel.

3 primary questions for market study problems:

1. How much money (profit) could we make if we enter this market?
2. Can we increase our revenues by either increasing prices (or spend/customer) or volumes?
3. Can we increase our revenues by either increasing prices (or spend/customer) or volumes?

At the end of the day, you’ll have a lot of data with a lot of moving parts, and your job is to lay everything out in a clear structure, investigate each area to find the key issues, and develop practical recommendations to solve the issues.

Excel will be your best friend.

Model 4: Mergers & Acquisitions/Discounted Cash Flow

An M&A model—the most complex that you’ll face in consulting—involves a DCF analysis, used to determine the present value of a company, and knowledge of how to use NPV. It also often includes market sizing/growth projections and profitability, so you’re covering every single type of model here. The good news?  You rarely will be staffed on a Private Equity or M&A case as your first assignment, and you’ll NEVER own the model if so—so you have a little time to ease into this one.

How much the client is willing to pay for a company is a combination of how much the target is worth now, how much the client can make based on changes they make after the purchase, and potential exit options.  With Excel, you’ll develop a structure to identify the issues that you want to evaluate, such as:

• How much should the client purchase the company for?
• What is the potential of that company?
• What exit strategy, if any, makes sense?

As much as you can, follow the overall guiding principles for Excel.  Make your spreadsheet beautiful and easy to read.  Learn the firm’s code for formatting and stick with it wherever you can.  Make sure you’re incredibly organized so that you can always refer to where something in the data occurred.  And finally, build your spreadsheets in a way that minimize manual operations.

If you want the best training available on the market (the “I wish I would have thought of it first” kind), check out Brian DeChesare’s BIWS Financial Modeling courses.

Market sizing articles

• Market sizing frameworks
• Consulting case interview frameworks
• Bain and company case interview
• Steps to solving any sizing question
• Finance case interview