Excel and Open Notes on a Valuation Exam

So my valuation professor is allowing our class to use Excel, open notes, and open book for a midterm.

What kind of excel models, solvers, macros, etc, would you prepare for the test (considering time-efficiency).

Here is a overview of whats being tested and what to watch out for:

"What follows is a pretty comprehensive list of topics that you should be prepared for in order to succeed in the midterm examination (please note: while this list contains the most likely topics, it is not necessarily exhaustive...though, its pretty close):
• Definition of Free Cash Flow to the Firm (FCFF)
o Basic, Direct Method (like we studied in the beginning of the semester)
o Indirect Method (by looking at cash flows to creditors and equityholders)
o Accounting Definitions to know (e.g, how would you calculate?)
o Revenue, COGS, Gross Profit, Profit Margin, Operating Expenses (fixed, variable), Depreciation (MACRS vs. SLD), EBIT, NOPAT, Cash Flow from Operations (know how to get from NI to CFO...and remember that CFO is not the same term as Free Cash Flow).
• Accounting Identities
o Balance Sheet (i.e, Assets = Liabilities + Equity)
o Income Statement (i.e., Revenues - Expenses = Income)
o Stmt of Cash Flows (i.e., CFO + CFI + CFF = change in Cash)
o Note: You should generally understand how the statements relate to each other
• Simulation and Scenario Analysis
o What is a Monte Carlo simulation?
o Understand the general differences between various distributions (e.g., how is the normal distribution different from the uniform distribution?). In this context, understanding the shape of the distribution is what matters. I would expect that you know the shapes of at least the following: Normal, Uniform, Triangular, Poisson, and LogNormal.
o Be able to do a basic Goal Seek and/or a Solver problem in Excel (you will likely need to do one of these on the exam, so be prepared!)
o You will not need to do any @Risk problems on the exam
• Cost of Capital
o Cost of Debt
 Understand how to calculate the Cost of Debt
 Be able to calculate YTM, given bond price and terms (hint: you could use solver or goal seek). Remember: YTM is conventionally quoted annually. You have to "periodicize" it (is that a word?) when doing the calculations
 Be able to calculate bond price, given, bond YTM and terms
 Be able to calculate a bond term (e.g., number of periods), given other terms, YTM, and price (hint: you could use solver or goal seek).
 Be able to adjust bond price based upon a presumed level of default risk and recovery rate (hint: simply adjust the cash flow in the numerator of the term that's at risk of default with the following formula:
AdjCF = (OriginalCFRecovery Rate)Pr(Default) + (OriginalCF)(1-Pr(Default)).
It's just a weighted average. Then continue with calculating bond price with the adjusted CF.
o Cost of Equity
 Understand how to calculate Cost of Equity in Different Approaches
 CAPM (single factor model). You should know how to use the SML equation.
 You should be able to interpret CAPM regression results
 alpha (intercept term)...watch out for whether it is significant or not (you generally want a t-state with an absolute value greater than 2)
 beta (slope term)
 R2 (measure of how well the model explains the variability in the dependent variable; the dependent variable in this case would be the particular stock in question).
 Beta (Slope Term)
 You should know that beta measures market sensitivity, and that:
Beta(Market) = 1, Beta(Rf) = 0, and for example: if Beta(stock) = 1.2, and the market risk premium is estimated to be 10%, the stock risk premium is expected to be 12%
 Know how to calculate beta from historical data:
Beta = Cov (i, m) / Var (m) = Stdev(i)Stdev(m)Corr(i,m) / Var(m)
= Corr(i,m)[Stdev(i) / Stdev(m)]
 Know how to calculate beta from comparables data:
 Why do we use comparables?
 What beta is usually published in comparables data (levered beta)
 What's the procedure for incorporating comp beta to your company?
 Collect comparables' beta and capital structure (D/E) information
 Style note: I would personally include your own company/data in the list of "comparables" because it is a member of its own industry, and it also helps give at least a little weight to whatever the company's own beta is today.
 Unlever the comp betas based on each comp's respective D/E
(you should have the unlever formula handy - and you can use "Method 2": D/E ratios stable)
 Average the unlevered betas
 Take that average beta and lever back up to your company's D/E or assumed D/E level; now you have a new beta to use in SML.
 Calculate cost of equity for your company using the new beta, per usual SML equation.
 Fama/French 3 Factor (multifactor) Model
 What's the purpose of a Multifactor model versus a single factor model? How do I know if it is any better: (Hint: is there a measure to tell how effective the model might be?)
 What are the 3 Factors? (Market, SMB, HML) What do those mean? How would you calculate a cost of equity if I gave you FF inputs (perfect example, p.125-127)
 Good Ol' Fashioned DDM (Dividend Discount Model)
 Know how to calculate either value or cost of equity in the DDM. Remember that if you are solving for cost of equity, that's the "r" or discount rate in the DDM, so you'd probably need to use solver or goal seek to calculate it. Value is the left-hand side of the DDM. You should know how to use DDM for the following cases:
 No Growth (simple perpetuity)
 Constant Growth (V = Do(1+g)/r)
 Non-Constant Growth (at most I would only include 2 different growth rates)
 Basic procedure is to manually discount the cash flows using the first assumed growth rate until the change. Then, assume the 2nd growth rate is in effect (constant growth from that point on) until perpetuity.
 Don't forget!!: When you discount that second stage, the PV will bring you back to the "n-1" period. You then have to discount that PV from the "n-1" period back to "0".
 Example: 5% for 2 years then 3% thereafter. Grow your dividends by 5% for year 1 and year 2 and discount them to year zero manually. Then grow your dividend from year 2 to year 3 by 3% and so on. But recognize that you basically have a "constant growth" type of problem starting in year 3. So you can use the constant growth formula (using the second stage growth) to PV (year 3 to infinity). But remember: that PV then is stated as of year 2! So you then PV whatever that PV(year 3 to infinity) was back to year 0 (that would be bringing it back 2 years).
Common mistake: PV that PV(year 3 to infinity) back 3 periods. NO.
 Note: Be careful to see if the announcement says, "just paid" or "will pay today" or words to that effect to see whether to include year 0 (which isn't discounted). If they "just paid" or "already paid," then do NOT include anything for year 0.
o Cost of Preferred Stock
 Pref Stock is basically like a perpetuity. I wouldn't get too wrapped up on this for now, but do now the calculation/formula.
• WACC
o Know how to calculate weighted average cost of capital
o It is basically just like the term says: a weighted average of the underlying cost of each asset times the weight of that asset in the overall capital structure.
o Be Careful! : make sure you include the term (1-t) for tax-preference items (most commonly, debt).
o Note: WACC is used to discount FCFF to get Enterprise Value. This ties back to Ch. 6.
o Single WACC versus Multiple WACC (e.g., Divisional or Project WACC).
 Pros/Cons of each?
 For what kinds of firms would single WACC be better? What about divisional WACC? (Hint: think about consistency in the lines of business)
o Multiple WACC / Divisional WACC / Project WACC
 What are the two primary ways to finance individual projects/divisions?
 Using corporate (parent) financing
 Using subsidiary or project-based financing
 What are the pros/cons of each?
 What is recourse vs. nonrecourse financing? What is bankruptcy remoteness?
o Divisional WACC
 If the division is a "single line of business (LOB)" then we can look to comparable "pure-play" public companies in the industry that represents that LOB to get an estimate of what their cost of equity is. Then, we can look at the cost of debt for our particular firm (that the division is in), and combine it with the average debt ratio of the LOB industry to get to what that division's WACC should be.
 Said differently: To get Divisional WACC, combine the following (p.162-163)
 Cost of equity for pure plays in an industry that looks like your division's LOB
 You can get this from an Ibbotson guide or you can do it by getting the industry's beta and using CAPM to get the industry's cost of equity.
 Cost of debt for your own firm (that the division is in)
 Debt ratio of the industry
 Note: You won't need to do this from scratch. Just appreciate why its done this way.
o Project-Based Financing
 If we are evaluating project based financing, we are going to need to know a few things, what are the Project's FCFs and the Project's WACC?
 So what are the Project FCFs? (p. 167-169) Similar to FCFF (if the "Project" were "the Firm").
 But, the Project's WACC might get a little difficult, because we need: Debt specifically used to finance this project (D), and the cost of that debt, as well as the equity value of the project (we don't have this!) and the cost of equity (we don't have this either). So to get those last two:
 Calculate FCFE (Project Free Cash Flow to Equity):
Project FCF - Creditor Cash Flows (specific to this project) (p.169)
 Next, to get cost of equity, we do need to do a comparables analysis like we went through in a previous section above. However, a big part of that was the relevering process, which depends upon our current project D/E....but we don't have the E! So, we need to run an iterative process:
 1. Collect public comps, including their levered equity betas and their D/E ratios
 2. Unlever their levered equity betas to get unlevered equity betas
 3. Average the unlevered equity betas
 4. Take the averaged unlevered equity beta and re-lever it using your current project's D/E ratio. The debt figure comes from the nonrecourse debt specifically used to finance this project, while the equity figure will come from the historical investment the company put into the project. Obviously, this is not correct, because we need the actual equity value...but it's a place to start. We'll correct it in the next few steps.
 5. Take the levered equity beta you just calculated in #4 and use it in the CAPM SML equation to get a Project Cost of Equity.
 6. Take the Project Cost of Equity from #5 and use it as the discount rate to PV the Project FCFE's you calculated (in bold a few lines above). If its a perpetuity, you can just take the Project FCFE's and divide by the Cost of Equity. Now you have an estimate of the equity value. But remember, in this first go around, you used historical investment, not actual equity value in the D/E ratio, so we are going to..
 7. ..iterate the process! Take the equity value you just calculated in #6 and repeat steps 4-6, except in stead of "historical investment" for E (in D/E), you will use the equity value you just calculated in #6.
 Keep repeating the process until the equity values in #6 begin to converge. The final line will have a better estimate for equity value and a better estimate for your cost of equity.
 This is the cost of equity and equity value (for the project) that you will use (together with the cost of debt and debt value) in the formula for the Project WACC!
 This project WACC can now be used as a hurdle rate.
 Debt Capacity
 Debt capacity is defined as the amount of debt that a project could take on without affecting the Firm's credit rating or significantly increasing the Firm's cost of debt. That doesn't mean that it is the amount that the project will actually take on.
 The higher the debt capacity, the lower the project's risk and the lower the project's implied cost of capital will be. At the limit, at 100% debt capacity (meaning that the project can be fully financed with debt without impacting the Firm's credit rating), the project's implied cost of capital approaches the Firm's cost of debt.
 The lower the debt capacity, the higher the project's risk and the higher the project's implied cost of capital will be. At the limit, at 0% debt capacity, the project's implied cost of capital approaches the Firm's cost of equity.
 This is a subjective (and imprecise) way of estimating project cost of capital, but it is used in the industry and important to know. There isn't a precise mathematical relationship between debt capacity and cost of capital, nor is it a linear relationship as the book implies. Rather, it is important for you to know what the general relationship is between the two (negative correlation).
• Free Cash Flows Revisited (Ch. 6).
o You do not need to memorize any of the derivations from the first part of lecture on Thursday.
o You do need to go through the announcement I put up on Canvas related to that lecture. Keep a list of any formulas from that announcement handy.
o You should understand the distinction between operating and non-operating income.
 What things are included in op. income? What things are included in non-op. income?
 Why is it important to separate the two? What does it simplify?
 Where is non-op. brought back in the computation of FCFF?
 Hint: Think about what FCF/O "Free Cash Flow from Operations" means, and how that relates to FCFF.
 Understand the difference between the Direct and Indirect approaches to FCF (this was also specified in the first section above. Again, no need to memorize the formulas, but keep them handy, and understand what they are trying to say (how the various equations organize the information).
• Forecasting Future Financial Performance
o Know the Steps:
 1. Perform an analysis of Historical Financial Statements
 2. Construct Pro Forma Financial Statements for the Planning Period (usually, 3-5 yrs).
 3. Convert Pro Formas into Cash Flow Forecasts (using FCF relationships)
 4. Estimate Terminal Value of FCFF
o You should know the definitions of:
 Forecast Period (equals Planning Period plus Post-Planning Period)
 Planning Period
 Terminal Value
Whew! That should just about cover it. There's a lot of information, so please make sure you go through and organize your notes. Remember that even though this is open book/notes/laptop, you should review everything and keep them organized.
You do not have a lot of time to waste looking up information that you should already know; and you don't want to spend time hunting for info during the exam. That said, the exam is not intended to be "tricky" or have any "gotcha" questions.
""

Throw me your ideas! Thanks