FIN 630 University of Maryland Zeta Auto Corporation Excel Sheet & Paper Requirements .I)Use the comments on excel file named basic analysis to complete PR

FIN 630 University of Maryland Zeta Auto Corporation Excel Sheet & Paper Requirements .I)Use the comments on excel file named basic analysis to complete PROJECT 1.

II) Complete the parts highlighted in red and Crystal Ball.

What should we write in the report? What is its structure?

Per project description, you should write a report providing the CFO with your recommendation whether Zeta should set up the plant to produce the Spenza’s. In your report, please explain the results of each portion of your analysis. Report should also include at the beginning a one-page Executive Summary summarizing the results of your analysis and recommendation. Don’t forget to add references to sources used. So, the possible structure of report is:

Executive Summary
Introduction
Capital Budgeting recommendations
Risk analysis (including discussion of scenarios, breakeven, optional Monte Carlo simulation, possible discussion of risk factors)
Conclusion
References
Appendix (Excel file as a separate document)

When calculating FCF, you use OCF as a starting point. However, OCF takes into account interest paid, while FCF should assume all-equity financing. How to reconcile the two?

You may either assume all equity financing when calculating OCF ( = EBIT (1-Tax Rate) + Depreciation) or adjust FCF by subtracting interest rate tax shield.

I am still confused with calculating NWC cash outlay. Can you give an additional example?

Imagine hypothetically that you are planning to start operations in year 2019 (your year 1) and you estimate that your total direct costs in 2019 will be $300 M, in 2020 $400 M and in 2021 $200 M (sales go down). If your NWC is 5% of your total direct costs, it means that at the beginning of 2019, so technically in year 0, you will have to have 5% x 300 M = 15 M in NWC, in year 1 (before 2020) you will have to have 5% x 400 M = 20 M, and in year 2 (before 2021) you have to have 5% x 200 M = 10 M in NWC.

What cash flow effect will it have?

Before 2019 you have no NWC, so you have to outlay (15 – 0) = 15 M in year 0 (negative cash flow)

Before 2020 you already have 15 M of NWC, but you need 20 M. It means you need additional cash outlay of (20 – 15) = 5 M in year 1 (negative cash flow)

Before 2021 you already have 20 M of NWC and you need only 10 M that year. It means that in the language of outlay you have (10 – 20) = – 10 M, which effectively means that you have an inflow of 10 M.

The case says “Labor is unionized; number of workers and wages do not depend on the number of units produced” Why is it important?

It is important, because the total annual labor costs remain constant, they do not depend on the number of cars produced in a given year

What are the steps in finding Zeta’s cost of equity?

To find our company’s cost of equity you use CAPM formula, for which you need to find the levered beta and download T-bond rate as a proxy for risk-free rate (e.g., from U.S. Treasury website) and market risk premium (e.g., from Damodaran’s site – this is the link to equity premium table.)

Finding company’s levered beta consists of

1) finding levered betas of peer companies (Yahoo Finance has it),

2) unlevering each peer’s beta using this peer’s capital structure and tax rate.

3) finding the average unlevered beta

4) finding Zeta’s levered beta using Zeta’s target capital structure and tax rate.

What is the Target Debt/ Equity?

Target Debt/Equity (and Target Marginal Tax Rate) are Zeta’s Debt/Equity Ratio (and marginal tax rate) used for Spenza project. Information is given in the Project Description.

Can I use foreign car-making companies as peers?

Yes you can, but be very careful with currency conversion! One of very common mistakes is that students don’t realize in which currency (and in what units) financials are reported for these companies

Is Crystal Ball required?

Crystal Ball is completely optional. However, if you run a Monte Carlo simulation (Crystal Ball or any other software) and analyze its results in your report, you can receive an extra credit up to 5% of the total project’s grade.

How do I run Crystal Ball? For example, how do I define assumptions in Crystal Ball?

You might find this link (clickable) useful. It is an example of using CB for a different problem.

Here is how you define assumptions (it is described in the manual in more detail, but here is a much abbreviated version):

First of all, you have to decide, which inputs will be your assumptions. In this exercise I tried to help you – all cells which would contain assumptions are color coded bright green. Cells with assumptions should not contain any formulas. You may put numbers there (sometimes you even should put a number, if distribution of another cell depends on this cell’s value and the value can’t be zero). These numbers are irrelevant, because during the simulation they will be replaced. For example, B24, B27, B28 and other cells of the same color will contain assumptions

Second, you have to decide on distribution (uniform, triangular, etc.) and its parameters. You can choose your own or you can follow my suggestions (next to each cell).

Third, now you are ready to actually create assumptions. Select the cells, where the assumptions will be – you can select only one cell, or by keeping CTRL button, you can select all cells which would have the same type of distribution (e.g., uniform). Then click on “Define Assumption” in Crystal Ball menu and choose the distribution (e.g., uniform). Once you do that a dialog will open which would ask for distribution parameters. You input these parameters and click “OK”. If you selected several cells, it will move to the next cell, and you enter parameters for this cell. If you want the value a cell to be correlated with another cell, you click on “correlate” button. If you want to change the distribution type, you click on “Gallery” and change the distribution type.

Once you are done with assumptions, you have to define which cells will contain the Crystal Ball forecast – “Define Forecast”. It should be NPV and IRR. You might also want to have other outputs as well (e.g., FCF for each year).

Once you defined your assumptions and your forecast, you are ready to run the simulation. Click “Start”

Once the simulation is over you can create report or look at charts (“View Charts”) where you can choose, which charts you want to see. In fact, you can also use “Analyze” in the dialog box for simulation. Only assumptions and forecast cells will be available for viewing charts. Chart will show you distributions and allow to get answers to questions like “What is the probability of NPV > 0”.

If you want to create Tornado diagram or Sensitivity Chart (Spider Chart), you go to More Tools and choose Tornado Analysis.

Crystal Ball requires some effort to learn, but once you succeed you will see how powerful this tool is.

Student Workspace does not show files on my computer, what should I do to open a file to run Crystal Ball?

Working in Student Workspace is like working on a completely different computer somewhere in a cloud – you don’t have access to local files on your computer. In order to open an existing file you have to upload this file to a cloud storage space, like Dropbox or Google Drive. Then open a browser in Student Workspace and download your file from the cloud location. After you create the file with simulation results in Student Workspace, repeat the procedure – upload file to cloud and then download it to your computer. Zeta Spenza Project
Given
MACRS Schedule
year 1
33%
year 2
45%
year 3
15%
year 4
7%
Monza’s sales
Monza’s price
10.000
$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
$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%
NWC as % of direct manufacturing costs 4,75%
Monza Sales Cannibalization
1.000 cars
Electricity cost
Carbon Body Cost per Car
Percentage of electricity
Electricity used per car
Other Spenza direct costs
Body materials
(other than electricity)
Engine
$0,07 per kWh 70% of national average
$14.000
80%
160.000 kWh
$2.800
$4.000
Year 3
6.000
Year 4
4.000
Drivetrain
Battery Pack
Electronics
$6.000
$15.000
$5.000
Straight-Line depreciation
MACRS Depreciation
Solution
Choosing Depreciation
Year 1
Year 2
$62,5 Mil $62,5 Mil
$82,5 Mil $112,5 Mil
Year 3
$62,5 Mil
$37,5 Mil
Year 4
$62,5 Mil
$17,5 Mil
Projected Net Income
Year 2021 Year 2022
5.000
7.000
$0,0752
$0,0732
Year 2023
6.000
$0,0722
Year 2024
4.000
$0,0722
Your recommendation
Sales Volume
Projected electricity cost (per kWh)
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 $320,0 Mil
$60,2 Mil
$82,0 Mil
$69,3 Mil
$46,2 Mil
$14,0 Mil
$19,6 Mil
$16,8 Mil
$11,2 Mil
$20,0 Mil $28,0 Mil $24,0 Mil $16,0 Mil
$30,0 Mil $42,0 Mil $36,0 Mil $24,0 Mil
$75,0 Mil $105,0 Mil $90,0 Mil $60,0 Mil
$25,0 Mil $35,0 Mil $30,0 Mil $20,0 Mil
$224,2 Mil $311,6 Mil $266,1 Mil $177,4 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)
1.000 cars
$65.000
$46.000
$40,0 Mil
$20,0 Mil
$17,5 Mil
$65,1 Mil
$8,8 Mil
$56,3 Mil
$11,8 Mil
$44,5 Mil
Lost Profit (After-Tax)
OCF
CapEx
Investment in NWC
Opportunity Costs
Alternative Land Use
Lost Profit from Cannibalized
Sales
FCF
$15,01 Mil
Year 0
$,0 Mil
-$250,0 Mil
-$10,6 Mil
-$15,0 Mil
-$275,65 Mil
WACC (From WACC Tab)
NPV
IRR
Year 1
Year 2
Year 3
$101,9 Mil $165,6 Mil $122,5 Mil
Year 4
$62,0 Mil
-$4,2 Mil
$2,2 Mil
$4,2 Mil
$2,1 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
$67,75 Mil $137,71 Mil $96,73 Mil $34,09 Mil
8,00%
$11,4 Mil
9,95%
Six Scenarios
Year 2021
Reference case
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
Year 2022
Year 2023
Year 2024
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 5
3.000
Year 2025
3.000
$0,0722
$240,0 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
$,0 Mil
$46,9 Mil
$8,8 Mil
$38,2 Mil
$8,0 Mil
$30,2 Mil
Year 5
$30,2 Mil
$6,3 Mil
-$15,0 Mil
-$15,0 Mil
$6,47 Mil
Year 2025
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
Comparable Companies Unlevered Beta
Company
Levered
Beta
Market
Value of
Debt
Market
Value of
Equity
Debt/ Equity
Mean
Unlevered
Beta
Target Debt/
Equity
Target
Marginal
Tax Rate
Relevered
Beta
Peer Company A
Peer Company B
Peer Company C
Peer Company D
Peer Company E
Median
Mean
Relevered Beta
Zeta
WACC Calculation
Company’s Capital Structure
Equity/
Total
Assets
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
Cost of Debt
Cost of Debt
Taxes
After Tax Cost of Debt
WACC
Value given in problem
Formula/Calculation/Analysis required
Qualitative analysis or Short answer required
Margina
l Tax
Rate
Unlevered
Beta
? l = ? u ?? (1 + (1 ? Tc )
?
?u =
D?
?
E?
?l
D?
?
?1 + (1 ? Tc ) ?
E?
?
Scenario Summary
Reference
High Oil Price Low oil price
Case
Year 1
Year 2
Year 3
Year 4
Year 5
Result Cells:
Project_NPV
Project_IRR
High oil and gas
resource and tech
Low oil and gas
resource and tech
High resource without
Clean Power Plan
Sales Volume Scenario Summary
Reference Case High Sales Low Sales
Sales Volume
Volume
Volume
Changing Cells:
Year 1
5.000
5.500
4.500
Year 2
7.000
7.500
6.500
Year 3
6.000
6.500
5.500
Year 4
4.000
4.500
3.500
Year 5
3.000
3.500
2.500
Result Cells:
Project_NPV
Project_IRR
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.
Electricity breakeven
Annual Growth
0,50%
Year 1 electricity cost for
Breakeven
Year 0
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 (From WACC Tab)
NPV
IRR
8,00%
Year 1
Year 2
Year 3
Year 4
Solution Legend
Year 5
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
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
Sales Volume
Year 1
5.000
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
$0,07 per kWh 70% of national average
$14.000
80%
Electricity used per car
Other Spenza direct costs
Body materials
(other than electricity)
Engine
Drivetrain
Battery Pack
$4.000
$6.000
$15.000 Uniform distribution from $ 10 to 20 K
Electronics
$5.000
Solution
Projected Net Income
Year 1
Year 2
5.000
7.000
Uniform distribution with +- 500 units around projected v
Sales Volume
Projected electricity cost (per kWh)
Distribution assumption
Triangular distribution with min and max – 10% away from
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
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
Lost Profit from Cannibalized
Sales
FCF
WACC (From WACC Tab)
NPV
IRR
8,00%
MACRS Schedule
year 1
33%
year 2
45%
year 3
15%
year 4
7%
Year 3
6.000
m 125 to 375 Mil
m 25 to 75 %
m 4% to 5.5 %
m 500 to 1,500
m $ 10 to 20 K
Year 4
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
Year 5
3.000
Year 3
Year 4
Year 5
6.000
4.000
3.000
+- 500 units around projected volume each year, correlation 0.6
th min and max – 10% away from the mean, correlation 0.7
Electricity Prices Forecast (Prices in Nominal cc/kWh)
Source: U.S. Energy Information Administration
https://www.eia.gov/outlooks/aeo/data/browser/#/?id=8-AEO2019®ion=0-0&cases=ref2019~highmacro~lowma
To see the chart and the table at the source website, please copy and paste the entire URL above instead of clicking o
High
Low
High oil and Low oil and
Reference
High oil Low oil
economic economic
gas resource gas resource
case
price
price
growth
growth
and tech
and tech
Year
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
Prices are in nominal cents/kWh
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
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,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
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
=ref2019~highmacro~lowmacro~highprice~lowprice~highrt~lowrt~aeo2018no&start=2019&end=2029&f=A&linechart=~~~~~
L above instead of clicking on the link directly
AEO without
Clean Power
Plan
Electricity Prices Forecast (Prices in Nominal cc
12,00
11,50
11,00
Cents/kWh
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,50
10,00
9,50
2029&f=A&linechart=~~~~~~~~ref2019-d111618a.61-8-AEO2019~highmacro-d111618a.61-8-AEO2019~lowmacro-d111618a
Prices in Nominal cc/kWh)
Reference case
High economic growth
Low economic growth
High oil price
Low oil price
High oil and gas resource and tech
Low oil and gas resource and tech
AEO without Clean Power Plan
-8-AEO2019~lowmacro-d111618a.61-8-AEO2019~highprice-d111618a.61-8-AEO2019~lowprice-d111618a.61-8-AEO2019~hi
111618a.61-8-AEO2019~highrt-d111618a.61-8-AEO2019~lowrt-d111618a.61-8-AEO2019~aeo2018no-d121317a.61-8-AEO20
no-d121317a.61-8-AEO2019&sid=ref2019-d111618a.67-8-AEO2019&sourcekey=0
Zeta Spenza Project
Given
MACRS Schedule
year 1
33%
year 2
45%
year 3
15%
year 4
7%
Monza’s sales
Monza’s price
10,000
$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
$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%
NWC as % of direct manufacturing costs 4.75%
Monza Sales Cannibalization
1,000 cars
Electricity cost
Carbon Body Cost per Car
Percentage of electricity
Electricity used per car
Other Spenza direct costs
Body materials
(other than electricity)
Engine
$0.07 per kWh 70% of national average
$14,000
80%
160,000 kWh Correct.
$2,800
$4,000
Correct.
Year 3
6,000
Year 4
4,000
Drivetrain
Battery Pack
Electronics
$6,000
$15,000
$5,000
Straight-Line depreciation
MACRS Depreciation
Solution
Choosing Depreciation
Year 1
Year 2
$62.5 Mil $62.5 Mil
$82.5 Mil $112.5 Mil
Year 3
$62.5 Mil
$37.5 Mil
Year 4
$62.5 Mil
$17.5 Mil
Projected Net Income
Year 2021 Year 2022
5,000
7,000
$0.0752
$0.0732
Year 2023
6,000
$0.0722
Year 2024
4,000
$0.0722
Your recommendation
Sales Volume
Projected electricity cost (per kWh)
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 $320.0 Mil
$60.2 Mil
$82.0 Mil
$69.3 Mil
$46.2 Mil
$14.0 Mil
$19.6 Mil
$16.8 Mil
$11.2 Mil
$20.0 Mil $28.0 Mil $24.0 Mil $16.0 Mil
$30.0 Mil $42.0 Mil $36.0 Mil $24.0 Mil
$75.0 Mil $105.0 Mil $90.0 Mil $60.0 Mil
$25.0 Mil $35.0 Mil $30.0 Mil $20.0 Mil
$224.2 Mil $311.6 Mil $266.1 Mil $177.4 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)
1,000 cars
$65,000
$46,000
$40.0 Mil
$20.0 Mil
$17.5 Mil
$65.1 Mil
$8.8 Mil
$56.3 Mil
$11.8 Mil
$44.5 Mil
Lost Profit (After-Tax)
OCF
CapEx
Investment in NWC
Opportunity Costs
Alternative Land Use
Lost Profit from Cannibalized
Sales
FCF
$15.01 Mil Correct
Year 0
$.0 Mil
-$250.0 Mil
-$10.6 Mil
-$15.0 Mil
-$275.65 Mil
WACC (From WACC Tab)
NPV
IRR
Year 1
Year 2
Year 3
$101.9 Mil $165.6 Mil $122.5 Mil
Year 4
$62.0 Mil
-$4.2 Mil
$2.2 Mil
$4.2 Mil
$2.1 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
$67.75 Mil $137.71 Mil $96.73 Mil $34.09 Mil
8.00%
#VALUE!
#VALUE!
The formula is correct. It will work when you fix everything abov
The formula is correct. It will work when you fix everything abov
Six Scenarios
Year 2021
Reference case
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
Year 2022
Year 2023
Year 2024
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 5
3,000
Correct
Year 2025
3,000
$0.0722
$240.0 Mil
$34.7 Mil Correct
$8.4 Mil
Correct
$12.0 Mil
$18.0 Mil
$45.0 Mil
$15.0 Mil
$133.1 Mil Correct
$40.0 Mil
$20.0 Mil
You have to add recommendations in row 54
$46.9 Mil Correct
$8.8 Mil
$38.2 Mil
$8.0 Mil
$30.2 Mil Correct
Year 5
$30.2 Mil Wrong formula. It should be EBIT*(1-tax rate) + Depreciation
#VALUE! It will be G70*$B32
-$15.0 Mil
-$15.0 Mil
#VALUE!
ou fix everything above.
ou fix everything above.
Year 2025
You should fill in this part also
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
Comparable Companies Unlevered Beta
Company
Levered
Beta
Market
Value of
Debt
Market
Value of
Equity
Debt/ Equity
Mean
Unlevered
Beta
Target Debt/
Equity
Target
Marginal
Tax Rate
Relevered
Beta
Peer Company A
Peer Company B
Peer Company C
Peer Company D
Peer Company E
Median
Mean
Relevered Beta
Zeta
WACC Calculation
Company’s Capital Structure
Equity/
Total
Assets
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
Cost of Debt
Cost of Debt
Taxes
After Tax Cost of Debt
WACC
Value given in problem
Formula/Calculation/Analysis required
Qualitative analysis or Short answer required
Margina
l Tax
Rate
Unlevered
Beta
? l = ? u ?? (1 + (1 ? Tc )
?
?u =
D?
?
E?
?l
D?
?
?1 + (1 ? Tc ) ?
E?
?
Scenario Summary
Reference
High Oil Price Low oil price
Case
Year 1
Year 2
Year 3
Year 4
Year 5
Result Cells:
Project_NPV
Project_IRR
High oil and gas
resource and tech
Low oil and gas
resource and tech
High resource without
Clean Power Plan
Sales Volume Scenario Summary
Reference Case High Sales Low Sales
Sales Volume
Volume
Volume
Changing Cells:
Year 1
5,000
5,500
4,500
Year 2
7,000
7,500
6,500
Year 3
6,000
6,500
5,500
Year 4
4,000
4,500
3,500
Year 5
3,000
3,500
2,500
Result Cells:
Project_NPV
Project_IRR
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.
Electricity breakeven
Annual Growth
0.50%
Year 1 electricity cost for
Breakeven
Year 0
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 (From WACC Tab)
NPV
IRR
8.00%
Year 1
Year 2
Year 3
Year 4
Solution Legend
Year 5
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
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
Sales Volume
Year 1
5,000
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
$0.07 per kWh 70% of national average
$14,000
80%
Electricity used per car
Other Spenza direct costs
Body materials
(other than electricity)
Engine
Drivetrain
Battery Pack
$4,000
$6,000
$15,000 Uniform distribution from $ 10 to 20 K
Electronics
$5,000
Solution
Projected Net Income
Year 1
Year 2
5,000
7,000
Uniform distribution with +- 500 units around projected v
Sales Volume
Projected electricity cost (per kWh)
Distribution assumption
Triangular distribution with min and max – 10% away from
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
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
Lost Profit from Cannibalized
Sales
FCF
WACC (From WACC Tab)
NPV
IRR
8.00%
MACRS Schedule
year 1
33%
year 2
45%
year 3
15%
year 4
7%
Year 3
6,000
m 125 to 375 Mil
m 25 to 75 %
m 4% to 5.5 %
m 500 to 1,500
m $ 10 to 20 K
Year 4
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
Year 5
3,000
Year 3
Year 4
Year 5
6,000
4,000
3,000
+- 500 units around projected volume each year, correlation 0.6
th min and max – 10% away from the mean, correlation 0.7
Electricity Prices Forecast (Prices in Nominal cc/kWh)
Source: U.S. Energy Information Administration
https://www.eia.gov/outlooks/aeo/data/browser/#/?id=8-AEO2019®ion=0-0&cases=ref2019~highmacro~lowma
To see the chart and the table at the sou…
Purchase answer to see full
attachment