Financial Modeling 101: 6 Features Of A Killer Consulting Financial Model

Strategic thinking. Passion for results. Creativity. Structure.

These were the key words used during information sessions, interview introductions and sell weekends to describe my future life in consulting. As I look back on it now, they are the words that the partners used to describe Bain’s solutions and partnership strategy with clients, but these words only described 10% of my average day.

Yes, there was one word that they left off the list that everyone who has spent 1 month in consulting knows should be on there: financial modeling (or financial modelling for my non-US readers).

Please don’t confuse the glitzy runways of New York with the type of modeling I’m talking about. There’s nothing glitzy about a 3AM coffee run to keep you fresh as you manipulate thousands of lines of data, format toggle switches and try to remind yourself that a 22-year-old probably shouldn’t be determining the future of a multi-billion corporation with your little monkey Excel spreadsheet.

No, financial modeling isn’t for sissies. I just wish I would have known that earlier than I did.

My first project at Bain was a short 3-monther, a market entry assessment for a #2 player in business services. I had to assess the potential future revenue of the venture, and I thought it sounded fun – like a market entry case. Yes, my job was to run the model, and I had no idea how ‘in-over-my-head’ I was.

I am so thankful that my supervising consultant had a sit-down with me 2 days before my first update with the Manager, or I would have truly embarrassed myself. I had created a 1-page spreadsheet with 3 columns of hard-coded data and called that a model. Oh, what a silly fool I was! As the reality sunk in when he gently said, “Okay, well, we have a lot of work to do here,” I prepared for my first all-nighter just weeks into the job.

Thankfully, I’m a quick study, but that first project was an on-the-job learning experience I wish I didn’t have to have. And now, as I advise my Black Belt clients that rocked their MBB interviews on how to prepare for success in consulting, I have 4 words I always share: rock at financial modeling.

As did most of my peer consultants, I came on the job with strong soft skills; I could hold my own with a CEO over dinner, draw out information from a survey respondent on the phone, and capture insight from analysts during a cold-call. I had a huge measure of confidence. I just needed way more prep in the one hard skill that makes you a truly coveted analyst or associate – mastery of Excel.

6 Features of a Killer Consulting Financial Model

You must know your way around Excel—it’s a tool you’ll use on a daily basis throughout your consulting career. Generally, you’ll be using it for financial modeling, where you’re inputting a number of assumptions and determining the size or growth projection of a market, profitability of a new product, or key cost drivers. Even when you pass the management of the spreadsheets on to a junior analyst, you’ll still be dealing with data sheets, reports, and models on a regular basis—knowing your way around is a MUST.

You may already be familiar with Excel from business classes or internships—but more than likely your model, even if you worked with large data sets, was focused on running individual pieces of analysis. In consulting, you’ll build more complex models that run multiple pieces of analysis all at the same time.

There are a number of factors that make a great model—we’ll cover some of the key ones here.

The 6 factors that make a great model in Excel:

1. Formatting and layout
2. Flexibility
3. Formulas
4. Linking and hyperlinking
5. Pivot tables
6. Versioning

 

1. Formatting and Layout

There is a group of unspoken consulting best practices that sets the bar.  Every firm has slight differences in the styles they use, but there are some general guidelines you can expect to see.

Assumptions are usually identified by a single color, like blue; your hard coded entries will be coded with a particular color, like green; your final answer will be bold, etc. Pay attention to the firm’s code to differentiate between a calculated, a hard coded, and a hard coded but referenced item inside the document.  It’s crucial that you pick up on these in early training and adopt them promptly.

Why care about the way the spreadsheet looks?  A beautiful spreadsheet that has slight errors is so much more graciously accepted than an ugly spreadsheet that is partially wrong. A spreadsheet that is right is always better, but a beautiful one that is right is 10 times better than an ugly spreadsheet that’s right.

2. Flexibility is Key

Success in Excel involves managing your data very cleanly and clearly. You’ll need to work with the expectation that someone is going to go back and repurpose your data.  They’ll want to look at the scenario through a different lens—e.g., what happens if we run it for six years instead of five?  What happens if we increase the cost by 10% after the first season?  Your ability to respond to that on the spot will be a huge differentiator in distinctive performance, and you need to know how to integrate your assumptions flexibly—you want to hard-code as little as possible.

3. Knowledge of Formulas 

A key thing that differentiates consultants who are okay at Excel, good at Excel, and great at Excel is knowledge of formulas.  Excel has hundreds of formulas embedded in the system.  You want to have an understanding of what’s available and how they work.

VLookups, HLookups, concatenation, goalseek, and the basic financial formulas—SUM, AVERAGE, SUMPRODUCT (for weighted average), etc.—will save you time and, again, help define you as a valuable contributor.  Knowing which formula to use at the right time is a really key asset.  If you do nothing else, spend 2 hours going through the function lists in Excel and review what each is for —then plot out a scenario where you could apply each one.

By the time you start your first project, your goal is to get Excel to work for you instead of you being a slave to Excel.

Never link a source inside the site to something out of your control—like a link to an Internet site.  What if it’s moved or gone tomorrow?   Rather, save copies on your computer.  In addition, be prepared to set up a table of contents that links to each part of a client-ready model—having these links working will help you navigate throughout your model and save you time on clean-up early on.

5. Pivot Tables are Critical

In financial modeling, understanding everything you can do with pivot tables is essential—and will make you look like a genius (or a moron if you don’t know how to use them).  A lot of people have never used a pivot table before—I hadn’t before I started consulting—so having this skill is a huge boost.

6. Versioning 

Versioning is the regularly saving of your Excel documents under different filenames (v1, v2.2, etc.).  Get up to version 27 if you need to.  Every time you make a major change, or at least every day, version it, note it, and make sure you sourced everything that’s inside the document.  Why?  Because you will be probed, and your ability to follow your trail all the way back to the beginning, even if you’re working on something for multiple weeks at a time, is critical, both to the project and to your reputation.  Avoid learning the hard way—it’ll be embarrassing and you’ll kick yourself because “we told you so.”

Key skills

Here’s a summary of the key Excel skills you’ll want to, at the very least, be familiar with (if not master) before Day One on the job.

• Formulas (average, sum, subtotal, concatenate, weighted average, CAGR)
• Pivot tables
• Data analysis (vlookup, goalseek, scenarios)
• Sensitivity analysis
• Data cleaning (e.g., remove duplicates)
• “Dummy-proofing” (password protection, locking cells, conditional formatting, conditions on data, default drop down lists, etc.)
• Shortcut keys
• Macros (we could devote days to this alone)
• Graphs (mostly bar and line graphs)
• Linking and link tracking
• Formatting (print layout, header/footer, repeat rows at top, etc.)

Online Tutorials

If you’re looking for a great course to take you beyond self-study, we recommend ours – the only one on the market that is tailored specifically on the models you’ll build in consulting. If you’re looking to learn how to model like a banker, we recommend the one available at Breaking into Wall Street.  It’s expensive, but it’s also a very good course. It’s geared toward tooling up investment bankers who are known for being killer modelers—if banking or PE is in your target future, you should definitely get going with this standard now. It’s the most relevant, comprehensive modeling course available.

Final Thoughts

If you’ve never worked with Excel for 10 hours a day, it can be daunting. Our best advice is to just dive in, but get started now. There will be times when you will need to recreate the framework you’re using for your model because it’s not quite right, but that’s part of the process. The earlier you start, the earlier you’ll identify areas you need to adjust. You want to catch mistakes or misdirection as early as possible, so take a critical eye to your model early in the process.

It will take time, and that’s okay. It’s worth it. As you begin to work more with Excel, it will become apparent that taking the time to refine your model in the early stages actually creates short cuts later on. If you can hone this skill, you’ll become known as the ultimate modeler—you will always be in demand and you will always be a top performer. The ability to create accurate and usable models in Excel is a core analytical skill that is highly sought after in firms. No matter how well (or how poorly) you do at everything else, if you are known for building great models, you’ll create a name for yourself.

For more specific how-tos on Excel and other skills to master on your way to becoming a top-rated management consultant, purchase Excel for Consulting RIGHT NOW – it’s the course we wish we would have had when we started in consulting.