Purchase Solution

# Finance Excel Review Questions

Not what you're looking for?

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.

##### Solution Summary

The solution provides finance review questions already in Excel format.

Solution provided by:
###### Education
• MBA, Indian Institute of Finance
###### Recent Feedback
• "I've posted a similar question for another course. It's post 657940, and it's a practice problem that I'd like to use for the final exam. Your help will be greatly appreciated. "
• "thank you!"
• "Thank you again Jayant. You are super fast. "
• "Thank you Jayant. You are appreciated. "
• "Again, thank you Jayant. You are wonderful. "

##### Cost Concepts: Analyzing Costs in Managerial Accounting

This quiz gives students the opportunity to assess their knowledge of cost concepts used in managerial accounting such as opportunity costs, marginal costs, relevant costs and the benefits and relationships that derive from them.

##### Operations Management

This quiz tests a student's knowledge about Operations Management