Project 1 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...

1 answer below »
write a report
providing the CFO with your recommendation whether Zeta should set up the plant to produce the Spenza’s




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)








Project 1 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 2021 (your year 1) and you estimate that your total direct costs in 2021 will be $300 M, in 2022 $400 M and in 2023 $200 M (sales go down). If your NWC is 5% of your total direct costs, it means that at the beginning of 2021, so technically in year 0, you will have to have 5% x 300 M = 15 M in NWC, in year 1 (before 2022) you will have to have 5% x 400 M = 20 M, and in year 2 (before 2023) you have to have 5% x 200 M = 10 M in NWC. What cash flow effect will it have? Before 2021 you have no NWC, so you have to outlay (15 - 0) = 15 M in year 0 (negative cash flow) Before 2022 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 2023 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. 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.
major-project-1-template-2pc2mdso.xlsx projet-1-instructions-1xurc5th.docx
Answered Same DaySep 27, 2021

Answer To: Project 1 What should we write in the report? What is its structure? Per project description, you...

Neenisha answered on Oct 04 2021
151 Votes
Scenario Summary_IRR
        Scenario Summary
                Current Values:    Reference Case    High Economic Growth    Low Economic Growth
                    Created by Microsoft Office User on 03/10/2020
Modified by Microsoft Office User on 03/10/2020    Created by Microsoft Office User on 03/10/2020    Created by Microsoft Office User on 03/10/2020
        Changing Cells:
            $C$61    5,000    5,000    5,500    4,500
            $D$61    7,000    7,000    7,500    6,500
            $E$61    6,000    6,000    6,500    5,500
            $F$61    4,000    4,000    4,500    3,500
            $G$61    3,000    3,000    3,500    2,500
        Result Cells:
            Project_IRR    10.45%    10.45%    19.86%    -1.10%
        Notes: Current Values column represents values of changing cells at
        time Scenario Summary Report was created. Changing cells for each
        scenario are highlighted in grey.
Scenario Summary_NPV
        Scenario Summary
                Current Values:    Reference Case    High Economic Growth    Low Economic Growth
                    Created by Microsoft Office User on 03/10/2020
Modified by Microsoft Office User on 03/10/2020    Created by Microsoft Office User on 03/10/2020    Created by Microsoft Office User on 03/10/2020
        Changing Cells:
            $C$61    5,000    5,000    5,500    4,500
            $D$61    7,000    7,000    7,500    6,500
            $E$61    6,000    6,000    6,500    5,500
            $F$61    4,000    4,000    4,500    3,500
            $G$61    3,000    3,000    3,500    2,500
        Result Cells:
            Project_NPV    31461299.68    31461299.68    92671262.99    -29748663.63
        Notes: Current Values column represents values of changing cells at
        time Scenario Summary Report was created. Changing cells for each
        scenario are highlighted in grey.
Scenario Summary_Elec_NPV
        Scenario Summary
                Current Values:    Reference Case    High Economic Growth    Low Economic Growth    High on Price    Low on Price    High oil and gas supply    Low oil and gas supply    High renewable cost    Low renewable cost
                    Created by Microsoft Office User on 03/10/2020
Modified by Microsoft Office User on 03/10/2020
Modified by Microsoft Office User on 04/10/2020    Created by Microsoft Office User on 03/10/2020
Modified by Microsoft Office User on 04/10/2020    Created by Microsoft Office User on 03/10/2020
Modified by Microsoft Office User on 04/10/2020    Created by Microsoft Office User on 04/10/2020    Created by Microsoft Office User on 04/10/2020    Created by Microsoft Office User on 04/10/2020    Created by Microsoft Office User on 04/10/2020    Created by Microsoft Office User on 04/10/2020
Modified by Microsoft Office User on 04/10/2020    Created by Microsoft Office User on 04/10/2020
        Changing Cells:
            $C$62    $7.0000    $10.3100    $10.3400    $10.2400    $10.3500    $10.2500    $10.3300    $10.2800    $10.3300    $10.3300
            $D$62    $7.0350    $10.2100    $10.2500    $10.0900    $10.2300    $10.2900    $10.1900    $10.3200    $10.2300    $10.2300
            $E$62    $7.0702    $10.1900    $10.2400    $10.1300    $10.2300    $10.1800    $10.1300    $10.3600    $10.2000    $10.1900
            $F$62    $7.1055    $10.1800    $10.2400    $10.0700    $10.2000    $10.2000    $10.0700    $10.4200    $10.1800    $10.1700
            $G$62    $7.1411    $10.2400    $10.3300    $10.1600    $10.1900    $10.2700    $10.0800    $10.5100    $10.2500    $10.2200
        Result Cells:
            Project_NPV    31461299.68    24927028.18    24826846.87    25110977.62    24882589.55    24898153.24    25028794.69    24657760.49    24899062.84    24913681.78
        Notes: Current Values column represents values of changing cells at
        time Scenario Summary Report was created. Changing cells for each
        scenario are highlighted in grey.
Scenario Summary_Elec_IRR
        Scenario Summary
                Current Values:    Reference Case    High Economic Growth    Low Economic Growth    High on Price    Low on Price    High oil and gas supply    Low oil and gas supply    High renewable cost    Low renewable cost
                    Created by Microsoft Office User on 03/10/2020
Modified by Microsoft Office User on 03/10/2020
Modified by Microsoft Office User on 04/10/2020    Created by Microsoft Office User on 03/10/2020
Modified by Microsoft Office User on 04/10/2020    Created by Microsoft Office User on 03/10/2020
Modified by Microsoft Office User on 04/10/2020    Created by Microsoft Office User on 04/10/2020    Created by Microsoft Office User on 04/10/2020    Created by Microsoft Office User on 04/10/2020    Created by Microsoft Office User on 04/10/2020    Created by Microsoft Office User on 04/10/2020
Modified by Microsoft Office User on 04/10/2020    Created by Microsoft Office User on 04/10/2020
        Changing Cells:
            $C$62    $7.0000    $10.3100    $10.3400    $10.2400    $10.3500    $10.2500    $10.3300    $10.2800    $10.3300    $10.3300
            $D$62    $7.0350    $10.2100    $10.2500    $10.0900    $10.2300    $10.2900    $10.1900    $10.3200    $10.2300    $10.2300
            $E$62    $7.0702    $10.1900    $10.2400    $10.1300    $10.2300    $10.1800    $10.1300    $10.3600    $10.2000    $10.1900
            $F$62    $7.1055    $10.1800    $10.2400    $10.0700    $10.2000    $10.2000    $10.0700    $10.4200    $10.1800    $10.1700
            $G$62    $7.1411    $10.2400    $10.3300    $10.1600    $10.1900    $10.2700    $10.0800    $10.5100    $10.2500    $10.2200
        Result Cells:
            Project_IRR    10.45%    9.33%    9.32%    9.37%    9.33%    9.33%    9.35%    9.29%    9.33%    9.33%
        Notes: Current Values column represents values of changing cells at
        time Scenario Summary Report was created. Changing cells for each
        scenario are highlighted in grey.
Basic Analysis
    Zeta Spenza Project
    Given
    Monza's sales    10,000            MACRS Schedule            Solution Legend
    Monza's price    $65,000            year 1    33%        Value given in problem
    Monza Cost structure per car                year 2    45%        Formula/Calculation/Analysis required
    Body materials    $11,000            year 3    15%        Assumptions, Qualitative analysis or Short answer required
    Engine    $4,000            year 4    7%        Goal Seek, Scenario or Data Table cell
    Drivetrain    $6,000                        Crystal Ball Input
    Battery Pack    $20,000                        Crystal Ball Output
    Electronics    $5,000
    Labor (allocated)
Dima Leshchinskii: Dima Leshchinskii:
based on 10,000 Monzas sold annually    $4,000
    Overhead (allocated)
Dima Leshchinskii: Dima Leshchinskii:
based on 10,000 Monzas sold annually    $2,000
    Consulting Fees    $50,000
    Spenza Price    $80,000
    Spenza Sales projections (number of cars)
            Year 1    Year 2    Year 3    Year 4    Year 5
    Base Case Scenario        5,000    7,000    6,000    4,000    3,000
    Optimistic Scenario        5,500    7,500    6,500    4,500    3,500
    Pessimistic Scenario        4,500    6,500    5,500    3,500    2,500
    Plant Investment    250000000
    Alternative Land Use    15000000
    Plant Capacity    10,000 cars
    Depreciation period    4 years
    Percentage of Debt Financing    50%
    Interest Rate    7%
    Tax rate    21%
    NWC as % of direct manufacturing costs    4.75%
    Monza Sales Cannibalization    1,000 cars
    Historical 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    784 kWh
    Other Spenza direct costs
    Body materials
(other than electricity)    $11,000
    Engine    $4,000
    Drivetrain    $6,000
    Battery Pack    $15,000
    Electronics    $5,000
    Solution
    Choosing Depreciation
            Year 1    Year 2    Year 3    Year 4
    Straight-Line depreciation        62500000    62500000    62500000    62500000        Year    Reference case    High economic growth    Low economic growth    High oil price    Low oil price    High oil and gas supply    Low oil and gas supply    High renewable cost    Low renewable cost
    MACRS Depreciation        82500000    112500000    37500000    17500000        2020    10.31    10.34    10.24    10.35    10.25    10.33    10.28    10.33    10.33
                                2021    10.21    10.25    10.09    10.23    10.20    10.19    10.32    10.23    10.23
    Your recommendation    MACRS Depreciation Method because the machine will be used more in initial years post which the production capacity would decline                        2022    10.19    10.24    10.13    10.23    10.18    10.13    10.36    10.20    10.19
                                2023    10.18    10.24    10.07    10.20    10.20    10.07    10.42    10.18    10.17
                                2024    10.24    10.33    10.16    10.19    10.27    10.08    10.51    10.25    10.22
    Projected Net Income                            2025    10.39    10.44    10.26    10.20    10.43    10.14    10.70    10.38    10.34
            Year 2021    Year 2022    Year 2023    Year 2024    Year 2025    2026    10.51    10.58    10.38    10.22    10.47    10.22    10.87    10.52    10.47
    Sales Volume (number of cars)        5,000    7,000    6,000    4,000    3,000    2027    10.56    10.60    10.42    10.17    10.54    10.20    10.89    10.59    10.52
    Projected electricity cost (per kWh)
Dima Leshchinskii: Dima Leshchinskii:
See "Energy Prices Forecast" tab and adjust for local        $7.0000    $7.0350    $7.0702    $7.1055    $7.1411    2028    10.49    10.52    10.39    10.09    10.51    10.15    10.93    10.55    10.51
                                2029    10.40    10.43    10.30    9.97    10.47    10.02    10.86    10.46    10.37
    Revenues        400000000    560000000    480000000    320000000    240000000    2030    10.38    10.41    10.20    9.91    10.46    9.97    10.83    10.41    10.33
    Direct Costs                            2031    10.32    10.37    10.15    9.92    10.41    9.92    10.88    10.37    10.30
    Body materials
(electricity only)        3920000    5515440    4751157.6    3183275.592    2399393.977    2032    10.24    10.31    10.07    9.86    10.43    9.89    10.89    10.34    10.25
    Body materials
(other than electricity)        55000000    77000000    66000000    44000000    33000000    2033    10.27    10.32    10.06    9.86    10.43    9.87    10.95    10.33    10.26
    Engine        20000000    28000000    24000000    16000000    12000000    2034    10.26    10.29    10.03    9.82    10.39    9.83    10.94    10.31    10.16
    Drivetrain        30000000    42000000    36000000    24000000    18000000    2035    10.18    10.22    9.97    9.76    10.36    9.77    10.92    10.25    10.13
    Battery Pack        75000000    105000000    90000000    60000000    45000000    2036    10.13    10.17    9.89    9.70    10.34    9.74    10.87    10.23    10.08
    Electronics        25000000    35000000    30000000    20000000    15000000    2037    10.08    10.09    9.81    9.63    10.32    9.71    10.83    10.18    10.04
    Total Direct Costs        208920000    292515440    250751157.6    167183275.6    125399394    2038    10.10    10.09    9.75    9.57    10.28    9.66    10.78    10.16    10.00
    Fixed Costs                            2039    10.05    10.10    9.74    9.57    10.30    9.62    10.78    10.16    9.95
    Labor        40000000    40000000    40000000    40000000    40000000    2040    9.98    10.04    9.65    9.55    10.24    9.59    10.80    10.14    9.92
    Overheads        20000000    20000000    20000000    20000000    20000000    2041    9.95    10.04    9.57    9.55    10.21    9.56    10.80    10.16    9.88
    Depreciation        82500000    112500000    37500000    17500000    0    2042    9.93    10.00    9.56    9.58    10.17    9.54    10.78    10.09    9.83
    EBIT        48580000    94984560    131748842.4    75316724.41    54600606.02    2043    9.87    9.93    9.48    9.55    10.12    9.49    10.70    10.06    9.74
    Interest        8750000    8750000    8750000    8750000    8750000    2044    9.87    9.96    9.46    9.58    10.12    9.43    10.73    10.05    9.72
    EBT        39830000    86234560    122998842.4    66566724.41    45850606.02
    Taxes        8364300    18109257.6    25829756.9    13979012.13    9628627.265
    Net Income        31465700    68125302.4    97169085.5    52587712.28    36221978.76
    Projected FCF
    Monzas Lost Profit
    Volume    1,000 cars
    Price    $65,000
    Direct Costs (per car)    $41,784
    Lost Profit (After-Tax)    64958216
                        
        Year 0    Year 1    Year 2    Year 3    Year 4    Year 5
    OCF        120878200    187537802.4    141581585.5    77000212.28    43134478.76
    CapEx    250000000
    Investment in NWC        9923700    13894483.4    11910679.99    7941205.591    5956471.214
    Opportunity Costs
    Alternative Land Use    15000000    15000000    15000000    15000000    15000000    15000000
    Lost Profit from Cannibalized Sales        23174216    23174216    23174216    23174216    23174216
    FCF    -265000000    72780284    135469103    91496689.51    30884790.69    -996208.4561
    WACC (From WACC Tab)    4.90%
Dima Leshchinskii: Dima Leshchinskii:
Use value of WACC from
WACC Tab
Before you find it, temporary use 8% as a plug
    
Dima Leshchinskii: Dima Leshchinskii:
based on 10,000 Monzas sold annually    
Dima Leshchinskii: Dima Leshchinskii:
based on 10,000 Monzas sold annually    
Dima Leshchinskii: Dima Leshchinskii:
See "Energy Prices Forecast" tab and adjust for local    NPV    31461299.68
    IRR    10.45%
    Six Scenarios
            Year 2021    Year 2022    Year 2023    Year 2024    Year 2025
    Reference case        $5,000    $7,000    $6,000    $4,000    $3,000
    High economic growth        $5,500    $7,500    $6,500    $4,500    $3,500
    Low economic growth        $4,500    $6,500    $5,500    $3,500    $2,500
    High oil price
    Low oil price
    High oil and gas supply
    Low oil and gas supply
    High renewable cost
    Low renewable cost
WACC
                                Value given in problem
                                Formula/Calculation/Analysis required
                                Qualitative analysis or Short answer required
    Your assumptions
    Risk Free Rate of 10 year Australian Bond is taken
    Marlet Risk Premium is taken From Australian Government Bond
    To compute the beta of the company, competitors beta is used to compute the mean beta
    Comparable Companies Unlevered Beta
    Company
Author: Author:
Please replace stabs below by real peers names. The number of peers does not have to be five    Levered Beta    Market Value of Debt    Market Value of Equity    Debt/ Equity    Equity/ Total Assets    Marginal Tax Rate    Unlevered Beta
    Peer Company A    0.79    10,692,898,000    183,820,000,000    0.06    0.945    23.74%    0.398
    Peer Company B    1.14    40,940,000,000    41,773,000,000    0.98    0.505    43.66%    0.725
    Peer Company C    1.47    65,924,000,000    43,591,000,000    1.51    0.398    18.84%    0.857
    Peer Company D    2.07    833,048,100    5,960,000,000    0.14    0.877    28.00%    1.056
    Peer Company E    1.33    3,141,512,000    13,990,000,000    0.22    0.817    28.00%    0.687
    Median                            0.725
    Mean                            0.744
    Relevered Beta    Mean Unlevered Beta    Target Debt/ Equity    Target Marginal Tax Rate    Relevered Beta
    Zeta    0.744    0.5    0.21    0.6663
    WACC Calculation
    Company's Capital Structure
    Debt to Total Capitalization    50.00%
    Equity to Total Capitalization    50.00%
    Debt to Equity Ratio    0.50
    Cost of Equity
    Risk-free rate    0.81%    Taken from Australian 10 year bond
    Market risk Premium    5.20%    Australian Market Risk Premium
    Levered Beta    0.67
     Cost of Equity    4.275%
    Cost of Debt
    Cost of Debt    7.00%
    Taxes    21.00%
     After Tax Cost of Debt    5.53%
    WACC    4.90%
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
Electricity Scenario Summary
        Scenario Summary
            Reference case    High economic growth    Low economic growth    High oil price    Low oil price    High oil and gas supply    Low oil and gas supply    High renewable cost    Low renewable cost
        Year 1    $10.31    $10.34    $10.24    $10.35    $10.25    $10.33    $10.28    $10.33    $10.33
        Year 2    $10.21    $10.25    $10.09    $10.23    $10.29    $10.19    $10.32    $10.23    $10.23
        Year 3    $10.19    $10.24    $10.13    $10.23    $10.18    $10.13    $10.36    $10.20    $10.19
        Year 4    $10.18    $10.24    $10.07    $10.20    $10.20    $10.07    $10.42    $10.18    $10.17
        Year 5    $10.24    $10.33    $10.16    $10.19    $10.27    $10.08    $10.51    $10.25    $10.22
        Result...
SOLUTION.PDF
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here
Have files?