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

In our daily life, we need to project the expense and profit of any month or year for our company or living. Microsoft Excel has several functions and commands to do this type of projection. In this article, we will demonstrate four distinct methods to calculate the projected cost in Excel. For the profit estimation, the process will be similar. If you are curious to know about the procedure, download our practice workbook and follow us.

Download Practice Workbook

Download this practice workbook for practice while you are reading this article.

Projected Cost.xlsx

4 Easy Methods to Calculate Projected Cost in Excel

To demonstrate the approaches, we consider a dataset of the cost of the first 6 months of any year. The name of those months is in column B, their sequence number is in column C, and the amount of cost is in column D.

1. Using Conventional Formula

In this method, we will build a conventional formula to calculate the projected cost in Excel. For that, we mentioned the cost of 6 months of 2021 in the range of cells D5:D10. We consider 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.

The steps to complete the procedure are given below:

📌 Steps:

First of all, select cell E5.
Now, 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.

Then, double-click on the Fill Handle icon to copy the formula up to cell E10.

You will get all the projected cost values for each month.

Thus, we can say that our formula worked perfectly and we are able to calculate the projected cost in Excel.

2. Use of FORECAST.LINEAR Function

In this following process, we will use the FORECAST.LINEAR function to calculate the projected cost in Excel. It will calculate or project the future value based on existing value. The cost of the first six months is in the range of cells D5:D10. We are going to calculate the projected cost value for the next three months of that year in the range of cell E11:E13.

The steps of this method are given below:

📌 Steps:

First, select cell E11.
Then, write down the following formula into the cell. Make sure that you 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)

After that, press Enter.

Now, double-click on the Fill Handle icon to copy the formula up to cell E13.
You will get all the projected cost value for our desired three months.

Finally, we can say that our formula worked successfully and we are able to calculate the projected cost in Excel.

Similar Readings

3. Applying TREND Function

In this approach, we are going to use the TREND function to calculate the projected cost in Excel. The cost of the first six months is in the range of cells D5:D10. We are going to calculate the projected cost for the next three months of that year in the range of cell E11:E13.

The steps of this approach are given below:

📌 Steps:

At first, select cell E11.
After that, write down the following formula into the cell. Make sure that you 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)

Now, press Enter.

Then, double-click on the Fill Handle icon to copy the formula up to cell E13.
You will get all the projected cost value for our desired three months.

At last, we can say that our formula worked precisely and we are able to calculate the projected cost in Excel.

4. Using Forecast Sheet Command

In this following method, we will use the Forecast Sheet command from the Data tab. 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.

The procedure of this process is explained below:

📌 Steps:

At the beginning of this process, select the range of cells B5:D13.
Now, 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.
Besides it, choose the chart type as a line chart.
Finally, click on Create.

You will see 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.

To fix this issue, go to the Chart Design tab. Then, select the Select Data option.

As a result, the Select Data Source dialog box will appear. Click the Edit option from the Horizontal (Category) Axis Labels section.

Another dialog box entitled Axis Labels will appear.
Then, select the range of cells B5:B13 from the sheet titled Forecast Sheet and click OK.

Again click the OK button to close the Select Data Source dialog box. You will the month’s name will appear on the X-axis.

Besides it, you can also modify the chart style from the Chart Design and Format ribbon.
For our chart, we choose Style 6 from the Chart Styles group.
After that, select the Chart Elements icon and checked the Axes and Legend at the Top option.

To visualize the data changing pattern more precisely, we reduce the lower boundary of the Y-axis of our chart. For that, double-click on the values of the Y-axis.
A side window called Format Axis will appear.
Then, click the drop-down arrow of the Axis Options and go to the Axis Options tab.
After that, set the Minimum bound as 2000 and press Enter. You will see all the class intervals in the Y-axis below 2000 will disappear and the data pattern will show in a large view.

All the task for this chart is completed.

Similarly, you can add a column chat from the Create Forecast Worksheet dialog box. However, in this case, you won’t get the upper and lower projected cost range.
For that case, choose the Column Chart option.

The projected cost value and the chart will appear in front of you on another new sheet. Modify the chart style according to your desire.

So, we can say that our process worked perfectly and we are able to calculate the projected cost in Excel.

Conclusion

That’s the end of this article. I hope that this article will be helpful for you and you will be able to calculate the projected cost in Excel. Please share any further queries or recommendations with us in the comments section below if you have any further questions or recommendations.

Don’t forget to check our website ExcelDemy for several Excel-related problems and solutions. Keep learning new methods and keep growing!

Get FREE Advanced Excel Exercises with Solutions!

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

Download Practice Workbook

4 Easy Methods to Calculate Projected Cost in Excel

1. Using Conventional Formula

2. Use of FORECAST.LINEAR Function

3. Applying TREND Function

4. Using Forecast Sheet Command

Conclusion

Related Articles

Leave a reply Cancel reply