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—from budgeting, to marketing and expansion, you name it.
With a little math and some data to crunch, you can future-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 Forecasting Function
If you have two sets of related data, like 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.
If gross sales are your x-value and your years in business are your y-value, you can use this function to instantly predict your gross sales for next year.
Another easy way to create a 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.
As 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, including for 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.