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	12600	16800	14400	43800
Selling price per unit	x $60	x $60	x $60
Sales revenue	$756000	$1008000	$864000	$2628000

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

45% of sales in the month of sale
25% in the month following the sale, and
30% in the second month following the sale.

Cash receipts budget
	April	May	June	Total
Collect from current sales	340200	453600	388800	1182600
Collect from last month	225000	189000	252000	666000
Collect from 2 mos. Prior	248400	270000	226800	745200
Cash receipts	$ 813600	$ 912600	$ 867600	$ 2593800

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	453600	604800	518400	1576800
plus desired ending inventory	60480	51840	41040	41040
Total needs	514080	656640	559440	1617840
less beginning inventory	(45360)	(60480)	(51840)	(45360)
Cost of purchases	468720	596160	507600	1572480

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 17100 each month.

Sales and Admin Budget
	April	May	June	Total
Variable S&A expenses	151200	201600	172800	525600
Fixed S&A (including depreciation)	171000	171000	171000	513000

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.)

Compute the beginning balance in cash for June.  

Compute the ending balance in cash for June.  

Answer 1

Cash Budget for May and June
	May	June
Beginning Cash balance	$185,000.00	$196,340.00
Cash receipts from Sales	$912,600.00	$867,600.00
Total Cash Available	$1,097,600.00	$1,063,940.00
Cash disbursements
Purchase payments	$596,160.00	$507,600.00
Variable S & A expenses	$151,200.00	$201,600.00
Fixed S & A expenses	$153,900.00	$153,900.00
Total Cash Disbursements	$901,260.00	$863,100.00
Ending Cash balance	$196,340.00	$200,840.00
The beginning cash balance for June =		$196,340.00
The ending cash balance for June =		$200,840.00