# How to Calculate Projected Cost in Excel (4 Effective Ways)

### Method 1 – Using Conventional Formula

We mentioned the cost of 6 months of 2021 in the range of cells D5:D10. We considered a projection of a 20% cost increase for the following months of the next year 2022. The value of the projection rate is in cell G5.

Steps:

• Select cell E5.
• Write down the following formula into the cell. Make sure that you input the Absolute Cell Reference with cell G5.

`=D5*(1+\$G\$5)`

• Press Enter.

• Double-clickÂ the Fill Handle icon to copy the formula up to cell E10.

• Get all the projected cost values for each month.

Our formula worked perfectly, and we were able to calculate the projected cost in Excel.

### Method 2 – Use of FORECAST.LINEAR Function

The cost of the first six months is in the range of cells D5:D10. Calculate the projected cost value for the next three months of that year in the range of cell E11:E13.

Steps:

• Select cell E11.
• Write down the following formula into the cell to input the Absolute Cell Reference for the range of cells C5:C10 and D5:D10.

`=FORECAST.LINEAR(C11,\$D\$5:\$D\$10,\$C\$5:\$C\$10)`

• Press Enter.

• Double-click on the Fill Handle icon to copy the formula up to cell E13.
• Get all the projected cost value for our desired three months.

Our formula worked successfully and we can calculate the projected cost in Excel.

### Method 3 – Applying TREND Function

The cost of the first six months is in the range of cells D5:D10 to calculate the projected cost for the next three months of that year in the range of cell E11:E13.

Steps:

• Select cell E11.
• Write down the following formula in the cell. Input the Absolute Cell Reference for the range of cells C5:C10 and D5:D10.

`=TREND(\$D\$5:\$D\$10,\$C\$5:\$C\$10,C11)`

• Press Enter.

• Double-clickÂ the Fill Handle icon to copy the formula up to cell E13.
• You will get the projected cost value for our desired three months.

We were able to calculate the projected cost in Excel.

### Method 4 – Using Forecast Sheet Command

This command provides us a maximum and minimum range of those projected values. In addition, this option will also supply us with a chart for understanding the data trend.

Steps:

• Select the range of cells B5:D13.
• In the Data tab, select the Forecast Sheet option from the ForecastÂ group.

• A dialog box called Create Forecast Worksheet will appear.
• Set the Forecast End at 9, if the value is showing less than 9.
• Choose the chart type as a line chart.
• Click Create.

• A new sheet will open with the chart and the projected values.
• You may notice that the X-axis of our chart is showing the month sequence instead of the monthâ€™s names.

• Go to the Chart DesignÂ tab and select the Select Data option.

• The Select Data Source dialog box will appear. Click EditÂ from the Horizontal (Category) Axis Labels section.

• A dialog box entitled Axis Labels will appear.
• Select the range of cells B5:B13 from the sheet titled Forecast Sheet and click OK.

• Press OK to close the Select Data Source dialog box. You will see the monthâ€™s name will appear on the X-axis.

• You can also modify the chart style from the Chart Design and Format ribbon.
• We chose Style 6 from the Chart Styles group.
• Select the Chart Elements icon and check the Axes and Legend at the TopÂ option.

• To visualize the data changing pattern more precisely, we reduced the lower boundary of the Y-axis of our chart. Double-click on the values of the Y-axis.
• A side window called Format Axis will appear.
• Click the drop-down arrow of the Axis Options and go to the Axis Options tab.
• Set the Minimum bound as 2000 and press Enter. The class intervals in the Y-axis below 2000 will disappear and the data pattern will show in a large view.

• The task for this chart is completed.

• Add a column chat from the Create Forecast Worksheet dialog box. You wonâ€™t get the upper and lower projected cost range.
• Choose Column Chart.

• The projected cost value and the chart will appear on another new sheet. You can modify the chart style according to your preference.

## Related Articles

Get FREE Advanced Excel Exercises with Solutions!
Soumik Dutta

Soumik Dutta, having earned a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, plays a key role as an Excel & VBA Content Developer at ExcelDemy. Driven by a profound passion for research and innovation, he actively immerses himself in Excel. In his role, Soumik not only skillfully addresses complex challenges but also demonstrates enthusiasm and expertise in gracefully navigating tough situations, underscoring his unwavering commitment to consistently deliver exceptional, high-quality content that... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF