You have been hired as a financial consultant by BUBBA corporation. BUBBA is con
ID: 2721594 • Letter: Y
Question
You have been hired as a financial consultant by BUBBA corporation. BUBBA is considering investing in a new machine to produce dog biscuits. BUBBA has provided you with the following information:
Full price of machine is $115,000.
BUBBA has a 30% average tax rate. BUBBA has a 40% marginal tax rate.
The machine falls into the MACRS 3-year class.
BUBBA will use the machine for 5 years and then plans to sell it for $10,000 at the end of year 5.
The machine is expected to increase earnings before depreciation by $35,000 a year for the life of the machine.
BUBBA uses a capital structure with 50% common equity and 50% debt.
The before tax cost of debt is 8%. The cost of retained earnings is 12%.
Create a MS-Excel spreadsheet to calculate the NPV (this sheet should include cash flows in years 4 and 5 and the WACC should be a formula based on the weight of debt, cost of debt, weight of equity and cost of retained earnings). The spreadsheet will be used by the BUBBA managers to assist them in making the investment decision. The spreadsheet should be set up to allow for a sensitivity analysis to be conducted. The cells to input the full price, increased earnings, sale price in year 5, weight of debt, weight of equity, cost of debt and cost of retained earnings should be easily identified and allow the BUBBA managers to change their values.
Explanation / Answer
Calculation of WACC:
Formula :
WACC = (Cost of Debt * weight of debt) + (Cost of Equity * weight of Equity )
Cost of Debt = before tax cost of debt * (1-tax rate )
= 8% * (1-40%)
= 4.8%
WACC = (4.8%*50%) + (12% * 50%)
= 8.4%
Calculaton od NPV using WACC as Discount Rate (PVF):
Now
Year 1
Year 2
Year 3
Year 4
Year 5
Cost of Machine (A)
$(115,000.00)
Tax Saving on Depreciation:
Depreciation Rate (MACRS 3 Year )
$ 0.33
$ 0.44
$ 0.15
$ 0.07
Depreciattion = Cost * Depreciation Rate
$ 38,329.50
$51,117.50
$ 17,031.50
$ 8,521.50
Tax Saving on Depreciation = Depreciation * 40% (B)
$ 15,331.80
$20,447.00
$ 6,812.60
$ 3,408.60
Sale value of Machine at end of year 5 (Net of tax) = 10000*(1-40%) = (C)
$ 6,000.00
Incremental earnings before depreciation (Net of Tax) = 35000*(1-40%) = (D)
$ 21,000.00
$21,000.00
$ 21,000.00
$ 21,000.00
$ 21,000.00
Net Cash Flows (A+B+C+D)
$(115,000.00)
$ 36,331.80
$41,447.00
$ 27,812.60
$ 24,408.60
$ 27,000.00
PVF (8.4%)
1
0.922509225
0.85102327
0.785076818
0.724240607
0.668118641
PV = (Net Cash Flows *PVF )
$(115,000.00)
$ 33,516.42
$35,272.36
$ 21,835.03
$ 17,677.70
$ 18,039.20
NPV = Sum of PVs =
$ 11,340.71
Calculation of WACC:
Formula :
WACC = (Cost of Debt * weight of debt) + (Cost of Equity * weight of Equity )
Cost of Debt = before tax cost of debt * (1-tax rate )
= 8% * (1-40%)
= 4.8%
WACC = (4.8%*50%) + (12% * 50%)
= 8.4%
Calculaton od NPV using WACC as Discount Rate (PVF):
Now
Year 1
Year 2
Year 3
Year 4
Year 5
Cost of Machine (A)
$(115,000.00)
Tax Saving on Depreciation:
Depreciation Rate (MACRS 3 Year )
$ 0.33
$ 0.44
$ 0.15
$ 0.07
Depreciattion = Cost * Depreciation Rate
$ 38,329.50
$51,117.50
$ 17,031.50
$ 8,521.50
Tax Saving on Depreciation = Depreciation * 40% (B)
$ 15,331.80
$20,447.00
$ 6,812.60
$ 3,408.60
Sale value of Machine at end of year 5 (Net of tax) = 10000*(1-40%) = (C)
$ 6,000.00
Incremental earnings before depreciation (Net of Tax) = 35000*(1-40%) = (D)
$ 21,000.00
$21,000.00
$ 21,000.00
$ 21,000.00
$ 21,000.00
Net Cash Flows (A+B+C+D)
$(115,000.00)
$ 36,331.80
$41,447.00
$ 27,812.60
$ 24,408.60
$ 27,000.00
PVF (8.4%)
1
0.922509225
0.85102327
0.785076818
0.724240607
0.668118641
PV = (Net Cash Flows *PVF )
$(115,000.00)
$ 33,516.42
$35,272.36
$ 21,835.03
$ 17,677.70
$ 18,039.20
NPV = Sum of PVs =
$ 11,340.71
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.