 The cash collections budget is a component of the cash budget and it is based on data from the sales budget. You can see that the cash collections budget, which is again part of the cash budget, is the ninth budget completed in the master budget process. This budget can be either done separately, like I am doing, or it can be done as part of the larger cash budget. Either way, we will use these results in the cash budget. To start with, we need to break out our sales between cash sales and credit sales. In this example, let's assume that 25% of sales revenue are cash sales. Let's flash back a moment to the operating expenses budget. In my example, I assume that bad debt expense is 1% of net sales revenue, which you see here. Often, though, a better way of doing it is to base bad debt expense off of net credit sales, as I've shown in this example. So in this video series, I'll just keep my example the same, but be aware that you might encounter a problem that uses net credit sales instead of total sales. Okay, back to the cash collection budget. Cash collections each quarter includes not only quarterly cash sales, but also cash collection from credit sales. In this example, let's assume that 60% of the current quarter's credit sales are collected in the same quarter. Additionally, 35% of prior quarter's credit sales are collected in the current quarter. Finally, 4% from two quarters ago, those credit sales are collected in the current quarter. Recall that 1% of sales aren't expected to be collected. That's our bad debt amount. So 25% of sales revenue is the cash sales in each quarter. 60% of the credit sales are collected in the current quarter. So we need to take the total of sales revenue, subtract the cash sales to arrive at the credit sales, then 60% of that is collected in the current quarter. Here is the math for Q2. The total sales revenue is $400,000. $100,000 are cash sales, so credit sales must be $300,000. Therefore, 60% of $300,000 is $180,000. 35% of the credit sales from the prior quarter are collected in the current quarter. So we need to take a total sales revenue, subtract cash sales to arrive at credit sales, then 35% of that is collected in the following quarter. Here's the math for Q1. The total sales revenue is $400,000. $100,000 are cash sales, so credit sales must be $300,000. Therefore, 35% of $300,000 is $105,000, and this amount of the Q1 credit sales is collected in Q2. 4% of the credit sales are from two quarters ago, and those are collected in this current quarter. We need to take a look at sales revenue, subtract cash sales to arrive at credit sales, then 4% of that is collected at two quarters from now. Again, here's the math for Q1. Total sales revenue is $400,000. $100,000 are cash sales, so credit sales must be $300,000. Therefore, 4% of $300,000 is $12,000. This amount of the Q1 credit sales will be collected in Q3. The cash collections for Q3 are as follows. Q3 cash sales, $125,000. Q3 credit sales, of which 60% is collected in Q3, is $240,000. Q2 credit sales, of which 35% is collected in Q3, that's $105,000. And Q1 credit sales, of which 4% is collected in Q3, that's $12,000. So our cash collection for Q3 is $482,000. So now let's look how we would do this in Excel. Notice the column letters and the row numbers. We need to reference this as we build our budget in Excel. Here we have both the sales budget and the cash collections budget. We start by linking the two budgets together. Cash sales in the cash collection budget is calculated by using a formula linking sales revenue in the sales budget. Then we enter formulas to calculate the amount of credit sales collected in each quarter. You can see the formulas here. Finally, some of these numbers I've just assumed because I want to keep the examples straightforward.