How to Forecast in Excel

Published: February 27, 2024 - 4 min read

Hannah Recker

Sales forecasting is pivotal for any business operation, especially for go-to-market strategies where predicting future sales helps in strategizing and planning effectively. However, businesses often grapple with challenges such as maintaining data accuracy and managing data from various sources, making efficient forecasting a daunting task.

Recognizing these challenges, this guide aims to simplify sales forecasting in Excel, offering a blend of manual methods and automated solutions with Coefficient—a tool that seamlessly integrates Salesforce CRM data into Excel for more dynamic forecasting.

How to Forecast in Excel: A Step-by-Step Guide

Coefficient is a no-code, two-way Excel Connector for Salesforce, HubSpot, and many other platforms, trusted by over 350,000 go-to-market professionals and data analysts for live data analysis and reporting.

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

It’s by far the fastest, easiest, and most accurate way to forecast in Excel. Let’s walk through how it works in Salesforce.

Step 1: Connecting Salesforce to Excel

Start by launching Coefficient from your workbook.

To install Coefficient, open Excel from your desktop or in Office Online.

Click ‘File’ > ‘Get Add-ins’ > ‘More Add-Ins.’

Accessing 'More Add-Ins' in Excel through the File tab

Type “Coefficient” in the search bar and click  ‘Add.’

Typing 'Coefficient' in Excel's Add-in Store search bar.

A pop-up will open up. Follow the prompts to complete the installation.

Completing Coefficient add-in installation with Excel prompts

Once finished, you will see a “Coefficient” tab at the top navigation bar.

Click ‘Open Sidebar’ to launch Coefficient.

Launching Coefficient via the Open Sidebar option in Excel

Once Coefficient’s sidebar opens, choose ‘Import from…’, then select ‘Salesforce’ as your data source.

Selecting Salesforce as data source in Coefficient's sidebar

Select ‘From Objects & Fields.’ 

Choosing 'From Objects & Fields' for data import in Coefficient

Choose the ‘Opportunity’ object and click ‘Next.’

Selecting 'Opportunity' object in Coefficient for data import

Select the following fields from the menu: ‘Name,’ ‘Close Date,’ ‘Forecast Category,’ ‘Stage,’ ‘Amount,’ and ‘Probability.’

Selecting fields for Salesforce data import to Excel via Coefficient

After selecting your fields, click ‘Import’ to send your data to Excel.

Importing selected Salesforce fields into Excel with Coefficient

Step 2: Calculating Forecasted Revenue in Excel

Now it’s time to calculate forecasted sales:

Create a Calculated Field: In Excel, adjacent to your imported data, create a column labeled ‘Forecasted Revenue.’

Creating 'Forecasted Revenue' calculated field in Excel

Enter the Forecast Formula: Forecasted Revenue is calculated by multiplying your ‘Amount’ times your ‘Probability’ divided by 100. In this example, =E3*F3/100.

Entering the formula for Forecasted Revenue calculation in Excel

Step 3: Analyzing the Data

With your forecasted revenue calculated, move on to analyzing the data:

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.

Start a Summary Sheet: Create a new sheet named ‘Summary.’

Starting a new 'Summary' sheet in Excel for forecast data

Assign Values: List out categories like ‘Forecast Category,’ ‘Pipeline,’ ‘Best Case,’ and ‘Closed Won’.

Listing sales forecast categories and values on Excel's Summary sheet

Summarize the Forecasted Revenue: Utilize Excel’s ‘SUMIFS’ formula to sum up forecasted sales revenue by the Salesforce forecast categories:

=SUMIFS(ImportedSheet!ForecastedRevenueColumn, ImportedSheet!ForecastCategoryColumn, CategoryCell)

Using SUMIFS formula in Excel to sum forecasted revenue

Step 4: Visualizing the Sales Forecast

Now, it’s time to visualize our forecasting data.

Insert a Chart: Go to ‘Insert > Chart’ and select a relevant chart type; a Waterfall chart works well for this visualization.

Inserting a Waterfall chart in Excel for sales forecast visualization

Customize the Chart: Customize your chart to display estimated sales revenue by forecast category.

Customizing the Waterfall chart to display sales revenue by category

And there you have it!

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

Simplify Your Sales Forecasting with Excel and Coefficient

Automating sales forecasting doesn’t have to be complicated. With the Salesforce Excel integration offered in Coefficient, businesses can streamline their forecasting process, creating a transparent view of their sales pipeline and estimated revenue.

Leverage the power of Excel and Coefficient today to enhance your sales forecasting. Simplify your process, reduce errors, and gain insightful forecasts that help in making informed business decisions for a successful go-to-market strategy.

Remember: the blend of Excel’s flexibility and the seamless integration with Salesforce through Coefficient paves the way for more efficient and accurate sales forecasting. Begin your journey toward streamlined sales forecasting today!

Get started for free with Coefficient today to learn more.

Connect Your CRM to Excel to Power Your Sales Forecast

Sync data from your CRM to Excel and set it on a refresh schedule to power your live sales forecast dashboard.

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.

Hannah Recker Growth Marketer
Hannah Recker was a data-driven growth marketer before partying in the data became a thing. In her 12 years experience, she's become fascinated with the way data enablement amongst teams can truly make or break a business. This fascination drove her to taking a deep dive into the data industry over the past 4 years in her work at StreamSets and Coefficient.
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