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 done separately, like I'm 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 assumed 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 and state of total sales
that uses net credit sales instead of total sales.
OK, 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 quarters 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 total sales revenue,
subtract cash sales, to arrive at credit sales.
and 35% of that is collected in the following quarter.
Here's the math for Q1.
The total sales revenue was $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 take a look at sales revenue, and subtract cash sales to arrive at credit sales
Then 4% of that is collected two quarters from now.
Again, here's the math for Q1.
Total sales revenue $400,000.
$100,000 are cash sales, so credit sales must be $300,000.
Therefore 4% of $300,000 is $12,000.
So this amount of the credit sales will be collected in Q3.
So 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 credits 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 at how we would do this in Excel.
Notice the column letters and the row numbers.
we need to reference as if 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 straight forward.