Question

You are required to prepare the cash budget for the second quarter of the year
for DMA for the months of April, May, and June; including the cash collections
schedule and cash disbursements schedule. Grades will be awarded as follows:
i. Cash Collections Schedule for the second quarter.
ii. Cash Disbursements Schedule for the second quarter.   
iii. Cash Budget for the second quarter.   

Sales Forecasts for 2020 in S July 3000 000 August3 000 000 March1 800 000 September3 200 000 April2 200 000October3 200 000 May 2 500 000 Novber 3000 000 June 2 800 000December3 400 000 January1 800 000 Febuary2 000 000 The chief financial manager (CFO) of the company has formed a group of five managerial accountants and the group has been assigned the responsibility for preparing the cash budget of the company in view of the above sales forecasts taking into account the following points 1. DMA has excellent experience in collecting receivables due from debtors. The companys credit policy is to collect 60% of the receivables in the month following the sale, and 38% to be collected in the second month of sale, two months after the sale. The remaining 2% is unlikely to be collected. 2. Purchases of raw material are the largest expenditure of the company, where the cost of these materials is 50% of the total value ofsales in each month. 3. Previous experience with the company's suppliers shows that 80% of the balances of the creditors are paid after one month from the time of purchase. The rest ofthe outstanding balance of 20% will be paid after two months from the date of purchase 4. Direct labour is determined based on the volume of sales and is equal to 20% of the sales of each month, That cost shall be paid in the same month as it is due General and administrative expenses are estimated at $ 2 640 000 per year. This amount includes the items shown in the following table. All expenses are allocated equally during the year with the exception of property taxes, which are paid in 4 equal instalments on the last month of each quarter 5. Annual Expected General and Administration Expenses in S Salaries 480 000 Rent 660 000 Property Taxes 240 000 Insurance 360 000 Utilities 300 000 Depreciation 600 000 6. There is a need to purchase equipment to assist the company in its operations Equipment purchases in April are estimated at 300,000 and $ 346,300 in May. 7. The Company has a policy of maintaining a cash balance at the end of each month of $ 100 000 so that cash is borrowed or invested to reach this amount It is estimated that any excess cash, which the company shall invest will yield a 1% monthly return. This monthly return shall be collected in the month following the investment. On the other hand, any deficit cash shall be borrowed from one ofthe commercial banks at an annual interest rate 12%. The monthly andlor any amounts borrowed, the contract is for 6 months

Answer 1

i)	Statement showing calculation of Cash Collection Schedule for the second quarter
Note: Debtors collection would be 60% of last month sales,38% of last to last month sales and remaning 2% shall be bad-debt.

Particulars	Amount received in
	Jan	Feb	Mar	April	May	Jun
Credit Sales	$     1,800,000.00	$     2,000,000.00	$     1,800,000.00	$ 2,200,000.00	$ 2,500,000.00	$ 2,800,000.00
Amount received of:-
60% of Last month		$     1,080,000.00	$     1,200,000.00	$ 1,080,000.00	$ 1,320,000.00	$ 1,500,000.00
38 % of Last to last month			$        684,000.00	$      760,000.00	$      684,000.00	$      836,000.00
Total Cash Collection				$ 1,840,000.00	$ 2,004,000.00	$ 2,336,000.00

ii)	Statement showing calculation of Cash Disbursement Schedule for the second quarter

Cash Disbursement Schedule
Second Quarter
Particulars	April	May	Jun
Payment of inventory purchases            ( Refer Working Note 4)	$   920,000.00	$1,060,000.00	$1,220,000.00
Labour Cost (Working Note 1)	$   440,000.00	$   500,000.00	$   560,000.00
General & Administrative Expenses (except property tax expenses) ( Working Note 2)	$   200,000.00	$   200,000.00	$   200,000.00
Property Tax expenses ( Working Note 3)	$                  -	$                  -	$     60,000.00
Equipment purchase (Given)	$   300,000.00	$   346,300.00	$                  -
Total disbursements	$1,860,000.00	$2,106,300.00	$2,040,000.00

iii)	Cash Budget
	Second Quarter
	Particulars	April	May	Jun
	Beginning cash balance	$   100,000.00	$   100,000.00	$   100,000.00
	Receipts:
	Collections of sales                        ( Refer solution i )	$1,840,000.00	$2,004,000.00	$2,336,000.00
	Interest on Investments (@1%)	$                  -	$                  -	$                  -
	Total cash available	$1,840,000.00	$2,004,000.00	$2,336,000.00
	Disbursements:
	Total cash disbursement (refer solution ii)	$1,860,000.00	$2,106,300.00	$2,040,000.00
	Interest on Loan*** ( @ 1%)	$                  -	$          200.00	$       1,225.00
	Total disbursements	$1,860,000.00	$2,106,500.00	$2,041,225.00
	Cash balance*	$     80,000.00	$      (2,500.00)	$   394,775.00
	Bank loan**	$     20,000.00	$   102,500.00	$                  -
	Investment	$                  -	$                  -	$   294,775.00
	Adjusted cash balance	$   100,000.00	$   100,000.00	$   100,000.00
	*Cash Balance= Beginning cash bal + recipt- payment
	** Since the Cash balance is $ 80000 in april and -2500 in may which is less than $ 100000, so bank automatically extends of credit so that the checking account balance is at least $100000 at month-end.Accordingly $ 20000 has been extended as credit in april and 102500 has been extended as credit in May .
	*** Since it is informed that Interest payment are paid at the end of the month @ 1 % per month ( 12% annually) following the loan so $ 20000 borrowed in april and $ 102500 borrowed in may has been paid Interest @ 1 % for the month may (20000*1%)i.e. $ 200 and [(20000+102500)*1%] i.e. 1225.
Working Note 1	Particulars	Amount paid in
		April	May	Jun
	Credit Sales	$     2,200,000.00	$     2,500,000.00	$     2,800,000.00
	20% of Each month sales paid in the same month	$        440,000.00	$        500,000.00	$        560,000.00
Working Note 2	General & Administrative Expenses (except property tax expenses) per month	=	Total General & Administrative Expenses - Property tax Expenses
			12 months
		=	26,40,000 - 2,40,000
			12
		=	$        200,000.00
Working Note 3	Property tax expenses ( paid in jun)	=	Total Property tax expenses
			4 qtr
		=	2,40,000
			4
		=	$           60,000.00
Working Note 4	Calculation of Cash paid to creditors
	Note:- Raw Material shall be the 50% of sales of each month which shall be paid as 80% of the purchase of last month and 20 % of the purchase of last to last month.

Particulars	Amount paid in
	Jan	Feb	Mar	April	May	Jun
Credit Sales	$     1,800,000.00	$     2,000,000.00	$     1,800,000.00	$ 2,200,000.00	$ 2,500,000.00	$ 2,800,000.00
Purchase Cost	$        900,000.00	$     1,000,000.00	$        900,000.00	$ 1,100,000.00	$ 1,250,000.00	$ 1,400,000.00
Amount paid of:-
80% of Last month		$        720,000.00	$        800,000.00	$      720,000.00	$      880,000.00	$ 1,000,000.00
20 % of Last to last month			$        180,000.00	$      200,000.00	$      180,000.00	$      220,000.00
Total Cash Disbursement				$      920,000.00	$ 1,060,000.00	$ 1,220,000.00