Financial forecasting gives you critical insights into your business and helps you project future sales and expenses. Let’s look at how you can create your financial projections in Microsoft Excel and some common mistakes to avoid.
[Read more: What Is the Opportunity Finance Network?]
Gather your documents
You’ll need the following documents to create a financial projection:
- Balance sheet.
- Cost of goods sold (COGS).
- Startup expenses.
- Sales forecast.
- Payroll costs.
- Income statements.
- Operating expenses.
- Cash flow statements.
- Break-even analysis.
- Financial ratios.
- Amortization and depreciation for your business.
Use Excel’s Forecast Sheet tool
Get a quick view of what to expect in the coming year using Excel's Forecast Sheet tool. It creates a chart based on any data sets in your spreadsheet. For instance, you can pull up your net revenue for the previous years and instantly generate a forecast.
Follow these steps to predict future revenue:
- Open an Excel sheet with your historical sales data.
- Select data in the two columns with the date and net revenue data.
- Click on the Data tab and pick "Forecast Sheet."
- Enter the date your forecast will end and click "Create."
- Title and save your financial projection.
You can also use this method to forecast cash flow and operating profit. Developing a financial projection in Excel from scratch can be time-consuming, and data entry or formula errors will lead to inaccurate results. Learn more by viewing Microsoft's tutorial on creating a forecast in Excel.
Use a small business cash flow projection template
If you don't want to build a financial projection from scratch, consider using one of Microsoft's premade templates. These templates are free to use and include a forecasting spreadsheet and chart showing your predicted monthly balances. However, there aren’t any instructions, so these spreadsheets can be difficult to navigate if you're unfamiliar with accounting or Excel formulas.
[Read more: Do You Need Financial Projection Software?]
If your revenue fluctuates from month to month, it’s important to account for this in your financial projections.
Get help from SCORE
SCORE provides a complete financial projection template that pulls data from various financial documents and creates financial forecasts for cash flow, operating profit, balance sheet, and income statements. It also provides a sales forecast, financial ratios, and a break-even analysis.
SCORE walks you through each step, so it’s the best option if you're new to financial forecasting or Excel. Plus, SCORE offers an on-demand online course on financial projections. You’ll start by downloading Score’s Financial Projections Template page. From there, you’ll review the instructions and fill out any information about your company.
This method requires more effort initially than projecting one financial statement. But it provides a complete set of documents needed by investors and lenders and gives you a clear view of your future finances.
Common mistakes in financial projections
Here are some common mistakes to avoid when creating financial projections:
- Overestimating revenue: Optimism is a great quality to have, but you want to be conservative when you’re estimating business revenue. Overestimating revenue can lead to cash flow problems and cause you to make poor decisions.
- Underestimating expenses: Underestimating your expenses can cause unnecessary financial problems. Look through historical data to create an accurate estimation of your expenses and have a plan for dealing with any unexpected bills that arise.
- Not accounting for revenue variations: Many businesses don’t bring in the same amount of revenue every single month. If your revenue fluctuates from month to month, it’s important to account for this in your financial projections.
- Ignoring external factors: Market trends and economic conditions can impact your company’s revenue. Scenario analysis can help you account for different factors that could impact your company’s cash flow.
- Relying on low-quality data: Using inaccurate or outdated data will affect the reliability of your financial forecast. Continuously update your data to ensure it’s accurate and relevant.
How to present financial projections to investors
You can also utilize financial projections when you’re pitching investors for funding. When you present financial projections to investors, it’s important that they’re realistic and data-driven. Explain the assumptions you made regarding your company’s expenses and revenue growth.
Focus on the fundamentals of your business, like the problem your company solves, your target market, and your plan to grow your revenue over time. Explain your funding needs and how you’ll use that money to drive growth within the company.
You should always be prepared to answer questions and defend your positions. Backing up your financial projections with market research and data will help you build trust with potential investors.
Jessica Elliott also contributed to this article.
CO— aims to bring you inspiration from leading
respected experts. However, before making any business decision, you
should consult a professional who can advise you based on your
individual situation.
CO—is committed to helping you start, run and grow your small business. Learn more about the benefits of small business membership in the U.S. Chamber of Commerce, here.
