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 April1,500 May1,000 June1,600 July1,400 August1,500 September1,700 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$100 Average purchase cost per unit$55 Desired ending inventory (%of next month's unit sales)60% Collections from customers: Collected in month of sales30% Collected in month after sale60% Collected two months after sale10% Projected cash payments: Variable expenses25% of sales Fixed expenses (per month)$10,000 Depreciation per month$1,000 --------------------------------------------------------------- Sales Budget JuneJulyAugustUnits1,6001,4001,500Dollars$160,000140,000150,000Unit Purchase Budget JuneJulyAugustDesired ending inventory8409001,020Current month's unit sales1,6001,4001,500 ------------------------Total units needed2,4402,3002,520Beginning inventory600840900 ------------------------Purchases (units)1,8401,4601,620 ========================Purchase (dollars)$101,200$80,300$89,100 ========================Cash Budget JuneJulyAugustCash balance beginning$8,000$8,000$8,000Cash receipts: Collections from customers: From April sales15,000 From May Sales60,00010,000 From June sales48,00096,00016,000From July sales 42,00084,000From August sales 45,000 --------------------------Total cash available$131,000$156,000$153,000Cash disbursements: Merchandise$74,800$101,200$80,300Variable expenses40,00035,00037,500Fixed expenses10,00010,00010,000Interest paid0720 ------------------------Total disbursements$124,800$146,227$127,800 ------------------------Cash balance before financing$6,200$9,773$25,200Less: Desired ending balance8,0008,0008,000 ------------------------Excess (deficit) of cash over needs($1,800)$1,773$17,200 ------------------------Financing Borrowing$1,800$0$0Repayment0($1,773)($27) --------------------------Total effects of financing$1,800($1,773)($27) -------------------------Cash balance, ending$8,000$8,000$25,173 ========================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 & equityFormula?

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

Answered: udget Spreadsheet Instructions: use the…

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.

Actual & Budgeted Unit Sales
April	1,500
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)	130,000
	-----------
Total assets	$298,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	$55
Desired ending inventory (%of next month's unit sales)	60%
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	25% 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	$160,000	140,000	150,000
Unit Purchase Budget
	June	July	August
Desired ending inventory	840	900	1,020
Current month's unit sales	1,600	1,400	1,500
	--------	--------	--------
Total units needed	2,440	2,300	2,520
Beginning inventory	600	840	900
	--------	--------	--------
Purchases (units)	1,840	1,460	1,620
	========	========	========
Purchase (dollars)	$101,200	$80,300	$89,100
	========	========	========
Cash Budget
	June	July	August
Cash balance beginning	$8,000	$8,000	$8,000
Cash receipts:
Collections from customers:
From April sales	15,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	$131,000	$156,000	$153,000
Cash disbursements:
Merchandise	$74,800	$101,200	$80,300
Variable expenses	40,000	35,000	37,500
Fixed expenses	10,000	10,000	10,000
Interest paid	0	72	0
	--------	--------	--------
Total disbursements	$124,800	$146,227	$127,800
	--------	--------	--------
Cash balance before financing	$6,200	$9,773	$25,200
Less: Desired ending balance	8,000	8,000	8,000
	--------	--------	--------
Excess (deficit) of cash over needs	($1,800)	$1,773	$17,200
	--------	--------	--------
Financing
Borrowing	$1,800	$0	$0
Repayment	0	($1,773)	($27)
	--------	---------	---------
Total effects of financing	$1,800	($1,773)	($27)
	--------	---------	--------
Cash balance, ending	$8,000	$8,000	$25,173
	========	========	========
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?

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.

udget 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 1,500 May 1,000 June 1,600 July 1,400 August 1,500 September 1,700