In Part 1 of this blog post, we asked 8020 consultant Yan Pronin to help us take a broad look at financial modeling, an analytic tool that more and more businesses are utilizing to project future performance. Today, we’ll continue our conversation and dig deeper to discuss financial modeling best practices that support the creation of elegant and effective models – those that are smartly constructed, streamlined, and easy to follow. Informed by these models, CFOs and other business leaders can gain competitive advantages that help them power their companies confidently forward.

“Everything should be made as simple as possible, but not simpler.” – Albert Einstein

A Financial Modeling Best Practices Overview

You’ve spent thousands of hours creating financial models across a wide range of industries, all while educating and mentoring colleagues. Can you share some of the best practices that you return to time after time?

Yan: Every financial modeler will have a unique set of best practices – the tricks of the trade that have always worked for him or her. But I do think there are some commonalities across the board. It’s helpful to consider best practices in two categories – conceptual and tactical. In other words: What’s the best way to think about the problem, and what’s the best way to model it?

Conceptual Best Practices

Yan: These conceptual best practices will serve any modeler well, no matter the questions at hand or the industry:

• Make your model parsimonious. To quote Albert Einstein: “Everything should be made as simple as possible, but not simpler.” The same thing applies to a financial model. One should focus on making the flow and logic as clear as possible without stripping things so bare that the model isn’t adding value. Remember that others will inherit your work. Don’t make them waste time having to untangle unnecessary layers of complexity. Somewhere down the line, someone will be heartily thanking you!

• Validate your assumptions. Include back-up references for the assumptions made and, if you’re using regression in your model, make sure to reference the ANOVA table.

• Validate your output. Look away from the spreadsheet and ask yourself if the outcome of the model is within the confines of common sense. Are you predicting double-digit revenue growth in a declining sector? Is your market share growing without deployment of revenue-building initiatives? Is the beta of your equity portfolio equal to that of a bond portfolio? Make sure you have sound logic to back up what you’ve created.

• Think ahead. A good modeler answers the questions asked, but a great one anticipates the questions that are sure to come later.

• Finally, build your model knowing that revisions are inevitable – often last minute. Be ready to roll with the changes!

Tactical Best Practices

So now you’re ready to start building your model. What guidelines can you offer?

Yan: To create a model that gets to answers in the most straightforward way, I’d offer the following advice:

• Use industry recognized formatting.
• Blue for inputs
• Black for calculations referencing the same sheet
• Green for calculations referencing other sheets
• Use a consistent layout of assumption tabs to allow for a simultaneous manipulation of data across multiple tabs.
• In computation-heavy models, turn off automatic calculations. Instead, use F9 to recalculate your work.
• As a time saver, use the “data table” feature to build sensitivity analysis that combines the set of assumptions used.
• Use the full spectrum of available add-in functionality. For example, with a data analysis package, one can quickly build correlation matrices, regressions, exponential smoothing, and more. While this won’t fully substitute for dedicated statistical applications like SAS or R, it can be quite helpful when working with small or medium data sets.
• Remember that not everyone has perfect vision. Format your output for printing so you don’t need a magnifying glass to read it.

That’s a solid tactical “to do” list. What’s the biggest tactical don’t?

Yan: It goes back to where we started – the beauty of simplicity. As I’ve already touched on, needlessly complex models can bog down those inheriting them. To prevent future confusion, avoid large, multi-level nested formulas whenever possible. Instead, try to break them out into easy-to-follow steps. Multi-step, elegant simplicity is always better than cluttered complexity.

And, while we’re on the subject of don’ts, avoid amateur mistakes like merging cells (opt for the “center across selection” function to facilitate data manipulation), using named ranges (cool in theory, but vexing for anyone trying to interpret your work), and “hard-coding” coefficients into formulas (better to make formulas reference the assumptions box).

The Competitive Advantage of Financial Modeling Best Practices

Thanks for these great tips! To sum up our conversation, it sounds like information gleaned from financial modeling has become indispensible to CFOs and other business leaders. Is that fair to say?

Yan: It is. Expertly crafted, elegant financial models can generate results that keep CFOs and other business leaders fully versed in the possibilities of their companies. Whether they’re navigating a merger/acquisition, building a budget, dealing with investors, or just deciding on next steps, they’ll be better prepared to anticipate and address a wider range of questions, issues, and challenges. And if your models aren’t meeting expectations, reach out to an expert to help with refinement. Your information is only as good as the model that generates it. Without a doubt, an experienced modeler with a keen understanding of advanced statistics can bring exponential benefits and be well worth the investment.

Want More?

If you're interested in learning more best practices in regards to financial planning & analysis or financial forecasting, you can download our free financial forecasting process guide by clicking the image below. It's a wonderful resource to improve the predictive value of your forecasts.

You can also contact us to learn more. Thanks for reading!

About the Interviewee

Yan Pronin has held leadership positions at Caesars Entertainment, Tropicana Entertainment, and SLS Las Vegas. He has also advised Warner Bros. on implementation of statistical modeling in revenue maximization. Before joining 8020 Consulting, Yan served as a Director of Revenue for SLS Las Vegas overseeing revenue management, operational analytics, and financial forecasting. He has passed the CFA Level 1 examination and holds a BBA from the University of Minnesota Duluth, a Graduate Certificate in Applied Statistics from Penn State University, and an MBA from the University of Southern California's Marshall School of Business. You can reach Yan at ypronin@8020consulting.com.