In step one, we will be converting the Monthly Income Statement, which has been given to us by management, to the Weekly Income Statement. Unfortunately, as I mentioned, months do not line up easily with weeks, as weeks each have seven days and months have a variety of day counts. Once we would determine on which date we are going to begin our 13-week cash flow, we can determine the number of days in that week that come from each month. For example, we are beginning our 13-week cash flow on November 6. Now as I mentioned, we have a historical weekly income statement which is most likely here because we had done this exercise perhaps in a previous quarter. I'm gonna go ahead and hide this data in a moment, because I just want to get it out of the way. But what we see here is our 13-week calendar is going to be beginning on 6th November in column Q. And I will blow this up a little bit more so we can see it in detail. This means that in that first cash flow week or period that ends on the 6th of November, we have to determine how many days in that week come from actually November, and how many days come from October because our data is broken down in months. So it's a little bit confusing to start, but I think it will make sense once we have the actual numbers in place. So again, since we are in November, I'm using this row six here just as a reminder of which month I'm in and what the end day is of that month so that we can determine how many days are in the month. So we will let Excel do some of the calculating, some of the heavy lifting here in terms of counting the days in the month and then we'll just be able to kind of check it as we go. So again, a week that ends on November 6th means it has six days from the current month, which is November, and then one day from the previous month. Six days from November, one day in October. And we see that as we move into the month, for example, the week that ends the 13th of November which is the second week in our cash flow statement. Well, that's gonna have seven days purely from November and no days, therefore from the previous month. And the same for the third week in November and the fourth week in November. And then once we move on into the fifth week of the cash flow statement, now we are at a weekend that is December 4th.

Therefore, again using the calculator below, we are now in December. Four days have come from December and three days will have come from November. And again, this is only important because we've been given monthly data. So if the sales and the expenses, whatnot are changing from month to month, we need to make sure that we're just pulling in the data from the correct month. So in practice, this really only matters in that first week of every month when it's possible that some of the days of that week come from the previous month, but we need to make sure that it's flowing throughout the 13 weeks of the cash flow statement. So to actually work with this calculator what we really need are daily sales because we're basically going to be breaking the sales down by days. How many days came from last month? How many days came from this month? However, if we look on our Monthly Income Statement, of course we don't have daily sales. And while it's possible to get daily sales, there's certainly a point to which you'll have too much data. So monthly sales is what we've been given and what we're now going to do is create a daily sales calculation. On the Monthly Income Statement, we have the income statement converted to a daily count in rows 34 through 40.

I will blow that up and I will freeze my titles so we can be sure to be seeing everything properly. The daily calculation of our Monthly Income Statement which will be used in our weekly income statement is in rows 34 through 48.

And this is done simply by taking our monthly revenue number and dividing it by the number of days in row two. So these are the figures that we will then be using in concordance with that calculator I just showed you on the weekly income statement. So as you can see, this model takes a little bit of setting up to do and that this is clearly not the only way to do this, but a lot of what we're doing here is data manipulation. So having these kinds of things in place, laying out the model properly is very important before we start working with the numbers. So back on the Weekly Sales tabs. And what I'm gonna do here is I'm gonna hide my Historicals and I'm just gonna hide them. I'm not gonna group them because if you group them, then you lose a little bit of real estate here on the screen with the slider buttons. I'm gonna go ahead and hide my columns. I'll leave one Historical column because that is always nice as a jumping off point. In order to get my cash flows from period one, I'm going to take six days from my November monthly forecast and I'm gonna take six revenue days from my November forecast and one day from my October monthly forecast. So I'll take six and then go over to my monthly statement and be sure that I get six from November.

And to that, I'm going to add back on my weekly one day which is Q8 times my October daily revenues.

And again, what that does is it simply just takes the correct number of days from the correct month on those odd weeks when we have a week that crosses over two months. Now this formula is copyable if we can anchor the monthly data because the days are copying across nicely here. However, the monthly data we wanna stay anchored to both November and October. So what we'll have to do here is we'll have to anchor our column for the monthly data. So I'll just drop that in with a dollar sign before the column of H and I. And now I can actually go ahead and copy that over. I can only copy that over for a month. Because once I get into the next month, I'm dealing with December and November, not November and October. So I'm gonna need to shift those two anchored columns over. It's possible that there is a more advanced way of doing this in Excel with using indirect lookup and things like that. I didn't wanna turn this too much into an Excel lesson because it's already a very complicated model to follow. So I decided to keep it as simple as I can. If it's something that you're particularly good at or wanna try, it's definitely possible to create something that might be a little bit more copyable than this is which we now need to change or recalculate every month. I'm gonna show you again how we did that in column U period five because we are at a new month and we effectively do need a new formula here, but it's going to work the same. I'm gonna take four days from December in week five. Go back to my Monthly Income Statement and take my daily revenues from the month of December. And here now I know what I'm gonna do. So I'm just gonna take, hit F4 while I'm in that cell three times and that gets me the anchored column that I need. And then I'm going to add that to the next part of the formula which is going to be the three days from the previous month, and that is now the month of November. So I'll go over to November, column I, hit F4 three times, and I've got a proper formula. And again, I can copy this now through the end of December which is going to be up through week eight. And then I can go ahead in week nine and do the same thing.

Now I'm looking at January. And again, I can anchor that plus six days from the previous week, because January 1st is our weekend for week nine and there's only one day from January actually in that week. The other six come from December. So I'll take six times my monthly income statement from December on a daily basis. Hit F4 three times and I've got a revenue forecast for that week nine which I can then copy through to the end of the month. And we're fortunately, the last four weeks of January are all gonna work nicely for us because they're all within that month, and this is where our 13-week cash flow period ends. And if we look at these numbers, they actually do look accurate. We can't exactly check these numbers, because if we take four weeks and we look at our sum total for the week, that's 39,262. If I go over to the Monthly Income Statement, that's not going to match up with the entire monthly forecast for November because the month has 30 days and the four weeks only have 28 days. But we can at least tell that things are looking reasonable based on the fact that the numbers are very close. So with that, we've completed the revenue section and now we can move on to the expenses.