Question

Gulf Company, a successful retailer located in Qatar, is preparing its budget for the upcoming quarter. The following data and information are available for the months of April, May, and June 2019.

1. Budgeted sales and expenses for the period:

                                                              April                May                June       

Sales................................................... $400,000       $600,000           $500,000
Merchandise Purchases....................... 240,000         360,000             300,000
Administrative Expenses....................... 18,000           18,000               16,000
Insurance Payments............................... 30,000           30,000               30,000
Advertising.......................................... 140,000         153,000             100,000
Machinery Purchases............................. 16,000              -------                 -------
Depreciation Expense............................ 20,000           20,000               20,000

2. All sales are on account. 20% of a month’s sales are collected in the month of the sale, 75% is collected in the month following the sale, and 5% is collected in the second month following the sale.
3. Accounts receivable on April 1 will be $303,000, of which $282,000 will be collected during April and $21,000 will be collected during May.
4. Merchandise purchases are paid in full in the month following the purchase. The March 31 accounts payable balance of $216,000 for merchandise purchases will be paid in April.
5. The cash balance on April 1 is budgeted to be $52,000.
6. The company requires a minimum cash balance of $50,000 at the end of each month. The company may borrow any amount from a local bank at the beginning of a month and will make repayments at the end of a month. The yearly interest rate is 5% and interest payments are due on any principal at the time it is repaid. For simplicity, assume that interest is not compounded.

Required:

1. Prepare a Schedule of Expected Cash Collections for April, May, June, and for the quarter in total.
2. Prepare a Cash Budget for April, May, June, and for the quarter in total.

Answer 1

1. Schedule of expected cash collections:

Particulars	April	May	June	Total
Accounts Receivable of April Month (Note 1)	$282,000	$21,000		$303,000
April Sales (Note 2)	$80,000	$300,000	$20,000	$400,000
May Sales (Note 3)		$120,000	$450,000	$570,000
June Sales (Note 4)			$100,000	$100,000
Total	$362,000	$441,000	$570,000	$1373000

2. Cash Budget

Particulars	April	May	June	Total
Opening Balance (Note 5)	$52,000	$50,000	$50,000	$152,000
Cash collections (Note 6)	$362,000	$441,000	$570,000	$1373000
A. Total cash available (Sum of opening balance and cash collections)	$414,000	$491,000	$620,000	$1525000
Cash outflows:
Merchandise payments (note 7)	$216,000	$240,000	$360,000	$816,000
Administrative expenses	$18,000	$18,000	$16,000	$52,000
Insurance	$30,000	$30,000	$30,000	$90,000
Advertising expense	$140,000	$153,000	$100,000	$393,000
Machinery expense	$16,000			$16000
B. Total cash outflow	$420,000	$441,000	$506,000	$1367000
C. Cash balance before borrowings (A-B)	($6000)	$50,000	$114000	$158000
D. Borrowings (Note 8)	$56,000	Nil	Nil	$56000
E. Repayment (Note 9)			($56,000)	($56000)
F. Interest (Note 10)			($700)	($700)
G. Ending cash balance (Note 11)	$50,000	$50,000	$57,300	$157300

Notes:

1. Accounts receivable is given in the question as on April is $303,000 which will be collected in April $282,000 and in May $21000.

2. April sales is given as $400000.

Out of this 20% of 400000 = 80000 is collected in April.

75% * 400000 = 300000 is collected in may.

5% * 400000 = 20000 is collected in May

3. May Sales is given as $600000

20% * 600000 = $120000 is collected in May

75% * 600000 = $450000 is collected in June

4. June sales is given as $500000.

20% * 500000 = $ 100000 is collectd in June. Remaining amount will be collected in July and august which is not required to be shown in this statement.

5. Opening balance of April is given as $52000. Opening balance of May and june would be the closing balances of the previous months.

6. Cash collections of each month is the total amounts as computed in the collection schedule

7. Merchandise payment:

April Month: It is mentioned that opening Accounts payable amount for merchandise is $216000 of previous month which is paid in April.

May: The April amount of 240000 is paid in may.

June: The may amount of $360000 is paid in june.

8. It is mentioned that the closing balance of $50000 has to be maintained. Since for April the ending cash balance amounts to negative $6000 hence amount of 56000 is borrowed to make the closing balance as $50000.

This closing balance becomes the opening balance of May. Now the closing balance of May is exact $50000. Here no repayments of loan or interest could be made as then the ending balance would fall below $50000

The closing balance of May become opening balance of June.

9. Ending balance before borrowings etc in June is $114000. Hence here finally repayment of $56000 is made.

10. Interest is computed for 3 months on the borrowed amount @5% per annum

= 56000* 5% = 2800 is the interest for 12 months

interest for 3 months = 2800 * 3/12 = $700.

11. Ending cash balance:

April : Borrowings of $56000 is receipt. So final balance = (6000) + 56000 = $50000

May : $50,000

June : Cash balance before borrowing - repayment - interest = $114000 - 56000 - 700 = $57300.

12. Depreciation is not cash expense and hence not shown in cash budget