Financial Modelling using Excel

Home » Monthly Promotion » Financial Modelling using Excel

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

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 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

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

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.