INTRODUCTION FOR FINANCIAL ANALYSIS & MODELLING
Financial and predictive modelling tools are important when analyzing massive amount of data in order to identify and predict findings for decision making. Learning the ability to perform modelling will equip a finance and business professional in a data driven digital economy.
The course will guide participants in how to build smart models and avoid potential pitfalls, working through real scenarios so you can apply your learning directly to your work. Excel financial modelling involves designing and building calculations to aide decision making.
Who should attend: Finance Leaders, Accountants, Financial Analysts, Business Analysts, Planners and Business Managers with financial modelling responsibilities
COURSE OBJECTIVES
On completion of the programme, participants will be able to:-
- Produce an accurate and robust forecast and budgets.
- Optimise with constraints using Solver.
- Produce forecast using exponential smoothing and growth model
- Understand practical excel formulas, functions for financial data analysis, lookup and formula referencing.
- Use Microsoft excel tools to support sensitivity analysis e.g. pivot tables, goal seek, data table and scenario manager
- Perform financial modelling based on case study simulated scenarios. i.e. capital budgeting and analysis using payback period, Discounted Cash Flow (DCF), Net Present Value (NPV) and Internal Rate of Return (IRR) to evaluate feasibility of project and build the management dashboard
COURSE CONTENT
1) Introduction and overview of Financial Modelling
- Define the Terms Model and Financial Model.
- Learn the 10 steps to create good Financial Models.
- The 12 steps to Improving traditional Financial Models.
- Use Flowcharting Techniques to improve your model.
2) Modelling techniques
Tips for best practice structure and design of financial models
- Separation of inputs, calculations and outputs
- Avoiding hard coding
- How to best include adjustments
- Effective styling to purpose
- Indicators of risk and mitigating the risk of error
- Using reconciliations and zero checks.
- Tips for effective reporting
- Absolute Versus Relative Referencing
- Relative Formulas
- Problems With Relative Formulas
- Creating Absolute References
- Creating Mixed References
3) Key practical formulas for financial data analysis, validation and modelling
Understanding Financial Functions and hands on session
- Using PMT
- Using FV
- Using NPV
- Using PV
- Using RATE
- Using EFFECT
- Using NOMINAL
Understanding Data Lookup Functions
- Using VLOOKUP
- Using VLOOKUP for Exact Matches
- Using HLOOKUP
- Using INDEX
- Using Match
- Using SUM IF and SUM IFs
Software: Microsoft Excel will be used for hands on session
4) Depths of capital budgeting long-range financial plan with key analysis
- Build forecast models using exponential smoothing method
- Use exponential growth rate by plotting data into scatter plots, and perform correlations analysis between business indicators for growth rate prediction.
- Introduction to What If Analysis AKA Sensitivity Analysis
- Use Solver to search feasible solutions and decide on the most optimal choice
- Use Break-even analysis
- Identify major financial ratios.
- Use Financial Ratios to Measure a Firm’s Financial Performance in financial forecasts and plans.
5) Case study practical hands-on session
Business case model build #1 – Create a Feasibility Study
Business case model build #2 – Clients have commissioned a model to forecast Cashflows of a potential investment.
Business case model build #3 – You have been engaged to assess the viability of a Development proposal, compile forecast Financial Statements.
Business case model build #4 – Create a Management Dashboard and Sensitivity Analysis on a model you have built.
Business case model build #5 – Mini-project to automate the payout calculation for investors given set parameters.
6) Protecting Financial Data and Worksheets
- Understanding Data Protection
- Providing Total Access to Cells
- Protecting a Worksheet
- Working With a Protected Worksheet
- Disabling Worksheet Protection
- Providing Restricted Access to Cells