Question

Balance Sheets Begin Cash AR Income Statement Sales (COGS) Gross Margin SG&A EBIT Interest EBT Taxes Net Income $ 71,400 $ (41,000) $ 30,400 $ 6,400 $ 24,000 $ 690 $ 23,310 $ 5,900 $ 17,410 IS spans Inventory Current Assets period between PPE Begin BS (Acc Dep) & End BS Net PPE $ 13,800 AP $ 4,200 Loans $ 16,800 Total Liabilities $ 34,800 Stock $ 34,900 RE $ (5,000) $ 29,900 Total Equity $ 10,000 Total Assets Dividends SCF: $ 64,700 Total L+E % d Assets End: Cash AR Inventory Current Assets $ 35,110 AP $ 5,400 Loans $ 6,700 Total Liabilities $ 47,210 Stock $ 34,900 RE $ (10,000) $ 24,900 Total Equity PPE (Acc Dep) Net PPE Per BS: Change in cash Total Assets $ 72,110 Total L+E 1

Please complete a financial statement analysis for the Paula assignment (see attached). The input columns are hidden and there is some additional information in yellow. Please common size and perform some simple trend analysis by calculating the percent change across the balance sheet (% d columns). Then create a Statement of Cash Flows in the space provided and calculate the listed ratios. Submit the Excel workbook here with clear formatting and using Excel formulas and references

Answer 1

1)	Calculation of percent changes across the balance sheet
	Percent changes = {(ending balance - beginning balance )/beginning balance}*100

Balance Sheet
Assets	End	Beginning	Increase/decrease	%d
Cash	35110	13800	21310	154.4203
AR	5400	4200	1200	28.57143
Inventory	6700	16800	-10100	-60.119
current assets	47210	34800	12410	35.66092
PPE	34900	34900	0	0
(accumulated dep)	-10000	-5000	-5000	100
Net PPE	24900	29900	-5000	-16.7224
Total Assets	72110	64700	7410	11.45286
Liabilites
AP	5400	5400	0	0
Loans	6900	6900	0	0
Total Liabilities	12300	12300	0	0
Stock	39000	39000	0	0
RE	20810	13400	7410	55.29851
Total equity	59810	52400	7410	14.14122
L+E	72110	64700	7410	11.45286

2)	Statement of Cash flow
	Particular	Amount
	Cash flow from Operating Activity
	Net Income	17410
	less : increase in AR	-1200
	Add : decrease In inventory	10100
	ADD: depreciation	5000
A)	Net Cash flow from operating activity	31310
B)	Cash flow from investing activity	-
	Cash flow from financing Activity
	Dividends	-10000
C)	Net cash flow from financing activity	-10000
	Net changes (A+B+C)	21310
	Beginning cash balance	13800
	Ending Cash balance	35110

Calculation Of Ratios

A) EPS = (net income - dividends ) / Number of shares outstanding

= (17410-10000) / 10000

= 0.741

B) PE ratio = share price / EPS

= 20 / 0.741

= 26.99

C)Div Pay = Dividends / Net income

= 10000 / 17410

= 0.57

D)Div yield = Annual dividend per share / price per share

= 1 / 20

= 0.05

E) BV per share = shareholders equity / number of shares

= 59810 / 10000

= 5.981

F) ROA = Net Income / average total assets

average total assets = (72110 + 64700 ) /2 = 68405

= 17410 / 68405

= 0.25

G) ROE = Net income / shareholders equity

= 17410/ 59810

=0.29

H) TIE ratio = earning before interest & taxes / interest expenses

= 24000/690

=34.78

I ) D/E Ratio = Total liabilities / total equity

= 12300 / 59810

= 0.2056

J) D/A ratio = Total liabilities / Total assets

= 12300 / 72110

= 0.17

K) Working capital = Current assets - current liabilities

= 47210 - 5400 = 41810

L )Current Ratio = Current assets / current liabilities

= 47210/5400

=8.74

M) Quick ratio = ( current assets - inventory ) / current liabilities

(47210 - 6700 ) / 5400

= 7.5

N) AR T/O = Net sales / average AR

Average AR = (4200 + 5400 ) / 2 = 4800

AR T/O = 71400 / 4800

= 14.875

O ) ACP = 365 / AR T/O

= 365 / 14.875

= 24.54

P) INV T/O = COGS / average inventory

average inventory = (16800+6700)/2 = 11750

= 41000 / 11750

= 3.489

Q) Profit margin = Net Income / Net sales

= 17410 / 71400

= 0.24

R) Asset T/O = Net sales / average total assets

Average total assets =( 64700 + 72110 ) / 2 = 68405

= 71400/68405

= 1.043