Financial Modeling in Excel (3 days)

Day 3 - Integrated Financial Model Standards

Participants begin with basic keyboard drills and shortcuts, and then rapidly progress to efficient formula construction and basic modeling and checking techniques. Although simple, the sample company contains the basic parts common to all projection models. Participants complete a basic projection model and stress test their assumptions. Interest income and expense are added and the resulting circularity is solved with iterations. Finally, the model is documented and integrity checked.

Key topics:

• Basic keyboard shortcuts
• Excel settings for efficiency
• Key modeling formulas and structures
• Standards for good models
• Income statement construction
• Balance sheet and cash flow statement construction
• Introduction to checking methodology
• Audit trails
• Incorporation of interest income and expense
• Circularity and iteration
• Ratios and summary

Day 4 AM - Financial Modeling and Forecasting

Participants will learn how to build a three statement model using a detailed revenue forecast with price and volume drivers. A full debt schedule, including a cash sweep, is incorporated into the model. In addition to the main class case model, participants are given exercises to help them understand more complex modeling issues (for example, detailed depreciation schedules and working capital items). Common errors are covered from balancing a non-balancing balance sheet to debugging a model that is non-intentionally circular.

Key topics:

• Complex 3 statement models
• Modeling a detailed revenue forecast
• Modeling a cash sweep
• Modeling a detailed debt schedule include a cash sweep
• Consolidating and re-applying knowledge of circularity, iteration, and a toggle switch
• Building cash flow statements from scratch
• Troubleshooting techniques for cash flow statements
• Finding errors and integrity checking

Day 4 PM - Integrity and Error Checking

Common errors are covered from balancing a non-balancing balance sheet to debugging a model that is non-intentionally circular.

Key topics:

• Troubleshooting techniques for cash flow statements
• Finding errors and integrity checking
• Using Excel tools to help with integrity checking
• Finding unidentified hard numbers quickly and easily
• Using Excel’s “Jump tool” to trace through formulas with ease
• Using Excel to show the formulas underlying output
• Using Excel to find inconsistencies in the model
• Using Excel’s auditing tool to trace formulas

Day 5 - Financial Modeling from Scratch

Participants start with an Excel worksheet with two years of historicals of the main case company pre-inputted. The financial statements of the case company are then analyzed, and the latest historical data is cleaned and prepared for the forecasting process. Ratios are calculated and assumptions for each line item in the financial statements are created. The full forecast model is then built. The forecasting techniques for several different items are examined separately, using dedicated Excel files, before being adapted for the case company.

Key topics:

• Identifying relevant information in the company’s financial statements, management discussion and analysis, and notes
• Adjusting historical performance for acquisitions and divestitures
• Analyzing and making reasonable projections for a company’s operating costs, non-recurring items, and other income and expense
• Creating assumptions and forecasting balance sheet accounts
• Revenues and costs
• Fixed assets - capital expenditure and depreciation
• Intangible assets
• Operating working capital
• Provisions
• Effective tax rate, taxes payable and the various deferred tax items
• Debt schedule
• Equity and share repurchases
• Building a cash flow statement from scratch
• Documentation techniques and ratio analysis
• Integrity checking