Sales Forecast Excel Template

Turnover forecast Excel template

With this Excel spreadsheet, you can compare and analyze multiple products and services by entering monthly sales quantities, unit prices, and cost price (COGS). It then calculates total sales, growth rate, margin per unit and gross profit. The estimation of monthly sales forecasts for one year is easy with this template because formulas and other table functions are already set up for you. This forecasting model uses weighted forecasts based on the probability that each opportunity will be closed. Modify the template or use the existing columns to track sales and relevant data.

Creating a forecast

When you have historic time-related information, you can use it to make a forecast. If you are creating a forecast, Excel generates a new spreadsheet that contains both a history and forecast value spreadsheet and a graph that shows this information. Forecasting can help you forecast things like sales, inventories and customer behavior.

For information on how the forecast is computed and which choices you can make, see the end of this paper. Type two corresponding datarows in a worksheet: Those readings are forecast for prospective use. Please note: The time line needs constant interval between its datapoints.

Examples include for example montly intervalls with value on the 1. of each months, annual intervalls or numeric intervalls. It' s okay if your axis row misses up to 30% of the datapoints or has several numbers with the same timestamp. Forecasts will continue to be precise. But if you summarize information before you generate the forecast, the forecast results will be more precise.

Choose both datasets. Tip: When you choose a row in one of your sets, Excel chooses the remainder of the grid for you. In the Forecast group on the Facts page, click Forecast Sheet. From the Create Forecast Worksheet field, choose either a line graph or a bar graph to visualize the forecast.

Select an end date in the Forecasted End field, and then click Create. A new spreadsheet is created in Excel that contains both a history and forecast value spreadsheet and a graph that shows this information. You will find the new spreadsheet directly to the leftside ("before") of the spreadsheet in which you typed the row.

Click Option to modify extended forecast preferences. For information on the individual selections, see the following chart. Select the date on which you want the forecast to begin. If you select a date before the end of the historic dates, only dates before the starting date are included in the forecast (sometimes called "hindcasting").

Launching your forecast before the last historic point gives you an idea of the forecasting precision as you can match the forecast serie with the real number. If, however, you run the forecast too early, the forecast you generate does not necessarily reflect the forecast that you receive with all the historic information.

The use of all your historic information allows a more precise forecast. When your dates are seasonally, it is advisable to start a forecast before the last historic point. Select or clear the Trust intervals box to show or hide it. This is the area around each forecasted value in which 95% of forecasted points are likely to drop due to the forecast (with normality).

Reliability intervals can help you determine the precision of the forecast. Smaller intervals mean more faith in the forecast for each point. In an annual sales circle where each point represents one months, for example, the seasonal nature is 12. Remark: If you set the seasons to manual, try to prevent a value for less than 2 historic periods.

For less than 2 rounds, Excel cannot detect the seasonality component. The value must be in the same value area. Here you can modify the area used for your value set. In order to treat missed points, Excel uses intepolation, i.e. a missed point is filled as a weight mean of its neighbouring points as long as less than 30% of the points are missed.

If your dataset contains more than one value with the same time stamp, Excel averages the value. If you want to use a different computation technique, such as median, select the computation from the dropdown menu. Select this option if you want to include extra statistic information about the forecast in a new workbook. In this way, a spreadsheet with stats created using FORECAST.ETS is added.

If you use a calculation to generate a forecast, it will return a spreadsheet with the historic and forecast dates and a graph. Prediction forecasts forecast value using your available time-based information and the AAA edition of the Exponential Smoothing (ETS) algorithms. You can include the following tables in the spreadsheet, three of which are calculate columns:

2 colums that represent the trust range (calculated with FORECAST.ETS.CONFINT). This column is only displayed if the confirmation range is activated in the Options section of the field.... At any time you can ask an Excel Tech user, get help from the answer communities, or suggest a new function or enhancement to Excel User Voice.

Mehr zum Thema