Finance

profilephuong.pht0271
Lecture9ExcelExampleFCFO-Solutions1.xlsx
Home>Business & Finance homework help>Accounting homework help>Finance

PART A

Lecture 9 Example
PART A
Key Variables In the table there should be ONLY formulas referencing
•Life of the project (Years) 10 the key variables.
•Number of new boxes 4
•Annual incremental revenue per box $400,000 Pressing F4 will make a term constant when you drag it
•Number of new seats 5,000 accross
•Annual incremental revenue per seat $2,500
•Incremental expense (% of revenue) 60%
•Construction cost (Cap exp) $10,000,000
•Depreciation per year $1,000,000 = $10,000,000/10 = $1,000,000
•Working Capital $1,000,000
•Tax rate 30%
•Cost of capital 10%
•Inititial Outlay $ 10,000,000.00
Year 0 1 2 3 4 5 6 7 8 9 10
Revenue $14,100,000 $14,100,000 $14,100,000 $14,100,000 $14,100,000 $14,100,000 $14,100,000 $14,100,000 $14,100,000 $14,100,000
-Operating Expenses -$8,460,000 -$8,460,000 -$8,460,000 -$8,460,000 -$8,460,000 -$8,460,000 -$8,460,000 -$8,460,000 -$8,460,000 -$8,460,000
EBITDA $5,640,000 $5,640,000 $5,640,000 $5,640,000 $5,640,000 $5,640,000 $5,640,000 $5,640,000 $5,640,000 $5,640,000
-Depreciation -$1,000,000 -$1,000,000 -$1,000,000 -$1,000,000 -$1,000,000 -$1,000,000 -$1,000,000 -$1,000,000 -$1,000,000 -$1,000,000
EBIT $4,640,000 $4,640,000 $4,640,000 $4,640,000 $4,640,000 $4,640,000 $4,640,000 $4,640,000 $4,640,000 $4,640,000
-Tax -$1,392,000 -$1,392,000 -$1,392,000 -$1,392,000 -$1,392,000 -$1,392,000 -$1,392,000 -$1,392,000 -$1,392,000 -$1,392,000
NOPAT $3,248,000 $3,248,000 $3,248,000 $3,248,000 $3,248,000 $3,248,000 $3,248,000 $3,248,000 $3,248,000 $3,248,000
+Depreciation $1,000,000 $1,000,000 $1,000,000 $1,000,000 $1,000,000 $1,000,000 $1,000,000 $1,000,000 $1,000,000 $1,000,000
Cash flow from operations $4,248,000 $4,248,000 $4,248,000 $4,248,000 $4,248,000 $4,248,000 $4,248,000 $4,248,000 $4,248,000 $4,248,000
Capital expenditure -$ 10,000,000.00
Working capital -$1,000,000 $1,000,000
FCF -$ 11,000,000.00 $ 4,248,000.00 $ 4,248,000.00 $ 4,248,000.00 $ 4,248,000.00 $ 4,248,000.00 $ 4,248,000.00 $ 4,248,000.00 $ 4,248,000.00 $ 4,248,000.00 $ 5,248,000.00
PV of FCF -11000000 3861818.18181818 3510743.80165289 3191585.2742299 2901441.15839082 2637673.78035529 2397885.25486845 2179895.68624404 1981723.35113095 1801566.68284631 2023331.18292618
NPV $ 15,487,664 Adding up PV of FCF's
$ 15,487,664 Using NPV Function

PART B

Lecture 9 Example
PART B
Key Variables The table should have no numbers in it
•Life of the project (Years) 10 just references to key variables and changes to key variables
•Number of new boxes 4
•Annual incremental revenue per box $400,000 Incremental Changes to key variables:
•Number of new seats 5,000 1. Reduction in EBIT years 1-10 $ 500,000.00
•Annual incremental revenue per seat $2,500 2. Reduction in revenue years 1-10 $ 600,000.00
•Incremental expense (% of revenue) 60% 3. New expensesr in years 1-10 $ 75,000.00
•Construction cost (Cap exp) $10,000,000
•Depreciation per year $1,000,000
•Working Capital $1,000,000
•Tax rate 30%
•Cost of capital 10%
•Inititial Outlay $ 10,000,000.00
Year 0 1 2 3 4 5 6 7 8 9 10
Revenue $13,500,000 $13,500,000 $13,500,000 $13,500,000 $13,500,000 $13,500,000 $13,500,000 $13,500,000 $13,500,000 $13,500,000
-Operating Expenses -$8,100,000 -$8,100,000 -$8,100,000 -$8,100,000 -$8,100,000 -$8,100,000 -$8,100,000 -$8,100,000 -$8,100,000 -$8,100,000
Other expenses -$ 75,000.00 -$ 75,000.00 -$ 75,000.00 -$ 75,000.00 -$ 75,000.00 -$ 75,000.00 -$ 75,000.00 -$ 75,000.00 -$ 75,000.00 -$ 75,000.00
EBITDA $5,325,000 $5,325,000 $5,325,000 $5,325,000 $5,325,000 $5,325,000 $5,325,000 $5,325,000 $5,325,000 $5,325,000
-Depreciation -$1,000,000 -$1,000,000 -$1,000,000 -$1,000,000 -$1,000,000 -$1,000,000 -$1,000,000 -$1,000,000 -$1,000,000 -$1,000,000
EBIT $3,825,000 $3,825,000 $3,825,000 $3,825,000 $3,825,000 $3,825,000 $3,825,000 $3,825,000 $3,825,000 $3,825,000
-Tax -$1,147,500 -$1,147,500 -$1,147,500 -$1,147,500 -$1,147,500 -$1,147,500 -$1,147,500 -$1,147,500 -$1,147,500 -$1,147,500
NOPAT $2,677,500 $2,677,500 $2,677,500 $2,677,500 $2,677,500 $2,677,500 $2,677,500 $2,677,500 $2,677,500 $2,677,500
+Depreciation $1,000,000 $1,000,000 $1,000,000 $1,000,000 $1,000,000 $1,000,000 $1,000,000 $1,000,000 $1,000,000 $1,000,000
Cash flow from operations $3,677,500 $3,677,500 $3,677,500 $3,677,500 $3,677,500 $3,677,500 $3,677,500 $3,677,500 $3,677,500 $3,677,500
Capital expenditure -$ 10,000,000.00
Working capital -$1,000,000 $1,000,000
FCF -$ 11,000,000.00 $ 3,677,500.00 $ 3,677,500.00 $ 3,677,500.00 $ 3,677,500.00 $ 3,677,500.00 $ 3,677,500.00 $ 3,677,500.00 $ 3,677,500.00 $ 3,677,500.00 $ 4,677,500.00
PV of FCF -11000000 3343181.81818182 3039256.19834711 2762960.18031555 2511781.98210505 2283438.16555004 2075852.87777277 1887138.97979342 1715580.89072129 1559618.99156481 1803378.73630663
NPV $ 11,982,189 Adding up PV of FCF's
$ 11,982,189 Using NPV Function

Sheet3