# 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!

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

Once the Coefficient sidebar launches, choose Import from…

Next, select Salesforce as your data source.

Click From Objects & Fields.

Under New Import, choose the Opportunity object.

Choose Select fields…

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

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

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

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

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.

425,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.

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)

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

To do so, go to Insert -> Chart.

The Chart editor will appear. Choose the Waterfall chart

This will display your estimated sales revenue by forecast category.

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!

## Try the Spreadsheet Automation Tool Over 500,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.
500,000+ happy users
Wait, there's more!
Connect any system to Google Sheets in just seconds.
Get Started Free

Trusted By Over 50,000 Companies