Question

Astro Company is preparing a cash budget for the fourth quarter of the year 2017. The following information is available:

August September October November December

Sales (in dollars) $160,000 $190,000 $170,000 $150,000 $210,000  

Purchases of Direct Materials (in dollars) $45,000 $58,000 $52,000 $37,000 $63,000

Direct Labor Costs $65,000 $78,000 $71,000 $60,000 $94,000

Overhead Costs $34,000 $34,000 $34,000 $34,000 $34,000

Selling and Admin Costs   $29,000 $29,000 $29,000 $29,000 $29,000

All sales are made on account. The accounts receivable are collected as follows: 20% in the month of sale; 70% in the month following the sale; and 10% in the second month following the sale.

All purchases of direct materials are on account. The purchases on account are paid as follows: 60% in the month following the purchase; and 40% in the second month following the purchase.

All other costs are paid as incurred. The overhead costs include $3,000 of monthly depreciation and the selling and administrative costs include $1,000 of monthly depreciation.

The projected beginning cash balance on October 1 is $5,000. Astro Company would like to maintain a $5,000 ending cash balance each month.

REQUIRED:

A. Prepare a schedule of cash collections from sales for October, November, and December.

B. Determine the expected accounts receiavble balance on December 31.

C. Prepare a schedule of cash payments for direct material purchases for October, November, and December.

D. Determine the expected accounts payable balance on December 31.

E. Prepare a cash budget for October, November, and December.

F. Based on the results of your budget, do you think some additional cash planning is required? Give TWO possible suggestions for amending the cash budget.

Answer 1

a)
	Cash collection Budget
	Particulars	october	November	december	Total
	Sales	170000	150000	210000	530000
	Credit sales	170000	150000	210000	530000
	Collection
	August	16000			16000
	September	133000	19000		152000
	october	34000	119000	17000	170000
	November		30000	105000	135000
	December			42000	42000
	Total cash collection for fourth quarter	183000	168000	164000	515000
b)	Accounts receivable balance
	November accounts receivable(150000*10%)	15000
	Decemeber accounts receivable (210000-42000)	168000
		183000
c)	Schedule of cash disbursements/ payments
	particulars	october	November	december	Total
	Purchase of direct materials	52000	37000	63000	152000
	cash payments towards purrchases
	August (45000*40%)	$18,000			$18,000
	September (58000*60%)(58000*40%)	$34,800	$23,200		$58,000
	october (52000*60%) (52000*40%)		$31,200	$20,800	$52,000
	November (52000*60%)			$22,200	$22,200
	Total payment towards purchases for the 4th quarter	$52,800	$54,400	$43,000	$150,200
d)
	November purchases (37000*40%)	$14,800
	December purchases	$63,000
	Accounts payable	$77,800
e)	Cash Budget
	particulars	october	November	december	Total
	Beginning cash balance	$                          5,000	$                                          5,200	$              (200)	$               5,000
	Cash collection	$                     183,000	$                                     168,000	$        164,000	$          515,000
	Total cash available	$                     188,000	$                                     173,200	$        163,800	$          520,000
	Less:
	Merchansise purchases	$                        52,800	$                                       54,400	$          43,000	$          150,200
	Direct labor	$                        71,000	$                                       60,000	$          94,000	$          225,000
	overhead cost ( Exclude depreciation)	$                        31,000	$                                       31,000	$          31,000	$             93,000
	Selling and administration ( Exclude depreciation)	$                        28,000	$                                       28,000	$          28,000	$             84,000
	Total Disbursements	$                     182,800	$                                     173,400	$        196,000	$          552,200
	Excess ( deficiency) of receipts over disbursements	$                          5,200	$                                           (200)	$        (32,200)	$          (32,200)
	Financing
	Borrowings		$                                                 -	$                   -	$                      -
	Repayments			$                   -	$                      -
	Interest			$                   -	$                      -
	Cash Balance Ending	$                          5,200	$                                           (200)	$        (32,200)	$          (32,200)

F) Based on the above cash budget, there is need for the cash planning, as it is showing negative cash balances in month of novemeber and december.

Company can change its current sales terms

company can negotiate with the creditor for change in payable terms

otherwise need borrow from the banks for its minimum balance purpose.

F)