Course Category

Advanced Financial Modeling

• How do you project a company's income statement from revenues and expenses down to net income?
• What are the different methodologies to forecasting the different types of assets on the balance sheet and how do they compare and contrast with projecting liabilities?
• How do you project the shareholders' equity account?
• What is the importance of financial ratios in building the balance sheet projections?
• How do you approach building an integrated cash flow statement?
• How do you build each component of the cash flow statement and why is cash the last item to project?

Supporting Schedules

• Incorporate calculation and payment of dividends into your integrated financial model
• Emulate announced share repurchase program by estimating implied price and shares repurchased

Integration and Balancing of Financial Model

• Balance the model using the debt schedule and debt sweep logic - the most important analysis in terms of balancing the model
• How does the cash actually flow through the model?
• Incorporate automatic debt payments and use cash generated to either pay down debt or build cash
• How does the revolver facility actually balance the model? Avoid messy nested if statements
• How does the balance sheet and financial statements balance by itself without the use of plugs?
• How are the financial statements integrated using the interest schedule?
• What are circular references, why should they be avoided and how to get around circular references

Advanced Segment Build-up Sensitivity Modeling

• Model out historical change in key drivers of growth and project future detailed growth
• Analyze and break down growth based on publicly available data and inputs from 10K filing
• Incorporate and remove effect of growth from non-core items such as foreign exchange rate fluctuations
• Project future detailed growth assumptions that roll up into larger projection model
• Instead of just calculating 10% growth rate in revenue, dig into deeper layers of growth drivers
• For instance, for a retailer, calculate Sales / Sq Foot / Type of Store, which captures: (i) number of stores (store count growth); (ii) size of each store (expansion and size creep); (iii) profitability of each sq foot and same store comps sales (YoY sales growth)

Operating & Division Segment Build-Up

• Calculate and analyze different operating segments as reported in public filings to roll-up into IS
• Adjust for extraordinary items by segment based on MD&A and disclosed footnotes
• Extract, utilize and incorporate volume and pricing increases into operating segment performance
• Estimate and project future revenue and segment income and allocate for corporate overhead
• Estimate projected COGS and SG&A on the entire base after operating build-up

Detailed Account by Account Build-Up

• Project sources of revenue based on growth in number of accounts and customers
• Model out revenue per account and associated commissions and expenses
• Incorporate rate increases into model
• Further enhance model via sensitivity & scenario modeling and analysis
• Detailed build-up consolidates into Consolidating Income Statement which feeds into model
• Account for inter-company eliminations in historical pro forma model and projections

Sensitivity Analysis and Multiple Cases

• Layer sensitivity analysis on top of segment build-up to incorporate various assumptions and cases
• Build multiple scenarios and cases, including Base Case, Optimistic & Pessimistic Cases
• Toggle and sensitize profitability and cash flow of model based on various case assumptions

Advanced Financial Modeling – Enhancements

• Build a stand-alone depreciation schedule to better estimate working capital changes and free cash flow by depreciating existing PPE as well as new capital expenditures
• - Capture and incorporate detail such as remaining useful life estimates
• - Allocate accumulated depreciation correctly
• - Depreciate existing Net PPE and new CapEx based on weighted average life
• Create quick financial summary exhibit that summarizes key figures from financial model
• Build an analysis of trading statistics that can be used to compare companies across an industry
• - Provides current snapshot of the current public market valuation
• Sensitize trading analysis through an “Analysis at Various Prices” analysis
• - Hypothetical “what if” scenario based on acquisition offer prices and implied multiples
• Residual value and EVA analysis
• Credit and leverage statistics ratio analysis with automated comparisons vs. S&P rating statistics
• - Distinguish between various types and tranches of debt