Explore BrainMass

Explore BrainMass

    Finance Excel Review Questions

    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
    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.

    © BrainMass Inc. brainmass.com June 4, 2020, 1:06 am ad1c9bdddf
    https://brainmass.com/business/finance/finance-excel-review-questions-378784

    Attachments

    Solution Summary

    The solution provides finance review questions already in Excel format.

    $2.19

    ADVERTISEMENT