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. The final solution correct answer check figure equals: (Total Liabilities & Equity $324,357) Actual & Budgeted Unit Sales April1,500 May1,000 June1,600 July1,400 August1,500 September1,200 Balance Sheet, May 31 19X5 Cash$8,000 Accounts receivable107,800 Merchandise Inventory52,800 Fixed assets (net)130,000 ----------- Total assets$298,600 ====== Accounts payable (merchandise)$74,800 Owner's equity223,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 sales20% Collected in month after sale60% Collected two months after sale20% Projected cash payments: Variable expenses30% of sales Fixed expenses (per month)$10,000 Depreciation per month$1,000 --------------------------------------------------------------- Sales Budget JuneJulyAugustUnits1,6001,4001,500Dollars$156,800137,200147,000Unit Purchase Budget JuneJulyAugustDesired ending inventory840900720Current month's unit sales1,6001,4001,500 ------------------------Total units needed2,4402,3002,220Beginning inventory600840900 ------------------------Purchases (units)1,8401,4601,320 ========================Purchase (dollars)$101,200$80,300$72,600 ========================Cash Budget JuneJulyAugustCash balance beginning$8,000$8,000$8,000Cash receipts: Collections from customers: From April sales29,400 From May Sales58,80019,600 From June sales31,36094,08031,360From July sales 27,44082,320From August sales 29,400 --------------------------Total cash available$127,560$149,120$151,080Cash disbursements: Merchandise$74,800$101,200$80,300Variable expenses47,04041,16044,100Fixed expenses10,00010,00010,000Interest paid0184356 ------------------------Total disbursements$131,840$152,544$134,756 ------------------------Cash balance before financing($4,280)($3,424)$16,324Less: Desired ending balance8,0008,0008,000 ------------------------Excess (deficit) of cash over needs($12,280)($11,424)$8,324 ------------------------Financing Borrowing$12,280$11,424$0Repayment00(8,324) --------------------------Total effects of financing$12,280$11,424($8,324) -------------------------Cash balance, ending$8,000$8,000$8,000 ========================Forecasted Income Statement For Quarter Ended August 31, 19X5 --------------------------------------------------- SalesFormula? Cost of goods soldFormula? ----------------- Gross profitFormula? ----------------- Expenses: Variable expensesFormula? Fixed expensesFormula? Depreciation expensesFormula? Interest expensesFormula? ----------------- Total expensesFormula? ----------------- Net incomeFormula? ========= Forecasted Balance Sheet August 31, 19X5 --------------------------------------------------- Assets: CashFormula? Accounts receivableFormula? Merchandise inventoryFormula? Fixed assets (net)Formula? ----------------- Total assetsFormula? ========= Liabilities & equity: Accounts payableFormula? Loans payableFormula? Owners equityFormula? ----------------- Total liabilities & equity$324,357

Forecasted Income Statement For Quarter Ended August 31, 19X5 Sales 4,41,000 =…

Answered: ster Budget Spreadsheet Instructions:…

Managerial Accounting

15th Edition

ISBN:9781337912020

Author:Carl Warren, Ph.d. Cma William B. Tayler

Publisher:Carl Warren, Ph.d. Cma William B. Tayler

Chapter8: Budgeting

Section: Chapter Questions

Problem 6PA: Budgeted income statement and balance sheet As a preliminary to requesting budget estimates of...

See similar textbooks

Related questions

Concept explainers

Question

100%

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. The final solution correct answer check figure equals: (Total Liabilities & Equity $324,357)

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 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	$324,357

Expert Solution

Trending now

This is a popular solution!

Step by step

Solved in 2 steps

SEE SOLUTION Check out a sample Q&A here

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, accounting and related others by exploring similar questions and additional content below.