Excel: Fix Home Depot's operating cash flow

In the Home Depot Excel workbook, the first tab is titled Fixing Home Depot A. So right here, it should look like this with what we'll see is a pretty disappointing time series graph. Here we have an operating cash flow forecast for Home Depot starting in 1986. So 1985, our real data, 1986 through the year 1990, those are the forecasted data. This is inspired by a famous Harvard Business School case that explores the severe cash flow difficulties experienced by Home Depot in 1985. The case is a great case. And when we do the case, we see that Home Depot is within three weeks of dying. It's just great. It's exciting. It should be turned into a full length motion picture. We're going to explore different ways to change Home Depot's operating parameters to fix the company's operating cash flow problems. and we're gonna do this using a spreadsheet. Now in this exercise together, we won't be making any spreadsheets. Instead, we will use a spreadsheet that my brother and I have made in order to illustrate the power of careful modeling and allowing for an exploration of the impact of different options. Now, this is not some mysterious spreadsheet. You're welcome to explore it. There's nothing here that you couldn't do, but we won't take the time to do it. Let's just use it as it's a fun thing to use. First, notice that the forecasted operating cash flow, shown in green, this brilliant green here, are negative and getting worse, from minus 29 million in 1986 to minus 106 million forecasted in 1990. And to see this illustrated graphically, look at this graph over here on the right-hand side, this time series graph to the right. This is not sustainable. If a company does not generate cash from its operating activities, it will eventually die. So let's see if we can fix Home Depot. First, let's try slowing down. Go to cell C18, it's right here, it's this 40%. This is the sales growth rate that's built into this forecast here. We're assuming that Home Depot sales will grow at 40% per year. Now, sales growth requires more operating assets, such as accounts receivable and inventory, to support these increased sales. That hurts operating cash flow. So let's try slowing down. Let's replace that 40% with 10% and watch what happens to the graph. Just sit back and let me do it. I've changed this 40 to 10. Watch the graph on the right side and there we go. Basically, the forecasted operating cash flows are now zero. Zero as far as the eye can see. And you say, well, this is great. No, this is not great. This is also not sustainable. A company with zero operating cash flow cannot repay loans, cannot pay dividends, and cannot undertake any strategic initiatives. Even with slowing down to 10% sales growth, Home Depot will still die. Die more slowly, certainly, but the company will still die. That's the power of financial modeling, because somebody could sit back in their armchair and say, well, if Home Depot were to just slow down, that would solve all their problems. No, it won't solve all their problems. So let's change it back. I'm gonna put this back to 40%. Let's try something else. Now, if you look up here in these forecasted operating cash flows, you see that the biggest cause of the negative amount is the increase in inventory. Increase in inventory in 1986, for example, consumes $62 million in cash. That's the problem. So let's try to manage Home Depot's inventory more efficiently so that their inventory doesn't go up so quickly. Let's go to cell C15 right here, where we have a ratio called number of days sales and inventory. This is the number of days of inventory that Home Depot has on hand. The company can continue selling at its normal pace for 108 days without buying any new inventory. That's almost four months of inventory sitting around on the shelves. Let's see what will happen if Home Depot manages its inventory more efficiently and gets this number down to 66 days. So I'm going to type 66 right here in cell C15. So they only have then about two months of inventory. We're using our inventory more efficiently. We don't have as much sitting around idle. Watch the picture on the right. Okay. First, the bad news. Notice that Home Depot is still going to die. As the years proceed, the forecasted operating cash flows go negative. If you look up here in the green numbers, minus four, minus seven, minus eight, look at the graph, they're negative. So Home Depot will still die even if they manage their inventory more efficiently. The good news is that for the first couple of years, 1986 and 1987, Home Depot's operations will actually generate positive operating cash flow. as it sells its accumulated surplus inventory without having to pay any cash to replace it. So managing inventory more efficiently delays Home Depot's death, gives them some breathing room in the first couple of years, but does not save the company. We better try something else. So let's go ahead and put that inventory number back to 108. We get the bad news back again. And let's do this. Go to cell H14, right here, H14, gross profit percentage. Now that reflects the profitability of each Home Depot sale. Currently, we've got in 26%, which reflects their current gross profit percentage as of 1985. Let's increase it from 26% to, I don't know, 30%. I'll put in 30. By the way, before I push this 30% button, Realize this is impossible. Retail companies don't increase their gross profit percentages by four percentage points. I mean, a 10th of a percentage point, three 10ths of a percentage point are huge increases in gross profit percentage. So to go from 26 to 30 is impossible. But let's just see, what if they did? What do you get? Basically, again, you get forecasted operating cash flows pretty much close to zero. Now, some people try to take some great comfort and say, oh, but it's plus 2 million out of 1990. Yeah, that's basically zero. You're gonna have a multi-billion dollar company that may generate positive operating cash flow of $2 million per year? No, that doesn't work. They're still gonna die. So apparently, fixing Home Depot is hopeless. Let's go ahead and change that gross profit percentage, sell H14 back to 26%. It's hopeless. Ah, but we've got this tab labeled Home Depot B down here. We probably should take a look at that. Let's see what's there. So go down here and tick on Home Depot B. And if you toggle back and forth between these two spreadsheets, the Fixing Home Depot A and the Home Depot B, what you see that is in Home Depot B, we're making the assumption that all of the operating ratios are a little better. So let's compare this average collection period. Average collection period, the number of days on average that it was taking Home Depot to collect its accounts receivable in 1985, 14 days. Home Depot, a little faster. Let's get our customers to pay us a little faster, nine days. With respect to the number of days sales and inventory, let's put in that 66. We're stretching out our accounts payable period a little bit from 48 days to 50 days. We've increased the gross profit percentage to 28%, up from 26%. We're doing everything a little bit better. Okay, a little bit better. Now, take a look at the spreadsheet and take a look at that graph. Is this company gonna die? No, this company is generating all kinds of operating cash flow from its operations. Will banks loan money to this company? Yes, they will. In fact, if Home Depot goes to a bank with this spreadsheet reflecting these changes that Home Depot reasonably expects that they can make in their operating parameters, banks will be falling all over themselves to loan money to Home Depot. These little changes in these little operating parameters that we see here in this beautiful blue compared to what Home Depot has been doing and what they could do changes this from a company that's going to die to a company that has a bright and brilliant future. By the way, this is exactly what Home Depot did. I probably shouldn't be telling you that. My brother wants to tell you that story. Now here we've seen the power of careful spreadsheet modeling in allowing us to explore the impact of different options. Now watch the concluding video in this course where my brother and I will tell the story of Home Depot's magical transformation back of in 1986. I said magical, that's wrong. It wasn't magical. It was roll up your sleeves, get into the business and do things better. And it transformed Home Depot from a company that was going to die into the biggest do-it-yourself retailer in the world.