Certificate

Morgan Stanley Huaxin Analysts Program 2020

Issued to:	Xueyi Xiao
Issued date:	August 19, 2020
Issued by:	AMT Training

Excel fundamentals

During this session, participants are introduced to Excel keyboard shortcuts, efficient worksheet navigation, formula construction and financial functions. The session includes lots of practical, real life Excel based exercises.

Key topics:

• Keyboard shortcuts
• Building a simple budget
• Formulas
• Financial functions
• MATCH
• Data validation

Financial statement analysis

Accounting fundamentals

The aim of this session is to provide participants (particularly those who have no prior knowledge of finance) with an introduction to financial statement analysis. The income statement, balance sheet and cash flow statement are introduced, and the key interactions between the income statement and balance sheet are explained. Participants will build simple financial statements using a list of transactions.

Key topics:

• The structure of financial statements
• The income statement
• Balance sheet
• Cash flow statement
• Key links between the financial statements
• How business transactions are reflected in the financial statements

Income statement

Throughout this module participants analyze a group of companies in the food manufacturing industry in order to calculate several income statement metrics. The matching / accruals concept and its impact on the income statement is covered in detail and the link between the income statement and the retained earnings account is investigated. Profitability is analyzed in various ways, using real companies’ financials to calculate key indicators of operating and financial performance. Participants complete a full profitability comparison for the peer group.

Key topics:

• Sales recognition
• Fixed and variable costs
• Accrual accounting and link between earnings and cash flow
• Expenses or assets - key concepts explained
• Adjusting cost of goods sold and selling, general and administrative expenses for depreciation and amortization
• Adjusting earnings for non-recurring items to produce normalized earnings estimates
• EBITDA, EBIT, net income and EPS
• Margin calculations

Working capita

This module provides participants with an understanding of the importance of working capital in the context of a company's financing structure and cash flows. The difference between working capital and operating working capital is analyzed using several ratios. Participants complete a working capital analysis for the peer group.

Key topics:

• Inventories, receivables, payables, prepaid items and accruals
• Definition of working capital and operating working capital
• Operating working capital and the cash cycle
• Working capital ratios and day calculations

Non-current assets

This session illustrates the difference between tangible and intangible assets and their use in a business. Participants learn about purchasing, depreciating / amortizing and selling tangible and intangible assets, and how these transactions are reflected in the financial statements.

Key topics:

• Difference between tangible and intangible assets
• Difference between identifiable intangibles and goodwill
• Finite life vs. indefinite life intangibles
• Capital expenditure and asset sales
• Depreciation, amortization and impairments
• Impairment testing
• Finding information in the financials
• Account analysis - inflows and outflows (B-A-S-E)
• Asset efficiency ratios

Debt and equity

In this session participants learn how companies finance their operations. The characteristics of debt and equity are analyzed, including how to account for new debt and equity issues. Various kinds of debt instruments are identified, and the main equity accounts are examined. Finally some of the most important ratios are covered.

Key topics:

• Debt disclosure in published financial data
• Bank debt and bond issues
• Net debt
• The difference between authorized, issued, and outstanding shares
• Common Stock and Additional Paid-in Capital accounts
• Share issues and repurchases
• Dividends and interest payments
• Coverage and leverage ratios

Cash flow statements

Participants learn how to build cash flow statements using historical and forecast balance sheets. The relationship between cash and changes in assets, liabilities and equity accounts is analyzed in detail, allowing participants to understand the full integration of the income statement, balance sheet and cash flow statement. The cash flow session is particularly relevant as a foundation for modeling skills.

Key topics:

• The components of the cash flow statement
• Why the cash flow statement is a reconciliation of two balance sheets
• How to divide a company's balance sheet into operating, investing and financing activities
• Calculate a cash flow statement from two balance sheets and an income statement
• Asset sales and impairments / restructuring
• EBITDA as proxy of operating cash flow and potential pitfalls

Financial modeling - Fundamentals Part 1

This session teaches participants how to build a three statement integrated financial model, comprising income statement, balance sheet and cash flow statement. Best-practice modelling techniques are demonstrated, to ensure maximum accuracy and efficiency. Participants also learn how to stress-test the assumptions used, to check the model for mistakes and to document it. This session uses simplified teaching models as well as a real company forecast model.

Key topics:

• Setting Excel up for maximum efficiency
• Best-practice keyboard shortcuts for modelling
• Key modeling formulas
• Alternative model layouts
• Structure of an integrated three statement forecast model
• Constructing the income statement, balance sheet and cash flow statement
• Balancing the forecast balance sheet using cash and revolver
• Incorporating interest income and expense
• Introduction to checking methodologies
• Performing audit trails

Financial modeling - Fundamentals Part 2

Using a real company forecast model, we start the session by reviewing the structure and key components of an integrated three statement forecast model. We then focus on modeling operating cash (required cash) and separating it from excess cash. We then teach participants how to build a forecast cash flow statement from scratch, using income statement and balance sheet inputs. The last part of this session focuses on interest calculations and circularities, where we teach participants the difference between using beginning, ending or average debt/cash balances and how to work safely and effectively with or without circular formulae in a model This session can be taken as a stand-alone module, or as a follow-up to the Part 1 session.

Key topics:

• Review of the structure and key components of a forecast model
• Review of the modelling steps to build a three statement integrated model
• Modeling operating cash (require cash) and excess cash
• Building a forecast cash flow statement from scratch
• Links to forecast income statement and balance sheet
• The 4 rules of cash
• Calculating the balance sheet plugs (cash and revolver)
• Calculating interest on cash and debt balances
• Using beginning, ending or average balances
• What is a circular formula?
• Working with intentional circular references
• Excel settings
• How does the iterative process work?
• Building and using circularity switches
• Avoiding non-intentional circular references

Financial modeling - Cash sweep

In this session we build a three statement operating model for a real company, which incorporates a detailed revenue forecast based on price and volume drivers. We set up a hierarchy of a series of debt items and we learn how to model the sequential debt paydown using a ‘cash sweep’ approach. Best practice modeling techniques are emphasized throughout.

Key topics:

• Building a detailed debt schedule
• The waterfall of debt repayments
• Building a simple cash sweep
• Modeling revolver
• Modeling a price/volume revenue forecast

Financial modeling - Troubleshooting

This session covers a range of error-checking techniques to find and correct the most common errors found in financial models. We use a series of financial models to allow participants to practice error-checking.

Key topics:

• Stabilizing a model
• Making a model map
• Sanity checking a model
• Fixing a non-balancing balance sheet
• Troubleshooting techniques for cash flow statements
• Checking the model’s matrix
• Finding unidentified hard numbers
• Using Excel’s ‘Jump tool’ to trace through formulas with ease
• Using Excel to show the formulas underlying output
• Using Excel’s auditing tool to trace formulas

Financial modeling - From scratch

This session teaches participants how to build a three statement forecast model starting from a blank Excel spreadsheet. We begin by setting up a roadmap to guide us through the modelling process and then we proceed to build up a model template which we populate with historical financial information drawn from the case company’s financial statements.  We analyse the company’s historical data by calculating financial ratios, which are then utilized as a basis to set the assumptions driving the financial forecasts. The full forecast model, comprising income statement, balance sheet and cash flow statement, is then built. As we build the model, we examine various forecasting techniques for the key elements of the financial statements. Once the model is complete, we test its integrity and we sanity-check the financial forecasts.

Key topics:

• Identifying relevant information in the company’s financial statements, management discussion and analysis, and notes
• Isolating historical non-recurring income and expenses
• Forecasting techniques
• Revenues and operating costs
• Operating working capital
• Fixed assets - capital expenditure and depreciation
• Intangible assets
• Provisions
• Effective tax rate, taxes payable and the various deferred tax items
• Debt schedule
• Equity and share repurchases
• Balancing accounts (balance sheet plugs)
• Building a cash flow statement from scratch
• Documentation techniques
• Integrity checking
• Sanity-checking the financial forecasts

Modeling Day

Participants will be asked to build a three statement model (including forecast assumptions) independently from scratch. This process helps ensure that the knowledge gained so far will 'stick' and that each participant is as ‘desk-ready’ as possible.  The instructor will review all models and will give individual feedback to the participants.

Valuation fundamentals

The session lays the foundations to build a solid understanding of corporate valuation in the context of investment banking. The most common valuation methodologies are introduced, explaining the difference between a company's fundamental value, and how much an acquirer would pay for the business. The concepts of enterprise value and equity value are explained, using simple but rigorous exercises. Finally, the basics of multiple valuation and discounted cash flow valuation are introduced. Exercises are used throughout the session.

Key topics:

• The importance of valuation in the investment banking industry
• Fundamental vs. transaction value
• Overview of the major valuation methods
• Trading comparables analysis
• Discounted cash flow analysis
• Transaction comparables analysis
• LBO analysis
• Enterprise vs. equity value
• Book values vs. market values
• Derivation of enterprise values using market values
• Derivation of enterprise values using a fundamental valuation approach

Trading comparables

Participants are introduced to preparing multiples using real company data and a case study including a range of international companies. We focus on how to select comparables, where to find data in published financials and equity research reports, how to clean the raw data, and how to document and check the output. The most commonly used multiples are explained and complexities such as normalizing for non-recurring expenses / income are also covered. The session ends with practical exercises on the application of multiple analysis to value a company.

Key topics:

• Screening companies to identify a suitable comparable set
• Calculating the company’s value
• Number of shares and value of share options
• Equity value
• Net debt calculations
• Minority interests and equity method investments
• Enterprise value
• Calculating the earnings numbers
• Cleaning non-recurring items from earnings and resulting tax adjustment
• Calendarization issues
• Last-Twelve Months (LTM) analysis
• Calculating a range of forward looking and historical earnings multiples
• Revenue
• EBITDA
• EBIT
• P / E
• P / E / G
• Industry-specific multiples
• Calculating and using operating and credit ratios
• Troubleshooting and checking the output
• Applying the results

DCF valuation

Participants learn how to build a discounted cash flow valuation model. The session starts with an overview of the valuation methodology, and the steps required in setting up a valuation model. We then focus on the calculation of free cash flow. A detailed ratio analysis is used to establish the reasonableness of the forecasts and to identify when the target company reaches steady state. We analyze the weighted average cost of capital, calculate terminal values, using both the exit multiple method and the perpetuity growth method. We discount the free cash flows to arrive at enterprise values and calculate the implied share price. Once the valuation is complete participants perform several checks on the analysis using key ratios, and sensitivity and scenario analysis.

Key topics:

• Calculating unlevered free cash flows
• Drivers of cash flow
• Ratio analysis
• Weighted average cost of capital
• Optimal capital structure using peer analysis
• Establishing the company’s forward looking cost of debt
• Cost of equity: understanding the risk free rate, the equity risk premium and beta
• Unlevering and relevering the beta
• Calculating WACC for the case company
• Calculating the terminal value
• Perpetuity growth (Gordon Growth model) method
• Exit multiple method
• Building a discounting model
• Mid-year adjustments
• Calculating enterprise and equity values
• Sanity checks
• Reinvestment rate and ROIC
• Implied multiples and growth rates
• Percentage of value in the terminal period

Valuation issues

This session covers the more advanced areas of multiples and DCF valuation. Enterprise value and income statement adjustments are addressed for both DCF and multiples, using case company examples.

Stock based compensation issues

Key topics:

• Essentials of stock based compensation accounting
• Intrinsic value of stock based compensation
• Treasury method of accounting for stock based compensation
• Restricted stock and performance stock units
• Multiples adjustments
• DCF adjustments

Non-controlling interests (NCI) and equity affiliates / associates issues

Key topics:

• Accounting for NCI
• NCI valuation
• Market values
• P / E multiples
• Book values, and Price to Book multiples
• And other methodologies
• Adjustments for NCI to DCF and multiples
• Accounting for equity affiliates / associates
• Equity affiliates and core, consolidated and total EV
• Equity affiliate valuation
• Market values
• P / E multiples
• Book values, and Price to Book multiples
• And other methodologies
• Adjustments for equity affiliates to DCF and multiples

Operating leases issues

Key topics:

• Differences between operating and financing / capital leases
• Fundamentals of operating and financing / capital lease accounting
• Operating leases adjustments to multiples
• Lease adjustment to earnings
• Lease adjustment to enterprise value for multiples and DCF
• Operating leases and DCF

Pensions issues

Key topics:

• Fundamentals of pension accounting
• Defined benefit vs. defined contribution plans
• Funded vs. unfunded plans
• Pension deficits and surpluses
• Pension adjustments to multiples
• Pension adjustment to earnings
• Pension adjustment to enterprise value for multiples
• Pension adjustments for DCF

IPO case study introduction

Key topics:

• Analyzing the business
• Building a model
• Valuing the business
• Preparation of a pitch book