Financial Analysis and Planning using attached Excel Sheet Template

fn2640_week3_lab2.xlsx

Week3_Lab2

Question:
Use a spread sheet to construct a common-size balance sheet from the following balance sheet and the income statement.
Balance Sheet
Assets		Liabilities
Cash	$10,000	Accounts payable	$12,000
Accounts receivable	100,000	Notes payable	50,000
Inventory	110,000	Total current liabilities	$62,000
Total current assets	220,000	Long-term debt	128,000
Gross plant and equipment	500,000	Total liabilities	190,000
Less: accumulated depreciation	125,000	Common stock ($1 par, 100,000 shares)	100,000
Net plant and equipment	375,000	Paid-in-capital	155,000
Total assets	595,000	Retained earnings	150,000
		Total stockholders' equity	$405,000
		Total liabilities and equity	$595,000
Income Statement
Sales	$2,175,000
Cost of goods sold	$575,000
Gross profit	$1,600,000
General and administrative expense	$200,000
Selling and marketing expense	$250,000
Depreciation	$50,000
Operating income	$1,100,000
Interest	$100,000
Income before taxes	$1,000,000
Income taxes (30%)	$300,000
Net income	$700,000
Answers:
Common-size Balance Sheet
Assets		Liabilities
Cash		Accounts payable
Accounts receivable		Notes payable
Inventory		Total current liabilities
Total current assets		Long-term debt
Gross P&E		Total Liabilities
Less accumulated		Common stock
Depreciation		Paid-in capital
Net P&E		Retained earnings
Total assets		Total stockholders’ equity
		Total liabilities and equity
Common-size Income Statement
Sales
COGS
Gross profit
G&A expense
Sales & Marketing expense
Depreciation
Operating income
Interest
Income before taxes
Income taxes (30%)
Net income