|
Equity Analysis of a Project
|
|
|
|
|
INPUT SHEET: USER ENTERS
ALL BOLD NUMBERS
|
|
|
INITIAL
INVESTMENT
|
|
|
CASHFLOW DETAILS
|
|
|
DISCOUNT RATE
|
|
|
|
Initial
Investment=
|
|
|
Revenues in year 1=
|
|
|
Approach(1:Direct;2:CAPM)=
|
|
|
|
Opportunity
cost (if any)=
|
|
|
Var. Expenses as % of
Rev=
|
|
|
1. Discount rate =
|
|
|
|
Lifetime
of the investment
|
|
|
Fixed expenses in year 1=
|
|
|
2a. Beta
|
|
|
|
|
Salvage
Value at end of project=
|
|
|
Tax rate on net income=
|
|
|
b. Riskless rate=
|
|
|
|
Deprec.
method(1:St.line;2:DDB)=
|
|
|
If you do not have the
breakdown of fixed and variable
|
c. Market risk premium =
|
|
|
|
Tax
Credit (if any )=
|
|
|
expenses, input the
entire expense as a % of revenues.
|
|
|
|
|
Other
invest.(non-depreciable)=
|
|
|
|
|
Discount rate used=
|
10.00%
|
|
|
|
|
|
LOAN DETAILS
|
|
|
|
WORKING
CAPITAL
|
|
|
Borrowing (if any) =
|
|
|
|
Initial
Investment in Work. Cap=
|
$10,000
|
|
Interest rate on loan=
|
|
|
|
Working
Capital as % of Rev=
|
25%
|
|
Time period for loan =
|
|
|
|
Salvageable
fraction at end=
|
100%
|
|
Type(1:Term;2:balloon)=
|
|
|
|
|
|
GROWTH
RATES
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
1
|
2
|
3
|
4
|
5
|
6
|
7
|
8
|
9
|
10
|
|
|
Revenues
|
|
Do not enter
|
|
|
|
|
|
|
|
|
|
|
|
Fixed
Expenses
|
Do not enter
|
10.00%
|
10.00%
|
10.00%
|
10.00%
|
0.00%
|
0.00%
|
0.00%
|
0.00%
|
0.00%
|
|
|
Default:
The fixed expense growth rate is set equal to the growth rate in revenues by
default.
|
|
|
|
|
|
|
|
|
YEAR
|
|
|
|
0
|
1
|
2
|
3
|
4
|
5
|
6
|
7
|
8
|
9
|
10
|
|
|
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
|
|
|
- Borrowing
|
$30,000
|
|
|
Net Initial
Inv
|
$32,484
|
|
|
|
|
SALVAGE
VALUE
|
|
|
Equipment
|
|
$0
|
$0
|
$0
|
$0
|
$0
|
$0
|
$0
|
$0
|
$0
|
$10,000
|
|
|
Working
Capital
|
$0
|
$0
|
$0
|
$0
|
$0
|
$0
|
$0
|
$0
|
$0
|
$14,641
|
|
|
|
|
OPERATING
CASHFLOWS
|
|
|
|
|
|
|
|
|
|
|
|
|
Lifetime Index
|
|
1
|
1
|
1
|
1
|
1
|
1
|
1
|
1
|
1
|
1
|
|
|
Revenues
|
|
$40,000
|
$44,000
|
$48,400
|
$53,240
|
$58,564
|
$58,564
|
$58,564
|
$58,564
|
$58,564
|
$58,564
|
|
|
-Var. Expenses
|
$20,000
|
$22,000
|
$24,200
|
$26,620
|
$29,282
|
$29,282
|
$29,282
|
$29,282
|
$29,282
|
$29,282
|
|
|
- Fixed Expenses
|
$0
|
$0
|
$0
|
$0
|
$0
|
$0
|
$0
|
$0
|
$0
|
$0
|
|
|
BTCF
|
|
$20,000
|
$22,000
|
$24,200
|
$26,620
|
$29,282
|
$29,282
|
$29,282
|
$29,282
|
$29,282
|
$29,282
|
|
|
- Depreciation
|
|
$10,000
|
$8,000
|
$6,400
|
$5,120
|
$4,096
|
$3,277
|
$2,621
|
$486
|
$0
|
$0
|
|
|
- Interest
|
|
$2,400
|
$1,991
|
$1,549
|
$1,072
|
$557
|
$0
|
$0
|
$0
|
$0
|
$0
|
|
|
Taxable
Income
|
$7,600
|
$12,009
|
$16,251
|
$20,428
|
$24,629
|
$26,005
|
$26,661
|
$28,796
|
$29,282
|
$29,282
|
|
|
-Tax
|
|
$3,040
|
$4,804
|
$6,500
|
$8,171
|
$9,852
|
$10,402
|
$10,664
|
$11,518
|
$11,713
|
$11,713
|
|
|
Net Income
|
|
$4,560
|
$7,205
|
$9,751
|
$12,257
|
$14,778
|
$15,603
|
$15,996
|
$17,278
|
$17,569
|
$17,569
|
|
|
+ Depreciation
|
|
$10,000
|
$8,000
|
$6,400
|
$5,120
|
$4,096
|
$3,277
|
$2,621
|
$486
|
$0
|
$0
|
|
|
ATCF
|
|
$14,560
|
$15,205
|
$16,151
|
$17,377
|
$18,874
|
$18,880
|
$18,618
|
$17,764
|
$17,569
|
$17,569
|
|
|
- ? Work. Cap
|
|
$0
|
$1,000
|
$1,100
|
$1,210
|
$1,331
|
$0
|
$0
|
$0
|
$0
|
$0
|
|
|
- Princ. Rep.
|
|
$5,114
|
$5,523
|
$5,965
|
$6,442
|
$6,957
|
$0
|
$0
|
$0
|
$0
|
$0
|
|
|
NATCF
|
$-32,484
|
$9,446
|
$8,683
|
$9,086
|
$9,725
|
$10,586
|
$18,880
|
$18,618
|
$17,764
|
$17,569
|
$17,569
|
|
|
Discount Factor
|
1.000000
|
1.100000
|
1.210000
|
1.331000
|
1.464100
|
1.610510
|
1.771561
|
1.948717
|
2.143589
|
2.357948
|
2.593742
|
|
|
Discounted CF
|
$-32,484
|
$8,588
|
$7,176
|
$6,826
|
$6,642
|
$6,573
|
$10,657
|
$9,554
|
$8,287
|
$7,451
|
$16,274
|
|
|
|
|
|
Investment Measures
|
|
|
|
NPV =
|
$55,544
|
|
|
|
IRR =
|
32.15%
|
|
|
|
ROE =
|
99.34%
|
|
|
|
|
|
LOAN DETAILS
|
|
|
Loan life index
|
|
1
|
1
|
1
|
1
|
1
|
0
|
0
|
0
|
0
|
0
|
|
|
Total Payment
|
|
$7,514
|
$7,514
|
$7,514
|
$7,514
|
$7,514
|
$0
|
$0
|
$0
|
$0
|
$0
|
|
|
Interest
payment
|
$2,400
|
$1,991
|
$1,549
|
$1,072
|
$557
|
$0
|
$0
|
$0
|
$0
|
$0
|
|
|
Princ. repaid
|
|
$5,114
|
$5,523
|
$5,965
|
$6,442
|
$6,957
|
$0
|
$0
|
$0
|
$0
|
$0
|
|
|
Rem. Balance
|
$30,000
|
$24,886
|
$19,364
|
$13,399
|
$6,957
|
$0
|
$0
|
$0
|
$0
|
$0
|
$0
|
|
|
|
|
|
BOOK VALUE &
DEPRECIATION
|
|
|
Book
Value (beginning)
|
$50,000
|
$40,000
|
$32,000
|
$25,600
|
$20,480
|
$16,384
|
$13,107
|
$10,486
|
$10,000
|
$10,000
|
|
|
Depreciation
|
|
$10,000
|
$8,000
|
$6,400
|
$5,120
|
$4,096
|
$3,277
|
$2,621
|
$486
|
$0
|
$0
|
|
|
BV(ending)
|
$50,000
|
$40,000
|
$32,000
|
$25,600
|
$20,480
|
$16,384
|
$13,107
|
$10,486
|
$10,000
|
$10,000
|
$10,000
|
|
|
- Debt Outstanding
|
$30,000
|
$24,886
|
$19,364
|
$13,399
|
$6,957
|
$0
|
$0
|
$0
|
$0
|
$0
|
$0
|
|
|
BV: Equity
|
$20,000
|
$15,114
|
$12,636
|
$12,201
|
$13,523
|
$16,384
|
$13,107
|
$10,486
|
$10,000
|
$10,000
|
$10,000
|
|
|
|
|
|
|
|
|
|
|
ANALYTICAL
STATISTICS
|
|
|
PV:
Net Contribution
|
0.141840
|
0.151479
|
0.162081
|
0.173744
|
0.186573
|
0.182441
|
0.178684
|
0.175269
|
0.172165
|
0.141118
|
|
|
|
|
|
WHAT IF?
|
|
|
|
Revenues
|
NPV
|
|
SOME SUGGESTIONS FOR
OTHER SENSITIVITY ANALYSES
|
|
|
-1.00%
|
$54,877
|
|
1. Change the Variable
cost as a % of Revenues
|
|
|
-2.00%
|
$54,211
|
|
2. Change Fixed costs in
$ amounts
|
|
|
-3.00%
|
$53,545
|
|
3. Change the lifetime of
the project
|
|
|
-4.00%
|
$52,879
|
|
4. Change the
depreciation method
|
|
|
-5.00%
|
$52,213
|
|
5. Change the working
capital as % of revenues
|
|
|
-10.00%
|
$48,882
|
|
6. Change working capital
salvage %
|
|
|
-20.00%
|
$42,220
|
|
7. Change borrowing
amount
|
|
|
-25.00%
|
$38,890
|
|
8. Change the discount
rate
|
|
|
-50.00%
|
$22,236
|
|
|
|