Question

Quagmire Inc. has prepared the following sales budget for the quarter of April, May and June:

Sales Budget
	April	May	June	Total
Sales in units	10200	14400	12000	36600
Selling price per unit	x $30	x $30	x $30
Sales revenue	$306000	$432000	$360000	$1098000

All of the sales are on credit.
Quagmire collects from customers as follows:

40% of sales in the month of sale
25% in the month following the sale, and
35% in the second month following the sale.

Cash receipts budget
	April	May	June	Total
Collect from current sales	122400	172800	144000	439200
Collect from last month	99000	76500	108000	283500
Collect from 2 mos. Prior	119700	138600	107100	365400
Cash receipts	$ 341100	$ 387900	$ 359100	$ 1088100

Quagmire expects cost of goods sold to be 60% of sales.
They keep 10% of next months expected cost of goods sold in ending inventory. Below are budgeted purchases of inventory.
All purchases are paid for in the SAME month as the purchase.

Purchases Budget
	April	May	June	Total
Budgeted cost of goods sold	183600	259200	216000	658800
plus desired ending inventory	25920	21600	16200	16200
Total needs	209520	280800	232200	675000
less beginning inventory	(18360)	(25920)	(21600)	(18360)
Cost of purchases	191160	254880	210600	656640

Below is the budget for variable and fixed selling and administrative expenses.
Selling and admin expenses are paid in the month AFTER they are incurred.
Fixed expenses include depreciation of 13500 each month.

Sales and Admin Budget
	April	May	June	Total
Variable S&A expenses	61200	86400	72000	219600
Fixed S&A (including depreciation)	135000	135000	135000	405000

Quagmire has $185000 of cash on hand at he beginning of May.

Prepare a cash budget for May and June. (Use excel so that multiple attempts are easier.)

1. Compute the beginning balance in cash for June.  

2. Compute the ending balance in cash for June.  

Answer 1

• All working forms part of the answer

	May	June
Beginning Cash balance	$                   185,000.00	$                           135,320.00
Cash receipts	$                   387,900.00	$                           359,100.00
Total Cash available	$                   572,900.00	$                           494,420.00
Cash paid for purchases	$                   254,880.00	$                           210,600.00
Selling & admin expenses:
Variable	$                     61,200.00	$                             86,400.00
Fixed (exc; depreciation)	$                   121,500.00	$                           121,500.00
Total Cash payments	$                   437,580.00	$                           418,500.00
Ending Cash balance	$                   135,320.00	$                             75,920.00

• Beginning cash balance for June = $ 135,320
• Ending cash balance for June = $ 75,920