How to Create a Financial Projection In Excel (2024)

Financial forecasting helps predict future surpluses and deficits. It also allows you to make critical insights to support business decisions. Small business owners return to their forecasts frequently to update data and ensure any predictions about growth rates or expenses are reasonable. Having an editable template in Microsoft Excel can save a significant amount of time.

Excel’s forecasting tool automatically generates a chart from your data. You can also use an Excel template from Microsoft or through SCORE. Below we'll list the documents used to create your financial projection in Excel and offer three options for building your forecasting template.

Gather financial statements and forms

Regardless of the method chosen to create a financial forecast in Excel, you need to enter data from various sources. You may already house this information in Excel spreadsheets or use accounting, point-of-sale (POS) or payroll software. Third-party tools often let you download your financial statements as an .xls extension so you can use them in your financial forecast template.

To create a financial projection, you need:

• 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.

[Read more: Creating a Financial Accounting Report With the Four Basic Statements]

Create a financial projection in Excel from scratch

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.

Likewise, you can 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 return 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're familiar with Microsoft Excel and don't want to start from scratch, consider one of Microsoft's pre-built templates. The Small Business Cash Flow Projection template is a free download that includes a forecasting spreadsheet and chart showing your predicted monthly balances. Instructions don't accompany the template, so this spreadsheet can be difficult to navigate if you're unfamiliar with accounting or Excel formulas.

Here's how to get your Excel financial projection template:

• Go to the Small Business Cash Flow Projection template.
• Download and save a copy of it to your computer.
• Open the file in your browser's download bar or the file folder.

The financial forecast template opens automatically in your Excel software. From there, customize your spreadsheet by adding your company name and starting date. Also, be sure to include the minimum monthly amount you need to meet your obligations. This way, Excel will flag any projections that don't reach your minimum.

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 breakeven analysis.

SCORE walks you through each step, making this the best option if you're new to financial forecasting or Excel. Through SCORE or other small business resource centers, business mentors can also help you understand financial forecasting. Plus, SCORE offers an on-demand online course on financial projections.

Here's how to create a financial projection using the SCORE template:

• Click "Download Template" on SCORE's Financial Projections Template page.
• Select the download link in your browser bar or file, and it'll open in Excel.
• Read the instructions page and fill out the 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.

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.

Follow us on Instagram for more expert tips & business owners’ stories.

Join us on October 8, 2024! Tune in at 12:30 p.m. ET for expert tips from top business leaders and Olympic gold medalist Dominique Dawes. Plus, access our exclusive evening program, where we’ll announce the CO—100 Top Business! - Register Now!

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.

Published January 27, 2022