Corporate Finance Case Study
Tuleenhb
CORPORATE1.xlsxSheet1
| CAPEX | Straight-Line Depreciation | ||||||||||||||
| Investment | Total | Useful life | Year | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | ||
| Factory Building | $ 40,000,000 | 25 | Dep. Exp Factory Building | $ 1,600,000 | $ 1,600,000 | $ 1,600,000 | $ 1,600,000 | $ 1,600,000 | $ 1,600,000 | $ 1,600,000 | $ 1,600,000 | $ 1,600,000 | $ 1,600,000 | ||
| Machinery | $ 55,000,000 | 10 | Dep. Exp Machinery | $ 5,500,000 | $ 5,500,000 | $ 5,500,000 | $ 5,500,000 | $ 5,500,000 | $ 5,500,000 | $ 5,500,000 | $ 5,500,000 | $ 5,500,000 | $ 5,500,000 | ||
| Office equipment | $ 3,000,000 | 4 | Dep. Exp. Office Equipment | $ 750,000 | $ 750,000 | $ 750,000 | $ 750,000 | ||||||||
| Trucks | $ 370,000 | 4 | Dep. Exp Trucks | $ 92,500 | $ 92,500 | $ 92,500 | $ 92,500 | ||||||||
| Other equipt | $ 1,630,000 | 4 | Dep. Exp. Other Equipment | $ 407,500 | $ 407,500 | $ 407,500 | $ 407,500 | ||||||||
| total investmet | $ 100,000,000 | Total Dep. Exp. | $ 8,350,000 | $ 8,350,000 | $ 8,350,000 | $ 8,350,000 | $ 7,100,000 | $ 7,100,000 | $ 7,100,000 | $ 7,100,000 | $ 7,100,000 | $ 7,100,000 | |||
| Revenue and cost Assumptions | |||||||||||||||
| Accum Deprec | Book Value | Market Value | Years | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | |
| Unitary Price of electric remote car | 175 | 180.25 | 185.6575 | 191.227225 | 196.96404175 | 202.8729630025 | 208.9591518926 | 215.2279264494 | 221.6847642428 | 228.3353071701 | |||||
| Unitary price of remote helicopter | 275 | 283.25 | 291.7475 | 300.499925 | 309.51492275 | 318.8003704325 | 328.3643815455 | 338.2153129918 | 348.3617723816 | 358.812625553 | |||||
| $ 16,000,000 | $ 24,000,000 | $ 40,000,000.00 | Unitary Price of 4S robot | 375 | 386.25 | 397.8375 | 409.772625 | 422.06580375 | 434.7277778625 | 447.7696111984 | 461.2026995343 | 475.0387805204 | 489.289943936 | ||
| Sales (Units) | $ 100,000.00 | $ 110,000.00 | $ 121,000.00 | $ 133,100.00 | $ 146,410.00 | $ 153,730.50 | $ 161,417.03 | $ 169,487.88 | $ 172,877.63 | $ 176,335.19 | |||||
| $ 3,000,000 | $ - 0 | $ - 0 | % of Sales Increase | 0 | 10% | 10% | 10% | 10% | 5% | 5% | 5% | 2% | 2% | ||
| $ 370,000 | $ - 0 | $ - 0 | Gross Revenue | $ 82,500,000.00 | $ 93,472,500.00 | $ 105,904,342.50 | $ 119,989,620.05 | $ 135,948,239.52 | $ 147,028,021.04 | $ 159,010,804.76 | $ 171,970,185.34 | $ 180,671,876.72 | $ 189,813,873.68 | ||
| $ 1,630,000 | $ - 0 | $ - 0 | |||||||||||||
| Production cost per unit of remote car | 35% | 6125% | 6309% | 6498% | 6693% | 6894% | 7101% | 7314% | 7533% | 7759% | 7992% | ||||
| Production cost per unit of remote helicopter | 40% | 11000% | $ 113.30 | $ 116.70 | 12020% | 12381% | 12752% | 13135% | 13529% | 13934% | 14353% | ||||
| $ 21,000,000 | $ 24,000,000 | $ 40,000,000.00 | Production Cost Per Unit of 4S robot | 45% | 16875% | $ 173.81 | $ 179.03 | 18440% | 18993% | 19563% | 20150% | 20754% | 21377% | 22018% | |
| COGS | $ 34,000,000.00 | $ 38,522,000.00 | $ 43,645,426.00 | $ 49,450,267.66 | $ 56,027,153.26 | $ 60,593,366.25 | $ 65,531,725.60 | $ 70,872,561.23 | $ 74,458,712.83 | $ 78,226,323.70 | |||||
| Admin Cost Per Unit | 20% | ||||||||||||||
| Adminstration Cost | $ 16,500,000.00 | $ 18,694,500.00 | $ 21,180,868.50 | $ 23,997,924.01 | $ 27,189,647.90 | $ 29,405,604.21 | $ 31,802,160.95 | $ 34,394,037.07 | $ 36,134,375.34 | $ 37,962,774.74 | |||||
| Income Statement Pro Forma | |||||||||||||||
| Year | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | |||||
| Gross Revenue | $ 82,500,000.00 | $ 93,472,500.00 | $ 105,904,342.50 | $ 119,989,620.05 | $ 135,948,239.52 | $ 147,028,021.04 | $ 159,010,804.76 | $ 171,970,185.34 | $ 180,671,876.72 | $ 189,813,873.68 | |||||
| COGS | $ 34,000,000.00 | $ 38,522,000.00 | $ 43,645,426.00 | $ 49,450,267.66 | $ 56,027,153.26 | $ 60,593,366.25 | $ 65,531,725.60 | $ 70,872,561.23 | $ 74,458,712.83 | $ 78,226,323.70 | |||||
| Gross Income | $ 48,500,000.00 | $ 54,950,500.00 | $ 62,258,916.50 | $ 70,539,352.39 | $ 79,921,086.26 | $ 86,434,654.79 | $ 93,479,079.16 | $ 101,097,624.11 | $ 106,213,163.89 | $ 111,587,549.98 | |||||
| Admin Cost | $ 16,500,000.00 | $ 18,694,500.00 | $ 21,180,868.50 | $ 23,997,924.01 | $ 27,189,647.90 | $ 29,405,604.21 | $ 31,802,160.95 | $ 34,394,037.07 | $ 36,134,375.34 | $ 37,962,774.74 | |||||
| Depreciation | $ 8,350,000 | $ 8,350,000 | $ 8,350,000 | $ 8,350,000 | $ 7,100,000 | $ 7,100,000 | $ 7,100,000 | $ 7,100,000 | $ 7,100,000 | $ 7,100,000 | |||||
| EBIT | $ 23,650,000.00 | $ 27,906,000.00 | $ 32,728,048.00 | $ 38,191,428.38 | $ 45,631,438.36 | $ 49,929,050.59 | $ 54,576,918.21 | $ 59,603,587.04 | $ 62,978,788.55 | $ 66,524,775.25 | |||||
| Taxes (income @ 40 % ) | $ 9,460,000.00 | $ 11,162,400.00 | $ 13,091,219.20 | $ 15,276,571.35 | $ 18,252,575.34 | $ 19,971,620.23 | $ 21,830,767.28 | $ 23,841,434.82 | $ 25,191,515.42 | $ 26,609,910.10 | |||||
| Net Income | $ 14,190,000.00 | $ 16,743,600.00 | $ 19,636,828.80 | $ 22,914,857.03 | $ 27,378,863.02 | $ 29,957,430.35 | $ 32,746,150.92 | $ 35,762,152.23 | $ 37,787,273.13 | $ 39,914,865.15 | |||||
| Working Capital | |||||||||||||||
| Year | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | |||||
| A/R as 16 % of Revenues | $ (13,200,000.00) | $ (14,955,600.00) | $ (16,944,694.80) | $ (19,198,339.21) | $ (21,751,718.32) | $ (23,524,483.37) | $ (25,441,728.76) | $ (27,515,229.65) | $ (28,907,500.28) | $ (30,370,219.79) | |||||
| Inventory as 8% of COGS | $ (2,720,000.00) | $ (3,081,760.00) | $ (3,491,634.08) | $ (3,956,021.41) | $ (4,482,172.26) | $ (4,847,469.30) | $ (5,242,538.05) | $ (5,669,804.90) | $ (5,956,697.03) | $ (6,258,105.90) | |||||
| A/P as 16% of COGS | $ 5,440,000.00 | $ 6,163,520.00 | $ 6,983,268.16 | $ 7,912,042.83 | $ 8,964,344.52 | $ 9,694,938.60 | $ 10,485,076.10 | $ 11,339,609.80 | $ 11,913,394.05 | $ 12,516,211.79 | |||||
| Working Capital | $ (10,480,000.00) | $ (11,873,840.00) | $ (13,453,060.72) | $ (15,242,317.80) | $ (17,269,546.06) | $ (18,677,014.07) | $ (20,199,190.71) | $ (21,845,424.76) | $ (22,950,803.25) | $ (24,112,113.89) | |||||
| working capital, variation | $ (10,480,000.00) | $ (1,393,840.00) | $ (1,579,220.72) | $ (1,789,257.08) | $ (2,027,228.27) | $ (1,407,468.00) | $ (1,522,176.65) | $ (1,646,234.04) | $ (1,105,378.49) | $ 24,112,113.89 | |||||
| Incremental free cash Flows | |||||||||||||||
| Year | 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | ||||
| Net Income | $ 14,190,000.00 | $ 16,743,600.00 | $ 19,636,828.80 | $ 22,914,857.03 | $ 27,378,863.02 | $ 29,957,430.35 | $ 32,746,150.92 | $ 35,762,152.23 | $ 37,787,273.13 | $ 39,914,865.15 | |||||
| Add Depreciation | $ 8,350,000 | $ 8,350,000 | $ 8,350,000 | $ 8,350,000 | $ 7,100,000 | $ 7,100,000 | $ 7,100,000 | $ 7,100,000 | $ 7,100,000 | $ 7,100,000 | |||||
| Operating cash flow | $22,540,000.00 | $25,093,600.00 | $27,986,828.80 | $31,264,857.03 | $34,478,863.02 | $37,057,430.35 | $39,846,150.92 | $42,862,152.23 | $44,887,273.13 | $47,014,865.15 | |||||
| working capital, variation | $ (10,480,000.00) | $ (1,393,840.00) | $ (1,579,220.72) | $ (1,789,257.08) | $ (2,027,228.27) | $ (1,407,468.00) | $ (1,522,176.65) | $ (1,646,234.04) | $ (1,105,378.49) | $ 24,112,113.89 | |||||
| investment | $ (100,000,000.00) | ||||||||||||||
| free cash flow | $12,060,000.00 | $23,699,760.00 | $26,407,608.08 | $29,475,599.95 | $32,451,634.75 | $35,649,962.35 | $38,323,974.28 | $41,215,918.18 | $43,781,894.64 | $71,126,979.04 | |||||
| discounted fcf | $9,648,000.00 | $15,167,846.40 | $13,520,695.34 | $12,073,205.74 | $10,633,751.67 | $9,345,423.73 | $8,037,119.93 | $6,914,883.62 | $5,876,306.43 | $7,637,201.22 | |||||
| Total discounted fcf | $98,854,434.08 | ||||||||||||||
| Discount rate | 25% | ||||||||||||||
| Net Present Value NPV | -$1,145,565.92 |
&"Helvetica,Regular"&12&K000000&P
