Total Annual Labor Costs Completion Of Project 1
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?
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:
Capital Budgeting recommendations
analysis (including discussion of scenarios, breakeven, optional Monte
Carlo simulation, possible discussion of risk factors)
Appendix (Excel file as a separate document)
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?
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)
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
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.
case says “Labor is unionized; number of workers and wages do not
depend on the number of units produced” Why is it important?
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?
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?
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?
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?
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
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):
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
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”
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?
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.