Are you making critical business decisions, like whether to hire a new employee or buy new equipment, based on your current bank balance alone?
It's a common but risky approach. A simple balance sheet forecast can give you the financial foresight you need to make informed decisions and steer your business toward sustained success.
Running a business without a balance sheet forecast is like driving blind. Without anticipating future financial positions, businesses often face unwelcome cash flow surprises, miss valuable growth opportunities, and struggle to secure essential funding from lenders or investors. This lack of financial visibility can hinder strategic planning and overall business stability.
Why should you bother forecasting your balance sheet as a business?
Businesses that want to achieve sustainable growth and financial stability need to forecast their balance sheet. It gives you a complete picture of your future financial health, so you can make decisions proactively instead of just solving problems as they come up.
Make smarter hiring and investment decisions: Know when you can afford it
A balance sheet forecast allows you to clearly see how future hiring and investment decisions will impact your assets, liabilities, and equity. This foresight enables you to determine the optimal timing for significant expenditures, ensuring you can afford new employees, equipment, or expansion projects without jeopardizing your financial health. It moves you beyond relying solely on current bank balances to make critical strategic choices.
Secure business loans and funding
Lenders and investors rely heavily on financial forecasts to assess the viability and risk of a business. Your balance sheet forecast demonstrates your financial acumen and provides a credible roadmap of your company's future. This greatly strengthens your position when seeking loans, lines of credit, or investment, as it shows you have a clear understanding of your financial needs and how you plan to meet them.
Improve cash flow management: Anticipate future shortfalls and surpluses
While distinct from a cash flow forecast, a balance sheet forecast indirectly contributes to better cash flow management. By projecting future asset and liability balances, you can anticipate periods where cash might be tied up in inventory or receivables, or when large liabilities are due. This helps you plan for potential cash shortfalls and identify periods of surplus, allowing for strategic deployment of funds or proactive measures to avoid cash flow problems.
Understand your business's health beyond just looking at profit to see the bigger picture of your company's value
Many businesses focus primarily on their profit and loss statement. However, profit doesn't always equate to financial strength. A balance sheet forecast provides a holistic view by projecting your assets (what you own), liabilities (what you owe), and equity (the owner's stake).
This shows you if your company can pay its bills, how much debt you have, and what the business is actually worth - not just whether you made profit this month. It allows you to see beyond just the bottom line and understand the underlying financial stability of your enterprise.
What you need before forecasting your balance sheet
Before you dive into forecasting your balance sheet, gathering a few key pieces of information and tools will make the process much smoother and more accurate.
Your last balance sheet: Where to find it
To create a good forecast, you need precise historical data. You will need your latest balance sheet, which you can get from your accounting software (like Xero or QuickBooks) or directly from your accountant. This document provides the starting point for all your future projections.
Your income statement forecast: A rough idea of future sales and expenses
While the balance sheet focuses on assets, liabilities, and equity at a specific point in time, its future state is heavily influenced by your expected income and expenses. Therefore, you'll need to have at least a rough forecast of your income statement ready. This means having an idea of your projected future sales revenue and the associated costs and operating expenses. This initial projection helps inform many of the balance sheet line items, such as cash, accounts receivable, and retained earnings.
The free Chaser Excel template: Your forecasting tool
To simplify the forecasting process, Chaser has created a free, downloadable Excel template specifically designed for businesses. This template will guide you through each step and help you organize your data effectively.
The step-by-step guide to forecasting your balance sheet in Excel
Now that you have your current balance sheet, your income statement forecast, and the Chaser Excel template, you're ready to begin forecasting.
This section will walk you through each key line item on your balance sheet, explaining how to project its future value using simple, actionable steps within the provided template. By the end, you'll have a complete and insightful balance sheet forecast.
Step 1: Setting up your forecast in Excel
This initial step involves preparing your Excel template to ensure it's structured correctly for accurate forecasting.
The Chaser Excel template is designed for clarity and ease of use, laid out with distinct sections to guide your forecasting.
- Historical data column: This column is where you'll input the figures from your most recent balance sheet. It serves as the baseline for all your future projections. Ensure these numbers are accurate as they are the starting point of your forecast.
- Future forecast columns: These columns represent the periods you wish to forecast (e.g., monthly, quarterly, or annually). Each column will correspond to a specific future date, and you'll populate them with your projected asset, liability, and equity balances.
- Formulas and links: The template will likely include pre-built formulas that link different line items and even pull information from your income statement forecast (if you input it into the template). Understanding these links is crucial for ensuring the integrity of your forecast. Avoid altering these formulas unless you are confident in doing so.
- Key assumptions section: Many good forecasting templates include a dedicated section for assumptions. This is where you'll document the key drivers behind your projections, such as sales growth rates, payment terms, or inventory turnover. Clearly stating your assumptions makes your forecast transparent and easier to adjust.
By familiarizing yourself with this structure, you'll be well-equipped to navigate the template and accurately build your balance sheet forecast.
Step 2: Forecasting your assets
Forecasting your assets involves projecting the future values of what your business owns. This includes both current assets (those convertible to cash within a year) and fixed assets (long-term assets).
Forecasting current assets
Current assets are crucial for a business's short-term liquidity. Accurately forecasting these helps you understand your immediate financial position.
Cash
Your cash balance is perhaps the most dynamic current asset. While directly forecasting cash on the balance sheet can be complex, it's intrinsically linked to your cash flow forecast. Your cash flow forecast provides a detailed projection of all cash inflows and outflows over a specific period.
The ending cash balance from your cash flow forecast for each period will directly become the cash balance on your balance sheet forecast for that same period. This ensures your balance sheet reflects the actual liquidity position of your business.
Debtors (accounts receivable)
Debtors, also known as accounts receivable, represent the money owed to your business by customers for goods or services delivered on credit. To forecast debtors, you'll need two key pieces of information:
- Sales projections: Your forecasted sales revenue from your income statement.
- Average collection period (days sales outstanding - DSO): This metric indicates the average number of days it takes your business to collect payment after a sale. You can calculate your historical DSO or use an assumed average.
The formula to forecast debtors is:
Forecasted Debtors = (Forecasted Sales Revenue / 365) * Average Collection Period (DSO)
For example, if you project £100,000 GBP in sales for a period and your average collection period is 30 days, your forecasted debtors would be (£100,000 GBP / 365) * 30 = £8,219 GBP.
Stock (inventory)
Stock, or inventory, refers to the raw materials, work-in-progress, and finished goods that a company holds for sale. Forecasting stock levels helps manage storage costs and ensure you have enough product to meet demand. To forecast stock, you'll typically use:
- Cost of goods sold (COGS): This comes from your income statement forecast and represents the direct costs attributable to the production of the goods sold by your company.
- Inventory turnover: This ratio indicates how many times a company's inventory is sold and replaced over a period. A higher turnover generally suggests efficient inventory management.
The formula to forecast stock is:
Forecasted Stock = (Forecasted Cost of Goods Sold / Inventory Turnover)
For instance, if your forecasted COGS is £50,000 GBP and your inventory turns over 5 times a year, your average forecasted stock would be £50,000 GBP / 5 = £10,000 GBP.
Forecasting fixed assets (property, plant & equipment)
Fixed assets are long-term assets that are not easily converted into cash. These include property, plant, and equipment (PP&E). Forecasting fixed assets involves tracking their initial cost, additions, and reduction through depreciation.
Start with the opening balance
Begin by taking the net book value (cost minus accumulated depreciation) of your fixed assets from your most recent historical balance sheet. This is your opening balance for the forecasting period.
Add planned purchases (capital expenditures)
Identify any planned capital expenditures (CapEx), which are investments in new fixed assets such as machinery, vehicles, or property. Add these planned purchases to your opening fixed asset balance for the relevant periods.
Subtract depreciation
Depreciation is the accounting method of allocating the cost of a tangible asset over its useful life. It reflects the gradual decrease in the value of an asset due to wear and tear, age, or obsolescence. While it's a non-cash expense, it reduces the book value of your assets on the balance sheet.
You'll need to estimate the depreciation expense for each forecasting period and subtract it from your fixed asset balance. For simplicity, you can use a straight-line depreciation method, where the cost of the asset (minus any salvage value) is divided by its useful life.
Step 3: Forecasting your liabilities
Forecasting your liabilities involves projecting the future values of what your business owes to others. This includes both current liabilities (due within a year) and long-term liabilities (due in more than a year).
Forecasting current liabilities
Current liabilities are short-term obligations that a business expects to settle within one year. Accurately forecasting these helps you manage your short-term cash commitments.
Creditors (accounts payable)
Creditors, also known as accounts payable, represent the money your business owes to suppliers for goods or services purchased on credit. To forecast creditors, consider your planned purchases of stock and overheads.
- Planned purchases of stock: If you've forecasted your inventory levels, you'll have an idea of how much stock you plan to purchase. Estimate the average payment terms you have with your suppliers (e.g., 30 days, 60 days).
- Planned overheads: Many operating expenses (like utilities, rent, and office supplies) are paid on a credit basis. Factor in your projected overhead costs and their typical payment terms.
The formula to forecast creditors is similar to debtors, but focuses on purchases:
Forecasted Creditors = (Forecasted Purchases (Stock + Overheads) / 365) * Average Payment Period
For example, if you project £20,000 GBP in purchases for a period and your average payment period is 45 days, your forecasted creditors would be (£20,000 GBP / 365) * 45 = £2,466 GBP.
VAT payable
VAT (Value Added Tax) payable is the amount of sales tax your business has collected on behalf of the government, minus any VAT you've paid on your purchases. To estimate VAT payable, you can use your sales forecasts.
Briefly estimate this based on your forecasted sales revenue, applying the relevant VAT rate for your sales, and then considering the VAT paid on your purchases (which would typically be a percentage of your cost of goods sold and overheads). The net amount is your VAT payable. This can be a simplified estimation for forecasting purposes, reflecting the typical VAT cycle in your business.
Forecasting long-term debt
Long-term debt refers to financial obligations that are not due within the next 12 months. Forecasting these liabilities helps assess your long-term financial commitments and solvency.
Start with the opening balance
Begin with the outstanding balance of your long-term debt from your most recent historical balance sheet. This is your starting point for the forecasting period.
Add any new planned loans
If your business plans to take out any new long-term loans (e.g., for expansion, significant capital expenditures, or refinancing), add the principal amount of these new loans to your long-term debt balance in the relevant forecasting periods.
Subtract scheduled principal repayments
Most long-term loans involve a repayment schedule that includes both interest and principal. For forecasting the balance sheet, you need to subtract only the scheduled principal repayments for each period.
The interest portion of the payment will be reflected on your income statement. Ensure you have access to your loan amortization schedules to accurately project these principal reductions.
Step 4: Forecasting your equity
Forecasting your equity involves projecting the future value of the owner's stake in the business. Equity is influenced by two primary components: share capital and retained earnings.
Share capital
Share capital represents the funds raised by the business through the issuance of shares to investors. For most businesses, this figure tends to remain constant unless there's a specific plan to issue new shares (e.g., to raise additional capital or bring in new partners) or repurchase existing shares. If new shares are planned, you'll simply add the value of these new shares to your opening share capital balance for the relevant forecasting periods.
Retained earnings
Retained earnings are the accumulated profits of the business that have not been distributed to shareholders as dividends. This is often the most dynamic component of equity and provides a crucial link between your income statement forecast and your balance sheet forecast.
The formula to forecast retained earnings is as follows:
Closing Retained Earnings = Opening Retained Earnings + Forecasted Net Profit - Forecasted Dividends
Here's a breakdown of each component:
- Opening retained earnings: This is the retained earnings balance from your most recent historical balance sheet or the closing retained earnings from the previous forecasting period.
- Forecasted net profit: This figure comes directly from your income statement forecast. It represents the profit your business expects to generate after all expenses, including taxes, have been accounted for.
- Forecasted dividends: This is the amount of profit your business plans to distribute to its owners or shareholders. If your business does not typically pay dividends, this figure will be zero.
By applying this formula, you can accurately project how your retained earnings will change over time, reflecting your business's profitability and dividend policy.
Step 5: The final check to balancing your sheet
This final and most crucial step ensures the accuracy and integrity of your balance sheet forecast. The fundamental principle of accounting dictates that a balance sheet must always balance.
The fundamental accounting equation
The entire structure of a balance sheet is built upon a core equation:
Total Assets = Total Liabilities + Equity
This equation signifies that everything a company owns (assets) is financed either by what it owes to others (liabilities) or by what its owners have invested or retained from profits (equity). When you forecast your balance sheet, this equation must hold true for every single forecasting period.
Creating a 'check' row in Excel
To verify your forecast's accuracy, you should add a "check" row at the bottom of your balance sheet in the Excel template. This row will calculate the difference between your total assets and the sum of your total liabilities and equity for each period.
In your Excel sheet:
- Locate the bottom: Find a row beneath your total assets, total liabilities, and total equity lines.
- Insert a formula: In this new "check" row, for each forecasting period column, input a formula similar to this:
`= (Total Assets Cell) - (Total Liabilities Cell + Total Equity Cell)`
If your forecast is accurate, the value in this "check" row for every single period should always be zero. A non-zero value indicates an imbalance.
Common troubleshooting steps if it doesn't balance
If your "check" row shows anything other than zero, it means there's an error in your forecast. Here are common troubleshooting steps:
Review formulas for errors:
- Typo or incorrect cell references: Double-check every formula used in calculating your asset, liability, and equity line items. A single incorrect cell reference or mathematical operator can throw off the entire sheet.
- Order of operations: Ensure your formulas are structured correctly, especially when combining additions, subtractions, multiplications, and divisions.
- Broken net profit link: The retained earnings calculation relies directly on your forecasted net profit from the income statement. Ensure the link to this cell is correct and unbroken.
- Other linked items: Check any other cells where values are pulled from your income statement forecast (e.g., COGS for inventory, sales for debtors).
Look for manual input errors: If you've manually entered any figures rather than using formulas, double-check these entries for typos.
Incremental review: Go back to "Step 1: Setting up your forecast" and systematically review each section. Start by ensuring your current assets balance, then current liabilities, and so on. This methodical approach can help pinpoint where the discrepancy first arises.
Depreciation calculations: Errors in depreciation calculations for fixed assets are a common source of imbalance. Ensure the depreciation expense is correctly calculated and subtracted.
Principal vs. interest for debt: When forecasting long-term debt, ensure you are only subtracting the principal repayments from the debt balance, not the total loan payment (which includes interest).
By diligently performing this final check and troubleshooting any imbalances, you can have confidence that your balance sheet forecast accurately reflects your business's projected financial position.
Common challenges you might face when forecasting and how to tackle them
This section addresses common hurdles encountered during balance sheet forecasting and provides practical strategies to overcome them.
"My numbers don't balance"
If your balance sheet isn't balanced (i.e., Total Assets ≠ Total Liabilities + Equity), it's a common issue, and usually boils down to a few key culprits. Here's a checklist of the top three places to look for errors:
- Check all formulas and cell references, especially links to your income statement: A common mistake is a typo in a formula or an incorrect cell reference. Pay close attention to how your retained earnings are linked to net profit from your income statement forecast, and how cash, debtors, and creditors are linked to sales or purchases.
- Verify opening balances: Ensure the starting figures for all asset, liability, and equity accounts, pulled from your last historical balance sheet, are accurately entered. Any error here will ripple through your entire forecast.
- Review depreciation and debt principal repayments: These are frequent sources of imbalance. Make sure depreciation is correctly subtracted from fixed assets and that only the principal portion (not interest) of debt payments is reducing your long-term debt balance.
"This is taking too much time"
It's easy to get bogged down in the details and feel like balance sheet forecasting is a huge time commitment. However, it's crucial to remember that a rough forecast is infinitely better than no forecast at all. Start simple.
Begin with your most significant line items and make reasonable assumptions. You don't need to predict every minor expense with perfect accuracy in your initial forecast. The goal is to gain financial foresight, and even a simplified model can provide immense value in guiding your business decisions. You can always refine your forecast over time as your business evolves and you gain more confidence.
"When should I use software instead of Excel?"
Excel is a powerful and flexible tool for balance sheet forecasting, especially for businesses starting out. However, there are clear signs that indicate it might be time to transition to dedicated financial forecasting software:
- Multiple currencies: If your business operates internationally and deals with transactions in several currencies, managing conversions and fluctuations in Excel becomes complex and prone to error. Software often automates these conversions and provides more robust multi-currency reporting.
- Complex inventory management: Businesses with a large number of SKUs, varying inventory turnover rates for different products, or intricate supply chains will find inventory forecasting in Excel cumbersome. Specialized software can integrate with inventory management systems, offering more accurate and automated stock projections.
- Need for real-time collaboration and version control: As your team grows and more people need to access and contribute to financial forecasts, Excel can quickly lead to version control issues and difficulties in collaborative editing. Cloud-based forecasting software offers real-time collaboration, audit trails, and robust version management, ensuring everyone is working on the most up-to-date and accurate information.
Wrapping it all up
You now have the knowledge and tools to transform your financial planning. Forecasting your balance sheet is a practical tool that gives business owners control over their financial destiny, moving beyond guesswork to informed, strategic decision-making.
As a next step, use your newly created balance sheet forecast to inform just one upcoming decision, whether it's big or small. This simple act is the first step toward building a more resilient and successful business, empowering you to navigate future challenges and seize growth opportunities with confidence.
