Finance Excel Review Questions

See the attached file.
REVIEW QUESTIONS ALREADY IN EXCEL FORMAT.

Given Solution Legend
HI Oil Dec-09 Dec-08 = Value given in problem
Sales $13,368.00 $12,211.00 = Formula/Calculation/Analysis required
Cost of Goods Sold (10,591.00) (9,755.00) = Qualitative analysis or Short answer required
Gross Profit 2,777.00 2,456.00 = Goal Seek or Solver cell
Selling, General, & Administrative Expense (698.00) (704.00) = Crystal Ball Input
Operating Income Before Deprec. 2,079.00 1,752.00 = Crystal Ball Output
Depreciation, Depletion, & Amortization (871.00) (794.00)
Operating Profit 1,208.00 958.00
Interest Expense (295.00) (265.00)
Non-Operating Income/Expense 151.00 139.00
Special Items - 20.00
Pretax Income 1,064.00 852.00
Total Income Before Taxes (425.60) (340.80)
Net Income $638.40 $511.20

Purchase of PP&E (CAPEX) 1,322 875
Increase in Net Working Capital (430) 102

TCM's average tax rate 40% 40%

Solution
a. PFCF Calculations for 2008-2009 Year
2009 2008
EBIT
EBIT(1-T) = NOPAT
Plus: Depreciation Expense
Less: CAPEX
Less: Working Capital Investment
Project Free Cash Flow - $- 0

b. Estimated PFCF for 2009-2014 Year
2009 2010 2011 2012 2013 2014
EBIT (Growing at 10% per year)
EBIT(1-.40) = NOPAT - -
Plus: Depreciation Expense 88 (13) (113) (213) (313) (413)
Less: CAPEX (1,000) (1,000) (1,000) (1,000) (1,000) (1,000)
Less: Working Capital Investment (100) (100) (100) (100) (100) (100)
Project Free Cash Flow

CALCULATIONS ALL VALUES IN $MILLIONS
A) Dec-09 Dec-08
Revenues
Less: Cost of Goods Sold
Equals: Gross Profits
Less: Operating Expenses excluding depreciation and amortization expense
Selling,General and Administrative Expense

Equals: Earnings before Interest,taxes,depreciation, amortization (EBITDA)
Less:Depreciation and Amortization ( DA)
Equals: Earnings before interest and Taxes. (EBIT) $0 $0
Less: Taxes
Equals: Net Operating Profit after Taxes. ( NOPAT)
Plus Depreciation and Amortization. (DA)
$0 $0
Less: Capital expenditures. (CAPEX)
Less: Increases in net Working capital (WC)
Equals Projected Free Cash Flow ( PFCF) $0 $0

The Major discrepancy is the Depreciation,Depletion and Amortization Expense
-------------------------------------

PROBLEM 2

Given MACRS Depreciation:
Investment cost (today) $(700,000) Year 1 33.33%
Project life 5 years Year 2 44.45%
Salvage Value 25% of original cost Year 3 14.81%
Waste disposal cost savings per year $40,000 Year 4 7.41%
Labor cost savings per year $45,000
Sale of reclaimed plastic waste $200,000
Required rate of return 18%
Tax rate 40%
Where The Value of the amount Initially Invested 700,000
Solution
Part a. Year
Cash flow estimation 0 1 2 3 4 5
Investment $(700,000)
Return on Investment at 18%. Revenues $126,000 $126,000 $126,000 $126,000 $126,000
Waste disposal cost savings per year
Labor cost savings per year
Proceeds from sale of reclaimed waste materials
Additional EBITDA
Less: Depreciation
Additional EBIT
Less: Taxes at 40%
NOPAT
Plus: Depreciation
Cash from Salvage Value 175,000
FFCF $- $- $- $-

NPV 0
IRR
Analysis

Part B: Probability Distribution of reclaimed plastic sales: NPV EXPTD NPV
Remain as projected 30% 352,068 105620.4
Decrease by 10% 40% 314,452 125780.8
Decrease by 30% 20% 248,872 49774.4
Decrease by 50% 10% 173,819 17381.9
298557.5
i) Scenario-1: 10% decrease in sales of reclaimed plastic waste:
Year
Cash flow estimation 0 1 2 3 4 5
Investment $(700,000)
Return on Investment at 18%. Revenues 126,000 126,000 126,000 126,000 126,000
Waste disposal cost savings per year
Labor cost savings per year
Proceeds from sale of reclaimed waste materials. 90%
Additional EBIT
Less: Depreciation
Additional EBIT
Less: Taxes
NOPAT
Plus: Depreciation
Cash from Salvage Value $175,000
FFCF $- $- $- $-

NPV 0
IRR

ii) Scenario-1: 30% decrease in sales of reclaimed plastic waste:
Year
Cash flow estimation 0 1 2 3 4 5
Investment $(700,000)
Return on Investment at 18%. Revenues $126,000 $126,000 $126,000 $126,000 $126,000
Waste disposal cost savings per year
Labor cost savings per year
Proceeds from sale of reclaimed waste materials. 70%
Additional EBIT
Less: Depreciation
Additional EBIT
Less: Taxes
NOPAT
Plus: Depreciation
Cash from Salvage Value $175,000
FFCF $0 $- $- $-

NPV 0
IRR

iii) Scenario-1: 50% decrease in sales of reclaimed plastic waste:
Year
Cash flow estimation 0 1 2 3 4 5
Investment $(700,000)
Return on Investment at 18%. Revenues $126,000 $126,000 $126,000 $126,000 $126,000
Waste disposal cost savings per year
Labor cost savings per year
Proceeds from sale of reclaimed waste materials 50%
Additional EBITDA
Less: Depreciation
Additional EBIT
Less: Taxes
NOPAT
Plus: Depreciation
Cash from Salvage Value $175,000
FFCF $0 $0 $0 $0 $175,000

NPV
IRR

Expected NPV: 298,557.50

B-E Sales of Reclaimed Plastic Waste: Using Goal Seek

Cash flow estimation 0 1 2 3 4 5
Investment
Waste disposal cost savings per year
Labor cost savings per year
Proceeds from sale of reclaimed waste materials
Additional EBITDA
Less: Depreciation
Additional EBIT
Less: Taxes
NOPAT
Plus: Depreciation
Cash from Salvage Value
FFCF

NPV
IRR

C. At what sales volume of reclaimed waste materials, Argyl Manufacturing would have a break-even NPV=0?
D. Imagine that after observing the first year sales, the company can terminate the project and sell the machine at 65% of its original value. Under given circumstances, does this option to terminate a project have additional value? Will it affect investment decision? Why/why not?

------------------------------------------

PROBLEM 5-2

Given Solution Legend
Debt Ratio (current) 30.0% = Value given in problem
Equity Ratio (current) 70.0% = Formula/Calculation/Analysis required
Cost of Debt 9.0% = Qualitative analysis or Short answer required
Market Risk Premium 12.0% = Goal Seek or Solver cell
Equity Beta 1.8
Debt Beta 0.6
Risk Free Rate 3.0%
Corporate Tax Rate 30.0%
New Equity Ratio 80%
New Debt Ratio 20%
Solution
a. Cost of Equity
b. WACC
c. New WACC
Old D/E
Unlevered beta (current debt levels)
New D/E
Revised Equity Beta
Cost of Equity
Proportion of Debt in the Project
Proportion of Equity in the Project
C. Revised WACC

-----------------------

Multiples Valuation

Given Solution Legend
EXHIBIT 1 = Value given in problem
Financial Information CoachTraveler VirtualExplorer Home World Stay-at-Home = Formula/Calculation/Analysis required
Shares Outstanding 150,000 600,000 500,000 1,000,000 = Qualitative analysis or Short answer required
Stock Price $12.00 $24.00 $34.00 $35.00 = Goal Seek or Solver cell
Market Capitalization $1,800,000 $14,400,000 $17,000,000 $35,000,000 = Crystal Ball Input
Short Term Debt $15,000 $- $200,000 $- = Crystal Ball Output
Long Term Debt $- $2,750,000 $1,245,000 $-
Cash & Equivalents $400,000 $700,000 $1,500,000 $4,000,000
Short Term Investments $90,000 $600,000 $250,000 $5,000,000
EBITDA $218,100 $395,300 $450,000 $1,540,000
Net Income $(40,500) $237,900 $291,800 $894,500
Calculated EPS

Solution
Average Modified average
Price to Earnings
Enterprise Value
EBITDA multiple
D/E.

Free Travel
EBITDA $400,000
Cash $600,000
Long Term Debt $1,000,000
Net income $265,000
Shares 300,000
EPS $0.88

CoachTraveler VirtualExplorer Home World Stay-at-Home Average Modified average
Imputed IPO price per share from PE ratio
Impute EV from EBITDA multiples
Owner's equity
Impute IPO price per share

D/E

a.
b.
c.
d.