Modeling Case Study - Balancing the Balance Sheet
- 04:54
How to wire the ending balance of the cash flow statement into the balance sheet using min and max functions. How to handle positive and negative cash balances and how they affect the cash and revolver lines.
Transcript
Now we've completed the cashflow statement. Our next step is to wire the ending balance of the cashflow statement into the balance sheet. And you can see right here we have a difference in 2024 of 775.7. That number should be exactly the same as the difference on the balance sheet, and it is, and we know that that means that when we wire that into the balance sheet, that is going to make it balance. If we take a look at the numbers down here, we can see they're all positive. And what that means is that the company we are forecasting, it's going to have a positive cash balance all the way through the forecast. However, if you change the assumptions, for example, if I make capital expenditure a really big percentage of sales, and I'm just doing this for illustrative purposes only, say 1,000 percent crazy, but just for illustrative purposes, at this point, the company will run out of cash. And so this balance now becomes negative. If it's negative, what that means is that the company will need to raise money, and we assume it will do it by borrow money and therefore issue of revolver. So if it's negative, it will be a revolver. If it's positive, it will be cash. I'm gonna undo that assumption change because I want to go back to the normal set of assumptions that we had previously. In order to make this flexible, I could use an if statement. I could say, if my cash balance is positive, pull it into cash. If it's negative, pull it into revolver and then flip the sign to make the revolver positive. But most people in modeling use a slightly more elegant solution, and that's using a min or max function. So for the cash line, I'm going to say give me the maximum of the number at the bottom of the cash flow statement. But before you take it, just compare it to 0. So it will take the maximum of that number in 0. So if that number is negative, it will take 0. If it's positive, it will take the number. And that means in our case, because we always have that number being positive, it will pull in the cash balance every single year. For the revolver line, we actually want to do the exact opposite. So there are two options here. You can either do a minus min function, so where you are taking the minimum of 0 and the number at the bottom of the cash statement, because the negative number is always less than 0. But then you need to flip the sign, and that's why we say minus min, or you could do the maximum of minus that G115 because minus minus will make a number positive. So if you put minus in front of a minus number, it will flip the sign to make it positive. And that Means you will compare the negative number flipped as a positive number to 0, and it will pull in the number if you have run out of cash. In our case, I'm going to take the minus the minimum, so minus the minimum of the number at the bottom of the cashflow statement or 0.
So this will compare the 775 and 0. And if you're looking for the minimum, if the cash balance is positive, it will pick 0 If you have a revolver balance. In other words, if the number is negative, it will pull in the negative number, but because you're doing minus min, it will then flip the sign of the number to make it positive. Let me show you how this works because it's much more intuitive if you actually look at the results. So currently we have a positive cash balance every single year, no problem. But if I go back and change my assumptions for capital expenditure, and again, I'll make it 1,000 percent, so we'll run out of cash in that year and actually going forward as well, you can see that we have cash as zero all the way during the forecast. And then we have a ginormous revolver. And the reason we have that revolver, because it's comparing the G115, which is negative and zero, so it will pick up the negative number in G115, but the minus min will flip the sign to make it a positive 113,761. The balance sheet should still balance. It does. And let's check at the bottom of the cash flow statement. And you can see we have that negative number, so minus the min of 113, negative and zero will pull in that 113 number as a positive number. This means that the balance sheet will now balance using the cash flow statement.