Question

Acct 202: Managerial Accounting Professor Edward J. Bysiek Final Project: Preparing the Master Budget Background information Noah manages Arcs & Barges, a retail operation that specializes in selling large cruise ships. When Noah closed his books at the end of December, retained earnings was $1,080,000. Sales are 100% on credit. Credit sales are collected as follows: . o o 50% in the month of the sale 10% in each subsequent month Cash on hand and Accounts receivable on December 31st were $250,000 and $750,000, respectively. The accounts receivable include past-due amounts from previous months, of which one-third is expected to be collected each month of the budgeted period. Each ship sells for $50,000. Sales (in ships) projected as follows: . January.10 ships 20 ships 0 ships 30 ships February April... Arcs& Barges maintains an inventory of at least two ships at the end of each month. The ships cost the company $40,000 each. Noah pays for his inventory purchases as follows: 90% during the month purchased and 10% during the next month. . The Company did not have any accounts payable at the end of December . Assume that Noah's incurs the following operating expenses each month: Rent Insurance Property taxes (February only) Licensing (January only) Other fixed expenses Commissions $14,500 2,500 20,000 10,000 5,000 10% of sales revenue All fixed and variable expenses are paid in the month incurred . The Company must maintain a minimum cash balance of $200,000. the event of a rainy day', it is able to draw on a line of credit it has with the bank to cover any shortages. Any borrowings must be repaid in the next month. In . Inventory on hand at December 31t included 2 ships. Noah is hiring you as a consultant to help him prepare the master budget he will need in order to effectively manage his company for the next several months. While he is very knowledgeable about his line of business, his background in accounting and finance is limited. Your task is to draft his master budget for January-March and educate him as to what its components are and how they interrelate from a business standpoint. Required (with, available points in parenthesis) o Sales Budget /Schedule of Cash Collections (4) o Purchasing Budget /Schedule of Cash Disbursements (4) o Selling and administrative expense budget (3) o Cash Budget (3) o Budgeted income statement (3) o Budgeted balance sheet (5) o All budgets completed using an Excel spreadsheet (3): . Formatted professionally (1) Formulas used where applicable (1) Numbers linked between budgets (1)

Answer 1

Please hit LIKE button if this helped. For any further explanation, please put your query in comment, will get back to you.
Sales Budget:
		Jan	Feb	Mar	Total	April
Budgeted Sale in Units		10	20	-	30	30
Budgeted Selling Price		$       50,000	$     50,000	$ 50,000	$     50,000
Budgeted Sale		$     500,000	$1,000,000	$           -	$1,500,000
Schedule of Cash Collection:
		Jan	Feb	Mar	Total	Accounts Receivable
Beginning Accounts Receivable		$     250,000	$   250,000	$250,000	$   750,000
Jan Sale	50% and 10%	$     250,000	$     50,000	$ 50,000	$   350,000	$                   150,000
Feb Sale	50% and 10%		$   500,000	$100,000	$   600,000	$                   400,000
Mar Sale	50% and 10%				$              -
Total cash Collection		$     500,000	$   800,000	$400,000	$1,700,000	$                   550,000
Purchasing Budget:
		Jan	Feb	Mar	Total
Budgeted Sale in units		10	20	-	30
Add: Desired ending inventory		2	2	2	2
Total needs		12	22	2	32
Less: Beginning inventory		-2	-2	-2	-2
Budgeted Purchase		10	20	-	30
Cost per unit		$       40,000	$     40,000	$ 40,000	$     40,000
Budgeted Purchase $		$     400,000	$   800,000	$           -	$1,200,000
Schedule of Cash payment:
		Jan	Feb	Mar	Total	Payable
Jan Pur		$     360,000	$     40,000		$   400,000
Feb Pur			$   720,000	$ 80,000	$   800,000
Mar Pur					$              -
Total Cash Disbursment		$     360,000	$   760,000	$ 80,000	$1,200,000	$                             -
Selling and Admin Expense Budget:
		Jan	Feb	Mar	Total
Rent		$       14,500	$     14,500	$ 14,500	$     43,500
Insurance		$         2,500	$        2,500	$    2,500	$        7,500
Property Tax			$     20,000		$     20,000
Licensing		$       10,000			$     10,000
Other Fixed Expense		$         5,000	$        5,000	$    5,000	$     15,000
Commission	10%	$       50,000	$   100,000	$           -	$   150,000
Budgeted Selling and Admin Expense		$       82,000	$   142,000	$ 22,000	$   246,000
Cash Budget:
		Jan	Feb	Mar	Total
Beginning Balance		$     250,000	$   308,000	$206,000	$   250,000
Add: Collection		$     500,000	$   800,000	$400,000	$1,700,000
Total Cash Available		$     750,000	$1,108,000	$606,000	$1,950,000
Less: Disbursment for:
Material		$     360,000	$   760,000	$ 80,000	$1,200,000
Selling and Admin		$       82,000	$   142,000	$ 22,000	$   246,000
					$              -
Total Disbursment		$     442,000	$   902,000	$102,000	$1,446,000
Ending Cash Balance		$     308,000	$   206,000	$504,000	$   504,000
Budgeted income statement:
Sales Revenue		$ 1,500,000
less: Cost of goods sold	30*$40,000	$-1,200,000
Gross Profit		$     300,000
Less: Selling and Admin Expense		$   -246,000
Net Income		$       54,000
Balance Sheet:
Assets:
Current assets:
Cash			$   504,000
Accounts receivable			$   550,000
Inventory	2*$40,000		$     80,000
Total Assets			$1,134,000
Retained Earning	$1,080,000+$54,000		$1,134,000
Total Liabilities and Equity			$1,134,000