NCIG

1. NCIG MIR Division - Meeting II 10/08/2014

2. Agenda • Trend & Risk Analysis • Qualitative • Quantitative • Regression Analysis • Time-series Analysis • What-if Analysis • Business Modeling • Excel • Assumptions • Financial Statements • Schedules • Circularity • Industry Pitches & Economic Research Review • Week Assigment • Next week…

3. Trend & Risk Analysis

4. Trend Analysis • Industry trend: Driver & Direction. • How strong is a trend?! • Depends on how much you can explain it • Find arguments: • Qualitative • Facts • Reasoning • Quantitative • Data • Stats

5. Qualitative Trend Analysis • Facts, reasons, what consumers are looking for: • Policies and regulations • Cultural changes • Movements, programs and initiatives • Business Strategies

6. Quantitative Trend Analysis • Fetch data! • Numbers are convincing... • What kind of numbers are we looking at? • Change over time (mainly in percentual terms) • Indices • Correlation • Volatility • Growth rates (CAGR is tipically used)

7. Industry Metrics • Industry Specific • Restaurant Performance Index • Restaurant Traffic and Sales (Same-store sales) • Financials • Costs (breakdown) • Revenues • Macroeconomics • Disposable Personal Income • CPI (food) • Consumer Confidence Index • GDP per capita • Commodity prices

8. Putting it together • The qualitative and quantitative analyses should support each other • Facts are interesting, numbers are compelling, reasoning is appealing • Three intuitive steps to do a trend analysis: • Facts • Data • Logical explanation

9. Trend: LNG becomes the new puppet among fossil fuels • LNG also benefitsfrom increases in natural gas resources and market expansion. LNG imports are expected to grow • In order to meet this growing demand, LNG liquefaction capacity is supposed to enlarge about fivefold in 25 year, from 133.5 million tonnes per annum (mtpa) in 2005 to 720 mtpa in 2030. • Since the US enjoys great supply of natural gas, it is predicted that the US uses part of this oversupply of natural gas to produce LNG and reverting its trading position from importer to exporter.

10. Measuring Risk • What-if analysis (sensitivity analysis) • If something happened, how would it affect the industry or company? • Think about the potential risks that can compromise the revenue generation process! • Come up with at least 3 possible scenarios and quantify the risk in terms of • Revenue • Profit • Costs • Cash Flows • Debt • Steps in modeling: • Design – Paper first • Collect Data • Build the model in Excel • Make your conclusions

11. Business Modeling

12. Excel Settings: Set Up • Open Options (Alt+FT): • Formula: • Iterations: OFF • Enable Background Error Checking: OFF • Advanced: • After pressing Enter, cursor down: OFF • Allow editing directly in cells: OFF • Show Paste Options Button: OFF

13. Assumptions • Historical Picture: • Ratios showing how the company is doing: • Growth ratios • Revenue • COGS • Percentage ratios • COGS • Inventory • A/P, A/R • Premises for future behavior of the financials • Average Historical Performance • More complex analyses such as time-series and regression analyses

14. Income Statement • Structure the Income Statement, fitting the type of company under analysis • Projecting the Income Statement accounts: • Properly link the year of the account with the assumption related to that account • Do so putting ALWAYS as first argument the assumption (This is VALID for all the other sheets) • Leave D&A for a separate spreadsheet!

15. Calculations for Mixed Accounts • PP&E • Beginning • Capex • Depreciation • Ending • Intangibles • Beginning • Additions • Amortization • Ending • Retained Earnings • Beginning • Net Income • Dividends • Ending • Operating Working Capital (OWC) • A/C • Inventory • Other Current Assets • A/P • Other Current Liabs. • OWC

16. Debt Schedule & Interest Exp. • A company pays interest on Debt and earns interest on Cash • Calculate interest expense from ST Debt: Revolver • Same for LT Debt: • Each Debt separately • If many and for the sake of simplicity • Group them • Weighted Interest Rate • Avg. Repayment Schedule • Calculate interest earned from Cash

17. Balance Sheet & Classifying Accounts • Structure the Balance Sheet • Linking to Assumptions and Calculation sheets • Balancing projected years of the Balance Sheet • Cash • Revolver • Classifying Accounts into: • Operating • Investing • Financing • Mixed

18. Cash Flow Statement • Cash Flow Statement keeps track of the yearly change in the Balance Sheet • Operating CF • Investing CF • Financing CF • Accounting for changes: • Assets • Negatively related with CF: -(DLT Assets) • Liabilities • Positively related to CF: (DLT Debt)

19. Balancing the Model • Use the Revolver and Cash accounts to compensate for differences in Total Assets and Total Liabs. and Equity projected. • Max/Min Functions • IF function • Turning Iterations ON • Dealing with Circularity!

20. Cash Circularity Interest Income (IS) Interest Income (Calc) Net Income (IS) Cash Cycle Cash (BS) Cash from Op. (CFS) Ending Cash (CFS)

21. Revolver Circularity Interest Expense (IS) Interest Expense (Calc) Net Income (IS) Revolver Cycle Revolver (BS) Cash from Op. (CFS) Ending Revolver (CFS)

22. Control Circularities & Testing the Model • What is a switch and where should it be plugged in? • Ways to build a switch: • Macro • Formula • Testing: • Stress Test • Matrix Integrity • Output Consistency

23. Industry Pitches &Economic Research

24. Picking the 2 Industries • Discuss: • Pros and Cons • Level of Interest • How could we analyze further this industry… Any potential players • Voting • Breaking into 2 teams

25. Assignments • Industry Presentations • Background • Current Scenario • Operations (Revenue Generation from Supply to Demand) • Trends and Risks • Qualitative • Pick 1 Trend and 1 Risk to do a Quantitative Analysis

26. Next week… • Valuation • DCF • WACC • Cash Flows • Discounted Economic Profit • ROIC • WACC • Multiples • Financial Multiples • Industry-specific Multiples • Financial Ratio Analysis • Peer Comp Financial Ratio Analysis • Cross-Section Analysis