Consumer Price Index is one of the most important indices for measuring inflation. In short, you could call it the percentage increase in the cost of a basket of products over two time ranges (usually years). If you wish to calculate CPI in Excel and create its graph, please read through this article.
Note: While creating this article, we are assuming the purchased quantity of products to be constant.
How to calculate CPI in Excel
To calculate the Consumer Price Index between two years in Excel, take a sum of all the amounts spent on the basket of products over those two years. Then use the following formula to find the CPI ratio:
=[(Cumulative price of basket of commodities in later year) - (Cumulative price of basket of commodities in earlier year)] / (Cumulative price of basket of commodities in earlier year)
To find the CPI percentage, simply select the cell with the CPI ratio and hit the percentage symbol.
The same could be done over a range of years.
Eg. We have created a sheet of data with prices of a basket of products from 2011 to 2016. The total of the values of the products is summed in row 9 of the respective columns. The CPI for each year from 2012 onward is mentioned in column K, starting from cell K3.
Since the summed-up price for 2011 is in cell C9 and the summed-up price for 2012 is in cell D9, the formula for the CPI ratio for 2012 would become:
=(D9-C9)/C9
Let us enter this formula in cell K3. Similarly, the formula for the CPI ratio for 2013 would become:
=(E9-D9)/D9
Just like this, we will create the list until we get the CPI ratio by 2016. However, this ratio is in decimal format. To convert the values to a percentage, select all of them, and hit the Percentage symbol.
Create a graph for CPI values in Excel
The best graph type for CPI values is the bar graph. To create it, select the CPI percentages. Then go to Insert > Bar and select the type of the Bar Graph.
Change the size and location of the bar graph accordingly.
The graph created above is static in nature. If you wish to create a dynamic graph, you would have to use tables for the data.
I hope it helps!
Read: How to create a Line Chart and Scatter Plot Graph in Excel.
How to calculate CPI in Excel?
To calculate CPI in Excel, you need to use a formula like this: =[(Cumulative price of basket of commodities in later year) - (Cumulative price of basket of commodities in earlier year)] / (Cumulative price of basket of commodities in earlier year). However, if you want to calculate the CPI percentage, you need to select the ratio cell and click on the percentage symbol.
How do you calculate CPI on a chart?
To calculate CPI and create a chart, you need to calculate the CPI using the aforementioned formula. Then, select all the cells containing CPI values. Next, choose Insert < Bar and select the graph you want to display. Finally, you can customize the graph as per your requirements.
Read: This value doesn’t match the data validation restrictions defined for this cell.
