Financial Modelling in Excel

Day One

Introduction & Course Objectives

• Overview of course objectives
• Review of models and their objectives

Overall Model Structure & Design

• Best financial modelling practice
• Overall structure of the model
• Logic flow within the model
• Separation of inputs, calculations and outputs
• Defining desired outputs
• Setting-up required inputs
• Use of switches to allow option selection
• Use of flags to control timing factors
• Set-up for flexibility
• Consistency in the model
• Accommodating multiple options
• Building assumptions off term sheets or other external inputs
• Using the assumptions sheets as a sign-off document
• Restricting ranges of inputs and validation criteria
• Version control
• Use of the corkscrew technique
• Tracking changes
• Documentation
• Exercise – creating a simple model with an assumptions / input sheet with built-in flexibility

Modelling Techniques for Revenue and Cost Forecasts

• Translating assumptions & inputs into a model forecast:
  • Overall growth
  • Split volume and price
  • Split volume into market growth and market share
  • Top-down market sizing, market share and price
• Build-up of construction or other capital costs
• Correct matching of units
• Modelling pricing & revenue assumptions
• Use of lookup and INDEX/MATCH functions to change expenditure timings
• Building in sensitivities into the model
• Exercises:
  • build-up revenue forecasts with different techniques and granularity levels
  • build capex forecast with flexible timing

Day Two

 Inflation / Escalation Factors

• Creating inflation indices
• Controlling start time of inflationary pattern
• Applying multiple rates to different cost & revenue items
• Varying inflation rates over life of the model
• Exercise – model multiple, variable inflation rates, with different starting-points and changing rates over the life of the model

Cash Flow Modelling

• Differences between income statement and cash flow
• Organising cash flow statements
• What is working capital?
• Modelling changes in receivables, inventory, payables
• Income statement items that are not cash: adjustments needed
• Cash items not in the income statement: adjustments needed
• Financing cash flows: changes in debt & equity
• Revolving facilities and their role in cash flow forecasting
• Exercise: create a cash flow model to forecast cash inflows and outflows for a trading business, and drawing on revolving facilities as needed.

Comparing a Model to Previous Versions of the Model

• Separate runs and variation of inputs
• Comparison of actuals to forecast
• Comparing results of different versions of same model
• Reviewing future implications of variances
• Example – from different versions of a modelled forecast, calculate variances and review future assumptions

Day Three

Sensitivity Analysis in a Model

• Stress-testing the model
• Varying inputs to assess effect on results
• Use of built-in sensitivity inputs
• Use of goal seek & solver
• Version control to allow comparison of outputs
• Use of Excel tools to support sensitivity analysis:
  • watch window function
  • data tables
  • scenario manager
• Exercise – from a given model of cash flows, P&L and balance sheet, calculate effect of varying inputs to a given degree, and stress-test model to break-even.

Reporting Outputs

• Design techniques to enable optimisation
• Consolidation techniques:
  • Add-through
  • Sum-through
  • Sum & Group
  • Consolidate function in Excel
  • Use of charts & graphics
  • Using pivot tables:
    • Pivot table wizard
    • Setting-up tables
    • Changing parameters
    • Analyzing pivot table data
    • Drill-down function

• Exercise – from a given set of outputs, create output tables, pivot tables and consolidations to enable flexible reporting

Advanced Excel Functions

• Advanced financial mathematics – PMT, PPMT, IPMT,NPER
• Summation & counting functions in Excel: COUNT, COUNTA, COUNTIF, AVERAGE, AVERAGEIF, SUMIF, SUMIFS
• Statistical functions in Excel
• Depreciation functions in Excel
• Date and Time functions in Excel
• Lookup and Reference formulae: Lookups, OFFSET and MATCH formulae
• Logical functions – using TRUE, FALSE, IF, AND & OR in combination
• Text functions in Excel: LEFT, RIGHT, MID, LEN and wildcard
• Exercises:
  • model a loan using some of the advanced functions learned
  • summarise messy data without cleaning it, using text functions

Wrap-Up

• Overall review
• Key points to re-iterate
• Brief introduction to further exercises & reading
• Final questions and issues to discuss

The Financial Modelling for Excel trainer over 30 years’ experience in a wide range of roles in finance. He has delivered training courses on behalf of a number of international training companies since 2005.

He trained as a Chartered Accountant at KPMG in South Africa and New Zealand, before moving into the industry with Ford Motor Company. He held various positions there in financial analysis, budgeting and forecasting, until he was appointed sales planning manager, responsible for forecasting models, production planning and supply logistics. He joined a multinational private consultancy group in Australia, as their general manager (finance); in this role, he guided the group through a period of major change and financial turnaround.

For the past 21 years, he has worked as a freelance financial modeler, analyst and trainer for a range of blue-chip clients. Assignments have included financial modelling for two major LNG projects in the Middle East, numerous renewable energy projects in both Europe and developing countries, structured financing for a large-scale property development, restructuring and outsourcing projects in the retail gas sector, and PPP transactions in the utilities, health and support services sectors.

With an extensive accounting background, he brings accounting knowledge and analytical skills to transactions and financial modelling. He has built up a great deal of experience in financial modelling in numerous sectors, including gas production, electricity generation, energy retailing, waste processing and property development sectors, as well as building financial models in central government departments, retail enterprises and high-volume online environments. He has built, developed and used models to support commercial negotiations, analyse risk, test scenarios and forecast results.

• Build structured and logical financial models that are flexible, transparent, and easy to update.
• Apply advanced Excel functions and techniques for forecasting, analysis, and reporting.
• Translate assumptions and inputs into accurate revenue, cost, and cash flow projections.
• Perform sensitivity and scenario analysis to test models under varying conditions.
• Present outputs effectively using charts, pivot tables, and consolidated reporting tools.

The learning methods used are practical, as practice of newly-learned techniques enables a deeper and more effective building of skills. Each section will be covered briefly as a module in a traditional class style, but the real learning experience is in the exercises within each module. Participants will practice what they have learned under the supervision of the course facilitator. Suggested model solutions to each exercise will be provided and discussed.  Participants will take away the course notes, exercises and model answers.

The course is aimed at people who have some knowledge of Excel, but need to take their skills to the next level.  Analysts, accountants, model-builders, budget controllers and those who prepare forecasts and business plans will all benefit from the new knowledge and best practice they will gain from the course.

The course teaches intermediate skills in using Excel, not just technically – although we will cover many functions and features that enhance productivity – but also the way that Excel spreadsheets can be structured and organised logically to make them user-friendly, time-saving and easily transferable.