Project-DCF-Calculation-Blank

Document Sample
Project-DCF-Calculation-Blank Powered By Docstoc
					Version 0 - Blank Version of Project DCF calculation.

This is a blank version of a resonably generic project valuation problem.

•The setup has a seperate worksheet for market analysis, which allows the project to be set up arround
assumptions about the market the project will compete in.

•Free Cash Flows and Capital and Depreciation are given their own worksheets.

•WACC and capital structure is given its own worksheet. That worksheet may be left blank, and the valuation
just tied to a specific discount rate.



Implemented Already

•The only coded items are the names assigned in the input boxes.
Inputs                                      Outputs
Operations Assumptions                      NPV
Price Per Unit                         5    IRR
Cost per Unit                          4    Payback Period
Fixed Costs                         1000
NWC for sales                        5%


Financing Assumptions
Discount Rate                         7%
Marginal Tax Rate                    35%

Scooter Market Assumptions
2009 Sales                         131000
2010-2012 Growth Rate                15%
Long Term Growth Rate                 5%
Maximum Market Share                 20%
Year until Max                          3

Capital Assets Assumptions
Initial Capital                    100000
MACRS Asset Class                      15
Salvage Value of Assets (Market)    10000




Free Cash Flows
OCF
ΔNWC
CapEx
FCF
PVF
PV

Cumulative Undiscounted CF
Period               0      1      2      3      4      5
Year              2009   2010   2011   2012   2013   2014
Operating CF
 Sales
 VariableCosts
 Fixed Costs
EBITDA
 Depreciation
EBIT
 Taxes
Net Income
 Depreciation
Operating CF




Free Cash Flows
OCF
∆NWC
CAPEX

Total FCF
PVF

Present Value
   6      7      8      9     10
2015   2016   2017   2018   2019
Period                                   0      1      2      3      4      5
Year                                  2009   2010   2011   2012   2013   2014
Capital Assets and Depreciation
Book Value
Depreciation Factor
Depreciation
Market Value at Project Termination
Taxes on Final Sale
Gain from Terminal Sale
CapEx CF

NWC
Working Capital
Change in Working Capital
   6      7      8      9     10
2015   2016   2017   2018   2019
Period                   0      1      2      3      4      5
Year                  2009   2010   2011   2012   2013   2014
Market for Scooters
US
Our Market Share
Out Units Sold
   6      7      8      9     10
2015   2016   2017   2018   2019
Cost of Capital Inputs
Market Risk Premium                           2%
Nominal Risk Free Rate                        6%




Debt (Currently in the Company)
                            Loan 1                 Loan 2
Ammount
Rate

Average Cost of Debt

Equity (Comparables)
Comparables                                                 Own Stock

                               Beta
                               Leverage (V/E)
                               Unlevered Beta
E[Return] Asset




WACC

                               Equity
                               Cost of Equity

                               Debt
                               Cost of Debt

                               WACC


Resulting Capitral Structure Ratios
                               D/V
                               E/V
                               D/E
MACRS Schedules
                                          Expected Property Life
               Recovery
               Year           3       5           7          10       15      20
Index Number                  2       3           4           5        6       7
                      1   33.3%   20.0%       14.3%      10.0%     5.0%    3.8%
                      2   44.5%   32.0%       24.5%      18.0%     9.5%    7.2%
                      3   14.8%   19.2%       17.5%      14.4%     8.6%    6.7%
                      4    7.4%   11.5%       12.5%      11.5%     7.7%    6.2%
                      5    0.0%   11.5%        8.9%       9.2%     6.9%    5.7%
                      6    0.0%    5.8%        8.9%       7.4%     6.2%    5.3%
                      7    0.0%    0.0%        8.9%       6.6%     5.9%    4.9%
                      8    0.0%    0.0%        4.5%       6.6%     5.9%    4.5%
                      9    0.0%    0.0%        0.0%       6.6%     5.9%    4.5%
                     10    0.0%    0.0%        0.0%       6.6%     5.9%    4.5%
                     11    0.0%    0.0%        0.0%       3.3%     5.9%    4.5%
                     12    0.0%    0.0%        0.0%       0.0%     5.9%    4.5%
                     13    0.0%    0.0%        0.0%       0.0%     5.9%    4.5%
                     14    0.0%    0.0%        0.0%       0.0%     5.9%    4.5%
                     15    0.0%    0.0%        0.0%       0.0%     5.9%    4.5%
                     16    0.0%    0.0%        0.0%       0.0%     3.0%    4.5%
                     17    0.0%    0.0%        0.0%       0.0%     0.0%    4.5%
                     18    0.0%    0.0%        0.0%       0.0%     0.0%    4.5%
                     19    0.0%    0.0%        0.0%       0.0%     0.0%    4.5%
                     20    0.0%    0.0%        0.0%       0.0%     0.0%    4.5%
                     21    0.0%    0.0%        0.0%       0.0%     0.0%    2.2%

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:26
posted:1/9/2011
language:English
pages:14