| Spring 1, 2013 |
| | | | | | | | | | | | | | | | | | | | 7/22/12 |
| Chapter 3. Ch 03 P15 Build a Model |
| Except for charts and answers that must be written, only Excel formulas that use cell references or functions will be accepted for credit. |
| Numeric answers in cells will not be accepted. |
| Joshua & White Technologies: December 31 Balance Sheets |
| (Thousands of Dollars) |
| Assets | | | 2010 | 2009 |
| Cash and cash equivalents | | | $21,000 | $20,000 |
| Short-term investments | | | 3,759 | 3,240 |
| Accounts Receivable | | | 52,500 | 48,000 |
| Inventories | | | 84,000 | 56,000 |
| Total current assets | | | $161,259 | $127,240 |
| Net fixed assets | | | 218,400 | 200,000 |
| Total assets | | | $379,659 | $327,240 |
| Liabilities and equity |
| Accounts payable | | | $33,600 | $32,000 |
| Accruals | | | 12,600 | 12,000 |
| Notes payable | | | 19,929 | 6,480 |
| Total current liabilities | | | $66,129 | $50,480 |
| Long-term debt | | | 67,662 | 58,320 |
| Total liabilities | | | $133,791 | $108,800 |
| Common stock | | | 183,793 | 178,440 |
| Retained Earnings | | | 62,075 | 40,000 |
| Total common equity | | | $245,868 | $218,440 |
| Total liabilities and equity | | | $379,659 | $327,240 |
| Joshua & White Technologies December 31 Income Statements |
| (Thousands of Dollars) |
| | | | 2010 | 2009 |
| Sales | | | $420,000 | $400,000 |
| Expenses excluding depr. and amort. | | | 327,600 | 320,000 |
| EBITDA | | | $92,400 | $80,000 |
| Depreciation and Amortization | | | 19,660 | 18,000 |
| EBIT | | | $72,740 | $62,000 |
| Interest Expense | | | 5,740 | 4,460 |
| EBT | | | $67,000 | $57,540 |
| Taxes (40%) | | | 26,800 | 23,016 |
Net Income
Michael C. Ehrhardt: Due to rounding, the numbers calculated in the Chapter 2 problem may differ slightly from these. | | | $40,200 | $34,524 |
| Common dividends | | | $18,125 | $17,262 |
| Addition to retained earnings | | | $22,075 | $17,262 |
| Other Data | | | 2010 | 2009 |
| Year-end Stock Price | | | $100.00 | $96.00 |
| # of shares (Thousands) | | | 4,052 | 4,000 |
| Lease payment (Thousands of Dollars) | | | $20,000 | $20,000 |
| Sinking fund payment (Thousands of Dollars) | | | $0 | $0 |
| Ratio Analysis | | | 2010 | 2009 | Industry Avg |
| Liquidity Ratios |
| Current Ratio | | | | | 2.58 |
| Quick Ratio | | | | | 1.53 |
| Asset Management Ratios |
| Inventory Turnover | | | | | 7.69 |
| Days Sales Outstanding | | | | | 47.45 |
| Fixed Assets Turnover | | | | | 2.04 |
| Total Assets Turnover | | | | | 1.23 |
| Debt Management Ratios |
| Debt Ratio | | | | | 32.1% |
| Times-interest-earned ratio | | | | | 15.33 |
| EBITDA coverage ratio | | | | | 4.18 |
| Profitability Ratios |
| Profit Margin | | | | | 8.86% |
| Basic Earning Power | | | | | 19.48% |
| Return on Assets | | | | | 10.93% |
| Return on Equity | | | | | 16.10% |
| Market Value Ratios |
| Earnings per share | | | | | NA |
| Price-to-earnings ratio | | | | | 10.65 |
| Cash flow per share | | | | | NA |
| Price-to-cash flow ratio | | | | | 7.11 |
| Book Value per share | | | | | NA |
| Market-to-book ratio | | | | | 1.72 |
| a. Has Joshua & White's liquidity position improved or worsened? Explain. |
| b. Has Joshua & White's ability to manage its assets improved or worsened? Explain. |
| c. How has Joshua & White's profitability changed during the last year? |
| d. Perform an extended Du Pont analysis for Joshua & White for 2008 and 2009. What did you find? |
| | ROE = | PM x | TA Turnover x Equity Multiplier |
| 2010 |
| 2009 |
| e. Perform a common size analysis. What has happened to the composition |
| (that is, percentage in each category) of assets and liabilities? |
| Common Size Balance Sheets |
| Assets | | | 2010 | 2009 |
| Cash and cash equivalents |
| Short-term investments |
| Accounts Receivable |
| Inventories |
| Total current assets |
| Net fixed assets |
| Total assets |
| Liabilities and equity | | | 2010 | 2009 |
| Accounts payable |
| Accruals |
| Notes payable |
| Total current liabilities |
| Long-term debt |
| Total liabilities |
| Common stock |
| Retained Earnings |
| Total common equity |
| Total liabilities and equity |
| Common Size Income Statements | | | 2010 | 2009 |
| Sales |
| Expenses excluding depr. and amort. |
| EBITDA |
| Depreciation and Amortization |
| EBIT |
| Interest Expense |
| EBT |
| Taxes (40%) |
| Net Income |
| f. Perform a percent change analysis. What does this tell you about the change in profitability |
| and asset utilization? |
| Percent Change Balance Sheets | | | | Base |
| Assets | | | 2010 | 2009 |
| Cash and cash equivalents |
| Short-term investments |
| Accounts Receivable |
| Inventories |
| Total current assets |
| Net fixed assets |
| Total assets |
| | | | | Base |
| Liabilities and equity | | | 2010 | 2009 |
| Accounts payable |
| Accruals |
| Notes payable |
| Total current liabilities |
| Long-term debt |
| Total liabilities |
| Common stock |
| Retained Earnings |
| Total common equity |
| Total liabilities and equity |
| | | | | Base |
| Percent Change Income Statements | | | 2010 | 2009 |
| Sales |
| Expenses excluding depr. and amort. |
| EBITDA |
| Depreciation and Amortization |
| EBIT |
| Interest Expense |
| EBT |
| Taxes (40%) |
| Net Income |
jandreg
ch03_p15_build_a_model12.xlsx
