Question

Tilson Corporation has projected sales and production in units for the second quarter of the coming year as follows:

	April	May	June
Sales	49,000	39,000	59,000
Production	59,000	49,000	49,000

Cash-related production costs are budgeted at $6 per unit produced. Of these production costs, 40% are paid in the month in which they are incurred and the balance in the following month. Selling and administrative expenses will amount to $60,000 per month. The accounts payable balance on March 31 totals $178,000, which will be paid in April.

All units are sold on account for $15 each. Cash collections from sales are budgeted at 60% in the month of sale, 30% in the month following the month of sale, and the remaining 10% in the second month following the month of sale. Accounts receivable on April 1 totaled $477,000 ($97,000 from February's sales and $380,000 from March’s sales).

Required:

a. Prepare a schedule for each month showing budgeted cash disbursements for Tilson Corporation.

b. Prepare a schedule for each month showing budgeted cash receipts for Tilson Corporation.

Note- Please show how you acquired your answers/ Cell formulas in excel. Thanks!

Answer 1

Tilson Corporation
Cash Disbursement schedule
	April	May	June
Budgeted Production (Units)	59,000	49,000	49,000
Production cost per unit	6	6	6
Total production cost	354,000	294,000	294,000
Accounts Payable, paid in Feb	178,000
Cash Disbursement in same month	141,600	117,600	117,600
Cash Disbursement in next month		212,400	176,400
Selling and admin expense	60,000	60,000	60,000
Total disbursement	379,600	390,000	354,000
Tilson Corporation
Cash collection schedule
	April	May	June
Budgeted sales (Units)	49,000	39,000	59,000
selling price per unit	15	15	15
Total production cost	735,000	585,000	885,000
Accounts Receivable,	382,000	95,000
Cash collection in same month	441,000	351,000	531,000
Cash collection in next month		220,500	175,500
Cash collection in next following month			73,500
Total collection	823,000	666,500	780,000