PROGRAM DETAILS

Introduction

Data is the lifeblood of an organization – from client information held in marketing and customer service applications to operational and competitive data in business intelligence systems and data warehouses. To get maximum benefit out of the ever increasing volumes of information, good data analysis and data modelling skills are essential.

In this course you’ll learn top-down business and application data modelling and how to analyze and normalize data. You’ll learn how to use entities attributes, keys, relationships and how to build data models using entity relationship diagrams. The course provides practical training in the core skills needed to model data that reflects the needs of the business.

Too often, finance-related courses stop short of making a connection between the theory and practice of real-world business. Data Analysis Techniques bridges this gap by providing a hands-on approach to the entire range of data analysis challenges. From deciding what data is required, and not, to gathering techniques, to analysis tool options, to modeling using Excel®, this course covers it all.

This is a very “hands-on” course with significant time spent working on the computer. Each participant should bring their own laptop loaded with Microsoft Excel® 2007 or newer version.

 Learning Objectives

 This program will help you learn how to:

• Use financial statements to evaluate the financial/strategic performance of an organization
• Understand discounted cash flow (DCF) techniques and their application to financial decision-making
• Use ratios to quickly pinpoint areas of concern
• Identify key success factors, weak financial signals, and strong financial signals in your own industry sector
• Understand & use both financial & operating leverage
• Learn the metrics used by the world’ leading companies, how to use them, & why
• Leverage the financial models provided to calculate answers immediately
• Confidently project your firm’s future performance through real-world modeling
• Analyze an entire range of past performance data for both decision making today & for the future.
• Learn new, advanced modeling techniques to improve results

 Target Audience

Professionals who wish to work on improving their skills with other similar individuals will find they need, and use, everything they learn in this program. Functional managers who have recognized their need for a bigger picture and a more integrative financial view will benefit. Professionals who wish to have a better “hands-on” knowledge & understanding of the relationship between analysis, modeling & decision-making, specifically:

• Finance
• Accounting
• Planning (strategic & operational)
• Operations
• Strategy
• Marketing

This is a very “hands-on” course with significant time spent working on the computer. Each participant should bring their own laptop loaded with Microsoft Excel® 2007 or newer version.

Training Methodology

The training process is based on a carefully planned mix of instructor input – with practical illustration of tools and concepts, group work on case studies (all video based – and lively), feedback, and selective work on individual issues in pairs.

The training is based on the 4 mat systems to enable complete learning. There will also be some opportunity for role play. You will receive a toolkit within the material for subsequent everyday use.

Program Outline

 Day 1 – Financial Modeling Principals

• Using Data Analysis to Solve Business Problems
• Learn the 10 steps to create good Financial Models
• The 13 steps to Improving traditional Financial Models
• Modeling Financial Problems in Excel: Five Options
• Define the Terms Model and Financial Model
• Learn to use Flowcharting Tools & Techniques in Excel©

 Case study to review a basic data analysis template

 Data Retention and Integration in Excel®

• Data retention in Excel®
• When to use — and NOT to use — Excel®
• Retaining data in Excel®
• Data integration and linking
• VLOOKUP function
• INDEX and MATCH functions

 Case study: use data analysis tools

 Day 2 – Data Analysis

 Pivot Tables (using Excel®)

• What are Pivot Tables & what is possible with them
• Preparing your data for Pivot Table Analysis
• Changing the default layout of a Pivot Table
• Adding new fields to a Pivot Table
• Eliminating blank cells
• Using legacy mode
• Pivot Table limitations
• Sorting & filtering
• Adding new items
• Showing percentages instead of values
• Exploring new uses of Pivot Tables

Case study: analyze the data using the Pivot Table template

 Financial Functions and Time Value of Money Considerations

• Overview of the Time Value of Money
• Apply Time Value Concepts to Financial Models
• Learn Why the Weighted Average Cost of Capital (WACC) Is Used in Capital
• Budgeting Models (Excel case study)
• Use Net Present Value (NPV) and Internal Rate of Return (IRR) Models in
• Making Capital Expenditure Decisions
• Why is Profitability Index (PI) & MIRR (Modified Internal Rate of Return) are good
• alternatives
• Learn to use the built-in functions to calculate NPV & IRR
• Automatic Functions: (Excel case study)
  • FV Function
  • Rate Function
  • Payment Function
  • NPER Function

 Day 3 – Sensitivity Analysis

• Overview: How to Use Data Tools
• Goal Seek
• Data Tables
  • One Way (Input)
  • Two Way (Input) (Excel case study)
• Scenario Manager: 3 ways to use this tool
• Using the Forms Toolbar: (1) Spinners; (2) Scroll Bars; (3) More

 Optimization with Excel’s Solver

• Overview: How to Use Solver
• Specific Examples: (1) Optimal Product Mix; (2) Profits; (3) Breakeven (Excel case study)

 Simulation Analysis

• Overview
• Simulating the Profit Scenario
• Monte Carlo Simulation
• Random number generation

 Case study to build a Monte Carlo Model

 Day 4 – Data Analysis of Cost of Capital & Capital Structure Considerations

• Estimating the Cost of Capital
• Choosing the Method of Project Financings: 3 Options
• Establishing the Firm’s Optimal Capital Structure (Excel case study)
• Estimating the Firm’s Discount Rate
• Calculating the Weighted After-Tax Cost of Capital (WACC)

 Analysis of Project Investment Decisions

• The Capital Budgeting Process: Five Steps (Excel case study)
• Creating the Pro forma income statement
• Calculating the Cash Flow From Assets (CFFA)
• Estimating Cash Flows
• Evaluating Capital Expenditures
• Making the decision
• Discounted Cash Flow Methods (Using Excel)
• Internal Rate of Return (IRR) and Modified IRR (Using Excel)
• Evaluating Projects with Different Lives using an Equivalent Annual Cost (EAC) method (Excel case study)

 Day 5 – Financial Analysis & Beyond

• Deciding on Benchmarks & industry standards
• Ratios, trends, & more
• Breakeven analysis & options
• Leverage: Operating versus Financial
• Altman’s Z-Score & its application

 Case study –Analyze a Public Firm

 Principles & Application in Building a Financial Model

• What to look for in projected performance
• Building a ratio driven model
• Building a break even model with more uses
• Lease versus buy calculations

 Case study –build a financial model for your firm