skip to Main Content

CLOUDEXTEND BLOG

Learn About Industry Trends, Best Practices, and Current Events

Forecasting Sales in Excel

Wouldn’t it be helpful to know what your sales figures will be a year from now? Most businesses understand that forecasting sales is invaluable for all kinds of planning. This includes budgeting, marketing, expansion, you name it.

With a little math and some data crunching, you can project sales figures to forecast the probable outcomes. The best part is, you don’t need any sophisticated programs or special knowledge. Microsoft Excel has you covered with its basic built-in features.

A Simple Sales Forecasting Function

First, you likely have two sets of related data, such as total sales by year. There’s an easy Excel function for extrapolating a trend to forecast future totals.

The function looks like this:

=FORECAST(x, known_ys, known xs)

It returns a predicted y-value for a given x-value, based on known x- and y-values, by using linear regression.

Next, if gross sales are your x-value and your years in business are your y-value: use this function to instantly predict your gross sales for next year.

sales dashboard example

Visualizing your sales metrics on a dashboard

Another easy way to create a sales forecast is by using Excel’s Chart features. With a few clicks, Excel can easily turn any data in your spreadsheet into a graphic visualization. These include line charts, pie charts, bar graphs, scatter plots, and other kinds of graphic data representations.

Once you’ve turned a set of data into a chart, you can click on the “+” sign next to the chart to open up the Chart Elements menu.

From the Chart Elements menu, you have the option of adding a trendline for a data series included in the chart. You can choose to have the trendline based on exponential, linear, logarithmic, moving average, or other projection methods. Helpfully, you can also select how far into the future you want to project the trendline.

These tools give you many flexible options for calculating and visualizing your sales forecast in a variety of ways.

WATCH: CloudExtend Excel Analytics 101: Dashboard Edition.

Conclusion

It can be tempting as it can be to seek out specialized apps and tools for your important analysis and planning activities. It’s good to remember what a versatile and powerful resource Excel can be.

There are endless off-label uses for Excel, like scheduling, as a customer info database, and much more. Don’t let the accounting department have all the fun. Make sure your sales team understands how they can use Excel to make their lives easier.