How to Make Graphs for Forecasting Sales in Excel
By Joy Campbell
Being a psychic isn't necessary for predicting future sales. All you need is your past sales data and Excel. Excel will do the work for you --- no programming or math degree required. For best results, collect more than a year's worth of data for your baseline. Otherwise, seasonal sales could be mistaken for long-term sales. The order of your data should be the earliest sales to the most recent sales. You will also need to compare weeks with weeks and not weeks with months.
Step 1
Start Microsoft Excel. Click on the cell you want to add information to and type in or copy your data, then press the "Enter" key. Continue adding your data, including headers, in the worksheet as needed.
Step 2
Click on the upper corner of your data and drag your mouse diagonally to the bottom corner to highlight all of your data. Click "Insert" from the main menu and then click on a nonstack column chart from the pull-down menu.
Step 3
Click anywhere on the chart to select it and click "Formulas" on the main menu. Click on "Layout" and then "Analysis." Click on "Trendline" and then "Linear Trendline."
Step 4
Forecast future sales by clicking on "Trendline" and then on "More Trendline Options." Click "Display R-squared Value on Chart," and click on "Linear." Click "Close" to accept.
References
Tips
- The closer the R-squared value is to one, the more reliable the trendline is.
- Choosing the right trendline for your data is important: choose a linear trendline if your data points resemble a line; choose a logarithmic trendline when there are quick increases and decreases; choose a polynomial trendline when data fluctuates; choose a power trendline when comparing measurements that increase at a specific rate, like a race car; choose an exponential trendline when the data rises or falls at increasing rates; and choose a moving average trendline to show a pattern in data that fluctuates.
Writer Bio
Based in southern Florida, Joy Campbell has been professionally writing since 2009. She is the author of "Journal of Ideas: Volume One." Campbell holds a Master of Education with a concentration in instructional technology from the University of South Florida.