How to Build a Sales Forecast Tracker Using Google Sheets

Tracking sales forecasts is an essential task for any business. It helps you estimate future revenues, compare actual performance, and make informed decisions. Google Sheets is a perfect tool to build a clear and interactive sales forecast tracker.

Why Is a Sales Forecast Tracker Important?

A sales forecast tracker helps you stay on top of your business by giving you a clear picture of where things are headed. It lets you compare what you expected to what’s actually happening, so you can spot trends, catch potential problems early, and make smarter decisions. It’s not just about numbers—it’s a tool that helps you plan, adjust, and keep your goals within reach, giving you confidence in your next steps.

Step 1: Open Google Sheets

Go to Google Sheets and click the “+ Blank” template to open a new spreadsheet. Rename the title to make it easily recognizable. You can use “Sales Forecast Tracker.”

Once you have a new spreadsheet, you may now map out what you need:

• Sections for estimates and actual results
• Charts to visualize key metrics (total units sold, net profit, growth rate)
• Summary tables for quick insights
• A variance section to compare estimates with actual results.

Step 2: Create Columns for Estimate Sales Data

In the first table, add columns for months (January to December). Under each month:

Include rows for:

• Number of Units Sold
• Average Unit Price
• Total Gross Revenue (calculated by multiplying units sold by the average price)
• Cost of Goods Sold (COGS)
• Net Profit (total revenue minus COGS).

Step 3: Add Formulas for Calculations

Now that your first table is all set, add the following formulas below to automate the process before you plot the information needed.

Total Gross Revenue:

Use the formula:

=B17 * B16

(Replace B17 and B16 with the relevant cells for units sold and average price).

Net Profit:

=B23-B18

(Subtract the total cost of goods sold from gross revenue).

Step 4: Include a Sales Growth Rate

Add a row to show the month-to-month sales growth percentage.

Use this formula:

=IF(B18=0, 0, (C18-B18)/B18)

(This calculates the percentage change in gross revenue from one month to the next).

Step 5: Build the “Actual” Section

Below your estimates, replicate the layout for actual sales data:

Include the same rows for units sold, average price, gross revenue, COGS, and net profit.

This section will be updated with real numbers as they come in.

Step 6: Create a Variance Comparison

Add a section to calculate the differences between the estimates and actuals.

For example, calculate the variance in net profit:

=C8 – C13

(Subtract the actual net profit from the estimated net profit for each month).

Include a percentage variance:

=(B25-B40)/B25*100

Step 7: Enter Your Data

Once your tables and formulas are all set, it’s time to see if they work. Start populating the spreadsheet with your data and adjust calculations if needed.

Step 8: Summarize Key Metrics

On the right side, include summary tables for both estimates and actuals:

Total Units Sold: Use =SUM(B2:M2) to sum the row for units sold.

Average Unit Price: Use =AVERAGE(B3:M3) to find the average.

Net Profit: Use =SUM(B6:M6) to sum the net profit row.

Step 8: Add Visual Charts

Use charts to make your data easier to understand. Highlight the relevant rows and columns, then click Insert > Chart. Choose a bar chart format and customize it for estimated and actual values.

Get the Free Sales Forecast Template

Get a copy of the free Sales Forecast Template. I’ve populated some cells as examples, but you can customize them as needed.

Final Thoughts

Building a sales forecast tracker in Google Sheets isn’t just about managing numbers—it’s about gaining clarity and control over your business decisions. Let it be a living tool, not a static document. The more you engage with it, refine it, and let it guide your planning, the more valuable it becomes. Keep it simple, keep it updated, and let the numbers tell your story.

Frequently Asked Questions

How can I calculate the variance between actual and projected sales?

To calculate the variance, use the formula = Projected Sales – Actual Sales. Assuming your Projected Sales are in column B and Actual Sales are in column C, the formula in column D would be: =B2 – C2.

How do I calculate revenue using Google Sheets?

To calculate revenue, use the formula = Quantity Sold * Unit Price. If ‘Quantity Sold’ is in column C and the unit price is a constant per row, the formula could be =C2 * 10 to get the revenue in column D, where 10 is the unit price.

What are the benefits of using Google Sheets for sales forecasting?

Google Sheets offers Cloud access, making your tracker available anywhere. It also offers Real-time updates and collaboration, Simple formulas and graphing tools, and Free usage with an internet connection.