By the conclusion of this powerful, practical two-day
workshop you will:
|
- Master the Art and Technology of Building Reliable, Flexible Financial Models
- Use a Proven Template to Flowchart Model Design, Layout and Specifications
- Develop Foundation Worksheets for Assumptions and a Complete Set of Budgets
- Link the P&L to the Balance Sheet, Cash Flow Statement and Valuation Model
- Project 5-Year Balance Sheet, P&L, Statement of Cash Flows and DCF Valuation
- Forecast Quarterly Cash Flows, Balances and Borrowing Requirements
- Take Home Custom Templates Designed to Forecast Your Firm’s Financials
- Change Inputs and Assumptions to Immediately Examine Financial Results
- Analyze Targeted Outcomes with Excel™’s Goal Seek and Scenario Manager
- Use Excel™’s Regression Analysis to Determine Key Drivers of Sales and Value
- Identify the Key Characteristics of a Dynamic, Dependable Financial Model
- Work Smarter, Not Harder – Construct Time Saving Macros for Repetitive Tasks
- Use Excel™’s Statistical Tool Pack for Budgeting, Auditing and Testing the Model
- Select the Best Presentation Techniques and Import Charts and Data from Excel™
|
A state-of-the-art workshop
designed for accounting and financial professionals including:
|
- CFOs, Controllers, Treasurers and their Accounting Staff
- Financial and Budget Directors, Managers, Analysts and their Staff
- Corporate, Financial and Strategic Planning Managers and their Staff
- Merger and Acquisition Specialists, Valuation Analysts and Investment Bankers
|
|
|
|
Effective Financial Modeling Techniques
|
- Key Characteristics of a Superior Financial Model
- Quantitative Representations Structured for Simulations
- GAAP Financial Statements
- Actual and Projected Inputs, Assumptions and Outcomes
- Interactive Financial Statements and Linked Secondary Worksheets
- Linked Graphics to Projections
- Complete Integrated Budgets - Sales, Operating, Cap Ex, Staffing, and More
- Analytical Worksheets and Metrics to Interpret and Summarize the Model
- Macros to Automate Repetitive Tasks
- Best Practices Using NPV, XNPV, IRR, MIRR and XIRR
- Charting the NPV Profile; Multiple IRRs and Other Exceptions
- Projecting from Historical Data
- Using LINEST, TREND, LOGEST, and GROWTH
- Adding Trend Lines and Formulas to Graphs
- Data Smoothing Tools from the Data Analysis Toolpak (DATP)
- Forecasting from Automated Formulas
- Key Elements that Create a Dynamic Model
- Accomplishing Business Objectives with Financial Models
|
|
Effective Excel™ Modeling Tools and Techniques - Key Skills
|
- How to Layout and Diagram the Model on an Excel™ Template
- Using Key Tools from Excel™
- Audit Toolbar
- Data Tables for Sensitivity Analysis
- Goal Seek, Solver and Scenario Manager
- Pivot Tables and Pivot Charts to Present Projection Scenarios
- Conditional and Nested Formulas
- Macros and Range Names to Work Smarter, Not Harder
- Creating and Using Range Names
- Macro Recording and Playback
- Types of Macros, Characteristics, Absolute vs. Relative
- Designing a Macro
- Embedding 'Must Have' Features into your Model
- Building, Populating and Naming Key Worksheets
|
|
Constructing the Integrated Financial Model
|
- Designing a Manufacturing/Service Enterprise Financial Model
- Flowchart for Enterprise Modeling
- Dashboard and Assumptions Worksheet
- Input/Output Charting on the Dashboard
- Interdependencies - Linking the Balance Sheet, P&L, Statement of Cash Flows and DCF Valuation
- Historical Data, Metrics, Operating and Financial Assumptions
- Budgeting Approach to Modeling - the Foundation Worksheets
- Operating Budget - Revenues
- Revenue Growth Algorithms and Compound Revenue Growth Using Excel™
- Regression Analysis: Key Drivers of Sales/Projecting Sales
- Operating Budget - Expenses that Impact Net Working Capital
- Receivables and Collections/Receipts Budget
- Production and Cost of Goods Sold Budget
- Inventory and Disbursements for Payables Budget
- Operating Budget - G&A Expenses
- Headcount and Staffing Budget
- Using Regression Analysis to Calculate Fixed and Variable Costs
- Importing Outside and Departmental Budgets
- Cap Ex Budget - Fixed Assets, Depreciation and Debt Schedule
- Building the Projected vs. Actual Work Sheet
- Operating and Financial Leverage with Goal Seek and Scenario Manager
- Designing Macros to Automate Periodic Budgeting Updates and Changes
|
|
Modeling Integrated Financial Statements and Valuation
|
- Income Statement - Five Year and Quarterly
- Balance Sheet - Balancing to Cash
- Statement of Cash Flows - Five Year and Quarterly
- Projecting Monthly Receipts and Disbursements of Cash
- Projecting Free Cash Flow
- Modeling Valuation
- Enterprise Value Modeling Overview
- Assumptions of the Capital Asset Pricing Model - Risk, Beta, More!
- Modeling the Average Weighted Cost of Capital
- Free Cash Flow to Equity (FCFE) and Free Cash Flow to the Firm (FCFF)
- DCF Valuation
- Using Scenario Manager to Assess Optimal Capital Structure and Risk
- Probabilistic Modeling Techniques
- Metrics for Measuring and Forecasting
- Navigating Your Model with Named Ranges and Other Tools
- Visual Representations
- Excel™ Graphing Techniques
- Data to Dynamically Drive Graphs and Charts
- Key Management Information Graphs
|
|
Testing a Financial Model
|
- Scenario Testing Using Excel™
- Change Inputs and Test Resultant Outputs
- Use Scenario Manager Report for Testing
- Two-Way Sensitivity Analysis: Two Variables and 2-D Array
- Capturing and Displaying your Testing Outcomes Using Excel™
- Using Excel™'s Goal Seek and Solver for Targeted and Optimal Outcomes
- Working Backwards from Desired Metrics to Required Performance
- Using Solver for Optimal Decisions: Capital Budgeting, Logistics, Optimizing a Portfolio of Real Corporate Assets, etc.
- Using Excel™'s Descriptive Statistics
- Using Excel™'s Data Analysis Tools for Moving Average and Exponential Data Smoothing
- Using Excel™'s Random Number Generator for Data Smoothing
- Histograms to Test the Central Limit Theorem
|
|
Methodologies for Auditing a Large Financial Model
|
- Identifying and Fixing Architectural, Structural and Strategic Problems
- Input/Output Testing
- Using the Watch Window on the Auditing Toolbar
- Using Spin Boxes to Ensure that Output Formulas are Linked to Input Variables
- Using Excel™'s Built-in Auditing Tools
- Background Error Checking
- Tracing Precedents and Dependents
- Trace Error Tool
- Using the Formula Tool and Formula Auditing Mode
- Using Excel™'s ANOVA (Analysis of Variance)
- Analytical Review of Metrics vs. Industry Average
- Building the Black-Scholes Option Pricing Formula in Excel™
|
|
Preparing Summary Presentations
|
- Presentation Logic - What Works and Why
- Importing Excel™ into PowerPoint
- Importing Graphics into PowerPoint
- Standard and Custom Charts
|
CPE Credits...
|
|
This group-live seminar is recommended for 16 hours of Specialized Knowledge and Applications.
The National Center for Continuing Education is registered with the National Association of State Boards of Accountancy (NASBA), as a sponsor of continuing professional education on the National Registry of CPE Sponsors. State boards of accountancy have final authority on the acceptance of individual courses for CPE credit. Complaints regarding registered sponsors may be addressed to the National Registry of CPE Sponsors, 150 Fourth Avenue North, Suite 700, Nashville, TN, 37219-2417. Visit the NASBA web site at www.nasba.org.
|
|
|
|
This seminar is ideal for accounting and
financial professionals including:
- CFO’s, controllers, treasurers and accounting staff
- Mergers and Acquisition Specialists; Valuation Analysts
- Budget directors, managers, analysts and their staff
- Financial directors, managers, analysts and their staff
- Operations Analysts and business development directors
- Financial and corporate planners; strategic planning managers
|
Seminar Prerequisites
|
|
Course Level: Basic
Prerequisites: Basic knowledge of Excel™
and financial statements
No Advanced Preparation Required
|
|
|
|
|
|
|
|
If you have a group of 12 or more employees who need to learn Modeling Integrated
Financial Statement Projections Using
Excel™ or need other financial training, then
NCCE’s customized training is the cost effective solution. Bring this seminar in-house to
transform your finance and accounting department into a world-class operation.
Some of our past in-house clients in clude
Hewlett-Packard, Stanley Tools, Harley-Davidson,
Fortune Magazine, Deloitte-Touche,
General Mills and Motorola.
For more information or to schedule a custom in-house program, e-mail NCCE at contact@nccetraining.com or telephone 800-635-9615.
Back to top
|
© Copyright 2008 Performance Seminars, Inc.
|
