| Complete this spreadsheet by calculating all the gray boxes. |
| This is a giant retailer that pays no dividends and has no preferred stock. |
| Calculate all ratios and figures in the boxes provided C22:E33 and state whether the ratios are getting better or worse or about the same. |
| INCOME STATEMENTS | 2014 | 2015 | 2016 | | Factor Cost of Sales | | | | CAGR | Industry | Factors | 2017E | Justification |
| | | | | | 2014 | 2015 | 2016 | Avg Rates | 2014-16 | 2013 |
| Sales | $3,369.50 | $3,493.25 | $3,665.05 | | | | | | | 3.50% | | | Assume Growth is 5.00% |
| Costs except depreciation | $3,002.70 | $3,120.60 | $3,289.40 | | | | | | | 90.00% | | | Use average over three years |
| Depreciation | $104.20 | $106.55 | $107.10 | | | | | | | 5.00% | | | Use industry rates and sales / not equip |
| Total operating costs | $3,106.90 | $3,227.15 | $3,396.50 | | | | | | | 95.00% |
| EBIT | $262.60 | $266.10 | $268.55 | | | | | | | 5.00% |
| Less interest (INT) | $37.85 | $43.30 | $38.10 | | | | | | | 1.00% | | | Find using targets and information below |
| Earnings before taxes (EBT) | $224.75 | $222.80 | $230.45 | | | Factor EBT | | | | 4.00% |
| Taxes | $78.75 | $76.35 | $80.50 | | 35.04% | 34.27% | 34.93% | | | 35.00% | | | Use industry rates |
| Net income for common (NI) | $146.00 | $146.45 | $149.95 |
| Shares of common stock | 100 | 100 | 100 | | | | | | | | | 100.0 |
| Earnings per share (EPS) | $1.46 | $1.46 | $1.50 |
| | | 2015 | 2016 | 2017E | Comparisons - Better/Worse/Same |
| Net operating profit after taxes |
| Net operating working capital |
| Total operating capital |
| FCF = NOPAT – Δ op. capital |
| Return on invested capital |
| EPS |
| Return on equity (ROE) |
| Return on assets (ROA) |
| Inventory turnover |
| Days sales outstanding |
| Total liabilities / TA |
| Times interest earned |
| Shares outstanding | | 100.0 | 100.0 | 100.0 |
| Additional Funds Needed |
| BALANCE SHEETS | 2014 | 2015 | 2016 | | 2014 | 2015 | 2016 | Avg Rates | 2014-16 | Industry | Factors | 2017E |
| Assets | | | | | | Factor Sales |
| Cash | $85.60 | $39.70 | $39.20 | | | | | | | 2.00% | | | Use 2.50% |
| Accounts receivable | $307.65 | $296.35 | $292.05 | | | | | | | 10.00% | | | Use average over three years |
| Inventories | $379.80 | $395.90 | $395.15 | | | | | | | 12.00% | | | Use average over three years |
| Other Current Assets | $87.60 | $90.50 | $93.00 | | | | | | | 2.00% | | | Use average over three years |
| Total current assets | $860.65 | $822.45 | $819.40 | | | | | | | | | $ - 0 |
| Net plant and equip. | $1,324.60 | $1,509.05 | $1,588.75 | | | | | | | 42.50% | | | Use industry rate |
| Total assets | $2,185.25 | $2,331.50 | $2,408.15 |
| Liabilities and equity |
| Accounts payable | $497.55 | $525.05 | $551.85 | | | | | | | 12.50% | | | Use industry rate |
| Accruals | $80.35 | $81.70 | $80.45 | | | | | | | 3.00% | | | Use average over three years |
| Notes payable | $5.95 | $189.30 | $149.70 | | | | | | | | | | Find |
| Total current liab. | $583.85 | $796.05 | $782.00 |
| Long-term bonds | $827.05 | $744.40 | $798.25 | | | | | | | | | | Find |
| Total liabilities | $1,410.90 | $1,540.45 | $1,580.25 |
| Common stock | $139.45 | $143.10 | $170.15 |
| Retained earnings | $634.90 | $647.95 | $657.75 |
| Total common equity | $774.35 | $791.05 | $827.90 | | | | | | | | | | Find |
| Total liab. & equity | $2,185.25 | $2,331.50 | $2,408.15 |
| | | | | | | | | | | | | Inv. Cap. |
| Invested Capital | | | | | | | | | | | Targets |
| NP% of IC | | | | | | | | | | | 8.00% |
| LTB% of IC | | | | | | | | | | | 42.00% |
| CS% of IC | | | | | | | | | | | 50.00% |
| Int. Rate on N/P | 3.00% | 3.00% | 3.00% | | | | | | | | 3.00% |
| Int. Rate on LTB | 4.56% | 5.05% | 4.21% | | | | | | | | 4.60% |
warpzone
Assignment2Fall2017-FIN470.xlsx
