Welcome to the example of how to do a spend analysis for module 4 of management 6082 domestic and international sourcing. This is a good example of what you will be required to do for your module 4 Dropbox assignment. A spending analysis is a great tool for companies to use to see where they're spending their money and where they can look for savings in their in their spending and to see where they can be rationalizing their supply base and uh reducing the number of suppliers they're using.

So you've been given some data for a company and uh this company this fictional fictitious company uses 20 part numbers um in their company. And what you're required to do is to do a spend analysis or ABC analysis by part number, by supplier, and by category. So you can see this data on the first tab. And if you go to the second tab, this is the tab where you'll do the ABC analysis by part number. And you can see in cells uh rows 23 through 27, it gives you the steps on how to do this.

So the first step it says is to calculate the dollar amount of annual usage for part number. Hence what we do for that is if you put your cursor in cell F2, click on the equal sign, and then click on D2 * E2 and hit enter. So for part number one, you can see we spend $2200 per year on this part number. We're going to click and drag this formula down the column. And now we can see how much we're spending for each part number for the entire year.

So the second thing it says to do is to sort the items according to their annual dollar usage. So to do this, um I'm putting my cursor in cell F2. Then I'm going to go over here to the sort and filter icon, click on it, and then I'm going to click on sort largest to smallest. So now we have done that. Now the second thing it says to do is calculate the cumulative annual dollar usage. So if you click on cell G2 and click on equals, um we're just going to click on cell F2 cuz cumulatively we've only spent that um part number.

Now in line three, I'm going to click on equals, then click on cell G2 plus cell F3. So cumulatively um we've spent $48,000. So I'm going to click and drag that formula down the column. And as you can see by the bottom, we've cumulatively spent $76,500 um on all parts for the year for this company. So the next thing it says is to calculate the percentage usage. This is a cumulative percentage usage. So to do this, we're going to put our cursor in cell H2, click on equals, click on cell G2 divided by cell G21.

So the cumulative percentage usage for this part number is 31%. So if you go back to this formula, I'm going to put dollar signs between before the G and before the G the 21. Um because I want to click and drag this formula down the column. So as you can see, our percentage cumulative usage for each part number. So by the bottom, we've spent 100% of the total spent. And I like to change the format of this column. So if you right click on this column, click on format cells, and I want to change that to a percentage.

And I don't like any decimal places. And now we can see the percentage usage. So, the first part number uses 31% of our total spend of all parts. So, now this is where we can now classify um this part as an A, B, or C. A is a part number um These are the parts that total the the 80% of the total dollar usage. So, until I hit 80, I'll classify it as an A. So, 0 to 80 is an A. And then 80 to 95 is considered a B. And then 95 to 100 is considered a C.

So, we've done the ABC analysis or spend analysis by part number. But, what is this telling us? That's the real important part of this exercise. And what it's telling me is that four out of the 20 part numbers that this company uses constitute 80% of the spend. So, if I'm looking for just a few numbers part numbers to focus on to try and find savings or improvements, these are the four part numbers that are going to give me the biggest bang for my buck.

I only have to focus on four part numbers to be focusing on 80% of where I spend my money. So, these four part numbers represent the highest level of spend and therefore the biggest opportunity for sourcing analysis and opportunities for cost savings and price reductions. So, that's why we do this type of price spend analysis. So, now let's move on to the ABC analysis by supplier. So, if I click on that tab, here we have the same data.

And we're going to start off by doing the same steps. So, as you can see down here, we have steps one through seven that tell us how to do an ABC analysis by supplier. We start off the same way. We want to calculate the annual usage for each part number. So, again, on cell F2, I'm going to click on the equal sign. D2 times E2. And again, I'm going to click and drag that formula down the column. So, this is where things go a little bit different.

I'm now going to sort the items according to supplier. So, if I put my cursor in cell B2, and I'm going to go over to the sort and filter icon, and it says sort. I'm going to sort A to Z. Click on that. So, I've now sorted by supplier. And now I'm going to subtotal, um, for each supplier. So, to do that, you go to the data tab. And then over here, you'll see the subtotal, um, icon. So, click on the subtotal icon, and I'm going to It says subtotal at each change in, and I want to subtotal at each change in supplier.

I'm going to use the sum function, and I don't want to add to total class. I want to add the annual usage. So, you want to click on that, and then click on okay. So, now I can see what I'm spending for each supplier. And what I want to do now is I just want to see the subtotals. So, if you click on this number two, I can see just the subtotals for each supplier. So, I can see what I'm spending for each supplier. What I want to do now is I want to sort um the annual usage for suppliers from smallest from largest to smallest.

So, if I click on a cell in F2 and if you go back to the home tab and if we go back over here to sort and filter and again I'm going to sort from largest to smallest. So, now I've sorted the usage per supplier from smallest from largest to smallest and now I'm going to come up with the cumulative totals. So, again I'm in cell G5 and I click on equals and then I click on cell F5 and I can't click and drag this formula down because it's a subtotal.

So, I'm going to have to do that same formula in each one. So, if I take G5 plus F10 oops, I made a mistake here. equals G5 plus F10 and then again if I click on equals G10 plus F15 and I click on equals G15 plus F18 equals G10 G18 plus F22 and equals G plus F27 So, as you can see my total cumulative total equals the total which is so that is good. So, now I want to calculate the percentage usage the cumulative percentage usage.

So, we do the same thing if I do equals G5 divided by the total. Oops, I made a mistake here. So, equals G5 divided by the total. And there I have 40%. So, I can click and drag that formula down, but again I want to make sure the dollar signs are on the denominator. And I can click and drag that total that formula down here. So, now I can see, um, how much of our total spend we're uh spending with each supplier. And we can now classify it as an A, B, or C.

So, again it's an A for anything between and 80. Oops, so that's a B. Okay. So, as you can see, this is telling us we're spending 80% of our money, um, with these two suppliers. So, that's good. We want to spend a a large amount of, um, money with a supplier, um, but we're spending 15% of our, um, total spend with these two suppliers and 5% of our total spend with these suppliers. So, this is telling us that, you know, we're not spending a lot of money with these two suppliers.

This is maybe an opportunity to, um, either get rid of these suppliers or, um, rationalize them because we're not spending a lot of money with them. So, it takes a lot of administrative effort, um, to just spend a little bit of money with just a couple of suppliers. So, a low spend per supplier is indicative, you know, of too many suppliers in that uh category. Um, and maybe the volume per that supplier should be increased.

So, maybe some of these other suppliers, um, could do provide us these parts that these suppliers are providing us with. So, let's move on to the ABC analysis by category. And here again, we have the same data. And we start with, um, the same steps. So, again, the steps are outlined here in rows 23 through 30, and we start off by doing the same thing. We calculate the annual usage for part number, so equals D2 * E2. And I'm going to click and drag that formula down here.

And again, we can see how much we're spending per year for each part number. And again, this is where we do it differently. So, this time we're doing it the analysis by category. So, I'm going to sort by category. So, if you put your cursor in cell C2 and go over here to the sort and filter, um, icon and click on sort A to Z. So, we've now sorted by category. And now I want to subtotal for each category. So, again, if we go to the data tab and you go over to the subtotal icon, and I'm going to subtotal this time at each change in category.

I'm going to sum, and I don't want to sum by class, so I unclick that. I want to sum by annual usage. So, click on okay. And there's our subtotal by category. Again, I want to click on this two, so I just see the subtotals. And I want to sort by annual usage. So, again, I click on, um, a cell in column F. And then I want to go back to the home tab. And I want to click on the sort and filter icon. And I'm going to click on sort largest to smallest.

So we can see we're spending most of our um spend on the mechanical uh category. That's our largest category. So I'm going to calculate the cumulative usage again. So to do that, I do equals F7. And then I'm going to do equals G7 plus F13. And then equals G plus F19. And then in cell G25, I do equals G19 plus F25. I must have made a mistake there. I put an equal sign instead of a plus sign. Okay. So again, I know this is right because the total cumulative equals our total.

So 76,500. So again, now I want to calculate the cumulative percentage usage. So to do that, in cell H7, I go equals 33,100 divided by our total. And again, I'm going to put dollar signs around the denominator. So I can click and drag that formula down. Okay. So now we can see where we're spending which category we're spending most of our money on. So again, if it's 0 to 80%, it's an A. 80 to 95% is a B and 95 to 100 is a C.

Okay, so what is this telling us again? Um, it's again telling us where we're spending most of our our money on what category. So, I like to expand um, all of this to give us a better idea of what's going on. And what this is telling me is that um, for the hardware I'm only spending um, we have five different parts and they're only constituting 5% of our spend in one category. So, when we have um, you know, when we have four different suppliers in this category, this is a low um, spend per supplier per category.

So, this is something we may need to look at. Um, and this low spend amount per supplier in this category is saying there's too many suppliers in this category and maybe we can look at um, you know, increasing one of the suppliers to provide more of these parts in one category. So, those are things you're looking at um, when you do this type of um, analysis by category. So, hopefully this will help you do your module for um, Dropbox assignment this week and please contact me if you have more questions.