Course Objectives
Upon completion of this program, candidates should be able to:
- Understanding of a spreadsheet ad its benefits
- Equip with essential skills of effectively utilizing spreadsheet software in a day-to-day business environment
- Be an Excel power-user with the aim of reducing non-value adding tasks.
- Create Financial Models and Investment Models
- Create Scenario Analysis and Dash Boards
- Create Drop-down boxes and scroll-bars for flash analysis
- Understand and apply Forecasting concepts and being able to track forecasting errors
- Apply certain new functions appearing in Excel 2007 onwards
- Perform What If Analysis using:
– Goal Seek
– Solver
– Input Table
– Scenarios
– Record a Macro and assign it to an Object
Pre-requisite
- Basic knowledge of Microsoft Excel (Foundation & Intermediate)
Course Outlines
- Defining Financial Modeling.
– What it is?
– Who uses it?
– Why it matters? - Looking at Examples of Financial Models
– Project finance models
– Pricing models
– Integrated financial statement models
– Valuation models
– Reporting models
- Making Sense of the Different Versions of Excel
– A rundown of recent Excel versions
– Focusing on file formats - Defining Modern Excel
- Recognizing the Dangers of Using Excel
– Capacity
– Lack of discipline
– Errors - Looking at Alternatives and Supplements to Excel
- Identifying the Problem That Your Financial Model Needs to Solve
- Designing How the Problem’s Answer Will Look
- Gathering Data to Put in Your Model
- Documenting the Limitations of Your Model
- Considering the Layout and Design of Your Model
– Structuring your model: What goes where
– Defining inputs, calculations, and output blocks
– Determining your audience
- Referencing Cells
– Relative cell referencing
– Absolute cell referencing
– Mixed cell referencing - Naming Ranges
– Understanding why you may want to use a named range
– Creating a named range
– Finding and using named ranges
– Editing or deleting a named range - Linking in Excel
– Internal links
– External links - Using Shortcuts
- Identifying the Difference between a Formula and a Function
- Finding the Function You Need
- Getting Familiar with the Most Important Functions
– SUM
– MAX and MIN
– AVERAGE
– COUNT and COUNTA
– ROUND, ROUNDUP, and ROUNDDOWN
– IF
– COUNTIF and SUMIF
– VLOOKUP and HLOOKUP - Being Aware of Advanced Functions and Functionality
- Identifying the Differences between Types of Analysis
- Building Drop-Down Scenarios
– Using data validations to model profitability scenarios
– Applying formulas to scenarios - Applying Sensitivity Analysis with Data Tables
– Setting up the calculation
– Building a data table with one input
– Building a data table with two inputs
– Applying probability weightings to your data table - Using Scenario Manager to Model Loan Calculations
– Setting up the model
– Applying Scenario Manager
- Deciding Which Data to Display
- Conveying Your Message by Charting Scenarios
- Deciding Which Type of Chart to Use
– Line charts
– Bar charts
– Combo charts
– Pie charts
– Charts in newer versions of Excel
- Getting to Know the Case Study
- Entering Assumptions
– Revenue assumptions
– Expense assumptions
– Other assumptions - Calculating Revenue
– Projecting sales volume
– Projecting dollar sales - Calculating Expenses
– Staff costs
– Other costs
– Depreciation and amortization - Building the Income Statement
- Building the Cash Flow Statement
- Building the Balance Sheet
- Building Scenarios
– Entering your scenario assumptions
– Building a drop-down box
– Building the scenario functionality
- Understanding How the Discounted Cash Flow Valuation Works
- Step 1: Calculating Free Cash Flow to Firm
- Step 2: Calculating Weighted Average Cost of Capital
- Step 3: Finding the Terminal Value
- Discounting Cash Flows and Valuation
- Getting Started
– Making a reusable budget model template
– Creating dynamic titles - Output 1: Calculating Cash Required for Budgeted Asset Purchases
- Output 2: Calculating Budgeted Depreciation
– Useful life
– Written-down date
– The depreciation schedule for the current year
– Depreciation in prior periods - Output 3: Calculating the Written-Down Value of Assets for the Balance Sheet.