What Is Financial Modeling? (And Why Are We Still Doing It in Excel?)
5 Min Read
What is a financial model?
At a very high level, a financial model is an umbrella term for a tool that helps people make important decisions about finances by displaying possible financial outcomes given different circumstances.
For example, if you were deciding between buying a used luxury convertible with cash or financing a brand new four-door sedan, you might create a simple table that lays out the pros and cons of each.
You might factor in regular maintenance costs for the older convertible, as well as monthly payments for the sedan, in order to determine an average monthly cost for both and whether that monthly cost is sustainable given your current income and cash flows.
Speaking of cash flows, you would also consider your debt payments, mortgages, and any other relevant factors before making the decision that’s best for your long-term financial health.
This is an example of a simple financial model, which is dynamic and accounts for the relationships between several variables. Typically, financial models include financial forecasts and account for the various scenarios that result from different permutations and combinations of the variables under consideration.
In corporate finance and accounting*, financial models are often complex spreadsheets that are built by financial analysts for specific purposes, such as projecting sales growth, determining budget allocations, calculating the cost of new projects, and even conducting company valuations and appraisals.
What is financial modeling?
Financial models are created and used by businesses to make important financial decisions that “should” result in positive outcomes, such as growth in sales, revenues, and profits.
That being said, financial models can also be used to determine the lesser of multiple evils, in the event that a business is unable to avoid a money-losing scenario (such as COVID-19).
Whatever the case, a financial model is simply an outcome-agnostic tool. It is only as good as the analysts who made it and only as useful insofar as stakeholders are able to understand it.
Every financial model is created to answer a specific set of questions and includes various assumptions, such as revenue growth, financing rates, and utility values for non-monetary choices (e.g., replacing one software solution or hire with another of equal cost).
The most common goals of an organization using financial modeling are to simulate the financial scenarios such as the following:
- Mergers and acquisitions (M&A): Whether you are looking to acquire a new business, expand, or merge with another company, a lot of money is on the line. Financial modeling helps analysts determine exactly how to get the best bang for their buck.
- Raising debt or equity: Before you can raise money from investors, you need to value your business properly. Then, you need a reliable way to project how quickly you must pay off your debts in order to stay solvent.
- Making internal investments: Financial modeling could be used to determine, among other things, how much of your profits you can safely pay yourself each year (or how much more your business might grow over 5–10 years if you simply reinvest those profits instead).
- Forecasting and budgeting: Risk assessment and risk management are vital parts of business management in general. By forecasting risks before they arise, you can take preemptive action and prepare for any and all outcomes.
Whatever the end goal, all financial models are usually built using information derived from a company’s financial statements and historical data.
*NOTE: In quantitative finance, “financial modeling” may also refer to sophisticated mathematical models used to predict asset price movements, volatility, returns, etc. for the purposes of trading and investing. This is not the type of financial model this article covers.
The history of financial modeling (and spreadsheets)
The history of financial modeling and financial projections is closely connected to both the history of accounting and the history of banking.
Records of goods traded or received have been found dating back to ancient times. The Egyptians and Babylonians were known for their record-keeping. Ledgers with dates and descriptions of trade activities emerged around the same time (circa 2000 B.C.), predating money itself.
But the adoption of currencies displaced the barter system and required more complex bookkeeping. Many merchants employed bookkeepers to maintain accurate records of what they owed and who owed them.
Much later, the first modern banks emerged in Rome. Julius Caesar famously passed an edict that allowed bankers to confiscate land from debtors unable to pay their loans. This was revolutionary because prior to this, debtors were often sold into slavery, ruining them financially (and giving creditors one less future customer).
After the fall of Rome, banking institutions closely connected to the Church sprang up around Europe and became increasingly sophisticated as businesses grew larger and their needs became more nuanced. Papal bankers arguably unified all of medieval Europe.
Then, in 1494, an Italian monk by the name of Luca Pacioli published a treatise that modernized bookkeeping by introducing the double-entry system (for credits and debits) and the balance sheet that has become the basis of all modern accounting.
A few centuries later, the rise of US corporations in the 18th to 19th centuries and the building of transcontinental railroads led to a demand for real-time shipping and logistics planning. Distribution networks required cost estimates, financial statements, and production reports.
With the rise of railroads came a boom in investing. In order to attract investors, corporations began publishing public financial statements (such as balance sheets, income statements, and cash flow statements) to prove that they were profitable.
Naturally, these public-facing documents soon included financial projections and forecasts, and in 1896 certified public accountants (CPAs) were established. Modern accounting had arrived.
How we got to Excel
Despite these advances, financial modeling as we know it didn’t develop until much later, mostly due to technological limitations. Hand-drawn spreadsheets simply aren’t scalable or efficient enough for practical, as-needed financial modeling.
Even earlier spreadsheet solutions from the 1970s (such as Prosper, which ran on punch cards) were far too unwieldy. According to one early financial analyst (whose job title was “Profit Planning Accountant”), building a financial model for a client required 40,000 punch cards!
It wasn’t until Apple introduced the Apple II computer in 1980 that spreadsheets, as we recognize them today, were introduced to the general public. The Apple II launched with Visicalc, which only offered 8 columns and 64 rows. Then came Lotus 1-2-3, created by a former employee of Visicalc, which you may have heard of.
Finally, Microsoft released Excel v2.0 in 1987 on Windows. By 1988, Excel was outselling Lotus and Quattro Pro. By 1993, Excel v5.0 came with out-of-the-box macros via VBA (Visual Basic for Applications), and the rest is history. Most of the business world continues to use Excel for professional accounting and financial modeling based valuation to this day.
But the problem with using Excel for financial modeling is that it hasn’t quite caught up with the times. While financial modeling Excel sheets are powerful and very customizable, it is also very much a manual labor of love, leading to unnecessary friction for end-users.
Why are we still using Excel?
It comes down to comfortability. At this point, most of us grew up learning the basics of accounting in Excel. We were told that Excel is the best and only option for custom financial reporting and modeling.
Even though Excel takes manual wiring and countless hours of tracking and maintenance, many modelers won't look away simply because they are so comfortable in spreadsheet-based software like Excel.
However, the tide is beginning to turn. Business Intelligence started out by finance professionals tracking and reporting on operational data in Excel, but now hundreds of companies offer BI services and key performance tools that can automate and manage more complete data at a faster pace.
So why hasn't that same proliferation of companies happened in the financial modeling space? Because it's hard! Taking today's data and reporting on it in various ways is a straightforward process. Trying to create a variety of scenario-based projections into the murky future is not.
But before we get into why that is hard, we need to understand what a financial model is, what kinds of financial models are out there, and how to build one.
What are the different types of financial models?
In order to understand why Excel is no longer the best option for financial modeling, we must first discuss the various types of financial models that a CFO or financial analyst might employ or be called upon to produce for stakeholders.
The various types of financial models analysts use today map to common goals of financial models, which include the following:
- Raising capital (debt or equity)
- Capital allocation (distributing resources efficiently)
- Making acquisitions (hires, businesses, or assets)
- Growing a business (opening new stores or entering new markets)
- Projecting growth (of sales, revenues, profits, etc.)
- Budgeting and forecasting (planning in advance)
- Valuing a business (for mergers and acquisitions)
As you can see, financial modeling can be used to accomplish very different goals and has a wide range of applications. That being said, there are at least 10 types of financial models commonly used in business:
-
Comparable company analysis (CCA) model
-
Discounted cash flow (DCF) model
-
Three-statement model
-
Leveraged buyout (LBO) model
-
Merger and acquisitions (M&A) model
-
Sum-of-the-parts model
-
Initial public offering (IPO) model
-
Consolidation model
-
Budget model
-
Forecasting model
How to build a financial model
Modeling a company’s finances for the purposes of valuations, forecasting, and budgeting can be very complex. A lot depends on the analyst’s assumptions and calculations. And, to a degree, their implicit biases can and will affect the integrity of the model.
But no matter how complex your financial model or how diverse your choices and possible outcomes, building a financial model usually involves the same 5-step process:
-
Input reliable historical data: Many financial models look at the past 3 years of historical data, if not more. This is a best practice, and unless your business simply doesn’t have enough history for 3 years of data, your model should always account for as much relevant historical data as possible in order to provide the most accurate forecast.
-
Identify your questions and assumptions: This is one of the trickiest parts of financial modeling. Are you asking the right questions and making the right assumptions? Have you considered every possible outcome of every possible choice? What about the probabilities of each outcome? The accuracy of your historical data and your assumptions will greatly impact how accurate the results of your model may be.
-
Build your financial model (usually in Excel): Assuming you’ve completed Step 2 to the best of your ability, building the actual model is usually much more straightforward. But if you’re building your models in Excel (like most analysts do), you have to be very careful when it comes to inputting algorithms and sharing different spreadsheet versions with collaborators. To err is human, which is probably why 90% of spreadsheets contain mistakes.
-
Present your findings to stakeholders: Every financial model should have very clear, easy-to-digest results. But data visualization is as much an art as it is a science, and Excel certainly doesn’t make it easy for analysts to present their results in an approachable format for stakeholders. This is why the results of financial modeling are often submitted to a separate design team that puts together a more accessible report and/or presentation.
-
Make the “best” decision: In an ideal universe, stakeholders will review the results of a financial model and agree on the best course of action going forward. Do you finance that new warehouse and pay off the debt, or do you run the risk of draining your capital too quickly? Should you hire that new sales lead, or would it make more sense to invest in a new marketing CRM instead?
Whatever choice you make, hopefully, your financial model paints an accurate picture of the degree of risk (and reward) behind each choice.
If you’d like to learn more about how to build a financial model, feel free to read this article.
Why is financial modeling important for modern businesses?
The countless applications of financial modeling across various industries is well outside the scope of this article. That being said, better financial modeling is absolutely critical for any business to continue growing with as little downside risk as possible.
As with nearly all business and investment decisions, you can’t “let your winners run” or “cut your losers short” if you don’t perform a detailed analysis of every possible outcome of your choices. And it goes without saying that avoiding losses is usually more important than chasing profits.
Put another way, a business can continue operations at 10% or 50% YoY growth. But if your 10% growth forecast carries 10% downside risk, while your 50% growth forecast forces you to take on massive debt (potentially exposing you to 50% downside risk), then you may want to reassess your choices.
This is especially relevant during the COVID-19 pandemic, as businesses across sectors are faced with unknown risks of a scale and scope they may have never encountered before.
Without competent financial modeling, any business in operation right now is like a ship adrift at sea without navigational instruments. While the seasoned navigator may—by gut feel or instinct—steer the ship in the right direction, his luck is bound to run out in dark and stormy weather.
Avoiding unnecessary risks and focusing on “low-hanging fruit” wins may be the best way forward.
If you’d like to learn more about how better financial analysis and modeling could help your business stay afloat in these uncertain and volatile times, please review the following articles:
The biggest problem with building financial models in Excel
We’ve come a long way since the 1970s. Technology has exploded in a way few of us could have envisioned back when Lotus was still popular. And while Excel is certainly more powerful than ever and adequate for basic financial modeling, it is also completely outdated for more sophisticated scenarios.
When using Excel, analysts are forced to waste countless hours tweaking their rows and columns, methodically double-checking algorithms, carefully curating access across different spreadsheet versions for different collaborators, and meticulously crafting presentations from scratch—just for one set of assumptions and questions!
If your stakeholders come back to you and say, “That’s great, but what about X, Y, and Z?” — it’s right back to the drawing board. You’ll have to create a different spreadsheet and rebuild your financial model each time because Excel simply doesn’t allow analysts to toggle assumptions in real time. It’s like painstakingly drawing scenes one by one when the expected end result is a motion picture.
Taken further, Excel is only two-dimensional. Even with the spreadsheet-gymnastics that some analysts are able to perform, it’s nearly impossible to adequately model multi-dimensional problems.
Fortunately, this is the 21st century. There are now powerful, flexible, intelligent financial modeling solutions with robust capabilities that can help you optimize the financial modeling process. Whatever your end goal, eliminating wasted time—such as the countless redundancies and points of friction that crop up in spreadsheet-based solutions—should be a top priority.
Synario’s patented Multiverse Modeling technology, for example, allows analysts to explore a near-limitless number of different scenarios and outcomes with a single model. By enabling multiple “layers” of variables and assumptions that can be turned on and off or toggled as needed in real-time, Synario allows you to perform numerous analyses that would have taken weeks in just a few days.
Even better, you can instantly switch from Analytical to Presentation mode for a clear-cut view of your results that stakeholders and organizational leaders will easily understand. Every single change you make in Analytical mode is immediately reflected in Presentation mode as well. You can even “show your work” with a drill-down view of the algorithms behind every value in your model.
Our customer’s success stories are just the beginning. If you’d like to learn more about what Synario can do for your business, please set up a call with one of our specialists through the link below.
