How to Make Sales Forecasts or Projections of a Product in Excel – Example

Excel is a Microsoft proprietary program that allows handle numeric or alphanumeric data grouping them in rows and columns. This organized information helps to create different tables of data that are quite useful in the mathematical and financial realm. In addition, Excel offers the option of doing arithmetic calculations by creating formulas that are applied automatically.

The large amount of information that Excel can hold makes it a powerful weapon when doing complex analyzes. Many companies use this program to study their financial situation and, based on the data collected, make concrete decisions. The latter is known as financial forecasting and is vital to forecasting the number of sales or revenue. Today we will teach you how to make sales projections in an easy and practical way by using Excel.

How to make a sales forecast of a product in Excel?

One of the most useful functions that Excel has is exponential smoothing which allows project sales. To do this, the program analyzes the number of sales of a certain product made recently.

How to make sales projections with the exponential smoothing tool?

  1. You must add the sales data of the previous years, months or days in a common table. Use two columns, in one of them you put the time lapse and in the other the sales. In this last section it can be reflected either the number of units sold or the amount of money raised. At the bottom of the table you put the year, month or day whose sales you want to forecast.

    excel logo

  2. Once the data table is created you must click on the option data in the top menu of the program. A series of options will be displayed, you must choose «data analysis» in the upper right corner of the window.
  3. Then a table will appear with different analytical formulas. You must go ahead and click on the option exponential smoothing.
  4. Another box will be displayed in which you must enter the necessary information to perform the analysis. First of all, you must click on the «create chart» option at the bottom left. Later, indicates input rangeTo do this, select all the cells with the sales data. For example, if the data is in column B through row 8, you would have to select cells B1-B8.
  5. Then you will come across the section of «smoothing factor» A that indicates the most recent sales results. A value between 0 and 1 is assigned, which shows the degree of fluctuation in sales. If the assigned value is 0.5, sales have been constant recently, that is, fluctuations have not abounded. Values ​​between 0.6-1 indicate an increase in sales while values ​​between 0-0.5 indicate a decrease in sales.
  6. Later you must click «accept».
  7. Finally, select the place on the sheet where you want the exponential smoothing results to be displayed. Click on cell C2 and the sales forecasts for each year, month or day will appear. In addition, a graph will be displayed with the relationship between the forecasts and the actual sales.

    money graph

  8. Finally, you must click on the sales forecast for the last year, month or day analyzed. Then you must drag the arrow to the cell that corresponds to the period whose sales you want to project. The program will automatically show you the number of sales or the amount of income projected for that period of time.

What are the benefits of creating a sales projection?

  1. Help to have a better inventory control by allowing you to understand how likely the product is to sell.
  2. Based on the forecasts, the Staff amount that is needed in a given period of time. If high sales are projected it may be necessary to hire additional staff.
  3. Proper projections can make it easier to obtain the necessary loans to continue or run a particular business. Since lenders can have greater confidence in a project if there is a forecast of a high number of sales.
  4. It allows to anticipate the behavior of the clientele based on shopping habits. In addition, you can identify which products are purchased most frequently.

You may also be interested in:

Source by

How useful did you find this content?

Click on a star to rate!

Average score 0 / 5. Counting of votes: 0

So far, no votes. Be the first to rate this content.

I'm sorry this content was not useful for you!

Let me improve this content!

Tell me, how can I improve this content?

You may also be interested in:

Deja un comentario