Zeta Spenza Project

Given

Monza's sales in 2015 10,000 MACRS Schedule

Monza's price $65,000 year 1
Monza Cost structure per car year 2
Body materials $11,000 year 3
Engine $4,000 year 4
Drivetrain $6,000
Battery Pack $20,000
Electronics $5,000
Labor (allocated) $4,000
Overhead (allocated) $2,000

Consulting Fees $50,000

Spenza Price $80,000

Spenza Sales projections

Year 1 Year 2 Year 3
Sales Volume 7,000 7,000 4,000

Plant Investment $250 Mil

Alternative Land Use $15 Mil
Plant Capacity 10,000 cars
Project life 4 years
Percentage of Debt Financing 50%
Interest Rate 7%
Tax rate 40%

NWC as % of direct manufacturing costs 5%

Monza Sales Cannibalization 1,000 cars

Electricity cost $0.07 per kWh 70% of national average

Carbon Body Cost per Car $14,000
Percentage of electricity 80%

Electricity used per car 160,000 kWh

Other Spenza direct costs

Body materials $2,800
(other than electricity)
Engine $4,000
Drivetrain $6,000
Battery Pack $15,000
Electronics $5,000

Solution
Choosing Depreciation
Year 1 Year 2 Year 3
Straight-Line depreciation $62.5 Mil $62.5 Mil $62.5 Mil
MACRS Depreciation $82.5 Mil $112.5 Mil $37.5 Mil

Your recommendation Using MACRS

Projected Net Income

Year 1 Year 2 Year 3
Sales Volume 7,000 7,000 4,000
Projected electricity cost (per kWh) $0.0753 $0.0732 $0.0722

Revenues $560.0 Mil $560.0 Mil $320.0 Mil

Direct Body
Costs materials
(electricity
Body only)
materials $84.3 Mil $82.0 Mil $46.2 Mil
(other than electricity) $19.6 Mil $19.6 Mil $11.2 Mil
Engine $28.0 Mil $28.0 Mil $16.0 Mil
Drivetrain $42.0 Mil $42.0 Mil $24.0 Mil
Battery Pack $105.0 Mil $105.0 Mil $60.0 Mil
Electronics $35.0 Mil $35.0 Mil $20.0 Mil
Total Direct Costs $313.9 Mil $311.6 Mil $177.4 Mil
Fixed Costs
Labor $40.0 Mil $40.0 Mil $40.0 Mil
Overheads $20.0 Mil $20.0 Mil $20.0 Mil
Depreciation $82.5 Mil $112.5 Mil $37.5 Mil
EBIT $103.6 Mil $75.9 Mil $45.1 Mil
Interest $8.8 Mil $8.8 Mil $8.8 Mil
EBT $94.9 Mil $67.2 Mil $36.3 Mil
Taxes $37.9 Mil $26.9 Mil $14.5 Mil
Net Income $56.9 Mil $40.3 Mil $21.8 Mil

Projected FCF

Monzas Lost Profit

Volume 1,000 cars
Price $65,000
Direct Costs (per car) $46,000
Lost Profit (After-Tax) $11.4 Mil

Year 2020 Year 2021 Year 2022 Year 2023

OCF $.0 Mil $139.4 Mil $152.8 Mil $59.3 Mil
CapEx -$250.0 Mil
Investment in NWC -$15.7 Mil $.1 Mil $6.7 Mil $.0 Mil
Opportunity Costs
Alternative Land Use -$15.0 Mil -$15.0 Mil -$15.0 Mil -$15.0 Mil
Lost Profit from Cannibalized Sales -$11.4 Mil -$11.4 Mil -$11.4 Mil
FCF -$280.7 Mil $113.1 Mil $133.1 Mil $32.9 Mil

WACC (Next Tab) 6.11%

NPV -$5.2 Mil

IRR 5.07%

Six Scenarios

2021 2022 2023

Reference case $0.0733 $0.0730 $0.0724
High oil price $0.0733 $0.0730 $0.0724
Low oil price $0.0717 $0.0719 $0.0726
High oil and gas resource and tech $0.0710 $0.0708 $0.0706
Low oil and gas resource and tech $0.0743 $0.0747 $0.0752
High resource without Clean Power Plan $0.0743 $0.0747 $0.0752
MACRS Schedule Solution Legend
33% Value given in problem
45% Formula/Calculation/Analysis required
15% Assumptions, Qualitative analysis or Short answer required
7% Goal Seek, Scenario or Data Table cell
Crystal Ball Input
Crystal Ball Output

Year 4 Year 5
4,000 3,000
 Year 4 Year 5
$62.5 Mil $62.5 Mil
$17.5 Mil $.0 Mil

Year 4 Year 5
4,000 3,000
$0.0722 $0.0751

$320.0 Mil $240.0 Mil

$46.2 Mil $36.1 Mil

$11.2 Mil $8.4 Mil
$16.0 Mil $12.0 Mil
$24.0 Mil $18.0 Mil
$60.0 Mil $45.0 Mil
$20.0 Mil $15.0 Mil
$177.4 Mil $134.5 Mil

$40.0 Mil $40.0 Mil

$20.0 Mil $20.0 Mil
$17.5 Mil $.0 Mil
$65.1 Mil $45.5 Mil
$8.8 Mil $8.8 Mil
$56.3 Mil $36.8 Mil
$22.5 Mil $14.7 Mil
$33.8 Mil $22.1 Mil
Year 2024 Year 2025
$51.3 Mil $22.1 Mil

$2.1 Mil $6.7 Mil

-$15.0 Mil -$15.0 Mil

-$11.4 Mil -$11.4 Mil
$27.0 Mil $2.4 Mil

2024 2025
$0.0716 $0.0716
$0.0716 $0.0716
$0.0731 $0.0739
$0.0709 $0.0715
$0.0760 $0.0771
$0.0760 $0.0771
 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
current price percentage of the bond.

Comparable Companies Unlevered Beta

Equity/
Market Value Market Value
Company Levered Beta Debt/ Equity Total
of Debt of Equity
Assets
Ford 1.22 9926.15 48630 0.20 0.8305
General Motors Company 1.65 9844.89 49510 0.20 0.8341
Tesla Motors, Inc.  0.88 0 29970 0 1
Peer Company D
Peer Company E

Median
Mean

Mean Target
Target Debt/
Relevered Beta Unlevered Marginal Tax Relevered Beta
Equity
Beta Rate
Zeta 0.82 1 0.4 1.31

WACC Calculation
 Company's Capital Structure
Debt to Total Capitalization 50.00%
Equity to Total Capitalizatio 50.00%
Debt to Equity Ratio 100.00%

Cost of Equity
Risk-free rate 0.68% 10- Year US Treasury bond rate
Market risk Premium 5.60% 10- Year S&P 500 market return
Levered Beta 1.31

Cost of Equity 8.03%

Cost of Debt
Cost of Debt 7.00%
Taxes 40.00%
After Tax Cost of Debt 4.20%

WACC 6.11%
al sources
emium Value given in problem
Formula/Calculation/Analysis required
Qualitative analysis or Short answer required

e face value of bond with the

levered Beta
 l   u  1  (1  Tc 
D
Marginal 
Unlevered Beta  E
Tax Rate
l
40% 1.09 u 
 D
40% 1.47 1  1  Tc  
40% 0.88  E

1.09
1.15
 Scenario Summary
High Oil and Gas
Base Case High Oil Price Low Oil Price
Resource Availability
Changing Cells: Electricity Prices
$C$59 $0.0707 $0.0715 $0.0705 $0.0699
$D$59 $0.0721 $0.0721 $0.0722 $0.0710
$E$59 $0.0724 $0.0726 $0.0725 $0.0715
$F$59 $0.0718 $0.0724 $0.0721 $0.0706
Result Cells:
Project_NPV $.7 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.
 High Economic Low Economic
Growth Growth
ces
$0.0705 $0.0710
$0.0721 $0.0720
$0.0719 $0.0715
$0.0717 $0.0704

$1.0 Mil $1.3 Mil

6.57% 6.62%
 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 -$5.2 Mil $29.30 -$43.40
Project_IRR 5.07% 11.28% -1.61%
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 0.07062

Year 0 Year 1 Year 2 Year 3 Year 4

Electricity Price $0.0706 $0.0710 $0.0713 $0.0717

EBIT $108.8 Mil $78.4 Mil $45.6 Mil $65.4 Mil

Interest $8.8 Mil $8.8 Mil $8.8 Mil $8.8 Mil
EBT $100.1 Mil $69.7 Mil $36.9 Mil $56.7 Mil
Taxes $40.0 Mil $27.9 Mil $14.8 Mil $22.7 Mil
Net Income $60.0 Mil $41.8 Mil $22.1 Mil $34.0 Mil
OCF $142.5 Mil ### $59.6 Mil $51.5 Mil
CapEx -$250.0 Mil
Investment in NWC -$15.4 Mil $.0 Mil $6.6 Mil $.0 Mil $1.6 Mil
Opportunity Costs
Alternative Land Use -$15.0 Mil -$15.0 Mil -$15.0 Mil -$15.0 Mil -$15.0 Mil
Lost Profit from
-$11.4 Mil -$11.4 Mil -$11.4 Mil -$11.4 Mil
Cannibalized Sales
FCF -$280.4 Mil $116.1 Mil ### $33.2 Mil $26.7 Mil

WACC (Next Tab) 6.11%

NPV -$2.6 Mil

IRR 5.58%
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
 Zeta Spenza Project

Given

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 $50,000

Spenza Price $80,000

Spenza Sales projections

Year 1 Year 2
Sales Volume 5,000 7,000

Plant Investment $250 Mil Uniform distribution from 125 to 375 Mil
Plant Capacity 10,000 cars
Project life 4 years
Percentage of Debt Financing 50% Uniform distribution from 25 to 75 %
Interest Rate 7.00% Uniform distribution from 4 to 10 %
Tax rate 40%

NWC as % of sales 4.75% Uniform distribution from 4% to 5.5 %

Monza Sales Cannibalization 1,000 cars Uniform distribution from 500 to 1,500

Electricity cost $0.07 per kWh 70% of national average

Carbon Body Cost per Car $14,000
Percentage of electricity 80%

Electricity used per car 160,000 kWh

Other Spenza direct costs

Body materials $2,800
(other than electricity)
Engine $4,000
Drivetrain $6,000
Battery Pack $16,000 Uniform distribution from $ 10 to 22 K
Electronics $5,000

Solution
Projected Net Income
Year 1 Year 2
Sales Volume 5,000 7,000
Uniform distribution with +- 500 units around projected v
Projected electricity cost (per kWh) $0.0707 $0.0721
Distribution assumption Triangular distribution with min and max - 10% away from

Revenues $400.0 Mil $560.0 Mil

Direct Body
Costs materials
(electricity only)
Body materials $56.5 Mil $80.7 Mil
(other than electricity) $14.0 Mil $19.6 Mil
Engine $20.0 Mil $28.0 Mil
Drivetrain $30.0 Mil $42.0 Mil
Battery Pack $80.0 Mil $112.0 Mil
Electronics $25.0 Mil $35.0 Mil
Total Direct Costs $225.5 Mil $317.3 Mil
Fixed Costs
Labor $40.0 Mil $40.0 Mil
Overheads $20.0 Mil $20.0 Mil
Depreciation $82.5 Mil $112.5 Mil
EBIT $32.0 Mil $70.2 Mil
Interest $8.8 Mil $8.8 Mil
EBT $23.2 Mil $61.4 Mil
Taxes $9.3 Mil $24.6 Mil
Net Income $13.9 Mil $36.9 Mil

Projected FCF

Monzas Lost Profit

Volume 1,000 cars
Price $65,000
Direct Costs (per car) $46,000
Lost Profit (After-Tax) $11.4 Mil

Year 0 Year 1 Year 2

OCF $.0 Mil $96.4 Mil $149.4 Mil
CapEx -$250.0 Mil
Investment in NWC -$10.7 Mil -$4.4 Mil $2.1 Mil
Opportunity Costs
Alternative Land Use -$15.0 Mil -$15.0 Mil -$15.0 Mil
Lost Profit from Cannibalized
-$11.4 Mil -$11.4 Mil
Sales
FCF -$275.7 Mil $65.7 Mil $125.1 Mil

WACC (Next Tab) 6.11%

NPV -$18.3 Mil

IRR 2.82%
 MACRS Schedule Solution Legend
year 1 33% Value given in problem
year 2 45% Formula/Calculation/Analysis required
year 3 15% Assumptions, Qualitative analysis or Short answer required
year 4 7% Goal Seek, Scenario or Data Table cell
Crystal Ball Input
Crystal Ball Output

Year 3 Year 4 Year 5

6,000 4,000 3,000

125 to 375 Mil

25 to 75 %

4% to 5.5 %
500 to 1,500
 $ 10 to 22 K

Year 3 Year 4 Year 5

6,000 4,000 3,000
h +- 500 units around projected volume each year, correlation 0.6
$0.0724 $0.0718 $0.0718
th min and max - 10% away from the mean, correlation 0.7

$480.0 Mil $320.0 Mil $240.0 Mil

$69.5 Mil $45.9 Mil $34.5 Mil

$16.8 Mil $11.2 Mil $8.4 Mil
$24.0 Mil $16.0 Mil $12.0 Mil
$36.0 Mil $24.0 Mil $18.0 Mil
$96.0 Mil $64.0 Mil $48.0 Mil
$30.0 Mil $20.0 Mil $15.0 Mil
$272.3 Mil $181.1 Mil $135.9 Mil

$40.0 Mil $40.0 Mil $40.0 Mil

$20.0 Mil $20.0 Mil $20.0 Mil
$37.5 Mil $17.5 Mil $.0 Mil
$110.2 Mil $61.4 Mil $44.1 Mil
$8.8 Mil $8.8 Mil $8.8 Mil
$101.5 Mil $52.6 Mil $35.4 Mil
$40.6 Mil $21.0 Mil $14.2 Mil
$60.9 Mil $31.6 Mil $21.2 Mil

Year 3 Year 4 Year 5

$98.4 Mil $49.1 Mil $21.2 Mil
 $4.3 Mil $2.2 Mil $6.5 Mil

-$15.0 Mil -$15.0 Mil -$15.0 Mil

-$11.4 Mil -$11.4 Mil -$11.4 Mil
$76.3 Mil $24.8 Mil $1.3 Mil
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&region=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 economic Low economic

growth growth
Year Reference
2019 10.75 10.76 10.81
2020 10.46 10.51 10.46
2021 10.32 10.39 10.25
2022 10.32 10.42 10.26
2023 10.32 10.47 10.23
2024 10.40 10.51 10.34
2025 10.52 10.65 10.44
2026 10.59 10.73 10.54
2027 10.59 10.73 10.53
2028 10.60 10.70 10.51
2029 10.58 10.71 10.43
2030 10.60 10.71 10.43
2031 10.61 10.73 10.41
2032 10.65 10.74 10.43
2033 10.66 10.79 10.48
2034 10.67 10.77 10.41
2035 10.66 10.74 10.43
2036 10.67 10.80 10.38
2037 10.68 10.75 10.41
2038 10.66 10.74 10.33
2039 10.62 10.69 10.29
2040 10.56 10.74 10.28
2041 10.58 10.74 10.28
2042 10.54 10.71 10.22
2043 10.52 10.69 10.14

Prices are in nominal cents/kWh

0&cases=ref2019~highmacro~lowmacro~highprice~lowprice~highrt~lowrt~aeo2018no&start=2019&end=2029&f=A&linecha
entire URL above instead of clicking on the link directly

High oil and gas Low oil and gas AEO without
resource and resource and Clean Power
High Oil Price Low Oil Price tech tech Plan
10.85 10.73 10.75 10.79 11.04
10.63 10.40 10.42 10.67 11.31
10.48 10.25 10.15 10.62 11.27
10.42 10.28 10.12 10.67 11.31
10.35 10.38 10.09 10.74 11.34
10.23 10.44 10.12 10.86 11.36
10.23 10.56 10.22 11.02 11.49
10.19 10.65 10.26 11.13 11.52
10.15 10.68 10.25 11.25 11.55
10.13 10.67 10.21 11.31 11.56
10.15 10.70 10.19 11.37 11.56
10.21 10.70 10.18 11.47 11.58
10.25 10.76 10.22 11.52 11.60
10.37 10.80 10.18 11.52 11.58
10.43 10.83 10.24 11.57 11.56
10.41 10.83 10.13 11.61 11.53
10.45 10.79 10.17 11.64 11.50
10.44 10.82 10.15 11.65 11.49
10.51 10.83 10.05 11.64 11.47
10.52 10.78 10.04 11.68 11.46
10.51 10.77 9.99 11.62 11.45
10.55 10.69 9.91 11.60 11.44
10.58 10.69 9.93 11.60 11.41
10.62 10.67 9.84 11.59 11.38
10.61 10.62 9.76 11.55 11.35
nd=2029&f=A&linechart=~~~~~~~~ref2019-d111618a.61-8-AEO2019~highmacro-d111618a.61-8-AEO2019~lowmacro-d111

Electricity Prices Forecast (Prices in Nominal cc/kWh)

12

11.5

Reference case
High economic growth
11
Low economic growth
High oil price
Cents/kWh

Low oil price

High oil and gas resource and tech
Low oil and gas resource and tech
10.5 AEO without Clean Power Plan

10

9.5
20 20 20 20 20 20 20 20 20 20 20 20 20
19 21 23 25 27 29 31 33 35 37 39 41 43
EO2019~lowmacro-d111618a.61-8-AEO2019~highprice-d111618a.61-8-AEO2019~lowprice-d111618a.61-8-AEO2019~highrt

/kWh)

rence case
economic growth
economic growth
oil price
oil price
oil and gas resource and tech
oil and gas resource and tech
without Clean Power Plan
rice-d111618a.61-8-AEO2019~highrt-d111618a.61-8-AEO2019~lowrt-d111618a.61-8-AEO2019~aeo2018no-d121317a.61-8-A
o2018no-d121317a.61-8-AEO2019&sid=ref2019-d111618a.67-8-AEO2019&sourcekey=0