Sales Forecasting with Excel

By Anthony Muhl

04 June 2010

When you made the decision to enter the world of owning your own business, who would have thought that statistical analysis would soon appear as a job function on your resume. As a business owner however, understanding where your current business financials score is just a part of the whole picture that you will need in order to chart a successful future month. My parents as I am sure your parents spoke about the future, “one day” – a day where we will have it all.

Now the reality sits on your shoulders, the business is real and you are the Captain charting the course. There are many ways that you can plan for the future, to get you started without getting involved in a complex web of mathematical calculations we will explore Microsoft Excel and a build-in function called “FORECAST”. Excel and other spreadsheet programs such as Open Office provide many statistical analysis tools which you can easily utilize with just a little practice. This article today will cover a simple way of short-run forecasting based on your past twelve months of sales.

When you forecast there are factors that will influence the final outcome and only time will be the true judge of how well the numbers play out. As you advance with statistical analysis, make inclusions for market conditions, the competitions marketing efforts and pricing, the overall industry performance, and of course your own store’s marketing efforts. For this simple exercise to get you started, I will focus on twelve months of sales information -your total sales rather than for each product line.

On your computer, open MS Excel (or Open Office if you prefer). With your spreadsheet program in front of you: column “A” will represent the months of the year. If you are in January, use “1” to represent January; likewise, if you are in May, use “1” to represent May. Number Column “A” from 1 thru 13, in which 13 will represent next month (one month ahead). Take your monthly sales data for the past year (broken down by month) and enter the monthly totals into column “B”.

Once you have completely entered data into column “A” and “B”, you can create your formula to calculate next month’s sales. In the Column “B” cell next to the number 13, type “=FORECAST”. By entering the forecast formula, your program will bring up the required input data. The first piece of information you will want to enter is “13”, which represents next month with 12 months of data. The same can be true if you enter 14, which will give you your sales forecast for 2 months ahead.

The next input data will be the sales data, which is column “B”. Click on the first month of sales figures and highlight all the way down to month 12. You should now have a complete range of cells highlighted (selected) – B1 through B12. The last part of the formula is the range of how the data is correlated; for this example, the range is months of sales figures for a year – highlight column “A” starting with “1” and continue all the way to “12”. Once this step has been completed you are ready to press “enter” and let Excel perform the statistical calculations for you.

The number that you end up with should be a guide to help you plan your inventory levels, marketing efforts, and staffing needs. Now that you have found an easy way to help guide your future business, play with different ways to calculate forecasts. For example, rather than forecasting next month’s total sales, take the figures from different product lines and calculate expectations on sales for each line.

Try an experiment, rather than marketing only your entire store, for a few months, market an item in your store that has lower performing sales than you would like to see. This way, your company message gets out and you are able to promote an item to help drive additional sales. See how closely the sales figures compare to your forecast – after a couple of months you should be able to start to trend the amount in sales based on the amount of marketing you do.

Down the road you will be able to include your marketing efforts as an additional data set in the forecast which will provide you deeper insight to future sales. The good news is that with forecasting skills, you will be able to base your marketing dollars on real income levels that it produces. For now, the important part of this exercise is to help you get comfortable with the forecast function and properly plan your future in business.