Excel question
henry0427
DCF1.xlsxSingle Stage DCF EV
| Current EBIT | $1,500.00 | FCFF | NOPAT - CAP EX + DEPR - ChNWC | |||
| Tax Rate | 21.00% | FCFF | NOPAT - (CapxEx-Depr) -CHNWC | |||
| CapEx | $500.00 | FCFF | NOPAT - Ch Net PP&E - Ch NWC | |||
| Depr | $300.00 | |||||
| Chg in NWC | $150.00 | |||||
| Beta | 1.1 | |||||
| rF | 4.00% | NOPAT | $1,185.00 | TVat0 = FCFF at 1/(WACC -g) | ||
| MRP | 5.50% | FCFF at 0 | $835.00 | |||
| Cost of Debt | 8.50% | FCFF at1 | $876.75 | |||
| Growth | 5.00% | |||||
| A.T Kd | 6.71500% | |||||
| D/E | 0.25 | Ke | 10.0500% | |||
| D/E | .25/1 | WACC | 9.3830% | |||
| D | 0.25 | |||||
| E | 1 | EV at 0 | $20,003.42 | |||
| Value Ratio | 1.25 | |||||
| D/V | 0.2 | |||||
| E/V | 0.8 |
2 stage DCF EV
| Year | 0 | 0 | 1 | 2 | 3 | SS | |||
| Revenue | $15,000 | Growth | 15.00% | 15.00% | 15.00% | 6.00% | |||
| Operating Margin | 10% | NPPE Turnover | 4.5 | 4.5 | 4.5 | 4.5 | 4.5 | ||
| EBIT | $1,500 | NWC Turnover | 9 | 9 | 9 | 9 | 9 | ||
| EBIT Margin | 10% | 10% | 10% | 10% | 10% | ||||
| Tax Rate | 21.00% | ||||||||
| CapEx | $500.00 | Rev | $15,000.00 | $17,250 | $19,838 | $22,813 | $24,182 | ||
| Depr | $300.00 | NPPE | $3,333.33 | $3,833.33 | $4,408.33 | $5,069.58 | $5,373.76 | ||
| Chg in NWC | $150.00 | NWC | $1,666.67 | $1,916.67 | $2,204.17 | $2,534.79 | $2,686.88 | ||
| CH NPPE | $500.00 | $575.00 | $661.25 | $304.18 | |||||
| Beta | 1.1 | Ch NWC | $250.00 | $287.50 | $330.63 | $152.09 | |||
| rF | 4.00% | EBIT | $1,500.00 | $1,725.00 | $1,983.75 | $2,281.31 | $2,418.19 | ||
| MRP | 5.50% | NOPAT | $1,362.75 | $1,567.16 | $1,802.24 | $1,910.37 | |||
| Cost of Debt | 8.50% | FCFF | $612.75 | $704.66 | $810.36 | $1,454.11 | TVat0 = FCFF at 1/(WACC -g) | ||
| Growth | 5.00% | ||||||||
| PV | $560.19 | $588.95 | $33,462.39 | $42,982.81 | |||||
| D/E | 0.25 | $34,611.53 | |||||||
| D/E | .25/1 | ||||||||
| D | 0.25 | NOPAT | $0.00 | ||||||
| E | 1 | FCFF at 0 | -$350.00 | ||||||
| Value Ratio | 1.25 | FCFF at1 | -$367.50 | ||||||
| D/V | 0.2 | ||||||||
| E/V | 0.8 | A.T Kd | 6.71500% | ||||||
| Ke | 10.0500% | ||||||||
| WACC | 9.3830% | ||||||||
| EV at 0 | -$8,384.67 |
