FIN 630 University of Maryland Comparable Companies Unlevered Beta Excel Worksheet I need a tutor who can find the companies used for the solution on the a

FIN 630 University of Maryland Comparable Companies Unlevered Beta Excel Worksheet I need a tutor who can find the companies used for the solution on the attached excel row 19-27 and complete the EBIT as highlighted by my Professor. ( Comparable companies unlevered Beta), provide the link to the source and correct the answer on excel 91 row by using the formula EBITx (1-tax rate+ depreciation). WACC
1) Complete the table “Comparable Companies Unlevered Beta”. You will be using Yahoo
Finance! Or any other sources. Please provide the reference to the source you are using.
Column “Levered Beta” – Yahoo Finance! Provides beat on a summary page (front page)
of the company.
Market Value of Debt – you will be using Long-Term Debt from the Balance sheet for
the last available year.
Market Value of Equity – Yahoo Finance! provides Market Cap on the summary page.
Important: Market Value of Debt and Market Value of Equity must be in the same
units. Yahoo Finance! provides Market Cap in billions of dollars. However the balance
sheet in Yahoo Finance! is presented in thousands of dollars. You need to express the
Long-Term Debt in billions of dollars, so it is in the same units as Market Value of
Equity.
Calculate unlevered beta using the second formula on the right.
Calculate Median and Mean. Please provide your explanations what you are going
to use in your next calculations, Median or Mean.
2) Calculate Re-levered beta using formula #1 on the right.
3) Cost of Equity
Risk-free rate – we are using 10-year Treasury bond rate for this formula. You may want
to the 20-year or 30-year bond rate since the interest rates are very low now. It is a
mistake to use Treasury bill rates.
4) Calculate WACC. Plug in the result into the Basic Analysis sheet.
Electricity Scenario Summary
To complete this sheet you should first complete the Basic Analysis sheet, and WACC sheet.
After you completed WACC sheet, copy the result of WACC into the cell B99 (WACC) in Basic
Analysis sheet.
The main idea: You need to plug in the electricity prices for six scenarios, one after another,
into the Row 60 (Projected Electricity Costs). The model will calculate NPV and IRR for each
new input. And you will copy-paste the results into the Electricity Scenario Summary sheet.
To do that you need to change the formula in the row C60 to use C108, C109, C110, C111, or
C112 instead of C107.
Sales Volume Scenario Summary
To complete this page you will be using Reference Case for electricity prices and will be
changing Sales Volume (Row 59) in the Basic Analysis sheet. The model will calculate NPV and
IRR for each new input. And you will copy-paste the results into the Sales Volume Scenario
Summary sheet.
Electricity Breakeven
You need to fill in the cells in rows 8 – 26
Row 8 Electricity Price: Year 1 = B5
Years 2-4 it is 5% increase every year.
Row 10 EBIT
1) Price of the car
minus Direct Costs:
•
•
Electricity Price from row 8 for each year* Electricity Used per Car
Other Costs from cells $B$42:$B$46 sheet “Basic Analysis”
2) The result will be multiplied by the Sales Volume from cells C59 for each year in “Basic
Analysis” sheet
3) Minus Fixed Costs:
Capacity *(Labor per car from cell $B$13+ Overhead from cell $B$14)
4) Minus Depreciation calculated in Basic Analysis sheet
Rows 11-14: the same way as you did in Basic Analysis sheet
Row 15: OCF = EBIT*(1-Tax_rate)+Depreciation from Basic Analysis sheet for each year
Row 16: CapEx Year 0 – the same as it was in Basic Analysis sheet
Rows 17, 19-21 – the same way as you did in Basic Analysis sheet.
Row 23 – WACC – you can start with 8%, but actually it should be changed to WACC
calculated in WACC sheet
NPV and IRR – set up the formulas using the results in this sheet.
Now you need to set up the function Goal Seek for the cell $B$5:
Set cell $B$25
To Value 0
By Changing cell $B$5
Zeta Spenza Project
Given
MACRS Schedule
year 1
year 2
year 3
year 4
Monza’s sales in 2015
10,000
Monza’s price
$65,000
Monza Cost structure per car
Body materials
$11,000
Engine
$4,000
Drivetrain
$6,000
Battery Pack
$20,000
Electronics
$5,000
Labor (allocated)
$4,000
Overhead (allocated)
$2,000
Consulting Fees
Spenza Price
Sales Volume
Plant Investment
Alternative Land Use
Plant Capacity
Project life
Percentage of Debt Financing
Interest Rate
Tax rate
NWC as % of direct manufacturing costs
Monza Sales Cannibalization
Electricity cost
Carbon Body Cost per Car
Percentage of electricity
Electricity used per car
Other Spenza direct costs
Body materials
(other than electricity)
Engine
$50,000
$80,000
Spenza Sales projections
Year 1
5,000
Year 2
7,000
$250 Mil
$15 Mil
10,000 cars
4 years
50%
7%
21%
4.75%
1,000 cars
$0.07 per kWh 70% of national average
$14,000
80%
160,000 kWh
$2,800
$4,000
Year 3
6,000
Drivetrain
Battery Pack
Electronics
Straight-Line depreciation
MACRS Depreciation
Your recommendation
Sales Volume
Projected electricity cost (per kWh)
$6,000
$15,000
$5,000
Solution
Choosing Depreciation
Year 1
Year 2
$62.5 Mil $62.5 Mil
$82.5 Mil $112.5 Mil
Using MACRS
Projected Net Income
Year 2021 Year 2022 Year 2023
5,000
7,000
6,000
$0.0752
$0.0732
$0.0722
Revenues
Direct Costs
Body materials(electricity only)
Body materials(other than electricity)
Engine
Drivetrain
Battery Pack
Electronics
Total Direct Costs
Fixed Costs
Labor
Overheads
Depreciation
EBIT
Interest
EBT
Taxes
Net Income
$400.0 Mil $560.0 Mil $480.0 Mil
$60.2 Mil $82.0 Mil $69.3 Mil
$14.0 Mil $19.6 Mil $16.8 Mil
$20.0 Mil $28.0 Mil $24.0 Mil
$30.0 Mil $42.0 Mil $36.0 Mil
$75.0 Mil $105.0 Mil $90.0 Mil
$25.0 Mil $35.0 Mil $30.0 Mil
$224.2 Mil $311.6 Mil $266.1 Mil
$40.0 Mil $40.0 Mil $40.0 Mil
$20.0 Mil $20.0 Mil $20.0 Mil
$82.5 Mil $112.5 Mil $37.5 Mil
$33.3 Mil $75.9 Mil $116.4 Mil
$8.8 Mil
$8.8 Mil
$8.8 Mil
$24.6 Mil $67.2 Mil $107.6 Mil
$5.2 Mil $14.1 Mil $22.6 Mil
$19.4 Mil $53.1 Mil $85.0 Mil
Projected FCF
Monzas Lost Profit
Volume
Price
Direct Costs (per car)
Lost Profit (After-Tax)
Year 3
$62.5 Mil
$37.5 Mil
1,000 cars
$65,000
$46,000
$15.0 Mil
OCF
CapEx
Investment in NWC
Opportunity Costs
Alternative Land Use
Lost Profit from Cannibalized Sales
FCF
WACC (Next Tab)
NPV
IRR
Year 0
Year 1
Year 2
Year 3
$.0 Mil $101.9 Mil $165.6 Mil $122.5 Mil
-$250.0 Mil
-$10.6 Mil
-$4.2 Mil
$2.2 Mil
$4.2 Mil
-$15.0 Mil
-$275.6 Mil
-$15.0 Mil -$15.0 Mil
-$15.0 Mil -$15.0 Mil
$67.7 Mil $137.7 Mil
-$15.0 Mil
-$15.0 Mil
$96.7 Mil
8.48%
$8.6 Mil
9.22%
Six Scenarios
Reference
High Oil Price
Low Oil Price
High Oil and Gas Resource and Tech
Low Oil and Gas Resource and Tech
High Resource without Clean Power Plan
2021
$0.0722
$0.0733
$0.0717
$0.0710
$0.0743
$0.0789
2022
$0.0722
$0.0730
$0.0719
$0.0708
$0.0747
$0.0792
2023
$0.0722
$0.0724
$0.0726
$0.0706
$0.0752
$0.0794
MACRS Schedule
33%
45%
15%
7%
Year 4 Year 5
4,000
3,000
Solution Legend
Value given in problem
Formula/Calculation/Analysis required
Assumptions, Qualitative analysis or Short answer required
Goal Seek, Scenario or Data Table cell
Crystal Ball Input
Crystal Ball Output
Year 4
$62.5 Mil
$17.5 Mil
Year 2024 Year 2025
4,000
3,000
$0.0722
$0.0722
$320.0 Mil
$240.0 Mil
$46.2 Mil
$11.2 Mil
$16.0 Mil
$24.0 Mil
$60.0 Mil
$20.0 Mil
$177.4 Mil
$34.7 Mil
$8.4 Mil
$12.0 Mil
$18.0 Mil
$45.0 Mil
$15.0 Mil
$133.1 Mil
$40.0 Mil
$20.0 Mil
$17.5 Mil
$65.1 Mil
$8.8 Mil
$56.3 Mil
$11.8 Mil
$44.5 Mil
$40.0 Mil
$20.0 Mil
$.0 Mil
$46.9 Mil
$8.8 Mil
$38.2 Mil
$8.0 Mil
$30.2 Mil
Year 4
$62.0 Mil
Year 4
$30.2 Mil Wrong formula. It should be EBIT*(1-tax rate) + Depreciation
$2.1 Mil
$6.3 Mil
-$15.0 Mil
-$15.0 Mil
$34.1 Mil
-$15.0 Mil
-$15.0 Mil
$6.5 Mil
2024
$0.0728
$0.0716
$0.0731
$0.0709
$0.0760
$0.0795
2025
$0.0736
$0.0716
$0.0739
$0.0715
$0.0771
$0.0805
The next step will be estimating WACC. Using Yahoo Finance! or other financial
sources available on the course website find auto-making industry’s beta, market risk
premium and the risk free rate
Your assumptions
Market value of debt is determined using the morningstar website by multiplying the face value of bond with the cur
Comparable Companies Unlevered Beta
Company
Levered Beta
Market
Value of
Debt
9926.15422
9844.8904
0
Market
Value of
Debt/ Equity
Equity
48630 0.204115859
49510 0.198846504
29970
0
Equity
/ Total
Assets
0.83
0.834
1
Peer Company A
1.22
Peer Company B
1.65
Peer Company C
0.88
Peer Company D
Peer Company E
You need to provde the names of the companies! Please also provide the link to the source of the data.
Median
Mean
Relevered Beta
Zeta
Mean
Target Debt/
Unlevered
Equity
Beta
1.14700481
0.5
WACC Calculation
Company’s Capital Structure
Target
Marginal
Tax Rate
0.4
Relevered
Beta
1.491106248
Debt to Total Capitalization
Equity to Total Capitalization
Debt to Equity Ratio
Cost of Equity
Risk-free rate
Market risk Premium
Levered Beta
Cost of Equity
50.00%
50.00%
100.00%
1.72% 10- Year US Treasury bond rate
6.51% 10- Year S&P 500 market return
1.49
11.421535%
Cost of Debt
Cost of Debt
Taxes
After Tax Cost of Debt
WACC
7.00%
21.00%
5.53%
8.48%
Currect MRP is 5.35%. You
Value given in problem
Formula/Calculation/Analysis required
Qualitative analysis or Short answer required
e face value of bond with the current price percentage of the bond.
levered Beta
Margina
l Tax
Rate
40%
40%
40%
Unlevered Beta
1.086889206
1.474125213
0.88
source of the data.
1.086889206
1.147004806
Sales Volume Scenario Summary
Reference
Case
High Oil
Price
Low Oil Price
High Oil and Gas
Resource and Tech
Electricity Prices
Changing Cells:
Year 1
$0.0733
$0.0717
$0.0710
$0.0722
Year 2
$0.0730
$0.0719
$0.0708
$0.0722
Year 3
$0.0724
$0.0726
$0.0706
$0.0722
Year 4
$0.0716
$0.0731
$0.0709
$0.0728
Year 5
$0.0716
$0.0719
$0.0715
$0.0736
Result Cells:
Project_NPV
$.0 Mil
-$.1 Mil
$.6 Mil
$2.5 Mil
Project_IRR
6.51%
6.34%
6.49%
6.87%
Notes: Current Values column represents values of changing cells at
time Scenario Summary Report was created. Changing cells for each
scenario are highlighted in gray.
ry
ices
Low Oil and High Resource
Gas Resource Without Clean
and Tech
Power Plant
$0.0743
$0.0747
$0.0752
$0.0760
$0.0771
$0.0789
$0.0792
$0.0794
$0.0795
$0.0805
$1.0 Mil
6.57%
$1.3 Mil
6.62%
Looks good!
Electricity breakeven
Annual Growth
Year 1 electricity cost for
Breakeven
0.50%
0.066933958
Year 0
Year 1
$0.0669
Year 2
$0.0673
Year 3
$0.0676
Year 4
$0.0679
$40.0 Mil
$8.8 Mil
$31.2 Mil
$6.6 Mil
$24.7 Mil
########
$82.6 Mil
$8.8 Mil
$73.8 Mil
$15.5 Mil
$58.3 Mil
########
########
$8.8 Mil
########
$23.5 Mil
$88.5 Mil
########
$67.8 Mil
$8.8 Mil
$59.1 Mil
$12.4 Mil
$46.7 Mil
$64.2 Mil
-$250.0 Mil
-$10.3 Mil -$4.1 Mil
$2.1 Mil
$4.1 Mil
$1.6 Mil
Electricity Price
EBIT
Interest
EBT
Taxes
Net Income
OCF
CapEx
Investment in NWC
Opportunity Costs
Alternative Land Use
Lost Profit from
Cannibalized Sales
FCF
WACC (Next Tab)
NPV
IRR
-$15.0 Mil -$15.0 Mil -$15.0 Mil -$15.0 Mil -$15.0 Mil
-$15.0 Mil -$15.0 Mil -$15.0 Mil -$15.0 Mil
-$275.3 Mil $73.0 Mil ######## ######## $35.7 Mil
8.48%
$21.8 Mil
12.24%
Solution Legend
Value given in problem
Year 5
Formula/Calculation/Analysis required
$0.0683 Assumptions, Qualitative analysis or Short answer required
Goal Seek, Scenario or Data Table cell
$48.8 Mil Crystal Ball Input
$8.8 Mil Crystal Ball Output
$40.1 Mil
$8.4 Mil
$31.7 Mil
$31.7 Mil
$4.7 Mil
-$15.0 Mil
-$15.0 Mil
$6.3 Mil
Looks good!
Zeta Spenza Project
Given
Monza’s sales in 2015
Monza’s price
Body materials
Engine
Drivetrain
Battery Pack
Electronics
Labor (allocated)
Overhead (allocated)
10,000
$65,000
Monza Cost structure per car
$11,000
$4,000
$6,000
$20,000
$5,000
$4,000
$2,000
Consulting Fees
Spenza Price
$50,000
$80,000
Spenza Sales projections
Year 1
5,000
Sales Volume
Year 2
7,000
Plant Investment
Plant Capacity
Project life
Percentage of Debt Financing
Interest Rate
Tax rate
$250 Mil Uniform distribution from 125 to 375 Mil
10,000 cars
4 years
50% Uniform distribution from 25 to 75 %
7.00% Uniform distribution from 4 to 10 %
21%
NWC as % of sales
Monza Sales Cannibalization
4.75% Uniform distribution from 4% to 5.5 %
1,000 cars Uniform distribution from 500 to 1,500
Electricity cost
Carbon Body Cost per Car
Percentage of electricity
Electricity used per car
Other Spenza direct costs
Body materials
(other than electricity)
Engine
Drivetrain
Battery Pack
$0.07 per kWh 70% of national average
$14,000
80%
160,000 kWh
$2,800
$4,000
$6,000
$15,000 Uniform distribution from
$ 10
$ 10
to to
2022
kK
Electronics
$5,000
Solution
Projected Net Income
Year 1
Year 2
5,000
7,000
$0.0707
$0.0721
Triangular distribution with min and max – 10% away from
Sales Volume
Projected electricity cost (per kWh)
Distribution assumption
Revenues
Direct Costs
Body materials(electricity only)
Body materials(other than electricity)
Engine
Drivetrain
Battery Pack
Electronics
Total Direct Costs
Fixed Costs
Labor
Overheads
Depreciation
EBIT
Interest
EBT
Taxes
Net Income
$400.0 Mil
$560.0 Mil
$56.5 Mil
$14.0 Mil
$20.0 Mil
$30.0 Mil
$75.0 Mil
$25.0 Mil
$220.5 Mil
$80.7 Mil
$19.6 Mil
$28.0 Mil
$42.0 Mil
$105.0 Mil
$35.0 Mil
$310.3 Mil
$40.0 Mil
$20.0 Mil
$82.5 Mil
$37.0 Mil
$8.8 Mil
$28.2 Mil
$5.9 Mil
$22.3 Mil
$40.0 Mil
$20.0 Mil
$112.5 Mil
$77.2 Mil
$8.8 Mil
$68.4 Mil
$14.4 Mil
$54.1 Mil
Year 0
$.0 Mil
-$250.0 Mil
-$10.5 Mil
Year 1
$104.8 Mil
Year 2
$166.6 Mil
-$4.3 Mil
$2.1 Mil
-$15.0 Mil
-$15.0 Mil
-$15.0 Mil
Projected FCF
Monzas Lost Profit
Volume
Price
Direct Costs (per car)
Lost Profit (After-Tax)
OCF
CapEx
Investment in NWC
Opportunity Costs
Alternative Land Use
1,000 cars
$65,000
$46,000
$15.0 Mil
Lost Profit from Cannibalized
Sales
FCF
WACC (Next Tab)
NPV
IRR
-$275.5 Mil
8.48%
$12.2 Mil
10.60%
-$15.0 Mil
-$15.0 Mil
$70.5 Mil
$138.6 Mil
5 to 375 Mil
MACRS Schedule
year 1
33%
year 2
45%
year 3
15%
year 4
7%
Year 3
6,000
Year 4 Year 5
4,000
Solution Legend
Value given in problem
Formula/Calculation/Analysis required
Assumptions, Qualitative analysis or Short answer required
Goal Seek, Scenario or Data Table cell
Crystal Ball Input
Crystal Ball Output
3,000
Year 3
Year 4 Year 5
6,000
4,000
3,000
$0.0724 $0.0718
$0.0718
in and max – 10% away from the mean, correlation 0.7
######## ########
$240.0 Mil
$69.5 Mil
$16.8 Mil
$24.0 Mil
$36.0 Mil
$90.0 Mil
$30.0 Mil
########
$45.9 Mil
$11.2 Mil
$16.0 Mil
$24.0 Mil
$60.0 Mil
$20.0 Mil
########
$34.5 Mil
$8.4 Mil
$12.0 Mil
$18.0 Mil
$45.0 Mil
$15.0 Mil
$132.9 Mil
$40.0 Mil
$20.0 Mil
$37.5 Mil
########
$8.8 Mil
########
$22.6 Mil
$84.9 Mil
$40.0 Mil
$20.0 Mil
$17.5 Mil
$65.4 Mil
$8.8 Mil
$56.6 Mil
$11.9 Mil
$44.7 Mil
$40.0 Mil
$20.0 Mil
$.0 Mil
$47.1 Mil
$8.8 Mil
$38.4 Mil
$8.1 Mil
$30.3 Mil
Year 3
Year 4 Year 5
######## $62.2 Mil
$30.3 Mil
$4.2 Mil
$2.1 Mil
$6.3 Mil
-$15.0 Mil -$15.0 Mil
-$15.0 Mil
-$15.0 Mil -$15.0 Mil
-$15.0 Mil
$96.6 Mil $34.3 Mil
$6.6 Mil
wer required
Energy prices
Year
Reference
High Oil Price
Low Oil Price
2019
2020
2021
2022
2023
2024
2025
2026
2027
2028
2029
2030
2031
2032
2033
2034
2035
2036
2037
2038
2039
2040
2041
2042
2043
10.75
10.46
10.32
10.32
10.32
10.40
10.52
10.59
10.59
10.60
10.58
10.60
10.61
10.65
10.66
10.67
10.66
10.67
10.68
10.66
10.62
10.56
10.58
10.54
10.52
10.85
10.63
10.48
10.42
10.35
10.23
10.23
10.19
10.15
10.13
10.15
10.21
10.25
10.37
10.43
10.41
10.45
10.44
10.51
10.52
10.51
10.55
10.58
10.62
10.61
10.73
10.40
10.25
10.28
10.38
10.44
10.56
10.65
10.68
10.67
10.70
10.70
10.76
10.80
10.83
10.83
10.79
10.82
10.83
10.78
10.77
10.69
10.69
10.67
10.62
High Oil and Low Oil and Gas
Gas Resource
Resource and
and Tech
Tech
10.75
10.42
10.15
10.12
10.09
10.12
10.22
10.26
10.25
10.21
10.19
10.18
10.22
10.18
10.24
10.13
10.17
10.15
10.05
10.04
9.99
9.91
9.93
9.84
9.76
10.79
10.67
10.62
10.67
10.74
10.86
11.02
11.13
11.25
11.31
11.37
11.47
11.52
11.52
11.57
11.61
11.64
11.65
11.64
11.68
11.62
11.60
11.60
11.59
11.55
Low eonomic
Growth
High Economic
Growth
11.04
11.31
11.27
11.31
11.34
11.36
11.49
11.52
11.55
11.56
11.56
11.58
11.60
11.58
11.56
11.53
11.50
11.49
11.47
11.46
11.45
11.44
11.41
11.38
11.35
10.81
10.46
10.25
10.26
10.23
10.34
10.44
10.54
10.53
10.51
10.43
10.43
10.41
10.43
10.48
10.41
10.43
10.38
10.41
10.33
10.29
10.28
10.28
10.22
10.14
10.76
10.51
10.39
10.42
10.47
10.51
10.65
10.73
10.73
10.70
10.71
10.71
10.73
10.74
10.79
10.77
10.74
10.80
10.75
10.74
10.69
10.74
10.74
10.71
10.69
Electricity Prices F
12.00
11.50
Cents/kWh
High Resource
without Clean
Power Plan
11.00
10.50
10.00
9.50
ectricity Prices Forecast
Reference
High Economic Growth
Low eonomic Growth
High Oil Price
Low Oil Price
High Oil and Gas Resource and
Tech
Reference case
High economic growth
Low economic growth

Purchase answer to see full
attachment

Don't use plagiarized sources. Get Your Custom Essay on
FIN 630 University of Maryland Comparable Companies Unlevered Beta Excel Worksheet I need a tutor who can find the companies used for the solution on the a
Just from $13/Page
Order Essay
Homework On Time
Calculate the Price of your PAPER Now
Pages (550 words)
Approximate price: -

Why Choose Us

Top quality papers

We always make sure that writers follow all your instructions precisely. You can choose your academic level: high school, college/university or professional, and we will assign a writer who has a respective degree.

Professional academic writers

We have hired a team of professional writers experienced in academic and business writing. Most of them are native speakers and PhD holders able to take care of any assignment you need help with.

Free revisions

If you feel that we missed something, send the order for a free revision. You will have 10 days to send the order for revision after you receive the final paper. You can either do it on your own after signing in to your personal account or by contacting our support.

On-time delivery

All papers are always delivered on time. In case we need more time to master your paper, we may contact you regarding the deadline extension. In case you cannot provide us with more time, a 100% refund is guaranteed.

Original & confidential

We use several checkers to make sure that all papers you receive are plagiarism-free. Our editors carefully go through all in-text citations. We also promise full confidentiality in all our services.

24/7 Customer Support

Our support agents are available 24 hours a day 7 days a week and committed to providing you with the best customer experience. Get in touch whenever you need any assistance.

Try it now!

Calculate the price of your order

Total price:
$0.00

How it works?

Follow these simple steps to get your paper done

Place your order

Fill in the order form and provide all details of your assignment.

Proceed with the payment

Choose the payment system that suits you most.

Receive the final file

Once your paper is ready, we will email it to you.

Our Services

No need to work on your paper at night. Sleep tight, we will cover your back. We offer all kinds of writing services.

Essays

Essay Writing Service

You are welcome to choose your academic level and the type of your paper. Our academic experts will gladly help you with essays, case studies, research papers and other assignments.

Admissions

Admission help & business writing

You can be positive that we will be here 24/7 to help you get accepted to the Master’s program at the TOP-universities or help you get a well-paid position.

Reviews

Editing your paper

Our academic writers and editors will help you submit a well-structured and organized paper just on time. We will ensure that your final paper is of the highest quality and absolutely free of mistakes.

Reviews

Revising your paper

Our academic writers and editors will help you with unlimited number of revisions in case you need any customization of your academic papers