The Art of Financial Modeling

January 10, 2023

It is difficult to conclude if financial modeling is an Art or a Science. It is rather a mix of both. While several online service providers cover the “Science” part of financial modeling, I feel sharing my learnings on the “Art” side of financial modeling can add some value for the readers.

During the last 9 years, I have built or supervised the preparation of financial models covering all major sectors (excluding E&P) for clients ranging from global financial institutions, DFIs, government entities, public listed corporations, conglomerates, family offices, asset managers, entrepreneurs, and start-ups. The scope of these financial models included business valuation, strategic options analysis, strategic budgeting, feasibility analysis, project finance, and leveraged buy-outs (LBOs). Below is a summary of my key learnings on the “Art” side of financial modeling:

PUO: The most important thing before building a financial model is to document the Purpose, User, and Output (PUO) of the financial model. This is an important step because the financial model design should be based on the requirements of PUO. For example, a model which has to be used by senior management of a corporation for strategic options analysis will require a user-friendly dashboard with a switch between strategic options showing the impact on the firm’s financials and output parameters such as firm valuation.
80/20 rule: The 80/20 rule is what separates a good financial model from a bad one. The idea here is to focus on 20% of variables in the model which determine 80% of the output. During my experience, I have noticed that the 20% variables that require the most attention include Revenue, COGS (DM, DL, and/or Overheads), CAPEX, cash conversion cycle, and terminal value assumptions. Solidifying these inputs makes the model outputs robust.
The unit of the model: It is best practice to determine a unit of production/sales for building the operating model such as “tons” for a cement manufacturer (exception: banks, financials). The unit helps to make the operating model more accurate as you may forecast price and volume separately, and link opex to the volume component of revenue. In the alternative approach where opex is considered as a percentage of revenue, both price and volume factors of revenue impact the opex which is a flawed approach. Linking the model to a unit does not only limit the opex to the volume component of revenue but also helps build cost escalations based on the economics of individual cost items (such as inflation and rate per unit).
The simpler the better: A good model is the one that is simple and easy to understand for the users. One of my professors during my undergrad mentioned that the excel file size tells a story about the skill of the financial modeler. This holds true for all the financial models which do not require Macros or VBA. If your file size is above 500 KB, there is a high probability you might have messed up with the formatting. One good practice is to always paste numbers using “unformatted text” function in the model and avoid copying any pictures.
Ratio analysis: Finance professionals often limit ratios to the analysis of historical statements. However, I am an advocate of applying ratios to the forecasts of financial statements. The key benefit is to make sense of your forecasts and compare them with benchmarks (public listed comparable). Variations in margins as compared to the industry need to be explained by the business’s overall strategic positioning.
Organized is better: Reduce the number of sheets in the model to 5 or 6 excluding the table of contents and introduction sheets. My six sheets generally include financial statements, valuation, inputs/dashboard, debt schedule, fixed assets schedule, and working. Any additional sheets or data sources can be placed in the model after the six sheets, classified as data, or highlighted with a different color. An organized model is easy to understand for the end-users. A table of contents with links to appropriate sheets can further increase the ease of use of the model.
Analytics are important: I often used cashflow waterfall charts for project finance and LBO models. This helps understand the inflows and outflows of cash over the life of the project. Also helps determine the key areas for cost control during the project life cycle. For valuation models, similar waterfall charts can be used to see the key determinants of business value.
Importance of market sizing: Regardless of the industry, it is always best to project market demand and supply in both the “unit” and “currency” of the model. The gap helps you understand if your market share, pricing, and margin assumptions are accurate. The supply side cannot be quantified in Tech or other scalable sectors, but for manufacturing, this remains a very good module to sense check the revenue and cost assumptions.
Break down the model: If you are dealing with a business that operates in multiple layers or units, it is best to break down the model into the individual layers and forecast it separately. While modeling district developments (real estate), I used to break down the model in LandCo. (land developer), DevCo. (property developer), and PropCo. (leasing, renting, and maintenance). This helps to understand the expected performance of individual components of the business.
Documentation and references: It is a good practice to add a column for notes after the column with the terminal year forecasts. This column can be used to add the sources for inputs or workings/formulas in the operating model. This makes the model more user-friendly and adds credibility.

Here are some limitations of the financial models:

A financial model is only a tool for analysis and not the analysis itself. The outputs are only as good as the inputs and the structure of the financial model. A good analyst can come up with a better analysis using a simpler model as compared to a “not so good” analyst using a complex model with macros and VBA.
A financial model is a linear tool. Balance sheets are for the end of the year. Therefore, any fluctuations in working capital are not reflected unless the model is developed on a monthly/weekly basis. Therefore, using annual models to project cash or working capital requirements is not a good idea unless you know how to use cash conversion cycle assumptions dynamically.
Valuation models are highly sensitive to the terminal value which can explain up to 80-85% of the business value in some cases. Regardless of how detailed the operating model is, if the assumptions used to calculate a terminal value are flawed, the model output will be flawed.
The best way to validate the outcome of a financial model is to compare results with the market evidence (such as a multiples-based approach in the context of valuation). If the results are significantly different, it is a good idea to check the structure and the assumptions again. This doesn’t mean that the financial model and market evidence always have to give similar results.

Overall, I think the financial models are an extremely useful tool. However, it is important to know the purpose and limitations of the financial model before we draw any important conclusions. A good analyst will know the trade-off between detail orientation and its marginal benefits in the financial modeling exercise. Furthermore, a good analyst will give equal importance to the accuracy of the inputs as compared to the structure of the model.Hope you found the post useful. Please feel free to share your insights, comments, and queries.P.S. All credits to my colleagues, clients, mentors, instructors, and friends.

Need Help?

Schedule a discovery call with one of our consultants

LATEST INSIGHTS

The Art of Financial Modeling

Need Help?

The Crucial Role of Commercial Integration in M&A Success

Portfolio Valuations: Why It Matters for Private Capital Investors

Developing the Value Chain of Wind Turbines and Equipment