This programme can run as an online or classroom training
(Available also for Customised Training by Duration, Venue & Fee)
(PARTICIPANTS MUST BE WITH THEIR LAPTOPS)
Programme Description
Modeling techniques that this course teaches are used in investment appraisal, capital planning, budgeting, valuation, financial analysis, and forecasting to make accurate financial and business decisions at the right time. The course will deliver competencies in the use of Financial Tools designed for Account/Finance, Risk Management and Financial Control.
It is for delegates who already can create moderately complex spreadsheets and want to build models. The course will examine very briefly some foundational and intermediate topics and then progress to advanced functionality such as: Pivot Tables, Vertical Lookup (Vlookup), Goal-seeking and Solver, Cost of Capital, Ratio Analysis, Financial Forecasting, Profit Volume & Break-even Analysis, “What if”, Investment Return Appraisal, Financial Model Design and Constructions, Project Evaluation and Sensitivity Analysis.
Learning Objectives
After completing this course, participants should be able to perform the following using excel models:
Advanced financial performance presentation and analyses
Loan management and loans schedules determination
Product sales, marketers and distributors performance analyses
Profit ehaviorn
Product costing and pricing decisions
Investment appraisals and capital rationing solutions
Cost of capital determination
Cash flow planning
Business forecasting.
Pre-requisite
Participants should already be using Excel on a regular basis.
Course Contents
DAY ONE
Data Modeling
Meaning and Purpose of Financial Modeling
Advance Referencing
Date manipulation
if and nested if’s functions
Pivot table & Chat
INDEX & MATCH functions
Filter and Advanced Filters
Subthe ehaviorn, Transpose
Sum, Sum IF, Sum IF’s analysis.
Formula Auditing Tools
Revealing Formulae
Tracing Precedents/Dependents
Goto Special
Linking sheets in the same file
Linking different Excel files
Using Edit, Links
Viewing different files at once
Saving a workspace
Viewing different sheets at once
Window Split
Data consolidation.
Conditional Functions and Data Calculation
Use If Statements, Nested If
Use And, Or, Not
Nesting If, And, Or, Not
Use the Sumif, Countif, Averageif
Apply Conditional Formatting
Date Calculations
Calculate working days
Use the DAY(),MONTH(),YEAR() functions
Create and use Time calculations.
Worksheet Management and Linking
Link sheets in the same workbook
Link cells in different Excel files
Manage and edit Links
View different files at same time
Saving a workspace
View different sheets at same time
Data consolidation (within same file)
Data consolidation (across workbooks).
Data Management List
Create an Excel data list
Use the Excel List Tools
Use Autofilter
Sort the Data
Use the Advanced Filter
Add Sub the organisations to a list
Apply Data Validation to keep data clean
Group and Outline your data
Use the Data Form.
DAY TWO
Advanced Excel Charts/Graphs
Create a Chart with keyboard
Use the Chart Wizard
Edit and Format a chart
Change the Chart source Data
Apply Trendlines
Change Charts from objects to sheets
Add labels and axes to chart
Show specific Data Points
Save custom chart types
Change the default chart type
Using the Chart Wizard
Editing and Formatting charts
Saving custom chart types
Setting a default chart type.
Lookup Functions and Application
Build the Vertical Lookup (Vlookup) function
Build the Horizontal Lookup (Hlookup) function
Build the Match and Index functions
Build and use Database Functions
Create A PivotTable
Change the PivotTable layout
Manipulate PivotTable fields
Format the PivotTable and apply Styles
Use Banding (Formatting)
Group & Ungroup items
Filter data in the PivotTable
Insert calculated fields.
Pivot Table Creation and Manipulation
Create A PivotTable
Change the PivotTable layout
Manipulate PivotTable fields
Format the PivotTable and apply Styles
Use Banding (Formatting)
Group & Ungroup items
Filter data in the PivotTable
Insert calculated fields
Change calculation options
Change PivotTable Options
Display and hide data in fields
Lay out reports on worksheet
Create a PivotChart from report
Manipulate PivotChart fields.
Articulating Profit Volume & Break-even Analysis
Creating break-even model
Knowing when a business break-even
Application in price strategy.
What If Analysis (Sensitivity Testing)
Create and manipulate Scenarios
Create and change Custom Views
Build scenario Reports
Use the Goal Seek tool
Use Solver & Advanced Solver Features
Build Data Tables
Apply Data validation rules
Read only and Pass wording of files.
DAY THREE
Building Financial Models
Financial Statement Design using Cashbook and Trial Balance
Financial Model Design and Construction from scratch using Deterministic Approach (Du Pont format)
Statement of Comprehensive Income (SCI)
Building Statement of Financial position (SFP)
Cash flows modeling.
Ratio Analysis and Ratio Interpretation from the Model
Liquidity Ratios
Activity Ratios
Gearing Ratios
Insolvency Ratios
Profitability Ratios.
Comparison your Ratios with Industry Standard
DAY FOUR
Capital Budgeting Techniques (Linear Programming)
Multi-Product Mix Problem – Product Optimisation(Solver)
Capital Budgeting Problem – Multi-Period Capital Rationing
Investment Planning Problem
Replacement Decision Problem.
Cost of Capital
Debt Financed Business
Equity Financed Business
Weighted Average Cost of Capital.
Project Evaluation Techniques
Present Value (PV)
Future Value (FV)
Payback period (PBP)
Net Present Value (NPV)
Internal Rate of return (IRR)
XNPV
XIRR
MIRR.
DAY FIVE
Business and Financial Forecasting Techniques
Forecast function
Trend function
Growth function
Coefficient of determinant (R2) interpretation
Business Break Even Analysis (Cost Volume Analysis Curve).
Introduction to Macro and Vbscripting
Review the purpose of Macros
Record a macro
Save a macro
Edit a Macro.
