13-week cashflow statement modeling: Building the model.

We know just a little bit about SGC at this point in time. What we need to understand is in light of what's going on, does it have room to borrow under its current facilities? Can it meet its obligations? And if not, is there time to restructure its obligations before defaulting on its debt and/or seeking legal protection such as bankruptcy? Having the 13-week cashflow statement done in advance of this will give us a lot of information before its too late. The issues for SGC involve having the liquidity to deal with their end of year sales, both in terms of the inventory buildup required to have product on hand and the potential for a decreased amount of revenue because of the ongoing pandemic. And at this point in time when we're building this, it's not clear how much travel and entertaining is going to be happening at the end of 2020, but they need to prepare for the worst. As with many companies during the pandemic, sales on account have grown as customers, wholesale customers have been late to pay. Inventories are growing due to backlogs and lower sales, and payments to suppliers are coming due. There are cutbacks in payroll. They have been able to cut back payroll slightly, but again, the union issue makes this difficult. They also have end of year sales commissions, and to the extent it can, management has to honor those commitments. SGC was able to survive the downturn during the typically slower summer months of the pandemic, but there is a real question about whether they can survive a slow holiday season. So there is most likely divisional or internal budgeting and reporting that will be translated into a monthly forecast. This is, of course, an unaudited forecast. It's internal. It probably would not be seen by anybody outside of the company, but it would be in the form of a balance sheet, income statement and cashflow statement. And that will help us understand where SGC is on a monthly basis. What we're gonna have to do, however, because SGC doesn't forecast really on a weekly basis, we're gonna have to take a monthly forecast and translate it into a weekly forecast. And then from that weekly forecast, we will be able to calculate a 13-week cashflow statement.

The data we will be provided include that monthly income statement and balance sheet. We don't need the cashflow statement on a monthly basis because we're going to create our own on a weekly basis. We'll also have a forecasted income statement from management. We're also going to receive an accounts receivable aging schedule. We'll receive some detail about the inventory and we'll receive detail about their revolver and the borrowings. In terms of the output that we're going to be generating, we will first create a weekly projected income statement, operating working capital analysis. We're going to use that to create a revolver base calculation since the revolver is pegged to accounts receivable and inventory, then we'll be able to create a 13-week cash flow statement that tells us both what their cash needs are and what availability there is under their facilities. And then finally, we will reconcile cashflow statement with EBIDTA. And this is important because EBITDA is still the most common metric in terms of defining leverage and defining covenants and whatnot. So we do always wanna make sure that we are in check with EBITDA. In terms of building the model, the first thing you're gonna need to do is download the Excel template which has all of the supplemental data included. And then for each step of the way, there will be a template provided with the previous work done in case you wanna check your work or you always wanna make sure that your version of the model matches the model that we are leading you through in the next step. The step-by-step videos will take us from start to finish in the model. The videos will be all in Excel. There won't be any more PowerPoint slides until the very end when we wrap up the case in point. There's also a downloadable PDF manual with screenshots that will summarize all of the steps as displayed in the video in case it is easier for you to follow along in that without having to go back and watch all the earlier videos. I always like to map out a model in terms of the steps and the process, just so I understand where I'm going. So again, step one, convert the monthly income statement to a weekly income statement. Step two will be calculate the projected weekly operating working capital base analysis. These are often called roll forwards. Step three will be to calculate the balance sheet base analysis roll forward. So any of the non-working capital accounts like property, plant, equipment will have a roll forward or base analysis in addition. Step four, calculate the weekly revolver base. Step five will be to then calculate the 13-week cashflow statement. Step six will be reconciling cash. Step seven will be calculating the revolver in terms of what we can draw on from the availability from the available funds under the facility. Step eight will be calculating the debt and interest calculations on any outstanding financing. Step nine will be completing the summary of balance sheet accounts. And this is just a step that we need to do, kind of an intermediate step, to get us prepared for the final step which is to reconcile EBITDA to operating cashflow. So we're not building a complete model here, we're really just building a cashflow statement. It's gonna be different from other models you may have built which have three statements and we tie them all together and make sure that everything is in check. This is a little bit different, but we will reconcile our cashflow statement to EBITDA to make sure that they are in check. So I will see you in the Excel recordings.