Transcript
Having completed the weekly revolver base calculation, we now have what we need to calculate the 13-week cash flow. So I'm going to move to that tab. Now, we've already seen an example of the 13-week cash flow in the earlier material of the course. Again, I have the benefit here of a historical 13-week cash flow statement. I've hidden those columns because I just think it's easier to see this page without as many numbers on it. I do have one historical year just so we can see in general how we're working here, but basically we have our cash receipts, we have our operating disbursements, we have our operating cash flow, and then we have our non-operating cash flows, which lead us to our net total cash flow. And then all of these numbers we've already calculated, they will be very easy to import. The challenge here will will be determining the cash balance and determining the availability under the revolver. So that's going to be the challenge on the cash flow statement here, and that's really where we're going to learn the most about this particular company's situation. So the first thing we're gonna do is start by importing our data here, and again, the roll forward and the revolver base calculations have really done the heavy lifting for us. We're gonna primarily just be linking here. The total revenue receipts here, we're going to go to our weekly roll forward and we're going to get our customer receipts from the first period in row 13. Now, we have to be careful here because we're not looking for sales. We're looking for the actual cash receipts. Those are showing up as negative on the roll forward, 'cause they reduce accounts receivable. I need to flip them here to get them to be positive. The other income we did not do a roll forward for because we assumed that the other income was cash in nature, and typically it is. So this, we actually can go to the income statement to get, and we'll come down here to Q 20 and pick up the other income. I can borrow my sum total from the previous column. So for my payroll and benefits, I'm going to go to my weekly roll forward, and I need to go down to the wages because this is where I totaled both the wages for the cost of goods sold and the SG&A, and I'm gonna go ahead and grab my wages and salaries paid from column Q, and that's staying as a negative. The payments to the trade is going to be the amounts paid to suppliers in the accounts payable section. So I come down to supplier payments in column Q and I get my 91.68. These are payments that the company made to the trade, actual cash payments. CapEx we can get from our PP&E, and that's gonna also be on the roll forward page down in row 57, and I flipped that to be a negative because CapEx is a negative on the cash flow statement although it's a positive in the base calculation. And now the other operating disbursements are going to be other SG&A items which are included in this other accrued liabilities. That's going to be zero in week one, but it will pick up in later weeks. Copy my total operating disbursements over. And one thing I always like to do when I'm building a model is just check column to column and see if everything is okay. Now, there's a huge jump in payments to the trade here, and occasionally in working with these accounts on a weekly basis, there are going to be these odd kind of spikes in the numbers, and sometimes it has to do with the fact that bills come due. And in order to kind of forecast in a consistent way, we have to accept that some of these accounts are simply going to have to get paid down, like in this case, our accounts payable had been building in order for us to forecast inventory growth and all the accounts that are sort of linked into the cost of goods sold, the payments to the trade had to spike in order to normalize. So that's not uncommon, and it's also one of the things that can be a little bit jarring about getting down into weekly detail, is you start to see just how things can fluctuate from week to week, and what kind of an impact it really has on cash, and for a company to have to come up with that kind of cash right away. The operating cash flow I'm just gonna copy from the previous period, and that's simply going to be my cash receipts net of my operating disbursements, and that's negative. So that's not looking good at this point. You'll see I have revolver interest and term loan interest here. I have that coded in blue, and we're just gonna hold off on that for a moment. I'm gonna come back to it, but for now, I just want you to ignore it. Interest is not paid on a weekly basis. It tends to be paid either quarterly or semi-annually. There's no need to pay interest every week. We see that interest had been paid in week 13 of the previous cash flow statement. So we're gonna leave that for now. Our total advisory and legal fees, as you recall, those are also kind of intermittent expenses. We're gonna pick those up right on the income statement and those are also going to be zero for the time being. What we can do now is let's just hold off for a minute on copying. We'll copy over at the end, just to make sure we can get this cash flow statement into good shape first. But as of right now, we're looking at a negative cash flow here, and we'll see what kind of impact that has on our liquidity situation.