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 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?

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

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?

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.