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 SummaryIntroductionCapital Budgeting recommendationsRisk analysis (including discussion of scenarios, breakeven, optional Monte Carlo simulation, possible discussion of risk factors)ConclusionReferencesAppendix (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 yearWhat 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 of1) 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 beta4) 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 companiesIs 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 assumptionsSecond, 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.