Master Budget Spreadsheet Instructions: use the listed available Master Budget Spreadsheet excel data to list the required formulas to complete the Forecasted Balance Sheet including the missing spreadsheet items that are used in summing the initial $324,357 figure used in this given Forcasted Balance Sheet's Fixed assets (net) formula: $131,717=$324,357-$8,000-$145,040-$39,600.

Respective corresponding Fixed assets (net) Forecasted Balance Sheet formulas derived from the Master Budget Spreadsheet data:

$324,357=Spreadsheet data?

Cash: $8,000 Cash balance, ending

Accounts receviable: 145,040=(147,000*80%)+(137,200*20%) Sales buget August*80%+Sales budget July*20%

Merchandise inventory: 39,600=720*55 Desired ending inventory August*Average purchase cost per unit  

Actual & Budgeted Unit Sales
April	1,500
May	1,000
June	1,600
July	1,400
August	1,500
September	1,200
Balance Sheet, May 31 19X5
Cash	$8,000
Accounts receivable	107,800
Merchandise Inventory	52,800
Fixed assets (net)	130,000
	-----------
Total assets	$298,600
	======
Accounts payable (merchandise)	$74,800
Owner's equity	223,800
	-----------
Total liabilities & equity	$298,600
	=======
Average selling price	$98
Average purchase cost per unit	$55
Desired ending inventory (%of next month's unit sales)	60%
Collections from customers:
Collected in month of sales	20%
Collected in month after sale	60%
Collected two months after sale	20%
Projected cash payments:
Variable expenses	30% of sales
Fixed expenses (per month)	$10,000
Depreciation per month	$1,000
----------------------------------------------	-----------------
Sales Budget
	June	July	August
Units	1,600	1,400	1,500
Dollars	$156,800	137,200	147,000
Unit Purchase Budget
	June	July	August
Desired ending inventory	840	900	720
Current month's unit sales	1,600	1,400	1,500
	--------	--------	--------
Total units needed	2,440	2,300	2,220
Beginning inventory	600	840	900
	--------	--------	--------
Purchases (units)	1,840	1,460	1,320
	========	========	========
Purchase (dollars)	$101,200	$80,300	$72,600
	========	========	========
Cash Budget
	June	July	August
Cash balance beginning	$8,000	$8,000	$8,000
Cash receipts:
Collections from customers:
From April sales	29,400
From May Sales	58,800	19,600
From June sales	31,360	94,080	31,360
From July sales		27,440	82,320
From August sales			29,400
	--------	---------	---------
Total cash available	$127,560	$149,120	$151,080
Cash disbursements:
Merchandise	$74,800	$101,200	$80,300
Variable expenses	47,040	41,160	44,100
Fixed expenses	10,000	10,000	10,000
Interest paid	0	184	356
	--------	--------	--------
Total disbursements	$131,840	$152,544	$134,756
	--------	--------	--------
Cash balance before financing	($4,280)	($3,424)	$16,324
Less: Desired ending balance	8,000	8,000	8,000
	--------	--------	--------
Excess (deficit) of cash over needs	($12,280)	($11,424)	$8,324
	--------	--------	--------
Financing
Borrowing	$12,280	$11,424	$0
Repayment	0	0	(8,324)
	--------	---------	---------
Total effects of financing	$12,280	$11,424	($8,324)
	--------	---------	--------
Cash balance, ending	$8,000	$8,000	$8,000
	========	========	========
Forecasted Balance Sheet August 31, 19X5
---------------------------------------------------
Assets:
Cash	Formula?
Accounts receivable	Formula?
Merchandise inventory	Formula?
Fixed assets (net)	Formula?
	-----------------
Total assets	Formula?
	=========
Liabilities & equity:
Accounts payable	Formula?
Loans payable	Formula?
Owners equity	Formula?
	-----------------
Total liabilities & equity	Solution?