Advanced Financial Modeling

Course details

Many financial projection models are based on simple revenue growth rate and expense margin assumptions, resulting in reduced precision in the projection model. This course teaches various approaches to true, bottoms-up, fundamental analysis, from both - an account-by-account and business segment basis (very detailed build-up vs. division by division). The results of build-up analysis roll-up into a consolidating income statement that feeds into the income statement revenue items. This enhances the core integrated financial model by building a detailed depreciation schedule, honing in on complex method of calculating dilutive treasury options, WACC template, residual value, EVA analysis, analyzing financial ratios, and automating credit and leverage statistics.

Expected Accomplishments

• Build an integrated set of financials, including LOB, income statement, balance sheet and cash flow
• Learn how to balance a model utilizing debt sweep, no plugs and the danger of circular references
• Become super-efficient in Excel through intensive use of keyboard shortcuts and best practices
• Learn detailed revenue build-up algorithms for various types of businesses and scenarios
• Provide additional credibility, support and precision to your financial models
• Understand and analyze the true drivers of growth in a business and translate into Excel
• Build sensitivity analysis into model by incorporating different scenarios and cases
• Enhance financial model with additional detail and supporting analysis
• Build better precision and capture quality inputs into your model
• Perform credit ratio and analysis and build a robust tax schedule
• Construct basic revenue precision into financial projection model

Course Outline

5 Year financial statement projection model

• Projecting an income statement from revenues and expenses down to net income 
• Methodologies to forecasting the different types of assets on the balance sheet and their comparison Ft contrast with projecting liabilities 
• Projecting shareholders' equity account
• Importance of financial ratios in building balance sheet projections
• Building an integrated cash flow statement considering each component

Supporting schedules

• Incorporate calculation and payment of dividends into 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 
• Flow of cash through the model
• Incorporating automatic debt payments and using cash generated to either pay down debt or build cash
• Balancing the model with the revolver facility and avoiding messy nested 'if statements o How does the balance sheet and financial statements balance by itself without the use of plugs? 
• Integrating financial statements integrated the interest schedule? 
• Avoiding and getting around circular references

Enhancements to core integrated financial model

Building 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 a 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 

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. SEtP rating statistics 

Distinguish between various types and tranches of debt