As we've seen from the previous step, the negative cashflow in week one, left SGC in a position of needing to borrow cash to not fall below the $20 million as required by either the loan agreements or management. The question at this point and going forward is whether they have or will have enough under their revolver to satisfy the borrowing needs during this liquidity crisis. As we previously calculated, the borrowing base on the weekly revolver base tab, we now need to apply this information to the cashflow statement to determine the actual borrowing capacity. So first, we're gonna link our borrowing base eligibility from the weekly revolver base worksheet. Then we can compare it to the borrowing limit under the revolver. So I'm gonna go here and link my accounts receivable, availability from Q10, as well as my inventory availability from Q22, and I will add these up again. Now, I typically don't like to...

recreate formulas if they already exist and our gross availability does exist on the revolver base page. But I do wanna see kind of where my availability is coming from, so I am breaking that rule here. Now what I want to do is compare the availability according to my asset base to the revolver cap. The revolver cap's at $100 million, the current gross availability, however, is going to be the minimum are the smaller of these two numbers.

And that should make sense, because basically, we can only borrow up to the amount our assets allow us to. The ending availability, therefore, in terms of what we can actually borrow, is going to also be affected by the letters of credit or guarantees that we have outstanding. So I'm going to now net my letters of credit from this current gross availability.

And that tells me that I have $71,274.5.

The letters of credit are in effect guarantees by the bank on behalf of SGC that they will pay a debt to a counterparty. These are often used in international finance to settle payments quickly as opposed to doing a typical, a receivable or payable, which can take longer to settle internationally. The next thing we need to do is determine the amount of incremental availability, if any is available in the current period, which is in week one in Q43. We need to know the current borrowings at the end of last week or the beginning of this current week. And that's going to be this ending revolver balance here. And again, this is coming from that handy little summary that we're putting together the ending revolver balance as of the end of the previous 13-week cashflow period. If we didn't have this, we'd have to, again, ask management for it and that is going to carry over and become our beginning revolver balance. The ending availability in Q41 less the beginning revolver balance is going to be the incremental amount available for this period. However, if the beginning balance of the revolver has already put us over the limit, we would want this incremental availability amount to show as a zero. So we will have to wrap this calculation in a max. This is going to be equal to... max of Q41 less Q45, max of Q41 less Q45, and we need to use the beginning balance here, because the beginning balance is what we start the period with. Or zero. First, it asks, and this, again, formula will say, do we have enough under the current facility? And if we do, show me that as a positive. If we don't, don't show it as a negative, but rather show it as a zero, because we don't want a negative number. If it's negative, there is no incremental availability. So if for example, my beginning balance were 80, it would show as a zero, and that is because my ending availability is only $71,274.5. So I'm already out of room under the revolver. The surplus cash or revolver draw that is needed in row 30, Q30 to be specific, tells us what is needed to satisfy the funding requirement when it's negative, right? If it's positive, it's surplus cash. If it's negative, it's telling us what we need to satisfy that requirement. So we now need to compare that number in Q30 to the incremental availability in row 43 to calculate the actual revolver draw in Q46. So as of Q30, we're still kind of hypothetically saying, "This is how much we need." What we now need to do is compare it against what's available to see if we can actually take it. We cannot borrow more than the incremental amount in order to compare the revolver draw needed hypothetically in Q30 to the incremental availability in 43, we need to reverse the sign of Q30 to be able to compare them as absolute numbers. So, we will take the smaller of... the opposite of this number and compare it to this number. And it tells us that we can only borrow up to the available amount, therefore, the MIN formula. However, because row 46 is showing us the revolver draw in a given period and revolver draws are positive, we need to make sure that this number can never be a negative and therefore we will wrap this entire function in a MAX.

And now for example, if we have excess cash and we want to actually pay some of this revolver back, we can, so I'll just go ahead and make this $15,000 of surplus cash and see that this number now becomes zero. Because while we do have incremental availability, we don't need it because the model is generating enough cash not to have to draw down on the revolver. I'm gonna go and switch that back before I forget and it gets stuck with a hard number in my output. So now, I want to calculate the revolver amount paid back in the period. And again, this is, first of all, it's gonna be a negative number. We never want to repay more than we owe, because that's never a good thing to do. And it's also going to be comparing how much cash we have to how much we owe, so it'll be a -MIN, because it is a payback and it needs to show as a negative. It's gonna look at the beginning balance and it's going to look at the...

row 30 to see if I actually have the cash to execute the payback. So it's showing here as a positive number, because all it's simply done is looked at that surplus cash amount, flipped it, and said, "Well, that's smaller than the beginning balance, so why don't I apply that to be paid down?" However, we know for a fact that this is not actually cash, so that means we're not quite finished with this formula. What we need to do is actually say, show the minimum of zero or this number.

This way, when this number is negative, it will actually take that number, that's the number we want. When it's positive, it's actually just simply the reversal of the number that's in Q30. So we're playing kind of some tricks with the positive, negative here and that's simply to make sure that we're getting the right balance in this cell. So we're gonna have a MIN formula here. It's gonna say zero or... the second argument, which is the part of the formula we just did. And now that puts it back to zero. So let's just go ahead, and for the sake of testing our formulas, see what happens here if I change this again back to a very, kind of positive cashflow amount or positive surplus cash amount if we get the proper trickle down. So the $15,000, I have surplus cash, so I no longer need to borrow the revolver. That part we proved is true, and now it's taking that extra $15,000 of surplus cash, and it's applying it to my revolver. It's doing that as a negative number, which is what we want. We only want positive numbers here, and we only want negative numbers here, and that's why we wrap this in a MAX, we wrap this in a MIN, and we have our revolver ending balance. So I'm just gonna go ahead and turn that back from hard number to formula. And then I want to...

take the sum of these. take the sum of these.

And I'm going to put my formulas out here for everyone to see. Let's just go ahead and do one last quick testing of the numbers, positive and negative. So, here I have first scenario is, again, what I just showed, positive cash of $15,000. That means I have positive surplus cash of $15,000. That means that regardless of my incremental availability, I do not need the revolver. I can actually pay back some of the revolver. So I never get a negative number here, only positive. Here's where the negative number shows up and the repayment, and that gives me an ending balance of the revolver here. So that's working correctly. Now, we can see what happens if we need an amount that's under the incremental availability in the period, but we now should test to see what happens if we need an amount that's over the incremental amount. So the incremental amount that we can borrow is $7,162. So let's go ahead and make this -25,000 and see what happens. So the first thing that happens is we see that we can't actually borrow the $25,000 that we need. We can only borrow the amount that's available. That's the $7,162, that then puts the $7,162 here, and it puts nothing in the payback. So what this tells us now is that we would need to calculate a funding gap. Meaning, what is the amount that we need to borrow, but can't. We need to put a formula in here for the funding gap. Before I get into that, I'm going to unwind that hard code, and I'll start building the funding gap. And then, of course, I will go back and test. Again, if the ending revolver balance as calculated based on what is needed is greater than what is available, that's the funding gap. This is the amount we need as evidenced in Q30, right? This is the, what I call the hypothetical number, but that we can't actually get, now in this case, we can get it. So we're just gonna put these formulas in, and then go back and test them, and that's what happens when you have this kind of model. You don't always have this scenario at hand that you need to build the formulas for. You need to build them sort of proactively or in preparation for those alternate scenarios. The amount of the funding gap is going to be equal to my revolver ending balance less the beginning balance of the revolver minus the actual amount that the model minus the actual amount that the model is telling me that I need. And I'm minusing, I'm subtracting here, because this is a negative amount and I want to add this to the beginning revolver balance. So what this is basically saying is that if I have enough under the availability, then I'm able to borrow enough, which is shown here. This $5,600.1 is the same as what my model is telling me that I need. But if I needed $15,000, for example, then what's happening is the model is only allowing that I borrow the $7,162.6, because that is what is available under the facility. So what the funding gap then calculates is how much did you need to borrow? That's the $64,112 plus the $15,000.

And how much did you actually in reality end up with? That's the $71,274.5, and that is the funding gap.

And we only want this amount to be negative. I'm actually just gonna go unwind that really quickly. I'll go back and try it again for everyone to see. But we only want this amount to be negative, because if this is positive, then doesn't make any sense. There's no such thing as a positive funding gap, right? The funding gap can only be negative, if we have enough, we're paying the revolver back, we don't need to know that amount. So I'm simply going to wrap this in a MIN, and I'm going to compare the ending balance with that formula that we just created. That means, I'm only gonna show it if it's negative, right? Only gonna show it if it's negative. So again, let's go back and see what happens here. If I'm positive, do I get a number here? I do not. Thanks to my MIN formula. And if I'm massively negative, it shows me the amount that I'm missing that I need. And this is really the crux of the 13-week cashflow statement telling us the trouble that we might possibly be in. I'm also going to put one more formula in here for us. That's the incremental availability in the period, which I missed out on. Again, these formulas, in addition to being in the solution file, are also in the PDF manual instructions for how to build the model. Also need to just unwind again, that hard coding and then we can go ahead and put that formula back in here. And we have actually completed the revolver section, and it's time to actually now start building the interest calculations for our revolver balances. One quick note, you'll notice that when this did show me a funding gap, you'll notice that I had conditionally formatted this cell to turn red. And that's just something that you can do conditionally format the cell if it goes less than zero, so that it jumps out at you on the page that you're having a funding gap issue. Let's just remember to undo that before we move on to the next section.