Project Risk Analysis -Sensitivity Analysis

profilexoon
chapter03_s.haizel.xls
Home>Business & Finance homework help>Project Risk Analysis -Sensitivity Analysis

CB_DATA_

Problem 3-1

PROBLEM 3-1: Clayton Manufacturing Company
Given Solution Legend
EBITDA (Year 1) $ 200,000 = Value given in problem
Growth Rate in EBITDA 5% = Formula/Calculation/Analysis required
Initial investment $ 800,000 = Qualitative analysis or Short answer required
Depreciation (Straight line) over 5 years = Goal Seek or Solver cell
Estimated salvage value $ - = Crystal Ball Input
Tax rate 35% = Crystal Ball Output
Cost of capital 12%
Solution
Years
a. 0 1 2 3 4 5
EBITDA $ 200,000
Less: Depreciation Expense
EBIT
Less: Taxes
NOPAT
Plus: Depreciation Expense
Less: CAPEX - - - -
Less: Change in Working Capital - - - - - -
Project FCF
b.
NPV
c.
Using "Goal Seek" to solve for the EBITDA in year 1 (C5) that yields a NPV of 0 (C28).
Breakeven Year 1 EBITDA

Problem 3-2

PROBLEM 3-2: Breakeven Sensitivity Analysis
Given Solution Legend
Investment (enter with "-" sign) $ (4,000,000) = Value given in problem
Plant life 5 Years = Formula/Calculation/Analysis required
Salvage value $ 400,000 = Qualitative analysis or Short answer required
Variable Cost % 45% = Goal Seek or Solver cell
Fixed operating cost $ 1,000,000 = Crystal Ball Input
Tax rate 38% = Crystal Ball Output
Working capital 10% (Percent of the expected change in revenues for the year)
Required Rate of Return 15%
Sales volume multiple 1.00
Year
0 1 2 3 4 5
Sales volume $ 1,000,000 $ 1,500,000 $ 3,000,000 $ 3,500,000 $ 2,000,000
Unit price 2.00 2.00 2.50 2.50 2.50
Revenues 2,000,000 3,000,000 7,500,000 8,750,000 5,000,000
Variable Operating Costs (900,000) (1,350,000) (3,375,000) (3,937,500) (2,250,000)
Fixed Operating Costs (1,000,000) (1,000,000) (1,000,000) (1,000,000) (1,000,000)
Depreciation Expense (800,000) (800,000) (800,000) (800,000) (800,000)
Net Operating Income $ (700,000) $ (150,000) $ 2,325,000 $ 3,012,500 $ 950,000
Less: Taxes 266,000 57,000 (883,500) (1,144,750) (361,000)
NOPAT $ (434,000) $ (93,000) $ 1,441,500 $ 1,867,750 $ 589,000
Plus: Depreciation 800,000 800,000 800,000 800,000 800,000
Less: CAPEX (4,000,000) - - - - 248,000
Less: Working Capital (200,000) (100,000) (450,000) (125,000) 375,000 500,000
Free Cash Flow $ (4,200,000) $ 266,000 $ 257,000 $ 2,116,500 $ 3,042,750 $ 2,137,000
NPV $ 419,435
IRR 18%
Equivalent Annual Cost $ 125,124
Solution
a. What are the key sources of risk that you see in this project?
b. Breakeven sensitivity analysis
Estimated Value Breakeven Value Percent Difference
Variable
Initial Capex
Variable Cost as a % of Sales 49%
Working Capital % of new Sales 27%
Sales volume multiplier 1 0.92
c. Discuss results of part b.
d. Should you always seek to reduce project risk?

Problem 3-3ab

PROBLEM 3-3ab: Bridgeway Pharmaceuticals
Given Solution Legend
Investment cost (today) $ (400,000) = Value given in problem
Project life 5 years = Formula/Calculation/Analysis required
Depreciation expense $ 80,000 = Qualitative analysis or Short answer required
Waste disposal cost savings per year $ 18,000 = Goal Seek or Solver cell
Labor cost savings per year $ 40,000 = Crystal Ball Input
Sale of reclaimed waste $ 200,000 = Crystal Ball Output
Required rate of return 20%
Tax rate 35%
Solution
Part a. Year
Cash flow estimation 0 1 2 3 4 5
Investment $ (400,000)
Waste disposal cost savings per year
Labor cost savings per year
Proceeds from sale of reclaimed waste materials
EBITDA
Less: Depreciation
Additional EBIT
Less: Taxes
NOPAT
Plus: Depreciation
Less: Capex - - - - -
Less: Additional working capital - - - - -
FCF
NPV
IRR
Analysis
b.
If sale of reclaimed waste drops in half, NPV
Critical B-E for sale of waste materials
Critical B-E Price decline in salvage materials
c. See next worksheet
The terminal period growth rates were estimated such that the intrinsic valuation of the firm's equity would equal the current market capitalization of the firm using the "Goal Seek" function.
To answer part b. simply substitute $100,000 for the sale of reclaimed waste in C10.
Solver has been used to find this answer. Details given in text box above.

Problem 3-3c

PROBLEM 3-3c: Bridgeway Pharmaceuticals
Given Solution Legend
Investment cost (today) $ (400,000) = Value given in problem
Project life 5 years = Formula/Calculation/Analysis required
Depreciation expense $ 80,000 = Qualitative analysis or Short answer required
Waste disposal cost savings per year $ 18,000 = Goal Seek or Solver cell
Labor cost savings per year $ 40,000 = Crystal Ball Input
Sale of reclaimed waste $ 200,000 = Crystal Ball Output
Required rate of return 20%
Tax rate 35%
Correlation (Year to year) in Proceeds from reclaimed waste 0.90
Solution
c. Year
Cash flow estimation 0 1 2 3 4 5
Investment
Waste disposal cost savings per year
Labor cost savings per year 40,000 40,000 40,000 40,000 40,000
Proceeds from sale of reclaimed waste
EBITDA
Less: Depreciation
Additional EBIT
Less: Taxes
NOPAT
Plus: Depreciation
Less: Capex - 0 - 0 - 0 - 0 - 0
Less: Additional working capital - 0 - 0 - 0 - 0 - 0
FCF
NPV
IRR
Part i.
Part ii.
Part iii.
Note: Your results from the simulation experiment will differ slightly from those reported here where you did not use the same "seed" value for the random number generator. In fact, if you do not "fix" the same seed value for each simulation your results will differ slightly from one simulation of the same problem to another (see Run Preferences/Sampling).

Problem 3-4

PROBLEM 3-4: TitMar Motor Company
Given Solution Legend
Assumptions and Predictions Estimates = Value given in problem
Price per unit $ 4,895 Part a. Substitute 5% for market share (%) . = Formula/Calculation/Analysis required
Market share (%) 15.00% Part b. Substitute $4,500 for the price per unit. = Qualitative analysis or Short answer required
Market size (Year 1) $ 200,000 = Goal Seek or Solver cell
Growth rate in market size beginning in Year 2 5.00% = Crystal Ball Input
Unit variable cost $ 4,250 = Crystal Ball Output
Fixed cost $ 9,000,000
Tax rate 50.00%
Cost of capital 18.00%
Investment in NWC 5.00% of the predicted change in firm revenues.
Initial investment in PP&E $ 7,000,000
Depreciation (5 year life w/no salvage) $ 1,400,000
Solution
Year
0 1 2 3 4 5
Investment $ (7,000,000)
Revenue 146,850,000 154,192,500 161,902,125 169,997,231 178,497,093
Variable Cost (127,500,000) (133,875,000) (140,568,750) (147,597,188) (154,977,047)
Fixed cost (9,000,000) (9,000,000) (9,000,000) (9,000,000) (9,000,000)
Depreciation (1,400,000) (1,400,000) (1,400,000) (1,400,000) (1,400,000)
EBT(Net Operating Income) $ 8,950,000 $ 9,917,500 $ 10,933,375 $ 12,000,044 $ 13,120,046
Tax (4,475,000) (4,958,750) (5,466,688) (6,000,022) (6,560,023)
Net Operating Profit after Tax (NOPAT) $ 4,475,000 $ 4,958,750 $ 5,466,688 $ 6,000,022 $ 6,560,023
Plus: Depreciation expense 1,400,000 1,400,000 1,400,000 1,400,000 1,400,000
Less: Capex (7,000,000) - - - - -
Less: Change in NWC (7,342,500) (367,125) (385,481) (404,755) (424,993) 8,924,855
Free Cash Flow $ (14,342,500) $ 5,507,875 $ 5,973,269 $ 6,461,932 $ 6,975,029 $ 16,884,878
Net Present Value $ 9,526,209
Internal Rate of Return 39.82%
Units Sold 30,000 31,500 33,075 34,729 36,465
a. If the market share is only 5% then the project's NPV =
b. If market share = 15% and the price of the PTV falls to $4,500 the NPV =
Breakeven Sensitivity Analysis Critical % Change Critical Value
Price per unit
Market share (%)
Market size (Year 1)
Growth rate in market size beginning in Year 2
Unit variable cost
Fixed cost
Tax rate
Cost of capital
Investment in NWC
Analysis:

Problem 3-5

PROBLEM 3-5: TitMar Motor Company
Given Solution Legend
Assumptions and Predictions Estimates = Value given in problem
Price per unit $ 4,895 = Formula/Calculation/Analysis required
Market share (%) = Qualitative analysis or Short answer required
Market size (Year 1) 200,000 = Goal Seek or Solver cell
Growth rate in market size beginning in Year 2 5.00% = Crystal Ball Input
Unit variable cost $ 4,250 = Crystal Ball Output
Fixed cost $ 9,000,000
Tax rate 50.0%
Cost of capital 18.00%
Investment in NWC 5.00% of the predicted change in firm revenues.
Initial investment in pp&e $ 7,000,000
Depreciation (5 year life w/no salvage) $ 1,400,000
Solution
Year
0 1 2 3 4 5
Investment - 0 - 0 - 0 - 0 - 0
Growth rate in market size
Market Size (total PTV sold)
Market Share (units sold by Titmar)
Revenue
Variable Cost
Fixed cost
Depreciation
EBT(Net Operating Income)
Tax
Net Operating Profit after Tax (NOPAT)
Plus: Depreciation expense
Less: Capex
Less: Change in NWC
Free Cash Flow
Net Present Value
Internal Rate of Return

Problem 3-6

PROBLEM 3-6: Biolizer Problem--Decision Tree
Given
EPA after-tax cost $ 80,000
Abandonment Value $ 350,000
Probability of Good EPA Ruling 80%
Solution Solution Legend
Panel a. No Option to Abandon = Value given in problem
2010 2011 2012 2013 2014 2015 = Formula/Calculation/Analysis required
Favorable EPA Ruling--Expected Project FCFs $ (580,000) $ 87,600 $ 78,420 $ 93,320 $ 109,710 $ 658,770 = Qualitative analysis or Short answer required
NPV (Favorable EPA Ruling) = = Goal Seek or Solver cell
= Crystal Ball Input
Unfavorable EPA Ruling--Expected FCFs = Crystal Ball Output
NPV (Unfavorable EPA Ruling)
Revised Expected Project FCFs
E[NPV] with No Option to Abandon
Panel b. Option to Abandon
2010 2011 2012 2013 2014 2015
Project Not Abandoned (Favorable EPA)
NPV (Favorable EPA Ruling) =
Project Abandoned (Unfavorable EPA) $ - $ - $ - $ -
NPV (Unfavorable EPA Ruling)
Revised Expected Project FCFs
E[NPV] with the Option to Abandon
Analysis:

Problem 3-7

PROBLEM 3-7: Introductory Simulation Analysis Exercises
a. Jason Enterprises
Given Solution Legend
Operating Earnings/Sales 25% = Value given in problem
Sales (upper limit) $ 10,000,000 = Formula/Calculation/Analysis required
Sales (lower limit) $ 7,000,000 = Qualitative analysis or Short answer required
= Goal Seek or Solver cell
Solution = Crystal Ball Input
Forecasted Sales = Crystal Ball Output
Operating Earnings
b. Aggiebear Dog Snacks, Inc.
Given
Revenues Minimum $ 18,000,000
Most likely $ 25,000,000
Maximum $ 35,000,000
Cost of Goods sold/Revenues Minimum 70%
Maximum 80%
Solution
Forecasted Sales
Cost of Goods Sold/Sales
Part i-iii.
Sales
Less: Cost of Goods Sold
Operating Earnings

Solution 3-8

PROBLEM 3-8: Rayner Aeronautics
Given Solution Legend
Investment Outlay (Year 0) $ 12,500,000 = Value given in problem
Year 1 Expected Cash Flow $ 2,000,000 = Formula/Calculation/Analysis required
Required Rate of Return 18% = Qualitative analysis or Short answer required
= Goal Seek or Solver cell
= Crystal Ball Input
= Crystal Ball Output
Solution
a.
Break-Even Growth Rate in Cash flows
Year Growth Rate Cash Flows
0 NPV =
1 0
2
3
4
5
b.
Simulation Model
Variable Mean Std. Deviation
Year 1 cash flow Normal distribution
Annual Growth Rates Triangular Distrbution
Year Most likely Minimum Maximum
2 40.00% 20.00% 80.00%
3 40.00% 10.00% 160.00%
4 40.00% 5.00% 320.00%
5 40.00% 2.50% 640.00%
Year Growth Rate Cash Flows
0
1
2
3
4
5
c.
Results of Simulation
NPV
IRR
Expected NPV see mean value in chart below
Expected IRR see mean value in chart below

Problem 3-9

PROBLEM 3-9: ConocoPhillips Natural Gas Wellhead Project
Given
ConocoPhillips's Cost of Capital for project 15.00%
Project life 10 years
Solution Solution Legend
1. Years = Value given in problem
0 1 2 3 4 5 6 7 8 9 10 = Formula/Calculation/Analysis required
Investment $ 1,200,000 = Qualitative analysis or Short answer required
Increase in NWC 145,000 = Goal Seek or Solver cell
MACRS Depr Rate (7 year) 0.1429 0.2449 0.1749 0.1249 0.0893 0.0893 0.0893 0.0445 = Crystal Ball Input
Natural Gas Wellhead Price (per MCF) 6 = Crystal Ball Output
Volume (MCF/day) 900
Days per year 365
Fee to Producer of Natural Gas $3.00
Compression & processing costs (per MCF) 0.65
Cash Flow Calculations
Natural Gas Wellhead Price Revenue
Lease fee expense
Compression & processing costs
Depreciation expense
Net operating Profit
Less: Taxes (40%)
Net operating profit after tax (NOPAT)
Plus: Depreciation expense
Return of net working capital
Project Free Cash Flow
NPV
IRR
2a-c. Scenario Summary
Current Values Best Case Most Likely Case Worst Case
Changing Cells
NG Price 6 8 6 3
Production Rate 900 1200 900 700
Result Cells
NPV
IRR
Notes: Current Values column represents values of changing cells at time Scenario Summary Report was created.
3. Breakeven Sensitivity Analsyis Students should use Goal Seek in Excel to answer this question.
a.
Breakeven nautral gas price for an NPV = 0
b.
Breakeven natural gas volume in Year 1 for an NPV = 0
c.
Breakeven investment for an NPV = 0
4. Student answers will vary but most will probably recommend the project.

Problem 3-10

PROBLEM 3-10: Blended Profile Applied, per Aircraft B737-700
Given Solution Legend
Purchase Cost (pre-installed) $000 $ (700,000) Airframe Maintenance Cost $ (2,100) per year = Value given in problem
Installation $000 $ (56,000) Useful Life (yrs) Average 20 = Formula/Calculation/Analysis required
Downtime Days (installation) 1 Runway Savings $ 500 per year = Qualitative analysis or Short answer required
Downtime Cost/Day $000 $ (5,000) Facility cost $ 1,200 per aricraft = Goal Seek or Solver cell
Salvage % 15.00% Depreciation MACRS (see below) = Crystal Ball Input
Gen. Escalation 3.00% Fuel Price (all-in) $ 0.80 includes delivery, taxes and into plane charges = Crystal Ball Output
Marginal Tax Rate 39.00% Fuel (gallons saved) 178,500
Discount Rate 9.28%
Solution
Year
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
Winglet Purchase
Winglet Installation
Install. Downtime costs
Airport Reconfiguration
Fuel Savings
Airframe Maint. Costs
Reduced restrictions (inflated 3%/yr)
Less: depreciation
EBIT
Less: Income Tax
Net Income
Plus: Depreciation
Operating Cash Flow
Salvage Value
Tax on Salvage Value
Total Project Cash Flow
b.
NPV
IRR
MIRR
DEPRECIATION DETAILS
MACRS Table Normal Table Normal Table x Year 1(a) Additional valid til 9/11/04
50.00% 50.00% Total (modified table) Tax Depr
1 14.29% 7.15% 50.00% 57.15%
2 24.49% 12.25% 12.25%
3 17.49% 8.75% 8.75%
4 12.49% 6.25% 6.25%
5 8.93% 4.47% 4.47%
6 8.92% 4.46% 4.46%
7 8.93% 4.47% 4.47%
8 4.46% 2.23% 2.23%
(a) Job Creation and Worker Assistance Act of 2002
c.
Breakeven fuel cost per gallon
Breakeven fuel savings gallons
d.
Current Values Best Case Worst Case
Changing Cells
Fuel Price $ 0.80 $ 1.10 $ 0.50
Gallons Saved 178,500 214,000 142,000
Result Cells
NPV
IRR
MIRR
Notes: Current Values column represents values of changing cells at time Scenario Summary Report was created.
e.
f. Impact on NPV and IRR if winglets have no salvage value.
NPV
IRR