please show work on xl, im having trouble mostly with the market value ratios.

please show what you pluged in on xl

..

AUTOSave FIN 456 module OSS Search File Home Insert Draw Page Layout Formulas Data Review View Help X Arial 10 - A A Currency 23 Wrap Text Merge a Center BIU $ % 9488 Clipboard Font Alignment Number E72 G H 2/1/2012 A B 1 2 3 Module 8. Student Ch08 P 8-15 Build a Model 4 5 6 (Thousands of Dollars) 7 8 Assets 2012 2011 9 Cash and cash equivalents $21,000 $20,000 10 Short-term investments 3,759 3,240 11 Accounts Receivable 52,500 48,000 12 Inventories 84.000 56.000 13 Total current assets $161.259 $127.240 14 Net fixed assets 218.400 $127,240 15 Total assets $379,659 S127240 16 17 Liabilities and equity 18 Accounts payable $33,600 $32,000 19 Accruals 12,600 12.000 20 Notes payable 19929 6.480 21 Total current liabilities S66,129 $50.480 22 Long-term debt 67,662 58 320 23 Total liabilities $133,791 $108,800 24 Common stock 183,793 178,440 25 Retained Earnings 62.075 40.000 26 Total common equity $245,868 $218.440 27 Total liabilities and equity $379,659 $327 240 28 29 Joshua & White Technologies December 31 Income Statements 30 (Thousands of Dollars) 31 2012 2011 32 Sales $420,000 $400,000 33 Expenses excluding depr and amort. 327 600 320,000 34 EBITDA $92,400 $80,000 35 Depreciation and Amortization 19 660 18 000 Build a Model Sheet2 Sheet3 Enter Type here to search AutoSave Ol! FIN 432 module 8 ss e' Search File Home Home Insert Draw Page Layout Formulas Data Review View Help X Arial 10 - A 2. Wrap Text BIO A. M Merge & Center $ % 98 Clipboard Font Alignment Number E72 X x G H B D F 25 Retained Earnings 62.075 40.000 26 Total common equity $245 868 $218.440 27 Total liabilities and equity $379,659 $327 240 28 29 Joshua & White Technologies December 31 Income Statements 30 (Thousands of Dollars) 31 2012 2011 32 Sales $420,000 $400,000 33 Expenses excluding depr. and amort. 327 600 320,000 34 EBITDA $92,400 $80,000 35 Depreciation and Amortization 19,660 18.000 36 EBIT $72,740 $62,000 37 Interest Expense 5740 4.460 38 EBT $67.000 $57,540 39 Taxes (40%) 26.800 23.016 40 Net Income $40.200 $34.524 41 42 Common dividends $18,125 $17.262 43 Addition to retained earnings $22,075 $17.262 44 45 Other Data 2012 2011 46 Year-end Stock Price $90.00 $96.00 47 # of shares (Thousands) 4,052 4,000 48 Lease payment (Thousands of Dollars) $20,000 $20,000 49 Sinking fund payment (Thousands of Dollar SO $0 50 51 Ratio Analysis 2012 2011 Industry Avg 52 Liquidity Ratios 53 Current Ratio 2.44 2.52 2.58 54 Quick Ratio 1.17 1.41 1.53 55 Asset Management Ratios 56 Inventory Turnover 6.00 n/a 7.69 57 Days Sales Outstanding 45.63 43.80 47.45 5B Fixed Assets Turnover 1.92 3.14 2.04 59 Total Assets Turnover 1.11 1.23 Build a Model Sheet2 Sheet3 Enter 3.14 AutoSave OM A FIN 432 module 8 ss Search File Home Home Insert Draw Page Layout Formulas Data Review View Help Arial 29 Wrap Text - 10 - A A 2 A BIU Currency $ % 98 Merge & Center Conditio Formatting Clipboard Font Alignment Number 72 fx F G H B 5 Year-end Stock Price # of shares (Thousands) B Lease payment (Thousands of Dollars) Sinking fund payment (Thousands of Dollar D $90.00 4,052 $20,000 $0 $96.00 4,000 $20,000 SO 2012 2011 Industry Avg 2.44 1.17 2.52 1.41 2.58 1.53 6.00 n/a 45.63 1.92 1.11 43.80 3.14 3.14 7.69 47.45 2.04 1.23 Ratio Analysis Liquidity Ratios 3 Current Ratio Quick Ratio 5 Asset Management Ratios 5 Inventory Turnover 7 Days Sales Outstanding 3 Fixed Assets Turnover Total Assets Turnover Debt Management Ratios 1 Debt Ratio 2 Times-interest-earned ratio 3 EBITDA coverage ratio 4 Profitability Ratios 5 Profit Margin 3 Basic Earning Power 7 Return on Assets B Return on Equity Market Value Ratios Earnings per share 1 Price-to-earnings ratio 2 Cash flow per share 3 Price-to-cash flow ratio 4 Book Value per share 5 Market-to-book ratio 6 7 a. Has Joshua & White's liquidity position in 35.2% 16.10 4.37 85.5% 17.94 4.09 32.1% 15.33 4.18 9.57% 19.16% 10.59% 16.35% 8.63% 48.73% 27.13% 15.80% 8.86% 19.48% 10.93% 16.10% $9.92 9.07 $8.63 11.12 NA 10.65 NA 7.11 NA 1.72 Autosave File Home Insert Draw Page Layout Formulas Data Review View Help Arial 10 ' ' 9 29 Wrap Text A Es Merge & Center B IU $ % 380 Clipboard Font Alignment Number 272 X R. G H D 10.59% 16.35% E 27.13% 15.80% F 10.93% 16.10% B 7 Return on Assets 8 Return on Equity -9 Market Value Ratios 0 Earnings per share 1 Price-to-earnings ratio *2 Cash flow per share 3 Price-to-cash flow ratio 14 Book Value per share 75 Market-to-book ratio 26 $9.92 9.07 $8.63 11.12 o NA 10.65 NA 7.11 NA 1.72 7 a. Has Joshua & White's liquidity position improved or worsened? Explain. 78 79 30 31 B2 b. Has Joshua & White's ability to manage its assets improved or worsened? Explain. 83 84 85 86 87 c. How has Joshua & White's profitability changed during the last year? 88 89 90 d. Perform an extended Du Pont analysis for Joshua & White for 2008 and 2009. 91 ROE = PM x L Turker X Equity Multiplier 92 2012 16.35% 93 2011 40.65% 94 95 96 97 98 99 e. Perform a common size analysis. What has happened to the composition 100 (that is, percentage in each category) of assets and liabilities? 101 Build a Model Sheet2 Sheets Type here to search o