In order to make confident and effective business decisions, company executives need good data. They need to know how the business has performed in the past, where it stands financially right now, and what its prospects are for the future. They also need to be able to accurately predict the potential outcomes of opportunities or circumstances as they arise.
Financial modeling helps to facilitate these business decisions, detailing not only where the company currently stands, but also predicting different outcomes based on which path the company takes in the future. These financial models not only help inform important business decisions, but are also helpful for business transactions such as raising capital, mergers or acquisitions, or strategic exits.
What Is a Financial Model?
A financial model is a financial summary combining historical data, current business metrics, and assumptions about the future. A financial model represents an overall picture of a company’s present and future financial position.
The main purpose of financial modeling is to provide insights into the present and future condition of a company. These insights help stakeholders and financial institutions make better-informed business decisions regarding the company.
For instance, a good financial model can assist with the following:
- Help a company evaluate the risks and benefits of a planned investment or acquisition
- Provide a lender with the assurance that the company will be able to pay its debts
- Enable financial analysts to predict the effect of a proposed decision on stock performance
- Provide the necessary data to enable corporate management to confidently chart a course into the future
A business may choose from various types of financial models depending on the company’s structure and the kinds of decisions the management team is looking to make. A good financial model is clearly laid out and easy to understand, while incorporating all of the data necessary to inform the target decisions. Most financial models are created with robust spreadsheet software such as Excel so that new data and “what-if” scenarios can be easily injected and the results instantly calculated.
Where Do Financial Models Go Wrong?
Unfortunately, a financial model is only as good as the data that goes into it. Computer programmers have an old saying, “garbage in, garbage out.” If the numbers, formulas, and assumptions that go into the spreadsheet are incomplete or inaccurate, the resultant corporate financial picture will be unreliable. This can lead to bad or even disastrous company decision-making.
A notorious example was the “Gaussian Copula” risk calculation formula introduced in 2000 by David X. Li, that many giant lending institutions incorporated into their financial models. The formula seemed brilliant but was in fact fatally flawed and led to the issuance of many thousands of bad loans. The use of this formula was a major factor in the 2008 financial crash.
There are certain common errors that lead to bad financial models. Even seasoned financial professionals sometimes make these mistakes. Here are some pitfalls to watch out for as you prepare your financial modeling.
1. The Balance Sheet Doesn’t Balance
This frequent error is typically caused by simple carelessness in entering data or defining formulas. Some of the most common issues are:
- Listing negative numbers as positive and vice-versa. Make sure you know which numbers to add and which to subtract.
- Including the wrong cells in a summation formula (such as including a subtotal cell along with the numbers used to calculate that subtotal).
- Forgetting to use parentheses. For instance, the formula D7-E4*12 gives a different result than (D7-E4)*12.
- Creating circular references by including the cell that contains a formula within the formula; for example, putting the formula SUM(E2:E18) in cell E18.
2. The Template Doesn’t Fit
If you don’t have a full-time or outsourced CFO to handle your financial modeling, you may be relying on paid or free templates to get your answers. While there are many spreadsheet templates available for download or purchase, these templates are meant to be starting points, not final products. Because every company is unique, there is no such thing as a one-size-fits-all template. And—buyer beware—the template might contain flaws.
Unfortunately, some small companies fail to recognize this and simply plug in their numbers, expecting the final result to be an accurate representation of their financial position. They may also inadvertently use a template designed for a different kind of decision-making than they need, or the template may be too inflexible to adapt to the company’s requirements.
If you use someone else’s template, it is important to analyze it, make sure it fits your needs, delete any irrelevant portions, add anything pertinent to your business that is missing, and check all the formulas to make sure they give the correct results.
3. An External Spreadsheet Is Missing or Outdated
Often, a financial model will include multiple spreadsheets that pull data from each other. This only works as long as all the spreadsheets are up to date and all available on the same computer or database on which the financial modeling spreadsheet resides. Otherwise, you may end up with a #VALUE! error, broken formula, or incorrect function.
It’s easy to mess things up by linking to a file located in a different folder. Another common issue is moving or deleting data in a spreadsheet that is linked elsewhere. A third issue is updating one spreadsheet but failing to update the other.
It is best to minimize the number of links between spreadsheets and to check for accuracy whenever files, links, or linked data are changed or moved. The formula auditing functions in Excel and some other spreadsheets can help.
4. Hardcoding Financial Projections
Hardcoding expected financial performance is the go-to strategy for many founders and beginners with no financial experience, especially when it comes to projecting sales. This approach is incorrect because 1) you do not know where the data is coming from and this makes it impossible to validate the numbers and 2) you can’t see the financial impact of changes in your assumptions.
Only the underlying factors that impact revenues and costs, also called revenue and cost assumptions, should be hardcoded. These assumptions come from your strategy and industry standards.
5. You’re Solving the Wrong Problem
As previously mentioned, there are many kinds of financial models for different kinds of companies and different kinds of decisions. Choosing the wrong model for your desired outcome can reduce its usefulness. Before creating a financial model, you should consider the following:
- What type of financial information are you trying to determine?
- Who will use this model and how do they plan to use it?
- What structure and format will be most useful and understandable to the audience?
- What data is needed to create an accurate model?
- What assumptions must be made?
6. There’s Too Much Guesswork
Assumptions are an important and necessary component of a financial model. However, the assumptions must be realistic, specific to the organization, and data-based. In order to make good assumptions, you need to be very familiar with your organization and industry. You need to understand what has happened in the past and why. You should be aware of your closest competitors and the factors that affect their business. You should have a good, justifiable reason to make and stand behind any assumption.
Assumptions should be carefully thought out and clearly designated in the model. Typically, the assumptions will be grouped together in a separate worksheet.
7. There Is No Executive Summary
An executive summary is a concise overview of the financial model. The intent is to provide the most salient data to executives who may not have time to study the full model. It should be written in clear, understandable language. It should define the purpose of the model and its expected use. It should point out any noteworthy or unanticipated findings. As appropriate, it may point out possible solutions and their justifications.
A model that lacks an executive summary may be ignored or misinterpreted by corporate officers. The summary is a very important component of the model that can help the business achieve its goals. Without it, the model is less likely to be a useful decision-making tool.
In Conclusion
Making confident, data-backed decisions is essential to the long-term success of a company. Financial modeling can help inform these critical decisions. However, it is important to keep in mind that a financial model is only as reliable as the figures, formulas, and assumptions that go into it.
Do you have questions about financial modeling or financial forecasting for your company? Reach out to Preferred CFO and speak with a CFO today.
This post was originally published in June 2021 and has since been reviewed and revised for sources and relevance.
About the Author
Wes Anderson, CFO
Wes Anderson is an experienced CFO with significant international finance experience. In his previous roles as Director of Financial & Tax Reporting, Senior Financial Controller, and Chief Financial Officer in the international travel industry, Wes coordinated two corporate restructurings involving entities in four countries. He also built and executed financial models consolidating multiple foreign subsidiaries into a parent holding company.
You may also be interested in...
Guide to HR Outsourcing: Strategies for Modern Business
HR Outsourcing for your business In this dynamic and ever changing business landscape, the questions of "What is HR outsourcing?" and “Do I need a fractional HR team?” have become increasingly relevant. HR outsourcing is a strategic practice that involves delegating...
Managing Business Expenses in Inflationary Times
Inflationary times pose challenges, but with strategic expense management, businesses can navigate these periods successfully. By understanding the impact of inflation, implementing cost-cutting measures, engaging in strategic financial planning, and embracing sustainable practices, businesses can not only weather economic storms but also position themselves for long-term success.
Why You Need Fractional CFO Services in 2024
Why You Need Fractional CFO Services in 2024 In the fast-paced, ever-evolving business environment of 2024, companies across the globe are increasingly recognizing the need for innovative financial management strategies. The US and global economy is in a state of...
20 Things Every Entrepreneur Needs to Know about Accounting
While entrepreneurs don’t need to become professional accountants, having a solid foundation in accounting principles and practices will enable them to make informed financial decisions, communicate effectively with financial professionals, and ensure the financial health and success of their business.
Top Human Resources Strategies for 2024
Welcome to our insightful webinar featuring Tom Applegarth, a renowned expert in the field of Human Resources and our expert Outsourced Human Resources Manager at Preferred CFO. In this webinar, Tom shared with us his valuable strategies and insights for HR success in...
Is it Time to Sell Your Business?
Deciding whether and when to sell your business is a significant and complex decision that should not be undertaken lightly. If the timing is right, the offer is right, and the necessary work is done correctly, selling your business can be highly beneficial. However,...
12 Things Venture Capitalists Look For in an Investment Opportunity
12 Things Investors Look for in an Investment Opportunity Being funded by a VC fund has been glamorized in the past 10 years—and it’s no wonder why. Venture capitalists not only provide funding for young and innovative businesses, but also bring a partnership with...
Understanding Organic Growth vs Inorganic Growth
Business growth requires both organic and inorganic growth. Each method carries its own set of advantages, challenges, and implications for the trajectory of a company. Whether you are a startup or established enterprise, understanding the dynamics of organic and...
Management Development Unlocked Podcast
This episode of Management Development Unlocked marks the 50th episode! Eric’s guest is Tom Applegarth. Tom’s entire career has been spent in human resources at multiple companies, both big and small. Today, he is the Vice President of Human Resources for Preferred...
Small Business Cash Flow Management: Strategies for Success
Recognizing Cash Flow Problems & How to Solve Them We know that the majority of small businesses fail within the first five years, but a study by Jessie Hagen, previously with U.S. Bank, drilled down into the reasons why this occurs. In her study, she found that...
Empower Your Business with a Virtual CFO
In today's dynamic business landscape, having a strategic financial perspective is more crucial than ever. However, not all businesses can afford to have a full-time Chief Financial Officer (CFO) on their roster. Many choose instead to utilize virtual CFO services – a...
Key Performance Indicators for Financial Success
Financial Key Performance Indicators (KPIs) are crucial measurements of a company’s fiscal health. These metrics provide a window into the current and projected profitability of an organization, enabling managers and stakeholders to make informed decisions. By...