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?

Definition Definition Estimate of an organization's cash flow for a future period. A cash budget forecasts future cash receipts and payments from various sources for a fiscal year. A cash budget can be created once a month or once a week to determine the organization's cash position and ensure its performance in relation to the budget. It aids in determining whether the company has enough cash and cash equivalents to meet its operational needs in the future.

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