Excel: Growth extrapolation and loan candidate evaluation

Let's get some simple practice in seeing how structuring financial statement numbers can yield quick insights that help in decision-making. Find the Excel spreadsheet named loan candidates and make sure you're in the first tab down here labeled one loan candidates data. So we're all in the same place. Specifically we're going to use these four financial statement numbers right here with these five companies and we're going to do some data analysis, some financial statement analysis. Now, what we're going to do is this. We're going to determine which of these five companies is the best candidate for receipt of a loan and which is the worst. We're going to view this from the standpoint of the lender, the bank. So the best is the company that's most likely to be able to repay a loan. The worst is the least likely. And this is a simple exercise. So we're not going to worry about adjusting interest rates to reflect risk, writing collateral agreements to reduce risk and so forth. We'll assume that the loan interest rate would be the same for all of the loans, that the size of the loan is reasonable. We're just keeping this simple. The four financial statement numbers are total liabilities, capital expenditures, total assets, and operating cash flow. And we'll use these with the five cleverly named companies, Anna, Bella, Caroline, Dahlia, and Eleanor. Now let's rearrange the data in preparation for computing two measures. These measures are going to be debt ratio and free cash flow. To compute debt ratio, we need to compare total liabilities to total assets. So let's group those two. I'm just going to capture total liabilities and just copy them down separately here, just so we have them separate. And what I'm going to be doing is comparing the size of total liabilities to the size of total assets. Copy total assets down, just like this. So I'm now prepared to compare the size of those two. To compute free cash flow, we're going to combine operating cash flow and cash paid for capital expenditures. So let's group those. Operating cash flow, I'll just copy that down here like this, and capital expenditures, I'll put it right next to it. So we've got the two items that relate to one another grouped together. There we go, beautiful. For a normal operating company, the debt ratio is between 50 and 60%. So we're going to create a formula that computes the debt ratio for these five companies. To do that, let's go ahead to the second tab, number two, Loan Candidates Sorted. So just click over here, and you see it looks the same. It just does what we just did ourselves, but here we've got it right in front of us. And we're going to compute the debt ratio for these five companies. Debt ratio is defined as total liabilities divided by total assets, and it's interpreted as the percentage of total financing that the company has obtained through borrowing. Normal operating company, typical debt ratio is between 50% and 60%. Higher than that means that a company has borrowed more than average already, so such a company is not a good candidate for more loans. Let's create a label, debt ratio. I'll put it right here, debt ratio. And we'll do a computation. What we want is to compute total liabilities divided by total assets. And mine is automatically formatted to be a percentage. If yours doesn't automatically format, then all you need to do is go up here, push the percentage button up here, and then adjust to the number of decimal places that you want. But if you do it in the same place that I just did it, it should be automatically formatted. And now what I want to do is to copy this formula to the four remaining companies, just like this. I'll just copy it across. And there we have debt ratio computed for each of our five candidate companies. Okay, now let's do this. Let's compute free cash flow, which is just operating cash flow and capital expenditures put together. We're just going to add them together. Free cash flow represents how much operating cash is left over after paying for necessary new building and equipment items. This free cash flow amount is the surplus cash generated by the company's operations that can be used for other purposes, such as for paying off a loan. Companies with lots of free cash flow have the financial flexibility to repay loans, increase dividends, expand more quickly, We acquire competitors and so forth. Let's type the label free cash flow. I'll type it right here, free cash flow. And then this is just a simple computation. We want to compute operating cash flow and capital expenditures, just add them together. So for Anna, it's 100. And we'll just copy that simple computation, that addition across to the other four companies. So we've got debt ratio and free cash flow for all five of our candidate companies. And that was pretty fun. Let's now compute to the next tab. Number three, loan candidates analysis, which looks the same as what we just did because it is what we just did. It's just there, the solution. Now we're going to sit back and do some analysis. We've done the computations. Now we're gonna do the analysis. And remember, what we're looking for to find the best loan candidate, the one with the lowest debt ratio and the highest free cash flow. Well, let's look across the debt ratios. Here we are with the debt ratios, and we look across there and we see, okay, the lowest debt ratio is Caroline. And remember, low debt ratio means Caroline hasn't borrowed very much already. That company has excess borrowing capacity. That's a very good loan candidate, 19.5%. Caroline has the lowest debt ratio. Caroline Company also has positive free cash flow of 400, which is the best of the five candidate companies. So Caroline Company is the best loan candidate on both dimensions. Let me make sure we see why. Caroline Company has the lowest existing debt relative to its assets, So it has excess borrowing capacity. Caroline is also generating surplus cash from its operating cash flow. Surplus cash that can be used to repay a loan. Carolines are a winner. In contrast, poor Eleanor company has an eighty nine point one percent debt ratio. If Eleanor went to a bank and said, I would like to borrow more, the bank would say, you've already borrowed more than you should have. So, on the debt ratio dimension, Eleanor's not going to get the loan, plus Eleanor has free cash flow of minus 400. A bank would wonder, Eleanor, where are you going to get the cash to repay the loan? Because already you're using more cash for capital expenditures than is being generated by your operations. Eleanor's the worst loan candidate. Now notice how the computation of a couple of simple financial measures can quickly bring and understanding of loan candidate quality.