CAPITAL BUDGETING WORKSHEET
5
Equity Analysis of a Project
INPUT SHEET: USER ENTERS ALL BOLD NUMBERS INITIAL INVESTMENT Initial Investment= Opportunity cost (if any)= Lifetime of the investment Salvage Value at end of project= Deprec. method(1:St.line;2:DDB)= Tax Credit (if any )= Other invest.(non-depreciable)= $40,000 $7,484 10 $10,000 2 10% 0 CASHFLOW DETAILS Revenues in year 1= Var. Expenses as % of Rev= Fixed expenses in year 1= Tax rate on net income= $20,000 20% 0 10% DISCOUNT RATE Approach(1:Direct;2:CAPM)= 1. Discount rate = 2a. Beta b. Riskless rate= c. Market risk premium = d. Debt Ratio = e. Cost of Borrowing = Discount rate used= WORKING CAPITAL Initial Investment in Work. Cap= Working Capital as % of Rev= Salvageable fraction at end= $10,000 25% 100%
If you do not have the breakdown of fixed and variable expenses, input the entire expense as a % of revenues.
GROWTH RATES 1 Revenues Fixed Expenses Do not enter Do not enter 2 10.00% 10.00% 3 10.00% 10.00% 4 10.00% 10.00% 5 10.00% 10.00% 6 0.00% 0.00% 7 0.00% 0.00% 8 0.00% 0.00%
CAPITAL BUDGETING WORKSHEET
Default: The fixed expense growth rate is set equal to the growth rate in revenues by default. YEAR 0 1 2 3 INITIAL INVESTMENT Investment $40,000 - Tax Credit $4,000 Net Investment $36,000 + Working Cap $10,000 + Opp. Cost $7,484 + Other invest. $0 Initial Investment $53,484 SALVAGE VALUE Equipment Working Capital OPERATING CASHFLOWS Lifetime Index Revenues -Var. Expenses - Fixed Expenses EBITDA - Depreciation EBIT -Tax EBIT(1-t) + Depreciation - ∂ Work. Cap NATCF ($53,484) Discount Factor 1 Discounted CF ($53,484)
6
4
5
6
7
8
$0 $0
$0 $0
$0 $0
$0 $0
$0 $0
$0 $0
$0 $0
$0 $0
1 $20,000 $4,000 $0 $16,000 $8,000 $8,000 $800 $7,200 $8,000 ($5,000) $20,200 1.11495 $18,117
1 $22,000 $4,400 $0 $17,600 $6,400 $11,200 $1,120 $10,080 $6,400 ($4,500) $20,980 1.243113503 $16,877
1 $24,200 $4,840 $0 $19,360 $5,120 $14,240 $1,424 $12,816 $5,120 $5,550 $12,386 1.3860094 $8,936
1 $26,620 $5,324 $0 $21,296 $4,096 $17,200 $1,720 $15,480 $4,096 $605 $18,971 1.54533118 $12,276
1 $29,282 $5,856 $0 $23,426 $3,277 $20,149 $2,015 $18,134 $3,277 $666 $20,745 1.722966999 $12,040
1 $29,282 $5,856 $0 $23,426 $2,621 $20,804 $2,080 $18,724 $2,621 $0 $21,345 1.921022056 $11,111
1 $29,282 $5,856 $0 $23,426 $486 $22,940 $2,294 $20,646 $486 $0 $21,132 2.141843541 $9,866
1 $29,282 $5,856 $0 $23,426 $0 $23,426 $2,343 $21,083 $0 $0 $21,083 2.388048456 $8,829
NPV = IRR = ROC =
Investment Measures $65,424 34.14% 88.45%
CAPITAL BUDGETING WORKSHEET
7
Book Value (beginning) Depreciation
$40,000 $8,000
$32,000 $6,400
BOOK VALUE & DEPRECIATION $25,600 $20,480 $16,384 $5,120 $4,096 $3,277
$13,107 $2,621
$10,486 $486
$10,000 $0
CAPITAL BUDGETING WORKSHEET
BV(ending) $40,000 $32,000 $25,600 $20,480 $16,384 $13,107 $10,486 $10,000 $10,000
8
CAPITAL BUDGETING WORKSHEET
9
2 10% 0.9 8.00% 5.50% 30.00% 9.00% 11.50%
9 0.00% 0.00%
10 0.00% 0.00%
CAPITAL BUDGETING WORKSHEET
10
9
10
$0 $0
$10,000 $7,321
1 $29,282 $5,856 $0 $23,426 $0 $23,426 $2,343 $21,083 $0 $0 $21,083 2.662554626 $7,918
1 $29,282 $5,856 $0 $23,426 $0 $23,426 $2,343 $21,083 $0 $0 $21,083 2.968615281 $12,937
CAPITAL BUDGETING WORKSHEET
11
$10,000 $0
$10,000 $0
CAPITAL BUDGETING WORKSHEET
$10,000 $10,000
12