I am trying to figure out how do I work the following problem for my final exam coming up please help. Can you also explain to me how to setup a schedule of cash collection?

Problem:

July
Expected sales in units 2,000
Selling price per unit $ 50
Total sales $100,000

August
Expected sales in units 3,000
Selling price per unit $ 50
Total sales $150,000

However, the actual sales were not quite as high as was anticipated. The actual sales were:

July
Actual sales in units 1,000
Selling price per unit $ 50
Total sales $ 50,000

August
Actual sales in units 2,500
Selling price per unit $ 50
Total sales $125,000

Create a schedule of cash collections for the budgeted sales for July and August. Assume that 80% of sales are collected in the month of sale and the remaining 20% of sales are collected the following month.

Thanking you in advance.

morgaine300

They're giving you a lot of information not necessary to do the cash collection budget. Perhaps the info is needed for another portion of the problem? Because it's asking for the budgeted collections, meaning the actual numbers aren't relevant. (They would be if you had to do an actual versus budget.) And the per unit info is not necessary for this.

The only thing you need is the total expected sales for July and August, which is $100,000 and $150,000 respectively.

When setting one of these up, it's easier to go forward with it. That is, take July sales and move forward and finish out when it will be collected. Without seeing this on-the-stop in person, it's difficult to show you what I mean.

But take July's sales of $100,000. It says 80% would be collected in the month of the sales. So 80% of that is collected in July, the same month. Then work forwards and finish that out. The other 20% is collected in the next month, which is August. Then do the same for August. There's $150,000 in sales. 80% of that gets collected in the current month, August. The other 20% would be collected in September, though they aren't asking for that.

I can't make the indents work the way I want -- have to experiment with that. So instead I just did it real quick in Excel and attached that. (Assuming the attachment works.) I color-coded it. The red is July's sales and carrying through with the collections, and the purple is the August's sales and carrying through with those collections. (I added the Sept just so you can what happens to the rest of August.)

How the format should be is sort of a "book thing." There isn't a definitive way these need to be, so you try to find an example in your book and see how they're setting it up. All I've done is the basic math part.

Attached Files

Cash Collections.xls (13.5 KB, 24 views)