.jpg)
April 8, 2022
Adam Hoeksema
Your banker or potential investor just asked you for your balance sheet forecast - don't panic! Although creating a balance sheet pro forma that actually balances is pretty tricky, I hope to walk you through the process and answer any questions you might have about balance sheet projections in this article. First, we need to make sure we are on the same page and have defined exactly what we are looking to create.
What is a Balance Sheet?
A balance sheet is a snapshot of an organization’s assets, liabilities and equity as of a specific date. Typically if you are completing your bookkeeping with a software tool like Quickbooks, Quickbooks will be able to generate your current balance sheet for you automatically.
Over my decade of experience as an SBA lender I saw many balance sheets that were pulled from Quickbooks that were just blatantly incorrect. The balance sheet might have said that the company had negative $50,000 in cash in the bank when I could clearly see a bank statement that showed positive cash in the bank.
Why is my Balance Sheet Wrong?
So why did I see so many incorrect balance sheets during my time as a lender? Well it is easy to make mistakes when completing your bookkeeping in Quickbooks or other bookkeeping software, and if you are not an accountant by trade you might not even realize anything is wrong. The reality is that just one incorrect journal entry in Quickbooks can throw off your balance sheet and really confuse a lender or investor. When you send an incorrect balance sheet to a potential funder it is a major red flag because the funder will then begin to question all of the financial statements that you sent to them. They will wonder, what else is wrong here?
So if you aren’t confident in your Quickbooks skills I would suggest that you send your lender your previous year tax return which will provide your income statement, and then manually fill out our current balance sheet template. Our free template will walk you through answering plain English questions without the business jargon and based on your answers will produce a clean balance sheet for you. We also have a video walkthrough to guide you step by step.
Different Types of Balance Sheets
There are essentially two types of balance sheets. A current balance sheet as of a particular date, and a future balance sheet.
Current Balance Sheet
A current balance sheet is a snapshot of your company’s assets, liabilities, and equity as of a specific date. When a lender or investor asks you for your balance sheet they are probably asking for a current balance sheet.
Balance Sheet Forecast
What is a balance sheet forecast? - A future balance sheet on the other hand might provide a snapshot of what your balance sheet is projected to look like and many different dates in the future. For example, you might provide a 5 year projected balance sheet to your SBA lender as part of your loan application. Balance sheet projections, a balance sheet forecast and a balance sheet pro forma are all synonyms that really mean the same thing. They are all different terms for a balance sheet at some point in the future.
In order to give you some visual context of the difference between a current balance sheet and projected balance sheet you can see examples from our templates below:
Current Balance Sheet Example
The current balance sheet will just have a single column of numbers of the specific date.
Balance Sheet Forecast Example
The forecasted balance sheet will likely have multiple columns with forecasted numbers as of specific dates in the future.
Now in order to produce a projected balance sheet, you really need to produce a full set of financial projections including a projected income statement and cash flow forecast in order to produce your balance sheet forecast. This is pretty tricky unless you have a solid template to start from. In this video I outline the process and complexity of creating a balance sheet forecast.
Why Do I Need a Projected Balance Sheet?
Given that it is more complex to create a projected balance sheet compared to a current balance sheet, you should want to know why you would even need a balance sheet forecast in the first place? What situations would call for a projected balance sheet. Typically the primary reason to create a balance sheet forecast is because a lender or investor asked you to. For example, the SBA will require a projected balance sheet for any loan applications for a business acquisition, or a brand new startup. ProjectionHub has tools that can help you produce a projected balance sheet for both situations:
Our templates will help you produce a full pro forma financial model which will include a 5 year projected balance sheet as well.
The Importance of a Projected Balance Sheet
For startups, getting an idea of what the future holds can bring much-needed confidence and stability to your business practices. A forecast lets you figure out what you’ve got, what you’re going to get, and what you need to give back. This information leads to designing budgets, reinforcing business models, and adjusting trajectories.
One of the major ways a balance sheet forecast helps is to get a handle on your debts and other liabilities. Having accurate projections for these helps reassure lenders that their contributions will be returned and increases funding opportunities.
A well-made projected balance sheet allows you to explore multiple possibilities in your projections. By changing the values in the projection formulae based on optimistic, realistic, and worst-case estimates, you can get an idea of the outcomes of multiple strategies and create plans accordingly. Want to invest in more equipment next year? Tweak the value in the table and see how the consequences play out in the prediction before ever committing.
This same mechanism allows you to test out various business model elements, the consequences of growth, the long-term effects of cutting expenditures, or really any variable involving your ins-and-outs that you can think of. You’re essentially running simulations on your ideas before realizing them without risking any losses.
Balance Sheet Forecast Methods
So, with all these advantages, it’s about time you learned how to make one. There are three main methods for predicting future outcomes, and they vary in complexity and objectivity. Different methods are suitable for different circumstances, so it’s handy to know the difference before deciding.
A balance sheet forecast typically spans five years and can use qualitative, time-series, or causal methodologies, depending on the data available.
- Qualitative Forecasting is often chosen when the company is in its infancy, and there isn’t much historical data available to extrapolate. These are subjective forecasting methods and rely on analysts’ or customers' opinions to form an ‘educated guess’ as to how the company will look in the future.
Since this method relies on very little hard data, it’s prone to bias and errors in judgment. As such, it’s the least reliable and should only be used when real data isn’t available. However, there are some advantages to it.
The flexibility of the method can gather valuable insights from the opinions of industry experts that don’t need to fit any structured numerical format. Further, at the early stages of a startup, it’s a much better option than having no balance sheet forecast at all.
- Time-Series Forecasting is a quantitative approach, and as such, it appeals to investors and other stakeholders.
Time-stamped, historical data is analyzed and used to build future policy and business direction models. It’s recommended that you start with at least two years of historical data for this, so it’s unsuitable for early start-ups, but if the data is available, it becomes a powerful and useful forecasting method.
This method takes the subjectivity out of the assessment and helps to find straightforward patterns, but it is more simplistic than causal forecasting.
Time-series forecasting assumes that present conditions are entirely determined by previous patterns, making it a simple trend projection. As such, it can miss out on other variables that affect future patterns.
However, the strength of this approach comes from this simplicity. More complex assessments are available, but they require a lot more variables to be projected, and therefore data requirements are higher, leading most projections to use this method instead.
- Causal Forecasting can take into account multiple variables and is often the most detailed financial forecasting method. Like any quantitative method, it is stronger with more data, but it differs from time-series forecasting by considering the causal relationships between variables to make predictions.
However, the more variables included in the forecast, the more difficult the forecast becomes. Another reason these models are complicated is that each independent variable also needs to be forecasted accurately to be able to predict how they will affect your business. As such, despite being the most detailed, this complex forecasting method isn’t suitable for many uses and is often less accurate than a time-series projection.
Therefore, the best forecasts are usually time-series due to the optimal balance between the amount of data and simplicity. Time series uses no extra variables, and although this can be a weakness, it typically results in a more accurate prediction over the more complicated methods with more necessary calculations.
Once you’ve decided on the suitable method for your needs, it’s time to learn what kind of data should go into a forecast.
How to Forecast a Balance Sheet
As mentioned, there are various methods of forecasting that might be useful under differing circumstances, but for this example, we’ll assume you’re going to be forecasting using a time-series method. For this, the historical data should cover at least two years, and from these data, you’ll be able to build a solid forecast in an app as simple as Excel.
Though the forecasting method will vary depending on the data available, on the whole, the line items should be the same, no matter which method you’re using.
The first step is to design a convenient format for the balance sheet forecast. This might involve changing the order of data from financial reports to make it easier to use – something that can be time-consuming and tricky but will save a lot of hassle in the long run. To make this process a lot simpler, there are templates for this like we have here at ProjectionHub for 70 different industries and business models.
There are many line items you can project, but it’s important for a balance sheet forecast to include line items covering assets, liabilities, and equity. These can be broken down in numerous ways, but here are some suggestions for what to include under each category:
Assets
- Revenue – By aligning your revenue data behind five years of forecasting columns, you can take the average increase in revenue as a percentage over the previous years – let’s say it’s 10% on the previous year - and add a simple formula to project what this will mean over the coming years. For this example, it means multiplying the previous year’s revenue by itself plus 10%.
- Gross Profit – Data for these should come from the cash flow or profit/loss statement. Looking at these values, you can calculate a simple trend from the previous years, as you did in the previous stage. Convert your gross profit margin to percentages and incorporate that percentage increase into your projection formula.
- Property Plant and Equipment – Fixed assets need to be considered in terms of depreciation and expenditure to get an idea of what you’ll have in the future. Simply add your capital expenditure and then remove depreciation from the value of your fixed assets for the previous year to project future fixed asset value. This step can be very complicated when it’s tricky to identify the change in depreciation.
Liabilities
- Debt – This involves forecasting both short and long-term debt, including the interest accrued on them. Review the current debts you have and how long until they are paid off. Total net debt is total debt minus cash. Use this to establish the change in debt and project the outcome over the following years.
Equity
- Shareholder capital – To calculate the change in this equity, you’re looking at the value added to stakeholders. In order to create your percentage figure for this, take your current return on shareholder capital and subtract the initial return from it.
Divide the difference by the initial return on equity and multiply it by 100 to get that number as a percentage value. This percentage value can then be used for your extrapolation formulae.
There are other line items that can be useful in a balance sheet forecast, but these are some of the common elements, and the process for all of them is similar: identify the measure of change and add it to the previous year to project onto the next one.
Although this practice can be complicated at first, and you may need to re-write your spreadsheet a few times to get it perfect, once it’s set up, it’s a relatively simple exercise. If this seems easy enough, have a look at some of the ways you can improve your forecasting practices.
Improved Forecasting Practices
There are some handy tips for improving your organization and agility with excel balance sheet forecast sheets.
- When using formulas in Excel, it’s useful to have a ‘forecasting table’ from which you can reference metrics such as the % increase in revenue expected. This way, it doesn’t have to be written into each formula and can be altered to see what happens to the projection when the value is changed from optimistic to realistic to worst-case estimates.
- Some elements of Generally Accepted Accounting Principles (GAAP) are not optimal for balance sheet forecasts. If you’re receiving financial statement from accounting in order to collate your historical data, you might find that it’s not in the best format for forecasting and needs to be modified.
For example, GAAP states that line items with the same drivers should still be separated, which isn’t useful for forecasting and can use up valuable space, complicating the process. Forecasting should be focused on the drivers rather than the items, and line items with the same drivers can be combined when forecasting to save time.
- The forecasting for all line items should be done in designated worksheets or tabs of the same worksheet. The final version of the balance sheet forecast will be a consolidated forecast from all of these calculations.
- Lastly, using pre-made templates is a huge timesaver. These templates can have tables and formulas already set up for everything you need to project and can be tailored to suit your specific business. They can also help you pick an appropriate projection model based on your available data and the needs of your projection. This option is great if you are starting up and not confident about designing the entire balance sheet forecast yourself.
ProjectionHub offers all of this and the option to adjust their templates based on your requirements. Their templates allow you to enter everything you know about your revenue, expenses, assets, and liabilities, and the projections come from their in-built formulae.
Conclusion
Learning how to forecast a balance sheet is a great way to get an impression of the direction of your company’s financial future. Depending on your capabilities and your historical data, a balance sheet projection can be rough and ready – as in the case with a qualitative forecast – or exceptionally intricate, such as with detailed, causal forecasts.
Most often, however, there’s a comfortable middle-ground in time-series forecasts, which are relatively uncomplicated and accurate enough for the needs of most.
Designing a projected balance sheet yourself can be time-consuming, especially if you have little to no experience in doing it, but the process is ultimately rewarding, and if you do get stuck or have little time, there are well made, tailored templates available from ProjectionHub that have everything set up and ready to go.
Making accurate projections as to the state of your company in the future isn’t just about your imagination; it’s about knowing how to use the data you’ve got, and from it, how to forecast a balance sheet. Using the appropriate assessment method and the right kind of information, you can peer well into your company’s future and design your business procedures accordingly.
Not ready to make a full blown projected balance sheet yet? Check out our free current balance sheet template in this video.
