Question

The Hale Company is currently working on its cash budget for the coming year. The following information is available:

Projected sales for the coming year:

Month	Projected Sales
January	$850,000
February	750,000
March	730,000
April	850,000
May	830,000
June	750,000
July	900,000

The collection history of the Hale Company has been as follows:

20% of sales are collected in the month of the sale.

60% of the sales are collected in the month following the sale.

12% of the sales are collected in the 2^nd month following the sale.

5% of the sales are collected in the 3^rd month following the sale.

The following information regarding costs is available:

The cost of goods sold is 54% of sales

Items for sale are purchased in the month of the sale.

80% of accounts payable are paid in the month following when the cost is incurred.

20% of accounts payable are paid in the 2^nd month following when the cost is incurred.

Wages are 28% of sales and are paid currently

Annual general and administrative costs are $1,411,200 and are incurred evenly throughout the year.

Annual property taxes are $14,000 and are paid semi annually in June and October.

A $10,000 cash capital purchase will be made in April.

The beginning cash balance in April is expected to be $47,000. The Hale Company has a policy of maintaining a minimum cash balance of $45,000. The company has an arrangement with a local bank for a line of credit that carries a 10% annual interest rate. If the ending monthly balance falls below $45,000, the company will borrow against the line of credit so that the minimum balance can be maintained. If the company has borrowed against the line of credit and a cash balance is expected to be above $45,000 at the end of a particular month, then repayments will be made bringing the cash balance down to $45,000. Interest on the line of credit is paid monthly. Assume that all line of credit transactions occur on the last day of the month.

Required:

Prepare a cash budget for the Hale Company for the 2^nd quarter of the year. Include April, May, June, and a quarter total in your budget.

Answer 1

	All amounts are in $
Particulars	April	May	June	Total quarter
Opening balance of cash	47,000.00	45,000.00	45,000.00	45,000.00
Cash collected from sales	740,500.00	801,100.00	786,500.00	2,328,100.00
(A): Total cash receipts before overdraft	787,500.00			787,500.00
Bank overdraft to be taken	69,140.00			69,140.00
Total cash receipts after bank overdraft	856,640.00	846,100.00	831,500.00	2,534,240.00
Cash paid to creditors	446,040.00	450,360.00	413,640.00	1,310,040.00
Wages paid in cash	238,000.00	232,400.00	210,000.00	680,400.00
Annual general and administrative expenses	117,600.00	117,600.00	117,600.00	352,800.00
Annual property taxes paid (14000/2)			7,000.00	7,000.00
Capital purchase	10,000.00			10,000.00
Interest paid		576.17	574.80	1,150.97
Repayment of bank overdraft		163.83	37,685.19	37,849.02
(B): Total cash payments	811,640.00	800,936.17	786,499.99	2,399,076.16
Closing cash balance	45,000.00	45,000.00	45,000.00	45,000.00
©: Cash balance before overdraft if any (A-B)	(24,140.00)			(24,140.00)
Thus, bank overdraft to be taken (45000 - C)	69,140.00			69,140.00

Working note:

	January	February	March	April	May	June	July
Sales	850000	750000	730000	850000	830000	750000	900000
20% Cash collection from sales				170000	166000	150000	180000
60% sales are collected in the following month				438000	510000	498000	450000
12% sales are collected in 2nd month following sales				90000	87600	102000	99600
5% of sales are collected in 3rd month following sales				42500	37500	36500	42500
Cash collected from sales				740500	801100	786500
Cost of goods sold @54% of sales	459000	405000	394200	459000	448200	405000	486000
cash paid for accounts payable
80% of the month				367200	358560	324000
20% in the following month				78840	91800	89640
Cash paid to the creditors				446040	450360	413640
Wages are 28% of the sales	238000	210000	204400	238000	232400	210000	252000
Wages paid in cash for the month of expenditures				238000	232400	210000
Annual general and administrative cost incurred evenly (1411200/12)	117600	117600	117600	117600	117600	117600	117600

Interest paid
April	0
May (69140 x 10%)x1/12	576.17
June (69140- 163.83)x10%x1/12	574.80