Description
The Spreadsheet model covers capital budgeting in detail.
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
DB)= Tax Credit (if any )= Other invest.(non-depreciable)= $50,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= $40,000 50% 0 40% DISCOUNT RATE Approach(1irect;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 4 INITIAL INVESTMENT Investment $50,000 - Tax Credit $5,000 Net Investment $45,000 + Working Cap $10,000 + Opp. Cost $7,484 + Other invest. $0 Initial Investment $62,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 ($62,484) Discount Factor 1 Discounted CF ($62,484)
6
5
6
7
8
$0 $0
$0 $0
$0 $0
$0 $0
$0 $0
$0 $0
$0 $0
$0 $0
1 $40,000 $20,000 $0 $20,000 $10,000 $10,000 $4,000 $6,000 $10,000 $0 $16,000 1.10685 $14,455
1 $44,000 $22,000 $0 $22,000 $8,000 $14,000 $5,600 $8,400 $8,000 $1,000 $15,400 1.225116923 $12,570
1 $48,400 $24,200 $0 $24,200 $6,400 $17,800 $7,120 $10,680 $6,400 $1,100 $15,980 1.356020666 $11,784
1 $53,240 $26,620 $0 $26,620 $5,120 $21,500 $8,600 $12,900 $5,120 $1,210 $16,810 1.500911474 $11,200
1 $58,564 $29,282 $0 $29,282 $4,096 $25,186 $10,074 $15,112 $4,096 $1,331 $17,877 1.661283865 $10,761
1 $58,564 $29,282 $0 $29,282 $3,277 $26,005 $10,402 $15,603 $3,277 $0 $18,880 1.838792046 $10,268
1 $58,564 $29,282 $0 $29,282 $2,621 $26,661 $10,664 $15,996 $2,621 $0 $18,618 2.035266976 $9,148
1 $58,564 $29,282 $0 $29,282 $486 $28,796 $11,518 $17,278 $486 $0 $17,764 2.252735252 $7,885
Investment Measures NPV = $47,928 IRR = 23.55% ROC = 60.12%
CAPITAL BUDGETING WORKSHEET
7
Book Value (beginning) Depreciation
$50,000 $10,000
$40,000 $8,000
BOOK VALUE & DEPRECIATION $32,000 $25,600 $20,480 $6,400 $5,120 $4,096
$16,384 $3,277
$13,107 $2,621
$10,486 $486
CAPITAL BUDGETING WORKSHEET
BV(ending) $50,000 $40,000 $32,000 $25,600 $20,480 $16,384 $13,107 $10,486 $10,000
8
CAPITAL BUDGETING WORKSHEET
9
2 10% 0.9 8.00% 5.50% 30.00% 9.00% 10.69%
9 0.00% 0.00%
10 0.00% 0.00%
CAPITAL BUDGETING WORKSHEET
10
9
10
$0 $0
$10,000 $14,641
1 $58,564 $29,282 $0 $29,282 $0 $29,282 $11,713 $17,569 $0 $0 $17,569 2.493440014 $7,046
1 $58,564 $29,282 $0 $29,282 $0 $29,282 $11,713 $17,569 $0 $0 $17,569 2.759864079 $15,294
CAPITAL BUDGETING WORKSHEET
11
$10,000 $0
$10,000 $0
CAPITAL BUDGETING WORKSHEET
$10,000 $10,000
12
doc_718052151.xls
The Spreadsheet model covers capital budgeting in detail.
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
DB)= Tax Credit (if any )= Other invest.(non-depreciable)= $50,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= $40,000 50% 0 40% DISCOUNT RATE Approach(1irect;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 4 INITIAL INVESTMENT Investment $50,000 - Tax Credit $5,000 Net Investment $45,000 + Working Cap $10,000 + Opp. Cost $7,484 + Other invest. $0 Initial Investment $62,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 ($62,484) Discount Factor 1 Discounted CF ($62,484)
6
5
6
7
8
$0 $0
$0 $0
$0 $0
$0 $0
$0 $0
$0 $0
$0 $0
$0 $0
1 $40,000 $20,000 $0 $20,000 $10,000 $10,000 $4,000 $6,000 $10,000 $0 $16,000 1.10685 $14,455
1 $44,000 $22,000 $0 $22,000 $8,000 $14,000 $5,600 $8,400 $8,000 $1,000 $15,400 1.225116923 $12,570
1 $48,400 $24,200 $0 $24,200 $6,400 $17,800 $7,120 $10,680 $6,400 $1,100 $15,980 1.356020666 $11,784
1 $53,240 $26,620 $0 $26,620 $5,120 $21,500 $8,600 $12,900 $5,120 $1,210 $16,810 1.500911474 $11,200
1 $58,564 $29,282 $0 $29,282 $4,096 $25,186 $10,074 $15,112 $4,096 $1,331 $17,877 1.661283865 $10,761
1 $58,564 $29,282 $0 $29,282 $3,277 $26,005 $10,402 $15,603 $3,277 $0 $18,880 1.838792046 $10,268
1 $58,564 $29,282 $0 $29,282 $2,621 $26,661 $10,664 $15,996 $2,621 $0 $18,618 2.035266976 $9,148
1 $58,564 $29,282 $0 $29,282 $486 $28,796 $11,518 $17,278 $486 $0 $17,764 2.252735252 $7,885
Investment Measures NPV = $47,928 IRR = 23.55% ROC = 60.12%
CAPITAL BUDGETING WORKSHEET
7
Book Value (beginning) Depreciation
$50,000 $10,000
$40,000 $8,000
BOOK VALUE & DEPRECIATION $32,000 $25,600 $20,480 $6,400 $5,120 $4,096
$16,384 $3,277
$13,107 $2,621
$10,486 $486
CAPITAL BUDGETING WORKSHEET
BV(ending) $50,000 $40,000 $32,000 $25,600 $20,480 $16,384 $13,107 $10,486 $10,000
8
CAPITAL BUDGETING WORKSHEET
9
2 10% 0.9 8.00% 5.50% 30.00% 9.00% 10.69%
9 0.00% 0.00%
10 0.00% 0.00%
CAPITAL BUDGETING WORKSHEET
10
9
10
$0 $0
$10,000 $14,641
1 $58,564 $29,282 $0 $29,282 $0 $29,282 $11,713 $17,569 $0 $0 $17,569 2.493440014 $7,046
1 $58,564 $29,282 $0 $29,282 $0 $29,282 $11,713 $17,569 $0 $0 $17,569 2.759864079 $15,294
CAPITAL BUDGETING WORKSHEET
11
$10,000 $0
$10,000 $0
CAPITAL BUDGETING WORKSHEET
$10,000 $10,000
12
doc_718052151.xls