I. Financial Modeling - The Key Elements and Components
Before you can begin to build a model, you need to understand its components. What are the characteristics of a superior model? What objectives do you need to achieve for your department, your business unit or your entire firm?
A. Key characteristics of a financial model
1. A Financial model as a quantitative representation or
simulation
2. Financial Statements using GAAP accounting standards
3. Actual and pro forma financial inputs and outcomes
4. Data to populate financial statements and secondary
worksheets.
5. Graphical worksheets
6. A complete set of budgets
7. Analytics worksheets for understanding the model
8. Macros that automate repetitive tasks
9. Best practices using NPV, XNPV, IRR, and XIRR
10.Projecting from historical data using LINEST, TREND,
LOGEST, GROWTH, adding trendlines to charted data
and forecasting from the associated equations
11.Data smoothing tools to use prior to forecasting
B. Accomplishing business objectives with a financial model
C. Key model elements that create a dynamic model
II. Getting More Comfortable with Excel
Financial modeling's most standard platform is MS Excel 2003. Using this software application, students will cover key Excel skills that are needed to build a good model, using financial accounting rules. Students will fill in any Excel knowledge gaps before starting to build a financial model.
A. Model template layout process
B. Special Excel Tools
1. Audit Toolbar
2. Goal Seek
3. Solver
C. Tools to write formulas/nested conditional formulas
D. Excel Macros
1. Types and characteristics; absolute vs. relative
2. How macros help
3. Designing a macro
E. Features that MUST be embedded in your model.
F. Building, populating and naming key worksheets.
Hands-on Exercise
Using templates provided by the instructor, students will build sections of the model's structure, algorithms that drive logic, data tables, PivotTables, graphs, scenarios, and other critical model components.
III. Constructing a Financial Model with Advanced Skills
A. Designing the general service/manufacturing corporate
financial model.
1. A flow chart for enterprise modeling
2. The Dashboard worksheet
3. The Assumptions worksheet
4. Foundation worksheets
a. Revenue and COGS/COS Model
b. Income Statement and Balance Sheet Budgets
c. Departmental expense budget
d. Headcount and Staffing budgets
e. CapEx Worksheet
5. Developing financial statements from the budgets
6. Building an Actual vs. Projected worksheet
7. Financial Statements interdependencies
B. How to design and record Macros for automating repetitive
tasks and for automating the update of CapEx,
Headcount/Payroll, and required Capital Infusions as
Revenue and Operating Expense inputs are varied.
C. Navigating and auditing the model using named ranges
and the Formula Auditing Toolbar
D. Visual representation
1. Data that drives Excel charts
2. Key management information graphs
3. Advanced Excel charting skills
4. Input/Output charting on the Dashboard
|
IV. Testing the Model
Once you have your model "up and running", you'll learn the best ways to test your results before you go "live". Using a host of tools and methodologies, you'll learn the basics of auditing and sensitivity testing in Excel and you'll learn where to look for problems.
A. Performing a scenario test in Excel
1. Test outputs as a function of changing inputs
2. Preserve and display the original baseline data and
the new scenarios in a Scenario Manager Report
B. Perform "what-if" analysis across changes in two variables
simultaneously
1. Display the output in a 2D array
C. Capturing, deploying and displaying testing outcomes
using Excel
D. Identifying targeted and optimal outcomes using Excel's
Goal Seek and Solver tools
1. Use Goal Seek to find the value of an input data value
that will produce a specific output value for a targeted
formula.
2. Use Solver to find optimal solutions to Capital
Budgeting, Logistics, Real Asset Portfolio Management,
and Operating Budget Management problems.
E. Understanding descriptive statistics, Moving Average Data
Smoothing, and Exponential Data Smoothing using the
Data Analysis Toolpak (DATP)
1. Use the RANDBETWEEN function to simulate
constrained random data values for data smoothing
exercises
F. Examine uses of DATP functions as Histogram
1. Use the Histogram tool to sample test the Central
2. Limit Theorem
V. The Art of Auditing a Financial Model
Once you have your functioning model built and running, you'll want to continually audit your work and calculations back at the office. In this section, you'll learn the best ways to audit for errors and detect any problems. You need to trust your results!
A. Methods for auditing a large model
1. Identifying and repairing architectural/structural/
strategic problems/solutions
2. Input/Output Testing
a. Using the Watch Window on the Auditing Toolbar
for remote output observation
b. Additional methods such as Spin Boxes for ensuring
that output formulas are properly linked to input
variables
3. Auditing on the fly as the model is being built
B. Using Excel's built-in auditing tools
1. Background error checking
2. Tracing precedents and dependents
3. Trace Error Tool
4. Evaluate Formula Tool
5. Formula Auditing Mode
C. Advance topic: Using analysis of variance (ANOVA) to
test other Toolpak skills for statistical significance
1. Test sales plans for statistically significant efficacy
2. Test the margins of model income-statement
projections for reasonableness against industry-average
margins
VI. Preparing Summary Presentations
VII. Class Wrap-Up and Discussion
As a group, discuss how we might create a favorable impact on financial best practices within our organizations
In-Class Group Exercises
Throughout this workshop, group exercises and case studies will "drive home" key information that you need to become a top-notch model builder. Some of the tools used in class are:
A Rule-based riddle - as a group, solve a riddle that helps you learn how to "think" when planning our model.
Case Studies - See how other analysts outside of your company deal with measuring, analyzing and presenting their data. Where do they get it right and where do they get it wrong!
Group Exercises - As a group, you'll learn how to extract and represent data from your model.
|
