Question

1. Using Microsoft Excel, create a balance sheet and an income statement using the relevant information below
2. Calculate the Net Working Capital and the Net Operating Working Capital for both years
3. Perform vertical analysis on both statements, for both years
4. Perform Horizontal analysis on both statements for the most current year
5. What was the Net Operating Profit After Taxes for both years?

2016 Accounts Receivable 400 Sales 5000 Accounts Payable 650 Inventory 1400 COGS 3000 Common Stock 1500 Cash 1600 Oper. Expenses before depreciation 900 Accruals 150 Depreciation Expense 250 Gross Fixed Assets 3200 Retained Earnings 3505 Interest Expense 100 Accumulated Depreciation 400 Notes Payable (short term) 395

2015 Accounts Receivable 700 Sales 3800 Accounts Payable 400 Inventory 1000 COGS 2100 Common Stock 1500 Cash 1300 Oper. Expenses before depreciation 600 Accruals 50 Depreciation Expense 250 Gross Fixed Assets 3200 Retained Earnings 3050 Interest Expense 200 Accumulated Depreciation 150 Notes Payable (short term) 1050

Additional Information:

• No Dividends were paid either year
• The tax rate for each year was 30%

Answer 1

1)
Balancesheet
Assets	Amount		Liabilities	Amount
	2016	2015		2016	2015
Accounts Receivable	400	700	Accounts Payble	650	400
Inventory	1400	1000	Accruals	150	50
cash	1600	1300	Notes Payble	395	1050
Total Current Assets	3400	3000	Current Liabilities	3211	3515
Gross Fixed Assets	3200	3200	Common Stock	1500	1500
Accumulated Depreciation	-400	-150	Retained Earnings	3505	3050
Total Non Current Assets	2800	3050	Share Holders Equity	5005	4550
	6200	6050		6200	6050

2)		2016	2015
Net Working Capital	Current asserts-Current Liabilities	=3400-3211	=3000-3515
		189	-515
Net Operating Working Capital	Non Current asserts-Non Current Liabilities	=2800-5005	=3050-4550
		-2205	-1500

3)	Vertical Analysis Baance sheet
Assets	Amount		Liabilities	Amount
	2016	2015		2016	2015
Accounts Receivable	6%	12%	Accounts Payble	10%	7%
Inventory	23%	17%	Accruals	2%	1%
cash	26%	21%	Notes Payble	6%	17%
Total Current Assets	55%	50%	Current Liabilities	19%	25%
Gross Fixed Assets	52%	53%	Common Stock	24%	25%
Accumulated Depreciation	-6%	-2%	Retained Earnings	57%	50%
Total Non Current Assets	45%	50%	Share Holders Equity	81%	75%
	100%	100%		100%	100%
	Vertical Analysis Income Statement
Income Statement
Pariculars	Amount	Amount
		2016	2015
Sales		100%	100%
COGS		60%	55%
Gross Profit		40%	45%
Operating Expences		18%	16%
Depreciation Expence		5%	7%
Income From Operations		17%	22%
Non operating Expence
Interest Expence		2%	5%
Pre Tax Income		15%	17%
Income Tax @ 30%		5%	5%
Net Income		11%	12%

4)	Horizontal Analysis of Balancesheet
Balancesheet
Assets	Amount		Horizontal Varience		Liabilities	Amount		Horizontal Varience
	2016	2015	Amount	Percentage		2016	2015	Amount	Percentage
Accounts Receivable	400	700	-300	-43%	Accounts Payble	650	400	250	63%
Inventory	1400	1000	400	40%	Accruals	150	50	100	200%
cash	1600	1300	300	23%	Notes Payble	395	1050	-655	-62%
Total Current Assets	3400	3000	400	13%	Current Liabilities	3211	3515	-304	-9%
Gross Fixed Assets	3200	3200	0	0%	Common Stock	1500	1500	0	0%
Accumulated Depreciation	-400	-150	-250	167%	Retained Earnings	3505	3050	455	15%
Total Non Current Assets	2800	3050	-250	-8%	Share Holders Equity	5005	4550	455	10%
	6200	6050	150	2%		6200	6050	150	2%
	Horizontal Analysis of Income Statement
Pariculars	Amount	Amount		Horizontal Varience
		2016	2015	Amount	Percentage
Sales		5000	3800	1200	32%
COGS		3000	2100	900	43%
Gross Profit		2000	1700	300	18%
Operating Expences		900	600	300	50%
Depreciation Expence		250	250	0	0%
Income From Operations		850	850	0	0%
Non operating Expence
Interest Expence		100	200	-100	-50%
Pre Tax Income		750	650	100	15%
Income Tax @ 30%		225	195	30	15%
Net Income		525	455	70	15%

5)

Particulars		2016	2015
Income From Operations		850	850
Income Tax @ 30%		255	255
Net Operating income After Taxes		595	595