In statistical analysis, we frequently need to predict results in order to plan ahead of time. Excel has some analytical functions and features for doing statistical projections. In this tutorial, we will show you how to calculate projected sales in Excel.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
2 Handy Approaches to Calculate Projected Sales in Excel
We’ve provided a data set containing information about the sales as well as their years in the image below. For example, we need to know what the sale price will be in the future. To do so, first, we will apply the FORECAST function in Excel. We’ll use the Forecast Sheet command to project sales values in another example.
1. Apply FORECAST Function to Calculate Projected Sales
Step 1: Insert the x Argument
- Firstly, click on cell F4 to insert the x, X is the value of the predicted time value in years.
Step 2: Select Range for known_ys Argument
- Select the range C5:C11 for the previous sales data as the known_ys argument.
Step 3: Insert the known_xs Argument
- Select the range B5:B11 and complete the FORECAST function formula with the years as the known_xs argument.
- Finally, press Enter to see the predicted result in cell F5 (5,366,992.57).
- Enter a year (2030) of prediction you want to get the sales value and get the result in sell cell F5 (10,237,774.82).
2. Use Forecast Sheet Command to Calculate Projected Sales
We can use the Forecast Sheet command to create a table of projected future values. It generates a graph chart from which we can obtain predicted data for a given time period, as well as their Upper Confidence Bound (highest possible value) and Lower Confidence Bound (lowest possible value).
Step 1: Select Cell
- Firstly, click on any cell containing any value of the data set.
Step 2: Apply Forecast Sheet Command
- Click on the Data tab, and select the Forecast Sheet command from the Forecast group.
- Therefore, a chart will show up containing the values of the data set.
- Then, select the year (2025) the ending time in the Forecast End box.
- Then, click on the Create button.
- As a result, your Forecast Sheet table will appear with the predicted sales value (071) up to 2025 years.
- The lowest possible sales value for the year 2025 is 69 and the highest possible sales value will be 7150286.45.
Step 3: Edit Chart with Labels and Style
- Click on the Chart Element icon.
- From the list, select the Data Labels option.
- Consequently, all of your data will appear with labels in the graph chart.
- From the Style option, select any of the styles you prefer.
- When you click any of the chart’s lines, the corresponding cell value in your data range will be highlighted, as shown in the image below.
I hope this article has given you a tutorial about how to calculate projected sales in Excel. All of these procedures should be learned and applied to your dataset. Take a look at the practice workbook and put these skills to the test. We’re motivated to keep making tutorials like this because of your valuable support.
Please contact us if you have any questions. Also, feel free to leave comments in the section below.
We, the Exceldemy Team, are always responsive to your queries.
Stay with us and keep learning.
- How to Calculate Average Sales in Excel (5 Simple Approaches)
- Calculate Annual Sales in Excel (4 Useful Methods)
- How to Calculate Sales Growth over 5 Years in Excel (3 Ideal Examples)
- Calculate Average Sales per Month in Excel (2 Easy Methods)
- How to Calculate Total Sales in Excel (With Easy Steps)
- Forecast Sales Using Regression Analysis in Excel (3 Methods)
- How to Calculate Sales Growth Percentage in Excel (2 Easy Approaches)