Scott McAllen
2/8/13
Finance Excel Spreadsheets
Chapter 1
1.2
Problem: A single cash flow of $932.47 is available now (in period 0). For this cash
flow, the appropriate discount rate / period is 3.9%. What is the period 4 value of this
cash flow?
Methodol
Scott McAllen
2/8/13
Finance Excel Spreadsheets
Chapter 1
1.1
Problem: Chapter 1 deals with cash flows. A single cash flow of $1,673.48 will be
received in 4 periods. For this cash flow the appropriate discount rate / period is 7.8%.
What is the present v
ANNUITY
Present Value
Inputs
Payment
Discount Rate / Period
Number of Periods
Present Value
$142.38
8
4.5%
6
6
5
$734.38
6
Annuity Present Value using a Time Line
Period
Cash Flows
Present Value of Each Cash Flow
Present Value
$734.38
Annuity Present Valu
CORPORATE FINANCIAL PLANNING
Global Impact P2P
Financial Plan
Key Assumptions
Sales Growth Rate
Tax Rate
Int Rate on Short-Term Debt
Int Rate on Long-Term Debt
Dividend Payout Rate
Price / Earnings
2006
Actual
16.1 An Integrated System
2007
Actual
2008
Ac
ANNUITY
Present Value
Inputs
Payment
Discount Rate / Period
Number of Periods
Present Value
$142.38
8
4.5%
6
6
5
$734.38
6
Annuity Present Value using a Time Line
Period
Cash Flows
Present Value of Each Cash Flow
Present Value
$734.38
Annuity Present Valu
Scott McAllen
3/13/13
Finance Excel Spreadsheets
Chapter 4
4.2
Problem: A project requires a current investment of $328.47 and yields future expected
cash flows of $87.39, $134.97, $153.28, $174.99, and $86.41 in periods 1 through 5,
respectively. All fig
Scott McAllen
3/13/13
Finance Excel Spreadsheets
Chapter 4
4.1
Problem: A project requires a current investment of $54.39 and yields future expected
cash flows of $19.27, $27.33, $34.94, $41.76, and $32.49 in periods 1 through 5,
respectively. All figures
Scott McAllen
4/16/13
Finance Excel Spreadsheets
Chapter 3
3.2
Problem: A project requires a current investment of $117.39 and yields future expected
cash flows of $38.31, $48.53, $72.83, $96.31, and $52.18 in periods 1 through 5,
respectively. All figure
Scott McAllen
4/16/13
Finance Excel Spreadsheets
Chapter 3
3.1
Problem: A project requires a current investment of $189.32 and yields future expected
cash flows of $45.19, $73.11, 98.54, $72.83, and $58.21 in periods 1 through 5,
respectively. All figures
PROJECT NPV
Basics
(in thousands of $)
Year 0
Year 1
Year 2
Year 3
Year 4
Year 5
Year 6
Year 7
Key Assumptions
Unit Sales
Inflation Rate
Real Cost of Capital
Tax Rate
5700
1.5%
9.3%
42.0%
8174
1.8%
9.5%
42.0%
11720
2.1%
9.7%
42.0%
16807
2.4%
9.9%
42.0%
24
PresentationScoringSheet:
onAttachedassessmentmeasures(1to4)
YourName:
Scoreona(1to10Scale)
ScottMcAllen
Content
Waspresenter
Coherent&
Knowledge
(beyondthecase Didtheyconveyasense
Organized
Ahlers, Michael S.
Arjona, Carlos R.
Burkland, Alexander W.
Dani
DEBT AND EQUITY VALUATION
Inputs
Value of Firm (V)
Firm Asset Std Dev ()
Risk-free Rate (kRF )
Two Methods
Analogous Inputs for a Black-Scholes Call Option on a Stock
$780.00 Stock Price
43.0% Stock Std Dev
Face Value of Debt (B)
Time to Maturity (t)
3.2%
REAL OPTIONS
NPV Correctly vs. NPV Ignoring Option
Call
Land Cost = Cost of Real Option
$0.70
4
Date 1 Expected Asset Value
$13.40
60
1
Discount Rate
12.15%
138
$12.60
26
Outputs
30.00%
30
Time / Period
0.175
4.70%
47
Riskfree Rate / Period
$0.01
$4.50
45
BREAK-EVEN ANALYSIS
Based On NPV
(in thousands of $)
Year 0
Year 1
Year 2
Year 3
Year 4
Year 5
Year 6
Year 7
Key Assumptions
Sales Growth Rate
Change in Sales Growth Rate
Inflation Rate
Real Cost of Capital
Tax Rate
72.0%
2.7%
10.2%
38.0%
2.5%
11.2%
38.0%
COST-REDUCING PROJECT
Basics
(in thousands of $)
Year 0
Year 1
Year 2
Year 3
Year 4
Year 5
Key Assumptions
Inflation Rate
Real Cost of Capital
Real Growth Rate
Tax Rate
2.6%
9.5%
41.0%
2.3%
9.3%
21.3%
41.0%
1.9%
9.1%
13.0%
41.0%
1.6%
9.0%
9.0%
41.0%
1.4%
CAPITAL STRUCTURE
Inputs
Debt Value
Equity Value
Unlevered Cost of Equity Capital
Cost of Debt
Modigliani-Miller With No Taxes
$2,900
$3,400
14.50%
8.30%
Data Table: Cost of Capital by Debt Value
Output Formulas:
Debt / Equity
Cost of Equity
WACC
Cost of
US Treasury Zero-Coupon Yield Curve
5.0%
4.5%
4.0%
3.5%
3.0%
Yield T o M at urit y (Annua l.)
2.5%
2.0%
1.5%
1.0%
0.5%
0.0%
0
5
10
15
T ime T o M at urit y (Ye a rs)
20
25
Column D
Column E
30
THE YIELD CURVE
Yield Curve Inputs
Today's Date
One Month Trea
7 .1 Static CAPM Using Fama-MacBeth Method
Input s
3
Asset Type
3
Stock
Barrick
Stock
IBM
Stock
KEP
Stock
Nokia
Stock
Telefonos
Stock
YPF
Mont hly Re t urns
Dec 2009
Nov 2009
Oct 2009
Sep 2009
Aug 2009
Jul 2009
Jun 2009
May 2009
Apr 2009
Mar 2009
Feb 2009
NPV USING CONSTANT DISCOUNTING
Nominal and Real Rates
(in thousands of $)
Inputs
Inflation Rate
Real Discount Rate
3.0%
3
6.300%
4
Outputs
Nominal Discount Rate
Net Present Value using a Time Line
Period
Cash Flows
Present Value of Each Cash Flow
Net Pres
NPV USING GENERAL DISCOUNTING
Nominal and Real Rates
(in thousands of $)
NPV Using General Discounting
60
0
40
20
0
-20
Cash Flows
-40
Present Value of Each Cash Flow
-60
-80
-100
-120
0
Inputs
Period
0
Cash Flows
Cumulative Discount Factor
Present Value
ANNUITY
Present Value
Inputs
Payment
Discount Rate / Period
Number of Periods
$80.00
6.0%
6
5
Annuity
8
5
140
120
100
80
Cash Flows
Present Value of Each Cash Flow
60
Present Value
$336.99
40
6
20
0
0
1
2
3
4
5
Period
Annuity Present Value using a Time Li
LIFE-CYCLE FINANCIAL PLANNING
Input s
Real Riskfree Rate
Retirement Age
3.0%
30
70
Basics
Real Salary
Real Salary & Consumpt ion
Over The Life-Cycle
Real Consumption
$150,000
Re al $100,000 n
Sa l & Co
$50,000
$0
-$50,000
30
40
50
60
Age
70
80
90
Real Fin
DU PONT SYSTEM OF RATIO ANALYSIS
Basics
Inputs
1.2
Net Profit
$170
17
Pretax Profit
$260
26
EBIT
$470
47
0.6
Sales
$4,600
46
0.4
Assets
$4,200
42
Components of ROE
1
Equity
Outputs
ROE = Net Profit / Equity
Components of ROE:
Net Profit / Pretax Profit
Pr
ANNUITY
Present Value
Inputs
Payment
Discount Rate / Period
Number of Periods
$80.00
6.0%
6
5
Annuity
8
5
140
120
100
80
Cash Flows
Present Value of Each Cash Flow
60
Present Value
$336.99
40
6
20
0
0
1
2
3
4
5
Period
Annuity Present Value using a Time Li
PROJECT NPV
Basics
(in thousands of $)
Year 0
Year 1
Year 2
Year 3
Year 4
Year 5
Year 6
Year 7
Key Assumptions
Unit Sales
Inflation Rate
Real Cost of Capital
Tax Rate
2000
2.0%
11.0%
35.0%
4000
2.5%
11.2%
35.0%
5600
3.0%
11.4%
35.0%
6800
3.5%
11.6%
35.0%