- If it says a % of SG&A, then don't give it as a % of Revenue. Pay attention to what the % is of.
- Make sure you copy the right numbers down to project
- Never accidentally put the = sign instead of the - sign
- BE VERY CAREFUL WITH THE SIGNS IN YOUR DRIVERS, MAKE SURE THOSE SIGNS ARE CONSISTENT. ALSO THE MOST ERRORS YOU MAKE ARE WITH SIGNS FROM DRIVERS WHEN PROJECTING. SUCH A HUGE PROBLEM. PAY CLOSE ATTENTION TO THAT.
- When linking in Depreciation, make sure you don't accidentally link in something else
- On an amortization or PPE schedule, 'Amortization Starting Balance' doesn't mean you pull last year's amortization balance, it means you pull last year's Intangibles and apply the amortization rate specified.
- When doing % growth rates, don't forget the (1+%), you mistakenly did just (%). Also make sure you * instead of +, when applying growth rate to a cell.
- In the debt schedule, be super careful with what you anchor
- Deferred Taxes = Cash Taxes - Book Taxes
- With the negative/positive signs for taxes etc, be consistent, stick with one format
- Remember to factor in amortization for intangibles, intangibles are amortized... on the BS
- When looking for Revenue, make sure you select total revenue and not just a segment revenue
- To do a sensitivity table, you have to create a data table after linking in the thing you want to sensitise on the top left. Then Alt, D, T.
- For growth rate, don't multiply by just the growth rate. Multiply by (1+ Growth Rate)
- When putting in change of current assets, into the CFO... you put "-" sign, but make sure to put brackets after that negative sign so e.g. +-(K143-J143)
- 'Additional Funding Required' will be for the projected period, not the historical period
- If 'Actual Liquidity Before Additional Funding' is defined as 'Ending Cash from Prior Period + Undrawn Revolver from Prior Period + All Cash Flow Statement line items except for Share Issuances/Repurchases, Net Debt Proceeds, and Revolver Repayment'... If you Ctrl + R something, make sure you don't lose your formula to project (you lost it and it took the formula from the historical assumptions instead)
- Make sure that your brackets and negatives are in EXACTLY the right places
- Capital Employed is Debt + Debt-like Items + Equity - Cash
- Return on Capital Employed = ROCE/AVERAGE CAPITAL EMPLOYED, you take the average of the two capital employed values.
- (Debt+Lease Liabilities)/Equity does not mean that you average the equity, you only average the equity with ROCE etc.
- To create an annualised Balance Sheet, you want to use INDEXMATCH, rather than SUMIF or AVERAGEIF, because it depends on the very last number... and INDEX MATCH will retrieve that for you. With balance sheet, you just want the very last number at the end of the 2H period.
- When you fix a formula, make sure you copy that formula across.
- Make sure you don't accidentally Ctrl + X
- Adding something on, or EBITDA (before something) is the same as Excluding (not accounting for).
- EBITDA is a proxy for CFO (bc you add back D&A there)... EBIT is a proxy for FCF (because FCF is after capex, similar to EBIT being after D&A).
- Only do one column. So you have a clear idea of what's historical, and what's projected. Then right at the end, you drag everything across.
- Net sales for Steel Dynamics would be = Shipment Per Ton x Average Sale Per Ton It's like doing Units x Average Unit Cost
- Operating Margin by Segment means that you divide by JUST that segment's revenue, as opposed to the full revenue
- When you are Index matching, remember to completely anchor the index array
- For Unlevered beta, you divide by the 1 + D/E * (1-T) + P/E to remove the cap structure For Levered beta, you multiply the unlevered beta by the cap structure to factor in the cap structure
- If, to get % debt, you do debt/(equity + debt + preferred) Then to get debt, you have to multiply that % with (equity + debt + preferred)
- Can setup a full PP&E schedule with Capex and D&A into different segments, but for a quick and dirty analysis you do Depreciation as % of Revenue
- During Terminal Period, both the discount rate and the cash flow growth rate stay constant. Cash flows change ofc, but the cash flow growth rates stay constant.
- When using multiples method, you always apply the EBITDA multiple to the EBITDA in your final year of your explicit forecast period. That will give you Terminal Value based on Multiples Method, then you calculate the Present Value of that Terminal Value.
- So you'll have your projected UFCF, and just below that you'll have your PV of UFCF, the PV of the UFCF is what matters, that's what has been discounted to today.
- When calculating Terminal Value from Perpetuity Growth, use the Final Forecast Period UFCF (not the PV of the UFCF)... use the actual projected UFCF.
- When calculating working capital changes, make sure to subtract CHANGES. Not just the actual number e.g. on Accounts Receivable.
- For CFS when doing assets & liabilities changes - Just always do 'Current Year - Previous Year'. Put subtract in front, if asset.
- BASE Analysis for RE (used for different things on the BS, think about everything that will flow into ending RE): Beginning 60 Addition +15 Subtraction -5 Ending 70
- Link every single circularity to the circuit breaker, you need to trap it. So that when you 0 and back to 1, then it will reset itself. Enable iterative calculations will allow you to flesh out your circularity issues (disabling it disables circularity and will give error).
- Sensitivity table is about picking 2 inputs and finding it's impact on 1 output
- Non-recurring expense is difficult to forecast and is usually taken as 0
- Cross sheet formulas: unlike tower model where all input cells are on the same sheet, in the cross sheet formula it's only going to the first reference. So when building formulas, start with the cell referencing from another worksheet first, then the cell in the current worksheet.
- Depreciation is a number you need in your IS and then also in the PPE schedule. So may as well do the PPE schedule first and then link from there.
- Dividends are not paid to option holders - so Dividends per share is based on basic WASO.
- Make sure you don't forget other “operating current liabilities” when doing the BS and CFS
- To represent change in revolver on the CFS, do the ‘current year - last year revolver’ (that's what it's supposed to be mathematically anyway, i.e. even if your revolver is 0)
- Remember that Capex is a negative cash outflow.
- Remember to beauty save (Ctrl + Home)
- Remember to have Cross-Sheet references first, because they will let you audit those first.
- Named cells will give you absolute references.
‣
- Net WC = Operating Current Assets - Operating Current Liabilities. You need a change in this. So you need the historical year also. Operating Assets - Operating Liabilities, But you treat OWC as an asset so do "-("
- Make sure that you link interest expense and income to the RIGHT thing.
- Different tax rates Effective and Marginal are different. For taxing EBT, we take the effective tax rate.
- If you've been given a dividend per share growth rate assumption, then use the growth rate for that
- Make sure you link the correct column year to the correct one in the other sheet
- Book value of debt is a good proxy for market value of debt (if not in distress). Market value of equity is different to book value, as it’s publicly traded and may include a growth premium.
- We try as much as possible to get the market value of items
- Interest Expense on debt is calculated by interest rate x average debt balance
- Can do just “x beginning debt balance” to avoid a circularity
- 'Investment in Properties' is an asset, so do "-(" on CFS when reflecting the BS impact on a CFS you are building
- Goodwill is straight-lined usually (unless you forecast to impair it), this is why you don't include it in your CFS
- This would be OK because there's no change if it's straight-lined, so nothing to account for on CFS (just 0s anyway)
- As far as possible you use the Market Value for Enterprise Value
- Enterprise value is the market value of NOA, Invested Capital is the book value version of EV
- BS not Balancing: 1) Look for the amount of the difference or half the difference in the CFS 2) Difference is the same in each period? Beginning balance issue 3) Sanity Check 4) Matrix integrity
- Common errors: - Incorrect sub-totals (always copy across historical formulas, can see this if BS is out of balance by a value not included in the sub-total) - Sign the wrong away around on CFS (divide the BS difference by 2 and look for this amount) - CFS is a good place to start looking for circularities (go on error checking, will show you them, look for the smallest number because circularity will most likely start from there)
- Hard-coded number mistakes can be in just one cell also, they don't have to be carried forward to all cells. Look for them in just one cell also.
- Multiplying PE by EPS won't get you the Enterprise Value - it will get you Equity Value.
- Get the net debt numbers, of the company you are valuing - from the balance sheet.
- Forward multiple means that only the operating value driver is forecasted, not the EV or the Equity Value.
- A PE mismatch between 2 companies doesn't mean much - you have to see it in the context of a fundamental driver - in this case, the growth in EPS. P/S, sales belongs to both equity and debt, therefore you can't use P/S - but if it's an all-equity financed company with no debt - then you can use P/S.
- Drivers of valuation are return on capital, cost of capital, and growth
- Remember to calculate the PV of Terminal Value, just calculating the Terminal Value isn't enough... You need the PV of it.
- FCF*(1+g)/(WACC-g)
- This is the Gordon Growth formula to calculate Terminal Value
- For a DCF, you use the Net Debt value of whichever respective year you are valuing
- I.e. you don’t change the year, just use the same one
- Just remember that assets can only be operating or investing, they can never be financing. Liabilities can only be operating or financing, they can never be investing.
- Use circ switch and average balances. If circ switch isn't a named cell, then you have to absolute reference it - this is a very common mistake. Make sure that circ switch is named.
- Your interest expense and income should be... Interest Rate x Average Balances on the BALANCE SHEET. You won’t find the total debt on the IS.
- P/E is NOT
- Equity Value/EPS - because that's not a per share metric. You need Equity Value Per Share (share price)/EPS, for it to be comparable
- Equity Value / Net Income will give P/E
- Share Price / EPS will give P/E
- Even if net debt is negative, you're still going to subtract it when going from Enterprise Value to Equity Value.
- When you link interest expense, make sure the sign matches your sign convention in the IS.
- ROE has to be seen in the context of risk to equity holders.
- Operating working capital is only the CURRENT assets and liabilities that are operational, there may be non-current operational, but you don't include them.
- Net Cash will go towards Source - An Existing Cash balance should be taken as a Source of Funds.
- The Gordon Growth method is just to get the Terminal Value.
- For LBO, Make sure it's a % of your EV in sources. It can't be a % of your Equity Value.
- For LBO, Your debt schedule in your LBO is based on your projected debt, i.e. the new debt of the company.
- For LBO, % of debt repaid is just debt ending/debt beginning-1
- Operating leverage - level of fixed costs. If you have high fixed costs, you'll be more affected by downturns ofc.
- 3 drivers of Beta: 1) Cyclicality 2) Financial Leverage 3) Operating Leverage
- For private companies, we get an industry beta (which is unlevered) and then lever it up using Private Company's Capital Structure
.
- Cost of equity formula implied. Gordon Growth Model.
- For key ratios - look at Marico Forecast DCF Template
- For LBO, A negative net debt (more cash than debt - or even just no debt) - means that in the use of funds there is no "refinanced debt". My Q) In this case we had no debt, but if we had negative net debt (more cash than debt) - but technically we still had debt, would we have to refinance that debt (and would that be a use of funds in that case)?
- The answer to the above is that for LBO, The cash is used to pay off the debt
- Tax expense sign is incorrect.. make sure you have correct sign for it
- Everything operating is a part of the NOPAT to UFCF calculation
- Never take WASO, always take shares outstanding as on the latest date. to calculate Look for share issuances/repurchases and stock splits. Many companies have multiple classes of shares, and different classes have different voting rights. We don't take preferred shares, we don't take weighted average shares - you don't take these when calculating Market Cap or Equity Value. Because preferred stock is different to Equity Value.
- ADR and GDR are already a part of Shares Outstanding, so you don't double count them.
- Always use Outstanding Options, as opposed to exercisable options.
- RSUs won't be added to the Diluted Equity Value - they will be added to the number of diluted shares. They are not equity value, they are just shares.