Hello everyone. In this video, you will learn how to do the ABC analysis by Excel. First, I like to show you the calculation of the spend for each item. Here's the data on the items, including item number, annual usage, and unit cost. To calculate the annual spend, we shall multiply the annual usage by the unit cost. Here is what do we do. Activate the cell and type in =. Then select the annual usage and multiply the unit cost, and then hit Enter. This is the cost for the first item, annual spend for the first item. Then highlight the created cell. See the little dot here, we drag it down, this cell, to repeat the same calculation for all the other items. Now let's rank the items by the annual spend. First select all the cells, and then go to Data and then Sort. Make sure the box next to my data has headers is checked. Now in a column sort by, you can select Annual Spend. For the order, let's select from largest to the smallest, then click Okay. Now, you can see that the items are sorted from the highest to lowest by the annual spend. Then let's calculate the cumulative spend in dollar value. For the first item, the cumulative spend in dollar value, is the same as its spend. Let's just type in = annual spend, and Enter. For the second item, the cumulative spend is equal to the cumulative spend of the first item plus the spend of the second item. Now, let's highlight a second cell and drag it down to repeat all the calculation, and then we find out that the total spend is $8,470. Finally, we calculate the cumulative spend in percentage by dividing the cumulative spend in dollar value for each item with the total spend, which is this one. For example, for the first item, we should type in =, then the cumulative spend of the first item divided by the total spend. Now, because we hope to use the same total spend for all items, so let's put in a dollar sign to make sure this value is fixed when we drag it down. Now I enter it. Now we can drag it down to repeat the same calculation for all the items. Let us now create a Pareto chart for this analysis. We first insert a blank bar chart. Go to Insert, and Bar chart. Now, right-click on the bar chart, go to Select Data. Here, let's first add the y value and label, such as the annual spend over here. Now click Okay. Now for the x label, we should also edit it. So edit x label. Let's select all the item numbers. Then click Okay. Now we have the bar chart for the annual spend. Now let's add the cumulative spend in percentage into this chart by right-click on the chart and go to Select Data. Now let's add the cumulative spend in percentage. Let's select the name of a series to be cumulative spend in percentage, and then select the value of y and Okay. Now makes sure also change the x labels for this data series. We have two data series already here, but we cannot see the second one because it's too small. So we need to change its y-axis. Let's right-click on this chart and select Format Plot Area. Here, in the plot area options, in this drop-down menu, select Cumulative spend percentage. Now, then go to Series Options. Here, click on Secondary Axis. See, now the axis for the cumulative percentage goes to the right. Now what we need to do is change this cumulative spend, the bars into a line. Let's right-click on the bar and select Change Series Chart Type. Here, you can see both data series. For the cumulative spend, let's change this to line with markers, and then click Okay. Now this is essentially the Pareto chart. Now you can enhance the chart by adding data labels for both data series, and you can also go to Design and click Layout to select a layout for your chart.