- Have any questions? Contact us free of charge
- 233245354352
- 5432431
- [email protected]
FIN202CBAProject_edited.xlsx
FIN202CBAProject_edited.xlsx
CAPM(WACC, DEBT AND EQUITY)
| Shares information: | ||||||||
| beta | 1.5 | PVA of Coupon | $51.18 | |||||
| Rf | 2% | PV of Face Value | $74.41 | |||||
| Expected(Rm) | 7% | Bond Price | $125.59 | |||||
| Shares outstanding | 4,000,000 | Value of Debt(D) | $251,181,217.02 | |||||
| Stock price | $57 | Value of Equity (E) | $228,000,000.00 | |||||
| Re | 9.5% | Total Debt&Equity (V) | $479,181,217.02 | |||||
| Debt information: | ||||||||
| Coupon | 6% | |||||||
| Yield | 3% | WACC | ||||||
| Face Value | $100 | Yield-to-maturity | 3% | |||||
| Bonds outstanding | 2,000,000 | Tax | 30% | |||||
| Bond Price | $125.59 | We | 48% | |||||
| Years to Maturity | 10 | Wd | 52% | |||||
| Interest payment | 6 | Re | 9.50% | |||||
| Tax rate | 30% | Rd | 3.00% | |||||
| WACC | 5.62% | |||||||
| r | 5.62% |
SALES
| Potencial market share informations | ||||||
| Market Size | Expected Market Share | Market Share Forecast | Overall market growth rate | |||
| Year 0 | 1,500,000.000 | 5% | ||||
| Year 1 | 1,575,000.000 | 10% | 157,500.000 | |||
| Year 2 | 1,653,750.000 | 13% | 214,987.500 | |||
| Year 3 | 1,736,437.500 | 16% | 277,830.000 | |||
| Year 4 | 1,823,259.375 | 19% | 346,419.281 | |||
| Year 5 | 1,914,422.344 | 22% | 421,172.916 | |||
| Year 6 | 2,010,143.461 | 25% | 502,535.865 | |||
| Year 7 | 2,110,650.634 | 25% | 527,662.658 | |||
| Year 8 | 2,216,183.166 | 25% | 554,045.791 | |||
| Year 9 | 2,326,992.324 | 25% | 581,748.081 | |||
| Year 10 | 2,443,341.940 | 25% | 610,835.485 |
PRODUCT&UNIT(PRICE AND COST)
| Pricing and Unit Costs&Marketing Costs | |||||
| Market growth rate | 5% | ||||
| Inflation | 1.5% | ||||
| Price drop | 10% | ||||
| Tax | 30% | ||||
| Commission to retailes | 15% | ||||
| Commission for printer at 3D Print-IT stores | 5% | ||||
| Sales revenue from specialty retailers | 60% | ||||
| Sales revenue from 3D Print-IT stores | 40% | ||||
| Unit Price & Unit Costs | |||||
| Unit Price | Unit Costs | Commission Cost IT | Commission Cost 3D Stores | ||
| Year 1 | $500.00 | $250.00 | $75.00 | $12.50 | |
| Year 2 | $450.00 | $253.75 | $67.50 | $12.69 | |
| Year 3 | $405.00 | $257.56 | $60.75 | $12.88 | |
| Year 4 | $364.50 | $261.42 | $54.68 | $13.07 | |
| Year 5 | $328.05 | $265.34 | $49.21 | $13.27 | |
| Year 6 | $550.00 | $269.32 | $82.50 | $13.47 | |
| Year 7 | $495.00 | $273.36 | $74.25 | $13.67 | |
| Year 8 | $445.50 | $277.46 | $66.83 | $13.87 | |
| Year 9 | $400.95 | $281.62 | $60.14 | $14.08 | |
| Year 10 | $360.86 | $285.85 | $54.13 | $14.29 | |
| Specialty Retailers | |||||
| IT Retailers Units | IT Retail Revenue | EBIT | Tax | Profit after tax (PAT) | |
| Year 1 | 94500.000 | $ 47,250,000.00 | $175.00 | $52.50 | $122.50 |
| Year 2 | 128992.500 | $ 58,046,625.00 | $128.75 | $38.63 | $90.13 |
| Year 3 | 166698.000 | $ 67,512,690.00 | $86.69 | $26.01 | $60.69 |
| Year 4 | 207851.569 | $ 75,761,896.81 | $48.41 | $14.52 | $33.88 |
| Year 5 | 252703.749 | $ 82,899,464.98 | $13.50 | $4.05 | $9.45 |
| Year 6 | 301521.519 | $ 165,836,835.53 | $198.18 | $59.45 | $138.73 |
| Year 7 | 316597.595 | $ 156,715,809.57 | $147.39 | $44.22 | $103.17 |
| Year 8 | 332427.475 | $ 148,096,440.05 | $101.21 | $30.36 | $70.85 |
| Year 9 | 349048.849 | $ 139,951,135.84 | $59.18 | $17.76 | $41.43 |
| Year 10 | 366501.291 | $ 132,253,823.37 | $20.88 | $6.26 | $14.62 |
| 3D Print-IT Stores | |||||
| 3D Print-IT Stores Units | 3D Print-IT Stores Revenue | EBIT | Tax | Profit after tax (PAT) | |
| Year 1 | 63000.000 | $ 31,500,000.00 | $237.50 | $71.25 | $166.25 |
| Year 2 | 85995.000 | $ 38,697,750.00 | $183.56 | $55.07 | $128.49 |
| Year 3 | 111132.000 | $ 45,008,460.00 | $134.57 | $40.37 | $94.20 |
| Year 4 | 138567.713 | $ 50,507,931.21 | $90.01 | $27.00 | $63.01 |
| Year 5 | 168469.166 | $ 55,266,309.99 | $49.44 | $14.83 | $34.61 |
| Year 6 | 201014.346 | $ 110,557,890.35 | $267.21 | $80.16 | $187.05 |
| Year 7 | 211065.063 | $ 104,477,206.38 | $207.97 | $62.39 | $145.58 |
| Year 8 | 221618.317 | $ 98,730,960.03 | $154.17 | $46.25 | $107.92 |
| Year 9 | 232699.232 | $ 93,300,757.23 | $105.25 | $31.57 | $73.67 |
| Year 10 | 244334.194 | $ 88,169,215.58 | $60.72 | $18.21 | $42.50 |
CFs
| Datas from previous sheet are actually relevant for calculations for yearly incremental earnings. | |||||||||||
| Cash Flows and all costs of ACME Inc. During cetain period | |||||||||||
| EBIT | Research & Development | Introductory | Marketing Cost – Specialty Retailer | Marketing Cost of 3D Print-IT stores | Production Cost (required expansion of the factory) | Residual Value of cost (Salvage cost) | Advertisment | Printer Update | Taxes | Yearly incremental earnings | |
| Year 0 | $ (2,000,000.00) | $ (6,000,000.00) | $ (5,000,000.00) | $ (10,000,000.00) | $ (1,000,000.00) | $ (2,000,000.00) | ($26,000,000.00) | ||||
| Year 1 | $78,750,000.00 | $ (7,087,500.00) | $ (787,500.00) | $ (39,375,000.00) | $ (250,000.00) | $ (9,450,000.00) | $ 21,800,000.00 | ||||
| Year2 | $96,744,375.00 | $ (8,706,993.75) | $ (1,091,061.56) | $ (54,553,078.12) | $ (253,750.00) | $ (9,717,972.47) | $ 22,421,519.09 | ||||
| Year 3 | $112,521,150.00 | $ (10,126,903.50) | $ (1,431,137.06) | $ (71,556,852.94) | $ (257,556.25) | $ (8,821,876.95) | $ 20,326,823.30 | ||||
| Year 4 | $126,269,828.02 | $ (11,364,284.52) | $ (1,811,215.76) | $ (90,560,787.77) | $ (261,419.59) | $ (6,760,061.99) | $ 15,512,058.38 | ||||
| Year 5 | $138,165,774.97 | $ (12,434,919.75) | $ (2,235,087.91) | $ (111,754,395.29) | $ (265,340.89) | $ (250,000.00) | $ (3,522,411.61) | $ 7,703,619.53 | |||
| Year 6 | $276,394,725.88 | $ (24,875,525.33) | $ (2,706,869.24) | $ (135,343,462.25) | $ (269,321.00) | $ (34,040,660.72) | $ 79,158,887.34 | ||||
| Year 7 | $261,193,015.96 | $ (23,507,371.44) | $ (2,884,845.90) | $ (144,242,294.89) | $ (273,360.82) | $ (27,167,551.12) | $ 63,117,591.80 | ||||
| Year 8 | $246,827,400.08 | $ (22,214,466.01) | $ (3,074,524.52) | $ (153,726,225.78) | $ (277,461.23) | $ (20,343,655.13) | $ 47,191,067.41 | ||||
| Year 9 | $233,251,893.07 | $ (20,992,670.38) | $ (3,276,674.50) | $ (163,833,725.13) | $ (281,623.15) | $ (13,544,646.92) | $ 31,322,553.00 | ||||
| Year 10 | $220,423,038.95 | $ (19,838,073.51) | $ (3,492,115.85) | $ (174,605,792.55) | $2,000,000.00 | $ (285,847.49) | $ (6,746,117.11) | $ 17,455,092.44 | |||
| $ 326,009,212.30 |
NVP,IRR,PB,DBP,PI
| Years | Total Cash Flows | Discounted cash flow | Cumulative DCF | NVP | IRR | PVCF | PI | PB | DBP | NPM | Verification of NVP | |||
| 0 | ($26,000,000.00) | ($26,000,000.00) | $ (26,000,000.00) | $ (26,000,000.00) | $210,091,581.75 | 87% | $ 236,091,581.75 | 9.08 | 1.187 | 1.27 | 18.21% | $ 210,091,581.75 | ||
| 1 | $21,800,000.00 | ($4,200,000.00) | $ 20,639,833.68 | $ (5,360,166.32) | ||||||||||
| 2 | $22,421,519.09 | $18,221,519.09 | $ 20,098,536.42 | $ 14,738,370.10 | ||||||||||
| 3 | $20,326,823.30 | $38,548,342.40 | $ 17,251,172.24 | $ 31,989,542.34 | ||||||||||
| 4 | $15,512,058.38 | $54,060,400.78 | $ 12,464,309.66 | $ 44,453,852.00 | ||||||||||
| 5 | $7,703,619.53 | $61,764,020.31 | $ 5,860,616.79 | $ 50,314,468.79 | ||||||||||
| 6 | $79,158,887.34 | $140,922,907.65 | $ 57,016,148.91 | $ 107,330,617.70 | ||||||||||
| 7 | $63,117,591.80 | $204,040,499.45 | $ 43,042,582.36 | $ 150,373,200.06 | ||||||||||
| 8 | $47,191,067.41 | $251,231,566.86 | $ 30,468,943.77 | $ 180,842,143.83 | ||||||||||
| 9 | $31,322,553.00 | $282,554,119.86 | $ 19,147,163.74 | $ 199,989,307.57 | ||||||||||
| 10 | $17,455,092.44 | $300,009,212.30 | $ 10,102,274.18 | $ 210,091,581.75 | ||||||||||
| r | 5.62% | |||||||||||||
Sensitivity Analysis
| Base Scenario | Good Scenario (+20%) | Bad Scenario (-20%) | Good Scenario (+20%) | Bad Scenario (-20%) | Good Scenario (+20%) | Bad Scenario (-20%) | |
| Cash Flows | Change in inital investment | Change in inital investment | Change in CF | Change in CF | Change in Rwacc | Change in Rwacc | |
| Year 0 | $ (26,000,000.00) | $ (20,800,000.00) | $ (31,200,000.00) | $ (26,000,000.00) | $ (26,000,000.00) | $ (26,000,000.00) | $ (26,000,000.00) |
| Year 1 | $ 21,800,000.00 | $ 21,800,000.00 | $ 21,800,000.00 | $ 26,160,000.00 | $ 17,440,000.00 | $ 21,800,000.00 | $ 21,800,000.00 |
| Year 2 | $ 22,421,519.09 | $ 22,421,519.09 | $ 22,421,519.09 | $ 26,905,822.91 | $ 17,937,215.28 | $ 22,421,519.09 | $ 22,421,519.09 |
| Year 3 | $ 20,326,823.30 | $ 20,326,823.30 | $ 20,326,823.30 | $ 24,392,187.96 | $ 16,261,458.64 | $ 20,326,823.30 | $ 20,326,823.30 |
| Year 4 | $ 15,512,058.38 | $ 15,512,058.38 | $ 15,512,058.38 | $ 18,614,470.06 | $ 12,409,646.71 | $ 15,512,058.38 | $ 15,512,058.38 |
| Year 5 | $ 7,703,619.53 | $ 7,703,619.53 | $ 7,703,619.53 | $ 9,244,343.44 | $ 6,162,895.63 | $ 7,703,619.53 | $ 7,703,619.53 |
| Year 6 | $ 79,158,887.34 | $ 79,158,887.34 | $ 79,158,887.34 | $ 94,990,664.81 | $ 63,327,109.87 | $ 79,158,887.34 | $ 79,158,887.34 |
| Year 7 | $ 63,117,591.80 | $ 63,117,591.80 | $ 63,117,591.80 | $ 75,741,110.15 | $ 50,494,073.44 | $ 63,117,591.80 | $ 63,117,591.80 |
| Year 8 | $ 47,191,067.41 | $ 47,191,067.41 | $ 47,191,067.41 | $ 56,629,280.90 | $ 37,752,853.93 | $ 47,191,067.41 | $ 47,191,067.41 |
| Year 9 | $ 31,322,553.00 | $ 31,322,553.00 | $ 31,322,553.00 | $ 37,587,063.60 | $ 25,058,042.40 | $ 31,322,553.00 | $ 31,322,553.00 |
| Year 10 | $ 17,455,092.44 | $ 17,455,092.44 | $ 17,455,092.44 | $ 20,946,110.93 | $ 13,964,073.95 | $ 17,455,092.44 | $ 17,455,092.44 |
| r | 5.62% | 5.62% | 5.62% | 5.62% | 5.62% | 4.78% | 6.46% |
| NPV | $210,091,581.75 | $215,291,581.75 | $204,891,581.75 | $257,309,898.10 | $162,873,265.40 | $221,233,500.60 | $199,627,247.76 |
| Difference | $5,200,000.00 | $5,200,000.00 | $47,218,316.35 | $47,218,316.35 | $11,141,918.84 | $10,464,333.99 | |
| $ 210,091,581.75 |