Your boss has come to you for advice on the current finances of the company and needs you to create an extended DuPont analysis, common size analysis, and percentage change analysis. Your boss wants you to fill out the attached Excel file because he/she has no idea what she is talking about and is going to use your report as her work.

Questions are in excel (photos attached) but are as follows:

-Has J&W's liquidity position improved or worsened? Explain

-Has J&W's ability to manage its assets improved or worsened? Explain

-How has J&W's profitability changed during the last year?

-Perform an extended DuPont analysis for J7W for 2015 and 2016. What do these results tell you? -Perform a common size analysis. What has happened to the composition (That is, the percentage in each category) of assets and liabilities?

-Perform a percentage change analysis. What does this tell you about the change in profitability and asset utilization?

Fill in all yellow Squares with the appropriate answer.

Transcribed Image Text:

A D E 2 Joshua & White Technologies: December 31 Balance Sheets 3 (Thousands of Dollars) 4 5 Assets 6 Cash and cash equivalents 7 Short-term investments 8 Accounts Receivable 9 Inventories B 10 Total current assets 11 Net fixed assets 12 Total assets 13 14 Liabilities and equity 15 Accounts payable 16 Accruals 17 Notes payable 18 Total current liabilities 19 Long-term debt 20 Total liabilities 21 Common stock 22 Retained Earnings 23 Total common equity 24 Total liabilities and equity 33 EBIT 34 Interest Expense 35 EBT 36 Taxes (40%) 37 Net Income. 38 39 Common dividends 40 Addition to retained earnings 41 42 Other Data 43 Year-end Stock Price 2016 2015 $21,000 $20,000 3,759 3,240 52,500 48,000 84.000 56.000 $161,259 $127,240 218.400 200.000 $379,659 $327.240 44 # of shares (Thousands) 45 Lease payment (Thousands of Dollars) 46 Sinking fund payment (Thousands of Do $33,600 12,600 19.929 $66,129 67.662 $133,791 183,793 62,075 $245.868 $379,659 25 26 Joshua & White Technologies December 31 Income Statements 27 (Thousands of Dollars) 28 29 Sales 30 COGS except excluding depr. and amort. 31 Depreciation and Amortization 32 Other operating expenses $18,125 $22,075 $32,000 12,000 6.480 $50,480 58.320 $108,800 178,440 40,000 2016 2015 $420,000 $400,000 300,000 298,000 19,660 18,000 27,600 22,000 $72,740 $62,000 5,740 4,460 $67,000 $57,540 26,800 $40,200 2016 $90.00 4,052 $218.440 $327.240 $20,000 $5,000 23,016 $34,524 $17,262 $17,262 2015 $96.00 4,000 $20,000 $5,000 F G 47 48 Ratio Analysis 49 Liquidity Ratios Current Ratio A 50 51 Quick Ratio 52 Asset Management Ratios 53 Inventory Turnover (Total COGS/Inventories) 54 55 59 60 56 Total Assets Turnover 57 Debt Management Ratios 58 Debt Ratio (Total debt-to-assets) B 63 64 65 Days Sales Outstanding Fixed Assets Turnover 61 EBITDA coverage ratio 62 Profitability Ratios Liabilities-to-assets ratio Times-interest-earned ratio Profit Margin Basic Earning Power Return on Assets 66 Return on Equity 67 Market Value Ratios 68 Earnings per share 69 Price-to-earnings ratio 70 Cash flow per share 71 Price-to-cash flow ratio 72 Book Value per share 73 Market-to-book ratio 90 91 92 93 94 95 96 2016 2016 2015 2015 Industry Avg 2.58 1.53 7.69 47.45 2.04 1.23 20.0% 32.1% 15.33 4.18 8.86% 19.48% 10.93% 16.10% ΝΑ 10.65 74 75 a. Has Joshua & White's liquidity position improved or worsened? Explain. 76 77 78 79 80 b. Has Joshua & White's ability to manage its assets improved or worsened? Explain. 81 82 83 ΝΑ 7.11 ΝΑ 1.72 84 85 C. How has Joshua & White's profitability changed during the last year? 86 87 88 d. Perform an extended Du Pont analysis for Joshua & White for 2008 and 2009. 89 ROE = PM X TA Turnover x Equity Multiplier G 94 95 A 109 110 Liabilities and equity 111 Accounts payable 112 Accruals 113 Notes payable 114 Total current liabilities 115 Long-term debt 116 Total liabilities 117 Common stock B 96 97 e. Perform a common size analysis. What has happened to the composition (that is, percentage in each category) of assets and liabilities? 98 99 100 Common Size Balance Sheets 101 Assets 102 Cash and cash equivalents 103 Short-term investments 104 Accounts Receivable 105 Inventories 106 Total current assets. 107 Net fixed assets 108 Total assets 118 Retained Earnings 119 Total common equity 120 Total liabilities and equity 127 EBIT 128 Interest Expense 129 EBT 121 122 Common Size Income Statements 123 Sales 124 COGS except excluding depr. and amort. 125 Depreciation and Amortization 126 Other operating expenses 130 Taxes (40%) 131 Net Income 132 133 134 135 126 C D 2016 2016 E 2016 2015 2015 F 2015 G A 156 Long-term debt 157 Total liabilities B 138 139 140 Percent Change Balance Sheets 141 Assets 142 Cash and cash equivalents 143 Short-term investments 144 Accounts Receivable 145 Inventories 146 Total current assets 147 Net fixed assets 148 Total assets 149 150 151 Liabilities and equity 152 Accounts payable 153 Accruals 154 Notes payable 155 Total current liabilities C 170 Interest Expense 171 EBT 158 Common stock 159 Retained Earnings 160 Total common equity 161 Total liabilities and equity 162 163 164 Percent Change Income Statements 165 Sales 166 COGS except excluding depr. and amort. 167 Depreciation and Amortization 168 Other operating expenses 169 EBIT 172 Taxes (40%) 173 Net Income 174 175 176 177 178 179 135 136 137 f. Perform a percent change analysis. What does this tell you about the change in profitability and asset utilization? D 2016 2016 E 2016 Base 2015 Base 2015 F Base 2015 G A D E 2 Joshua & White Technologies: December 31 Balance Sheets 3 (Thousands of Dollars) 4 5 Assets 6 Cash and cash equivalents 7 Short-term investments 8 Accounts Receivable 9 Inventories B 10 Total current assets 11 Net fixed assets 12 Total assets 13 14 Liabilities and equity 15 Accounts payable 16 Accruals 17 Notes payable 18 Total current liabilities 19 Long-term debt 20 Total liabilities 21 Common stock 22 Retained Earnings 23 Total common equity 24 Total liabilities and equity 33 EBIT 34 Interest Expense 35 EBT 36 Taxes (40%) 37 Net Income. 38 39 Common dividends 40 Addition to retained earnings 41 42 Other Data 43 Year-end Stock Price 2016 2015 $21,000 $20,000 3,759 3,240 52,500 48,000 84.000 56.000 $161,259 $127,240 218.400 200.000 $379,659 $327.240 44 # of shares (Thousands) 45 Lease payment (Thousands of Dollars) 46 Sinking fund payment (Thousands of Do $33,600 12,600 19.929 $66,129 67.662 $133,791 183,793 62,075 $245.868 $379,659 25 26 Joshua & White Technologies December 31 Income Statements 27 (Thousands of Dollars) 28 29 Sales 30 COGS except excluding depr. and amort. 31 Depreciation and Amortization 32 Other operating expenses $18,125 $22,075 $32,000 12,000 6.480 $50,480 58.320 $108,800 178,440 40,000 2016 2015 $420,000 $400,000 300,000 298,000 19,660 18,000 27,600 22,000 $72,740 $62,000 5,740 4,460 $67,000 $57,540 26,800 $40,200 2016 $90.00 4,052 $218.440 $327.240 $20,000 $5,000 23,016 $34,524 $17,262 $17,262 2015 $96.00 4,000 $20,000 $5,000 F G 47 48 Ratio Analysis 49 Liquidity Ratios Current Ratio A 50 51 Quick Ratio 52 Asset Management Ratios 53 Inventory Turnover (Total COGS/Inventories) 54 55 59 60 56 Total Assets Turnover 57 Debt Management Ratios 58 Debt Ratio (Total debt-to-assets) B 63 64 65 Days Sales Outstanding Fixed Assets Turnover 61 EBITDA coverage ratio 62 Profitability Ratios Liabilities-to-assets ratio Times-interest-earned ratio Profit Margin Basic Earning Power Return on Assets 66 Return on Equity 67 Market Value Ratios 68 Earnings per share 69 Price-to-earnings ratio 70 Cash flow per share 71 Price-to-cash flow ratio 72 Book Value per share 73 Market-to-book ratio 90 91 92 93 94 95 96 2016 2016 2015 2015 Industry Avg 2.58 1.53 7.69 47.45 2.04 1.23 20.0% 32.1% 15.33 4.18 8.86% 19.48% 10.93% 16.10% ΝΑ 10.65 74 75 a. Has Joshua & White's liquidity position improved or worsened? Explain. 76 77 78 79 80 b. Has Joshua & White's ability to manage its assets improved or worsened? Explain. 81 82 83 ΝΑ 7.11 ΝΑ 1.72 84 85 C. How has Joshua & White's profitability changed during the last year? 86 87 88 d. Perform an extended Du Pont analysis for Joshua & White for 2008 and 2009. 89 ROE = PM X TA Turnover x Equity Multiplier G 94 95 A 109 110 Liabilities and equity 111 Accounts payable 112 Accruals 113 Notes payable 114 Total current liabilities 115 Long-term debt 116 Total liabilities 117 Common stock B 96 97 e. Perform a common size analysis. What has happened to the composition (that is, percentage in each category) of assets and liabilities? 98 99 100 Common Size Balance Sheets 101 Assets 102 Cash and cash equivalents 103 Short-term investments 104 Accounts Receivable 105 Inventories 106 Total current assets. 107 Net fixed assets 108 Total assets 118 Retained Earnings 119 Total common equity 120 Total liabilities and equity 127 EBIT 128 Interest Expense 129 EBT 121 122 Common Size Income Statements 123 Sales 124 COGS except excluding depr. and amort. 125 Depreciation and Amortization 126 Other operating expenses 130 Taxes (40%) 131 Net Income 132 133 134 135 126 C D 2016 2016 E 2016 2015 2015 F 2015 G A 156 Long-term debt 157 Total liabilities B 138 139 140 Percent Change Balance Sheets 141 Assets 142 Cash and cash equivalents 143 Short-term investments 144 Accounts Receivable 145 Inventories 146 Total current assets 147 Net fixed assets 148 Total assets 149 150 151 Liabilities and equity 152 Accounts payable 153 Accruals 154 Notes payable 155 Total current liabilities C 170 Interest Expense 171 EBT 158 Common stock 159 Retained Earnings 160 Total common equity 161 Total liabilities and equity 162 163 164 Percent Change Income Statements 165 Sales 166 COGS except excluding depr. and amort. 167 Depreciation and Amortization 168 Other operating expenses 169 EBIT 172 Taxes (40%) 173 Net Income 174 175 176 177 178 179 135 136 137 f. Perform a percent change analysis. What does this tell you about the change in profitability and asset utilization? D 2016 2016 E 2016 Base 2015 Base 2015 F Base 2015 G

Answer rating: 100% (QA)

Question 1 Liquidity Position We evaluate based on the current ratio of the business The current rat... View the full answer