How to Automate Sales Forecasting with Google Sheets

Last Modified: June 9, 2023 - 3 min read

Kathryn Rossi

Sales forecasting is a crucial aspect of any enterprise. The practice helps companies visualize their sales pipeline and estimate sales revenue for the year.

However, automating sales forecasting is often easier said than done for many businesses. Obstacles such as data accuracy and disparate data sources make effective forecasting difficult.

Sales forecasting is a problem many of our customers face every day. That’s why we’ve created a sales forecasting template for Google Sheets to share with you!

In this guide, we will show you how to automate sales forecasting using Google Sheets and Coefficient.

Or simply download our free sales forecasting template for a pre-built solution. The template is based on your Salesforce CRM data.

Let’s dive in!  

Video Walkthrough: How to Automate Sales Forecasting with Google Sheets

Automating Sales Forecasting in Sheets: A Step-by-Step Guide

Coefficient not only simplifies your sales forecasting process but also automates it, saving you time and reducing errors.

Now let’s walk through how you can automate your sales forecasting process in Google Sheets.

Start by launching Coefficient from the extensions tab in the top menu of Google Sheets. Go to Extensions -> Coefficient -> Launch.

google sheets addon

Once the Coefficient sidebar launches, choose Import from…

import live data google sheets

Next, select Salesforce as your data source.

salesforce data imports google sheets

Click From Objects & Fields.

import objects and fields salesforce

Under New Import, choose the Opportunity object.

salesforce opportunity google sheets

Choose Select fields…

Select Salesforce object

Then select the required fields: ‘Name,’ ‘Close Date,’ ‘Forecast Category,’ ‘Stage,’ ‘Amount,’ and ‘Probability.’ 

Salesforce fields pivot mode

Once all the fields are selected, name your import. Then click import to send your data to Google Sheets.

Sales opportunity data google sheets

Now let’s add a calculated field to your import. First, select a new cell and label it ‘Forecasted Revenue.’

Forecasted revenue google sheets

Forecasted Revenue can be calculated by multiplying your ‘Amount’ times your ‘Probability’ divided by 100. In this example, =E3*F3/100.

forecasted revenue google sheets salesforce

Drag the formula down the column to calculate forecasted revenue for each row.  

Now, let’s build your analysis sheet by starting a new tab and labeling it ‘Summary.’ 

Next, assign each value. These are ‘Forecast Category,’ ‘Pipeline,’ ‘Best Case,’ ‘Commit,’ and ‘Closed Won.

Coefficient Excel Google Sheets Connectors
314,000 Pros Sync Live Data from Their Business Systems into Spreadsheet

Stop exporting data manually. Sync data from your business systems into Google Sheets or Excel with Coefficient and set it on a refresh schedule.

sales forecast category google sheets

Now, create a table that sums the amount of forecasted sales revenue by Salesforce forecast category.

You can use the SUMIFS formula in Google Sheets to accomplish this:

=SUMIFS(‘⚡️Opportunity’!G2:G,’⚡️Opportunity’!D2:D,B5)

forecast category google sheets

Finally, it’s time to visualize our forecasting data!

To do so, go to Insert -> Chart.  

chart builder google sheets

The Chart editor will appear. Choose the Waterfall chart

chart editor google sheets

This will display your estimated sales revenue by forecast category.

sales revenue forecasting graph

And there you have it!

With just a few clicks, you’ve been able to import your Salesforce opportunity data into Google Sheets and forecast your sales revenue automatically.

Simplify Your Sales Forecasting with Google Sheets and Coefficient

Automating sales forecasting isn’t as hard as it sounds. With Google Sheets and Coefficient, you can streamline the process and create a clear view of your sales pipeline and estimated revenue.

To automate sales forecasting in Sheets, all you need to do is follow the steps in this guide or download our free sales forecasting template.

And if you want to import your real-time Salesforce data into Google Sheets, get started for free with Coefficient to right now.  

Ready to get started? Check out our free sales forecasting template and begin your journey toward more efficient and accurate sales forecasting today!

Sync Live Data into Your Spreadsheet

Connect Google Sheets or Excel to your business systems, import your data, and set it on a refresh schedule.

Try the Spreadsheet Automation Tool Over 350,000 Professionals are Raving About

Tired of spending endless hours manually pushing and pulling data into Google Sheets? Say goodbye to repetitive tasks and hello to efficiency with Coefficient, the leading spreadsheet automation tool trusted by over 350,000 professionals worldwide.

Sync data from your CRM, database, ads platforms, and more into Google Sheets in just a few clicks. Set it on a refresh schedule. And, use AI to write formulas and SQL, or build charts and pivots.

Kathryn Rossi Product Specialist
Kathryn is a total spreadsheet ninja. She has her analyst and consulting experience in Oil & Gas to thank for that. Whether you need her to combine data from multiple sources into a destination like Notion or Google Sheets for easy consumption and analysis or need her to build a dashboard for building operations, she can build the perfect spreadsheet for it.
350,000+ happy users
Wait, there's more!
Connect any system to Google Sheets in just seconds.
Get Started Free

Trusted By Over 20,000 Companies