How to forecast Monthly Sales using Excel

Hello Everyone,

If you are an Entrepreneur or a Sales Professional in your organization, you would be definitely interested in forecasting your sales figures in advance so that you can measure your or your sales team’s efficiency effectively.

Monthly Sales Figure Chart

For Example, In case you had set a target of reaching a sales target of say $100,000 at the end of the year. As a sales manager, you must be interested in forecasting your sales in advance using your previous sales data so that you are well aware of your current condition.

With Sales forecast you can take up many actions for your team like

1. Increase the sales team head count to reach the target, in case the forecast is below your target.
2. In case your actual sales figure for the week or month falls below the forecasted value, you can take appropriate actions to counter that in real time.
3. Topline is the backbone of any organization. Hence comparing the forecasted figures and the actual sales figure, helps the senior management get a high level view of how the company is performing thus helping them align their business strategy with respect to that.

If you are part of a startup with a small team, probably you would not be using a high end Sales CRM.

But no worries, you can very well do the same task using Excel.

Let me show you how…

• Start Excel (You can either use MS Excel or LibreOffice’s spreadsheet or Google Sheets)
• Enter the data in tabular form. Sample has been shown below

Sales figure Sample

• In this case, we are having sales figures of past 11 months. Our objective is to forecast the sales figure for the 12th Month.
• We can very well do it using “FORECAST” function of Excel
• Create another column and name it as “Sales forecast”
• Now corresponding to the 12th month, in the “Sales forecast” column, type the following formula:  

=FORECAST(B13,C2:C12,B2:B12)

• You can also refer the screenshot given below:

Forecasted Sales figure

•  In the FORECAST function
• B13 refers to the 12th month for which the sales forecast has to be made
• C2:C12 refers to the Y values, i.e. the historical sales figures which has to be used to predict the next Y value.
• B2:B12 refers to the X values, i.e. the historic month numbers corresponding to the historic sales figures.
• After the entering the formula, the Sales value for the 12th months appeared as $12,314. This is the forecasted sales figure for the 12th month and hence would help the sales manager in determining whether they would be able to reach their pre-determined target or not.

Forecast function in this case uses Linear Regression in order to predict the sales figure. Sales prediction can also be done using various other methods like Moving Averages, Multiple Regression, Econometric Modelling, etc.

I hope this post was helpful, do comment below and let us know your thoughts. Do check out our other posts. as well.

Looking for a Sales CRM to organize your Sales Lead and Deal tracking? Try ToolsonCloud CRM, Its Free.