Excel Skills for Modeling
This session converts participants to using Excel in best practice modeling methods to create repeatable, consistent, flexible, and robust formulae and structures.
Excel set-up for financial modeling
Effective use of keyboard shortcuts
Dynamic referencing
Introduction to Modeling
How to effectively plan and construct your model, using best practice methods.
Effective construction of a model
Objective and structure
Initial preparation
3-Statement Model : Learn how to construct a complete 3-statement financial model.
Income statement
Revenue
Cost of sales, distribution costs, and administrative costs
Interest expense, taxation
Balance sheet
Property, plant, and equipment:
Capex
Depreciation
Working capital:
Inventories
Trade receivables
Trade payables
Debt
Equity
Cash flow statement
Creating a cash flow statement using the indirect method
Operating, investing, and financing items
Modeling net cash flow and ending cash balance
Abridged Cash Flow Modeling
A full 3 statement model is not always necessary. Delegates will learn how to build an abridge cash flow model
Key inputs required for cash flow modeling
Building Cash Flow for Debt Service and Repayments
Building Cash Flow to Equity
3-statement financial modeling
A review of core components of 3-statement financial modeling
Understanding the structure of a model – inputs, workings, outputs
Scope limitations – output requirements vs. input source
Using group edit tools to quickly set up a robust, consistent, and printable financial model
Input sheets – creating underlying assumptions/forecasts
Setting up date flexibility throughout the model – using EDATE and similar functions
Delegates will also build a simple three-statement financial model from a template to refresh and consolidate their understanding of critical components of modeling, including:
The structure of a matrix model and how to build formulae effectively
The links between the financial statements
Build the Financials of a Listed Company
Using an example annual report, we will build upon the financials by constructing integrated historical financial statements suitable for flexible forecasting. If we forecast financials, there are knock-on effects on other model elements. Are these interlinkages understood, and can they be consistently applied in order to maintain the model’s integrity? This session ensures that these connections are understood.
Extracting the relevant historical financials by defining the key numbers, e.g.
Revenues
EBITDA and net income
Operating cash flows
Debt, net debt, and capital structure
Other line items needed to calculate the key numbers, e.g., operating costs, interest, tax, etc.
Building the critical balance sheet items:
Intangibles
Property, plant, and equipment (PPE)
Checking the calculations using Capex/deprecation ratio
Working capital assumptions
Inventories
Trade receivables
Operating cash
Trade payables
Equity and retained income
Integrating/linking the financials
Income statement to cash flows
Income statement to balance sheet
Cash flows to the balance sheet
Building consistent checks
Forecasting
Having created the perfect structure for projecting the income statement, balance sheet, and cash flow numbers by combining Excel analytical tools with commercial knowledge, we will now develop and apply forecast drivers to calculate integrated forecasts.
Step-by-step forecasting/integration – a modular approach to forecasting
Identifying and forecasting the value drivers, e.g.
Sales growth, margins, Capex / depreciation, effective tax rates, working capital days
Applying the concept of “fade”
Forecasting techniques
Harnessing Excel’s forecasting tools
Forecasting for consistency
Integrating the forecasts in the financial statements
Approaches to ensure the avoidance of unintentional circular references
Building a fully flexible scenario manager
We are rarely content with a unitary outcome. What happens if a product fails, the currency strengthens, the business opens a new division, etc.? This session focuses on adding that flexibility to the model.
Base, Bear, Bull
Building switches
Data validation
Visual basic tools to enable the efficient switching between different scenarios
Key Functions: CHOOSE, INDEX, OFFSET
Building a more detailed Operating Model
Delegates will be taught how to analyse the key operating drivers of a business in more detail, including:
Revenues = Price x Volume. How is this modeled across different industries?
Operating costs – understanding fixed, variable and semi-variable costs and the concept of breakeven analysis
Case Study: Delegates will then apply this knowledge to build a more detailed operating model for a real company
Auditing a Model
During this session delegates will learn the tips and tricks needed when checking models and, importantly, how to fix it if something goes wrong.
Sanity checking: historic inputs, ratios & drivers, forecast drivers and financials
Auditing tools within Excel for checking models
Checking for common errors in models: sum, sign and matrix errors
Case Study – auditing and debugging a model
Debt Modeling and Structuring
Develop an understanding of the flow of debt modeling logic, and apply a standardized approach
Finding the debt capacity and optimizing debt structure
Overview of debt repayment: understanding the basics of debt repayment
Types of debt repayment: exploring different types of debt repayment methods, such as bullet, amortization, and balloon structures
Debt repayment schedules and payment calculation methods, including cash sweep, revolver drawdowns, and waterfall analysis
Interest calculations and their impact on cash sweep
Planning to avoid circularity
Best practices for cash sweep repayment modeling, including financial modeling best practices, data management, and modeling automation techniques
Once the techniques have been learned, delegates will build out a debt schedule within a 3 statement financial model and learn how to integrate the debt and interest into the 3 financial statements
Monthly Modeling and Period Consolidation
Often, we need to be able to build a more detailed period model looking at movements in key items intra-year. In this session, delegates will learn how to build a dynamic monthly model, incorporating:
Set up date and period structure – the use of flags and date functions
Eomonth, Edate, etc., for date functionality
Changing the start/transaction date; changing the length of forecast periods
Monthly models pose unique challenges, such as fluctuations in revenue, expenses, and working capital that must be accounted for to generate accurate forecasts
Seasonality: how to consider these fluctuations in forecasting to ensure accurate predictions
Consolidating monthly into yearly financial models
Building a monthly cash flow model
How to change the model for different periods (e.g. quarterly)
Tax Modeling
In this section, delegates learn how to create accurate, comprehensive, and flexible models to accommodate changing tax laws and regulations.
Overview of tax modeling concepts; understanding the tax code and tax regulations
The concept and principles of deferred taxes and their accounting treatment
Developing detailed accounting and tax depreciation schedules
Modeling tax losses (NOLs), their significance, recovery through carry forward, and their impact on future tax liabilities
Interest deduction modeling, limitations on interest expense deductions under the tax code
Best practices for modeling taxes, NOLs, interest deduction, depreciation impact on tax, and deferred taxes
Additional Modeling Tools
Advanced Scenario Management and Data Retrieval
This section will introduce data retrieval functions in Excel to address scenario management. Participants will learn to evaluate and identify the best possible method for their specific scenario.
The Lookup school compared: CHOOSE vs. VLOOKUP vs. HLOOKUP vs. INDEX vs. OFFSET
The advantages and disadvantages of each
Types of switches: data validation lists, versus VBA forms (e.g., ComboBox, ListBox, Radio Buttons)
Building a fully flexible output sheet: select the scenarios and required output(s), and the model does the rest
Dynamic Charting
Turn data into meaningful insight and facilitate decision-making.
Quick fixes: use charting to identify and correct errors or discrepancies in data
Dynamic charts: accommodate changes in input sources, start and end dates, titles, and labels
Dynamic value bridges: we can use dynamic value bridges to help identify relationships and trends that might not be otherwise apparent
Sensitivity Analysis
In financial modeling, one- and two-dimensional data tables are tools we can use to analyze the sensitivity of model outputs to input assumptions.
Basic data tables: use one and two-dimensional data tables to perform sensitivity analysis on model outputs based on a range of inputs
Self-centering data tables: adjust to coincide with the model's drivers and outputs, ensuring that analysis is always relevant
Tips for troubleshooting and resolving common issues when creating and working with data tables, such as circular references and slow model performance
Using the Goal Seek function to set targets, such as calculating the number of units to produce to achieve a specific profit margin, or calculating break-even points, such as determining the number of units that need to be sold to cover fixed costs
1309 G Street NW, Suite 900, Washington, DC 20005, USA
+1 919-503-4999
