How to Do Sales Forecasting in Excel

Sales forecasting in Excel is absolutely possible — and free. We’ll show you how to do it plus another free option to save you time. 

Sales forecasting is not the most fun task for small business owners, but it’s important to get it right. It impacts your cash flow, inventory needs, budgeting, hiring, and financing options. So we’ll give you examples of a sales forecast and how to create your own sales forecasting calculator. 

How to do sales forecasting in Excel: Step-by-step

We’ll walk you through the steps of creating your own sales forecasting calculator in an Excel workbook.

1. Create a new Excel worksheet

Open a new Excel spreadsheet and enter your historical data (sales over time). You need a time series parameter (dates, months, years) and the corresponding sales numbers (monthly sales for example). That’s your sales forecast Excel template. 

2. Create your forecast

Go to the Data tab and find the Forecast Sheet option.

Source

A Create Forecast Worksheet box will open. There, you can choose the form of your graph (a bar or a line chart) — how you want to see the forecast values displayed. Pick an end date as well. Then, click Create.

3. Adjust your sales forecast

Now, you can go into the finer details. Excel lets you customize:

• Forecast start date
• Confidence interval
• Seasonality
• Timeline range of your dataset
• Values range of your dataset
• Missing data points using interpolation

4. View your ready sales forecast

A new worksheet with the graph and the forecast data will be created. You’ll find your predicted sales as well as the lower and upper confidence bound. 

source

Forecasting models in Excel

Now, if you know your math, you might be interested in the different sales forecasting methods  or forecasting techniques available. Indeed, Excel offers many forecast functions depending on your business goals and data. 

So the forecasting methods available in Excel are:

• Straight line: Takes the historical growth rate as the only predictor of future sales
• Moving average: Uses the average sales of the previous periods, suitable for short-term forecasting 
• Simple linear regression: Basic prediction based on available data points without seasonality or cycles

Download our free sales forecast template here Sales Forecast Template

Why you shouldn’t forecast sales in Excel: Limitations

Microsoft Excel is a miracle app for sure, but it can also be very intimidating. While creating the forecast sheet is relatively easy, the real work comes after.

Constant data updating

Just think about the amount of manual work you have to do to update your Excel database all the time!

Any new data point (a new product for example) you want to include to see an updated sales growth projection, you have to add manually. Then, update the graph again. 

And if you need real-time data, it can be a pain — and someone’s daily task.

Let’s look at this example of a sales forecast, made in Excel.

To keep the forecast up-to-date, you have to go in the file and enter any new data on a regular basis (every month or day, depending on the granularity you’re after). And you still won’t have real-time data between date points.

Tied to human skill

You’ll have to come up with your own process of updating and keeping track of your sales forecasts in Excel. 

When your business grows and you delegate the task to an employee, you have to train them to keep the process up and running. You’ll probably need to set up reminders in another app, too, to make sure they don’t forget to do it regularly.

And all that assuming you’re comfortable enough with creating spreadsheets in Excel and using other tools to get sales data in the first place. 

Inflexible and unrealistic

Another problem with forecasting in Excel is that, despite its mathematical accuracy, it doesn’t take into account the sales process at all. It gives you a total sales number to expect but no insight into how individual deals impact this number. 

That leaves you with no way to improve projected performance before it’s too late.

A more practical approach is to look at the sales cycle and how deals at different stages impact revenue. This way, you know where to focus to increase your sales and hit your targets.

A better way to forecast sales than in Excel

Now, unless you’re an Excel wizz, all that effort doesn’t sound like fun. What if you could automate the whole process and do without Excel spreadsheets altogether?

What if your CRM suite where you manage your deals could do the sales forecasting for you too? Brevo can!

What’s Brevo Sales Platform?

It’s one part of our integrated multichannel marketing platform where you can track deals, manage your pipeline, monitor your sales funnel, and organize contacts and companies. 

In short, it’s a customer management system. You or your sales team input new leads, set up your deal stages, and keeping track of what’s going on with your sales suddenly gets easy.

The beginning of a sales pipeline you can customize 

What’s even better is that you have a sales forecasting calculator built-in!

Sales forecasting calculator

Brevo Sales Platform offers an alternative to sales forecasting in Excel that saves you time and effort. All you have to do is:

1. Open a free Brevo account and activate the app.
2. Import your contact list and input deals (it’s as easy as adding a new contact to your database).
3. Customize the sales pipeline to fit your business (one time only).
4. Start seeing your sales forecast in real time.

No manual updating, no steep learning curve and it’s free just like Excel! 

If you use our CRM suite to manage your sales deals, you’ll have an accurate forecast ready automatically, available any time. All it takes is a one-time setup unlike Excel.

What you have to input are the win probability percentages for all deal stages.

Don’t be alarmed: win probability is just how likely the prospect is to convert at that stage. Obviously, the further down the sales funnel, the more likely a sale is likely to happen. So your win probabilities can look something like this, starting from just 10% and growing to 80% (a win is clearly 100%):

How’s that sales forecasting, you wonder.

Naturally, the best-case scenario is when you close all your deals in the pipeline. It’s easy to calculate too — simply sum up the deal values of all deals and that’s the revenue to expect.

But if you want high sales forecast accuracy, Brevo calculates the expected sales you’ll get based on these win probabilities.

For example, you have 4 deals at stage Demo scheduled with a win probability of 25% (image above) and a total value of $4,000. The expected revenue equals 25% of $4,000 as it’s likely that you’ll close 25% of the deals at this stage. 

Brevo does this calculation automatically for every stage and deal in your pipeline in real time. As deals move through, the estimation is updated accordingly. This way, anytime you check, you get an up-to-date sales forecast so you’re always in control of your sales growth. No need to manually update aggregate data or switch between tools!

Related: 

• 8 Key CRM Benefits for Small Businesses
• CRM Sales Management: A Beginner’s Guide to CRM Software

The beauty of this automated sales forecasting without Excel is that no matter how many deals, products, prospects, and sales people you have, it stays that simple and requires no extra work on your side. It’s perfect for small businesses and larger ones alike because it’s flexible and easy to adopt.

Forecast sales without Excel headaches

Excel is a to-go tool for SMBs, but if you’re sick and tired of updating spreadsheets and dealing with formulas, you have another free way to forecast your sales revenue. Up your business planning game and hit your targets with a sales forecasting tool. 

Brevo offers all the sales tools you need to drive revenue and deliver seamless customer experiences. Track deals, book meetings, make phone calls, and collect payments — manage the entire sales cycle and all your contact data in one place with Brevo.

Try Brevo’s Sales Platform Unlimited contacts, deal pipeline management, tasks, inbox, automation, meeting scheduling — all in one place. Open my free Brevo account now >>