Accounting Project

budget_project_with_check_figures.xlsx

Given Assumptions

Actual and Budgeted Sales in Units
Actual			Budgeted
January	February	March	April	May	June	July	August	September
20,000	26,000	40,000	65,000	100,000	50,000	30,000	28,000	25,000
$10	Selling Price Per Unit
20%	Collected in the month of sale
70%	Collected in the following month
10%	Collected in the 2nd following month
$4	Merchandise Cost Per Unit
40%	desired ending inventory (% of following month's sales)
50%	suppliers paid in the month of purchase
50%	suppliers paid in the following month
Monthly Operating Expenses
Variable
4%	Sales Commission as % of Sales
Fixed
$200,000	Advertising
$18,000	Rent
$106,000	Salaries
$7,000	Utilities
$3,000	Insurance
$14,000	Depreciation
Account Balances as of March 31
Assets
$74,000	Cash
$346,000	Accounts Receivable
$104,000	Inventory
$21,000	Prepaid Insurance
$950,000	Property and Equipment
$1,495,000	Total Assets
Liabilities and Stockholders' Equity
$100,000	Accounts Payable
$15,000	Dividends Payable
$800,000	Common Stock
$580,000	Retained Earnings
$1,495,000	Total Liabilities and Stockholders' Equity
Cash Purchases
Equipment
$16,000	May
$40,000	June
Cash Management Policies
$50,000	Minimum Cash Balance
1%	Interest Per Month

Sales Budget
	April	May	June	Quarter
Budgeted unit sales	?	?	?	?
Selling price per unit	?	?	?	?
Total sales	?	?	?	$2,150,000
Schedule of Expected Cash Collections
	April	May	June	Quarter
February sales	?	?	?	?
March sales	?	?	?	?
April sales	?	?	?	?
May sales	?	?	?	?
June sales	?	?	?	?
Total cash collections	?	?	?	$1,996,000
Merchandise Perchases Budget
	April	May	June	Quarter
Budgeted unit sales	?	?	?	?
Add desired ending merchandise inventory	?	?	?	?
Total needs	?	?	?	?
Less beginning merchandise inventory	?	?	?	?
Required purchases	?	?	?	?
Merchandise cost per unit	?	?	?	?
Cost of purchases	?	?	?	$ 804,000
Budgeted Cash disbursements for Merchandise Purchases
	April	May	June	Quarter
Accounts payable	?	?	?	?
April purchases	?	?	?	?
May purchases	?	?	?	?
June purchases	?	?	?	?
Total cash payments	?	?	?	$820,000

Cash Budget for the Three Months Ending June 30
	April	May	June	Quarter
Beginning cash balance	?	?	?	?
Add collections from customers	?	?	?	?
Total cash available	?	?	?	?
Less cash disbursements:
Merchandise purchases	?	?	?	?
Advertising	?	?	?	?
Rent	?	?	?	?
Salaries	?	?	?	?
Commissions	?	?	?	?
Utilities	?	?	?	?
Equipment purchases	?	?	?	?
Dividends paid	?	?	?	?
Total cash disbursements	?	?	?	?
Excess (deficiency) of cash available over disbursements	?	?	?	?
Financing:
Borrowings	?	?	?	$180,000
Repayments	?	?	?	?
Interest	?	?	?	?
Total financing	?	?	?	?
Ending cash balance	?	?	?	$94,700

Budgeted Income Statement for the Three Months Ended June 30
Sales		?
Variable expenses:
Cost of goods sold	?
Commissions	?	?
Contribution margin		$1,204,000
Fixed expenses:
Advertising	?
Rent	?
Salaries	?
Utilities	?
Insurance	?
Depreciation	?	$1,044,000
Net operating income		?
Interest expense		?
Net income		$154,700

	Budgeted Balance Sheet June 30
	Assets
	Cash	?
	Accounts receivable	?
	Inventory	?
	Prepaid insurance	?
	Property and equipment, net	?
	Total assets	$1,618,700
	Liabilities and Stockholders’ Equity
	Accounts payable, purchases	?
	Dividends payable	?
	Common stock	?
	Retained earnings	$719,700
	Total liabilities and stockholders’ equity	?