Master Budget Spreadsheet Instructions: use the listed available Master Budget Spreadsheet excel data to list required formulas to complete the Forecasted Income Statement and Forecasted Balance Sheet. Actual & Budgeted Unit Sales April 2,000 May 1,000 June 1,600 July 1,400 August 1,500 September 1,700 Balance Sheet, May 31 19X5 Cash $8,000 Accounts receivable 107,800 Merchandise Inventory 52,800 Fixed assets (net) 150,000 ----------- Total assets $318,600 ====== Accounts payable (merchandise) $74,800 Owner's equity 223,800 ----------- Total liabilities & equity $298,600 ======= Average selling price $100 Average purchase cost per unit $60 Desired ending inventory (%of next month's unit sales) 70% Collections from customers: Collected in month of sales 30% Collected in month after sale 60% Collected two months after sale 10% Projected cash payments: Variable expenses 30% of sales Fixed expenses (per month) $10,000 Depreciation per month $2,000 ---------------------------------------------- ----------------- Sales Budget June July August Units 1,600 1,400 1,500 Dollars $160,000 140,000 150,000 Unit Purchase Budget June July August Desired ending inventory 980 1,050 1,190 Current month's unit sales 1,600 1,400 1,500 -------- -------- -------- Total units needed 2,580 2,450 2,690 Beginning inventory 700 980 1,050 -------- -------- -------- Purchases (units) 1,880 1,470 1,640 ======== ======== ======== Purchase (dollars) $112,800 $88,200 $98,400 ======== ======== ======== Cash Budget June July August Cash balance beginning $8,000 $8,000 $8,000 Cash receipts: Collections from customers: From April sales 20,000 From May Sales 60,000 10,000 From June sales 48,000 96,000 16,000 From July sales 42,000 84,000 From August sales 45,000 -------- --------- --------- Total cash available $136,000 $156,000 $153,000 Cash disbursements: Merchandise $74,800 $112,800 $88,200 Variable expenses 48,000 42,000 45,000 Fixed expenses 10,000 10,000 10,000 Interest paid 0 72 325 -------- -------- -------- Total disbursements $132,800 $164,872 $143,525 -------- -------- -------- Cash balance before financing $3,200 ($8,872) $9,475 Less: Desired ending balance 8,000 8,000 8,000 -------- -------- -------- Excess (deficit) of cash over needs ($4,800) ($16,872) $1,475 -------- -------- -------- Financing Borrowing $4,800 $16,872 $0 Repayment 0 0 ($1,475) -------- --------- --------- Total effects of financing $4,800 $16,872 ($1,475) -------- --------- -------- Cash balance, ending $8,000 $8,000 $8,000 ======== ======== ======== Forecasted Income Statement For Quarter Ended August 31, 19X5 --------------------------------------------------- Sales Formula? Cost of goods sold Formula? ----------------- Gross profit Formula? ----------------- Expenses: Variable expenses Formula? Fixed expenses Formula? Depreciation expenses Formula? Interest expenses Formula? ----------------- Total expenses Formula? ----------------- Net income Formula? ========= 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 Formula?

Master Budget Spreadsheet Instructions: use the listed available Master Budget Spreadsheet excel data to list required formulas to complete the Forecasted Income Statement and Forecasted Balance...

Master Budget Spreadsheet Instructions: use the listed available Master Budget Spreadsheet excel data to list required formulas to complete the Forecasted Income Statement and Forecasted Balance Sheet.

Actual & Budgeted Unit Sales
April	2,000
May	1,000
June	1,600
July	1,400
August	1,500
September	1,700

Balance Sheet, May 31 19X5
Cash	$8,000
Accounts receivable	107,800
Merchandise Inventory	52,800
Fixed assets (net)	150,000
	-----------
Total assets	$318,600
	======
Accounts payable (merchandise)	$74,800
Owner's equity	223,800
	-----------
Total liabilities & equity	$298,600
	=======
Average selling price	$100
Average purchase cost per unit	$60
Desired ending inventory (%of next month's unit sales)	70%
Collections from customers:
Collected in month of sales	30%
Collected in month after sale	60%
Collected two months after sale	10%
Projected cash payments:
Variable expenses	30% of sales
Fixed expenses (per month)	$10,000
Depreciation per month	$2,000
----------------------------------------------	-----------------
Sales Budget
	June	July	August
Units	1,600	1,400	1,500
Dollars	$160,000	140,000	150,000
Unit Purchase Budget
	June	July	August
Desired ending inventory	980	1,050	1,190
Current month's unit sales	1,600	1,400	1,500
	--------	--------	--------
Total units needed	2,580	2,450	2,690
Beginning inventory	700	980	1,050
	--------	--------	--------
Purchases (units)	1,880	1,470	1,640
	========	========	========
Purchase (dollars)	$112,800	$88,200	$98,400
	========	========	========
Cash Budget
	June	July	August
Cash balance beginning	$8,000	$8,000	$8,000
Cash receipts:
Collections from customers:
From April sales	20,000
From May Sales	60,000	10,000
From June sales	48,000	96,000	16,000
From July sales		42,000	84,000
From August sales			45,000
	--------	---------	---------
Total cash available	$136,000	$156,000	$153,000
Cash disbursements:
Merchandise	$74,800	$112,800	$88,200
Variable expenses	48,000	42,000	45,000
Fixed expenses	10,000	10,000	10,000
Interest paid	0	72	325
	--------	--------	--------
Total disbursements	$132,800	$164,872	$143,525
	--------	--------	--------
Cash balance before financing	$3,200	($8,872)	$9,475
Less: Desired ending balance	8,000	8,000	8,000
	--------	--------	--------
Excess (deficit) of cash over needs	($4,800)	($16,872)	$1,475
	--------	--------	--------
Financing
Borrowing	$4,800	$16,872	$0
Repayment	0	0	($1,475)
	--------	---------	---------
Total effects of financing	$4,800	$16,872	($1,475)
	--------	---------	--------
Cash balance, ending	$8,000	$8,000	$8,000
	========	========	========
Forecasted Income Statement For Quarter Ended August 31, 19X5
---------------------------------------------------
Sales	Formula?
Cost of goods sold	Formula?
	-----------------
Gross profit	Formula?
	-----------------
Expenses:
Variable expenses	Formula?
Fixed expenses	Formula?
Depreciation expenses	Formula?
Interest expenses	Formula?
	-----------------
Total expenses	Formula?
	-----------------
Net income	Formula?
	=========
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	Formula?

Jun 03, 2022

SOLUTION.PDF

Master Budget Spreadsheet Instructions: use the listed available Master Budget Spreadsheet excel data to list required formulas to complete the Forecasted Income Statement and Forecasted Balance...

Get Answer To This Question

Related Questions & Answers

Submit New Assignment