Explore BrainMass

# Finance Excel Review Questions

Not what you're looking for? Search our solutions OR ask your own Custom question.

This content was COPIED from BrainMass.com - View the original, and get the already-completed solution here!

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

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
Less: Depreciation
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%
Less: Depreciation
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%
Less: Depreciation
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%
Less: Depreciation
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
Less: Depreciation
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.