How to Forecast Sales with Excel
Forecasting is more of a science than an art with advent of computer programs that have the ability to accurately predict future event in case sales. One popular program that is very useful in sale forecasting is Microsoft Excel. Excel is powerful spreadsheet software that allows users to forecast future events based on a detailed statistical analysis.
Other People Are Reading
Instructions
-
-
1
Make sure the data is complete, correct and ordered. There needs to be enough historical sales data to accurately perform an analysis, typically seven to ten time periods; the longer that forecast timeline the more accurate the forecast. The data must be ordered from oldest to newest. If there is any missing data for a time period, then estimate the number as accurately as possible. the time periods need to be uniform; for example compare months to months or year to years.
-
2
Enter the data in an excel spreadsheet. Title the columns appropriately; for example one column entitled years the other entitled sales. Highlight the data to be included for the purposes of the forecast. From the main menu choose "Insert". From the insert menu select "Chart". In the chart dialogue box click the standard types tab. Locate the chart option and click on "Line". Choose "Finish".
-
-
3
Determine the trendline. Click on the chart area that was just created. The chart menu should appear. From that menu select "Add trendline." The trendline dialogue should be present. Choose the type tab in trendline dialogue box. Under the type tab click on linear. Now click the options tab. Choose the options tab and in the forward box type the number of years to forecast. Make sure the display R-square value option is checked. Click "OK".
-
4
Know what the R-squared value means. The closer the R-squared value is to one, the more accurate the data set is purported to be. The types of trendlines may need to be changed to R-squared result that is more accurate.
-
5
Change a trendline to verify the most accurate type was utilized. First select the chart. From the format menu choose "selected trendline." Change the type of trendline to logarithmic. Repeat the previous steps to change the type to a moving average trendline. Trying all three trandlines give the users a better idea of which one is most accurate based a R-squared result.
-
1
Tips & Warnings
With all forecasting there is no guarantee that past results can exactly predict future outcomes.
