A focused lecture series on implementing effective models & reports for finance professionals

The Key Topic Lectures Series is a set of 1 hour, ½-day and full-day lectures.  The sessions that make up this series focus on key subjects in the areas of design & construction of models for financial forecasting and valuation and reports for financial analysis.  For more information, see the Key Topic Lecture Series page.

Currently Available Lectures

The following accordion listing shows the current set of lectures available in the series, the order of listing being associated with their publishing date. The numbering system merely identifies each lecture and allows us to show the interdependencies, it does not indicate order for study.

Risk Subjects

R01 - Understanding & Managing Spreadsheet Risk
Duration:1 hour
Standard:Intermediate
Prerequisites:none

This module is a lecture with presentation. It discusses the role of spreadsheets in planning and management reporting. It asks the questions: "what is the business risk?" and "how do you manage and ameliorate those risks?" It identifies the key sources of error and their impact and contribution to spreadsheet risk.

It introduces simple but effective built-in techniques for identifying errors and canvasses some commercially available tools available to assist with this task. It suggests some strategies and standards required within an organisation to manage the risks in model construction, maintenance and usage. It proposes some straight-forward approaches to better manage these risks and address governance concerns.

R02 - Understanding Spreadsheet Risks and Implementing Internal Strategies to Mitigate
Duration:½ day
Standard:Intermediate
Prerequisites:none

This session begins with a lecture based on R01 - Understanding Spreadsheet Risks, which lays out the areas of concern in the use of spreadsheets in modelling and analysis in financial functions. This part of the session canvasses some of the server based solutions that can be deployed to enforce policies on spreadsheets within an organisation and identify changes and malfeasance.

The session then moves to a hands-on lecture in which various standardised prepared elements can be simply deployed to assist in documenting and managing the lifecycle of key models and analyses within an organisation. This represents a lower cost simpler implementation, that can be quickly deployed. It includes recommendations around building modelling policies, deploying and policing them.

Finally, the session ends with some hands-on examples of using tools built-into Excel to identify modelling issues. Some of this part of the session relies on audit tools available in Excel 2013 or later.

Technical Subjects

T01 - Function & Formula Fundamentals
Duration:½ day
Standard:Intermediate
Prerequisites:none

The core knowledge needed to effectively use formulas in Excel, removing complexity & redundancy is presented in this lecture. Many potential participants would assume that this course is beneath them and that they understand the subject area it covers, but most users will find new information available to them in this course.

It is suitable for those who need to improve their technical knowledge and as a revision for those who want to focus on the finer points. Participants should be familiar with Excel and comfortable working with formulas and functions such as IF, SUM, COUNT, SUMIF, VLOOKUP, IFERROR..

T02 - Advanced Analytic Formulas
Duration:½ day
Standard:Intermediate
Prerequisites:T01, A01, A02, FAC

The use of dynamic references, dynamic names, advanced conditional expressions, array formulas is canvassed in this session. Techniques to avoid redundant and complex calculations which directly impact on model size and performance are discussed and demonstrated. It also presents techniques for working with formulas to build more flexible analyses of performance data and structuring workbooks to facilitate these analyses.

Participants should be familiar with more complex formulas such as MATCH, INDEX, INDIRECT, OFFSET & have some knowledge of array formulas. Completion of A01 or A02 Streamlining Reporting Performance in Excel and T01 Function and Formula Fundamentals or prior attendance at the Financial Analysis Certificate is recommended.

T03 - Something Old, Something New...
Duration:1 hour
Standard:All Audiences
Prerequisites:none

For finance professionals who are making the transition or have recently made the transition to Office 365 from Office 2013 or Office 2016. A tight session which explains why the product has changed & the things you need to know to quickly get up to speed and embrace the changes.

It also covers key new formulas and how they can significantly reduce complexity in common modelling & analytical tasks and then revisits critical but under-utilised features which have been present in Excel for a long time, but are now much easier to understand & implement. Even those who already feel somewhat comfortable in the new interface will find useful productivity tips.

Originally introduced when Excel 2007 was released, and then again for Excel 2010 and Excel 2016, it has been updated to cover the latest generation of changes.

T04 - Date Sensitive Forecasting & Reporting
Duration:½ day
Standard:Advanced
Prerequisites:none

Excel provides a rich set of functionality to manage date-sensitive calculations. Yet in spite of this, it is common that issues in models and analyses that revolve around this fundamental area become needlessly complex. One alternative to working with dates in detail is demonstrated in lecture # 10 - Implementing Complex Scenarios in Cash Flows.

In this session the examples deal with implementing models with variable financial year-ends; those with variable reporting periods; and demonstrate techniques to deal with the apportionment of transactions into different reporting periods. The samples presented provide some simple yet very effective techniques that can be re-used in ongoing model construction.

Participants should be familiar with how dates are stored and calculated in Excel, and should be familiar with the functions DATE, YEAR, MONTH, DAY, EDATE and EOMONTH. They should also be familiar with custom formatting of dates. An understanding of array formulas would be an advantage.

T05 - Building a Dynamic Chart
Duration:1 hour
Standard:Advanced
Prerequisites:none

Presentation of data in charts is highly desirable to aid in the comprehension of trends, relationships and progress against targets. However, when executed manually, the preparation of charts can be a significant roadblock at critical times in the reporting cycle.

This workshop demonstrates a technique for building a dynamic chart that allows a user to select the data to be charted and the date range for which the chart should be drawn for one or more data series to vastly improve the flow of work at critical reporting times and to simplify the incorporation of data from changing data sets.

T06 - Building Workbooks with Style
Duration:½ day
Standard:Intermediate
Prerequisites:none

A hallmark of well-designed models is the use of styles to manage the formatting and appearance of a workbook. This allows the modeller to apply consistent formatting to key elements of a workbook, such as input, calculation, error checking, output/report cells and consistent headings.

The use of styles is an effective yet simple approach which builds user confidence, assists in the maintenance and audit of models & reports; makes it easier for users to understand and navigate complex calculations; and improves client confidence. It allows for the ready reconfiguration of the livery of a workbook which may be required to deal with accessibility issues and colour-blindness and can give management and other users a greater deal of confidence when using a well-structured workbook.

The process of building effective templates is surprisingly easy and the key techniques are demonstrated in this hands-on module in which a template with all of the key elements including title page, index to worksheets, error checking sheet, input styles, rounded reporting styles, advanced error checking styles and key navigation elements are all built from scratch.

T07 - Formula Auditing & Error Tracing & Handling
Duration:½ day
Standard:Intermediate
Prerequisites:none

This session demonstrates both basic and more advanced built-in approaches to formula auditing and tracing the flow of values through a worksheet. It also demonstrates built-in techniques for identifying constants and inconsistent formulas.

It presents key information to aid in understanding the cause of errors, how to identify their sources and how to manage errors in modelling and analytical projects without sacrificing clarity and without making formulas more difficult to trace or audit. Having demonstrated why many common approaches make tracing or auditing difficult to undertake, steps to implement better approaches are canvassed and demonstrated to aid in the testing, checking, audit & maintenance of future models. Error checking approaches and error checking reports are also discussed.

Participants should be familiar with Excel and comfortable working with formulas and functions such as IF, SUM, COUNT, SUMIF, VLOOKUP, ISERROR.

T08 - Array Formulas Reinvented
Duration:½ day
Standard:Advanced
Prerequisites:none

This session demonstrates the new dynamic array functionality provided in Excel 365, and why we don't need to use SUMPRODUCT for totalling conditionally or when working with arrays of weighting factors.  Some old array techniques that lived on a dusty shelf are dusted off and put to powerful use in new calculations, not previously possible in Excel.

It presents key information to understand when dynamic arrays are triggered, what functions do and what functions don't work in dynamic arrays, the functions that work, but not as desired and the workarounds.  The impact of using dynamic arrays on recalculation times is discussed and where to draw the line on complexity.

Participants should be familiar with Excel and comfortable working with formulas and functions such as IF, INDEX, MATCH, LOOKUP or VLOOKUP or or XLOOKUP, and SUMPRODUCT.

T09 - Using LET to Add Transparency to Complex Calculations
Duration:½ day
Standard:Advanced
Prerequisites:none

This session demonstrates the use of LET both in standard and array calculations to break down the complexity and to improve calculation performance through managing redundancy in references and calculations.  Ideally complexity is best handled by breaking a calculation down into multiple steps, but there are times where a multi-step calculation is desired for layout reasons, or because in some cases, redundancy in calculations can be sidestepped.

Approaches to building and testing LET calculations are demonstrated along with approaches to break them down to aid in auditing and understanding them.

Participants should be familiar with Excel and comfortable working with formulas and functions such as IF, INDEX, MATCH, LOOKUP or VLOOKUP or or XLOOKUP, and SUMPRODUCT.

T91 - Making Practical Use of the Macro Recorder
Duration:½ day
Standard:Intermediate
Prerequisites:none

This session shows how code can be recorded with the macro recorder which does not rely on lots of fixed, hard-coded ranges nor formatting being recorded. It demonstrates how a knowledgeable user of Excel can take a first step in creating useful and effective automation, by building on effective end-user techniques.

Unfortunately, when using the macro recorder, as its title suggests, it records macros that repeat a user’s actions, recorded while they carry out a sample task. If the actions that are recorded do not anticipate the changes in data and the reports or models, the attempt to reduce the effort of using the workbook can be defeated by introducing a constant series of changes and updates that must be made in the code. Worse still, the recorded results may cause more problems than they solve.

We strongly believe that the calculations and results derived in a workbook should be solely dependent upon its inputs and formulas, and so we do not encourage the wanton use of macros to derive the results. However, there are many simple tasks which might enhance a workbook’s usefulness by making it easier to print reports, place data into presentations, filter data, update PivotTables or import data. Provided that a good approach is used in recording and designing these macros (which is demonstrated in this session), many effective enhancements can be made to existing workbooks by the judicious addition of some helpful macros.

Modelling Subjects

M01 - Fundamentals of Model Design & Development in Excel
Duration:½ day
Standard:Intermediate
Prerequisites:none

For finance professionals who are looking to improve the flexibility and functionality of their financial models, this hands-on workshop will canvass important techniques for simplifying and improving the development of forecasting models in Excel using practical examples.

It demonstrates the rapid design and development of a forecasting model from scratch, given a number of key assumptions, implementing design elements to facilitate model maintenance and then a powerful technique for sensitivity analysis is introduced, incorporating a number of scenarios in the one model. Participants should be familiar with Excel and comfortable working with formulas such as IF and VLOOKUP, basic numeric formatting and the use of data validation.

M02 - Implementing Complex Scenarios in Cash Flows
Duration:½ day
Standard:Advanced
Prerequisites:M01, A02 or FAC

Aimed at finance professionals seeking techniques to manage sets of complex criteria in models, this workshop demonstrates techniques for structuring and implementing complex table-driven assumptions for cash flow type models.

Given some starting assumptions, it builds a sample construction cash flow from scratch, implementing scenarios in which the costs are variable as to amount, timing, phasing and in which the cash flows can be automatically allocated on an S Curve basis. Finally it implements sensitivity analysis to allow the comparison of all scenarios within the one model.

Participants should be familiar with more complex formulas, the use of Data Tables, data validation, IF, MATCH and INDEX functions. Some familiarity with array formulas would be an advantage.

M03 - Building More Effective Models with Less Complexity
Duration:1 day
Standard:Advanced
Prerequisites:none

This session is targeted at model builders or management responsible for modelling supervision. It canvasses key approaches to laying out models effectively to assist the end user to comprehend them and to make them easier to audit and review.

It constructs a high-level business valuation model which includes a discounted cash flow analysis. It allows for multiple scenarios in which a variable number of historical years can be entered and in which assumptions for future years can be keyed only where a change is required. It demonstrates an approach that permits multiple scenarios of two dimensional assumption sets to be managed. Sensitivity techniques involving the use of Data Tables are demonstrated.

An emphasis is placed on using consistent layout and formatting, naming conventions and simplified formulas to simplify construction, audit and use. It is a hands-on session that builds a simplified sample model and demonstrates and discusses the techniques and methodologies used.

Ideally, participants will already have experience building flexible forecasting or business valuation models.

M04 - Questions a CFO Should Ask
Duration:1 hour
Standard:All Audiences
Prerequisites:none

In financial modelling & forecasting, complex analyses are conducted. Sometimes very detailed and complex plans are constructed and subjected to sensitivity analysis. Yet, since we are only human, the risk of errors in those models is significant, and that can be exacerbated by poor technique and commonly adopted approaches which court errors.

This session canvasses key issues around standards adopted in an organisation to manage modelling tasks and manage the error risk, during construction, usage, maintenance and evolution of the models.

It raises some issues which need to be addressed if this vital aspect of management planning is not to founder on poor technique; complex and error-prone formulas and a lack of standards and testing. It proposes some straight-forward approaches to better manage these risks and address governance concerns.

Analytical & Reporting Subjects

A01 - Streamlining Performance Reporting in Excel
Duration:½ day
Standard:Intermediate
Prerequisites:none

Targeted at finance professionals aiming to improve reporting processes, remove common time-consuming bottlenecks and reduce manual intervention in data update, this session demonstrates changes in workbook design to meet these objectives.

It canvasses techniques for structuring reporting workbooks; properly segregating data; implementing flag - or switch - formulas to simplify reporting and aggregation formulas to streamline the reporting process. It introduces techniques for designing user-configurable reports. It includes the construction of Waterfall & Bridge charts which will automatically classify items and shows a simple yet sophisticated technique to build the chart so the scale will be adjusted automatically.

Participants should be familiar with Excel and comfortable working with formulas such as IF and VLOOKUP, basic numeric formatting, the use of Data Validation and basic charting features in Excel.

A02 - Streamlining Performance Reporting in Excel (Short Version)
Duration:2 hours
Standard:Intermediate
Prerequisites:none

Module 04 is a shortened version of Module 03 - Streamlining Performance Reporting in Excel, requiring an hour less tuition. That enables one of the one-hour lectures to be incorporated into the program in the same ½-day and provides flexibility in scheduling sessions in conference programs with differing length session times.

To achieve this, it uses a slightly simpler report design with more standardised inputs & is not as ambitious in the charts included in the report. It still includes all the essential aspects of the report design & implementation & manages to effectively communicate & demonstrate the key elements of the lesson.

A03 - Formatting & Rounding to Simplify Reporting
Duration:1 hour
Standard:Intermediate
Prerequisites:none

This one-hour workshop demonstrates how to use the formatting and rounding features in Excel to present properly rounded reports that add through their sub-totals and totals, without the need for manual adjustment. It also demonstrates how to make the level of precision a dynamic feature available to the report user via a drop-down menu.

A04 - Dashboarding in Excel, a Practical Example
Duration:½ day
Standard:Advanced
Prerequisites:none

This hands-on session demonstrates the preparation of a reusable dashboard that allows the user to filter and drill-down on the data presented. It is based on a set of financial data extracted from a management accounting system and some averages and KPIs derived from that data.

The session includes practical tips and advice on layout, design & construction of dashboards. It demonstrates the use of styles to standardise and create a coherent view. Some advanced concepts are used and demonstrated, but full instructions are provided in the handout, so Intermediate users should be able to apply the content.

A05 - PivotTable Fundamentals
Duration:½ day
Standard:Intermediate
Prerequisites:none

The PivotTable, introduced into Excel in the 1990s, allows for the dynamic cross tabulation of data from within workbooks and from external data sources to allow for the easy aggregation and analysis of data by end-users. This session covers all the fundamental knowledge about how PivotTables work, how to build and manage them, how to add calculations and control the outputs from the analysed data.

It covers important issues which are not well understood by many users which address problems with the formatting and analysis of data in the PivotTables. Improvements in the PivotTable engine in the more recent versions of Excel are covered. Useful filtering and sorting techniques are demonstrated. It also covers the use of Slicers, introduced in Excel 2010 and Timelines that were introduced in Excel 2013.

A06 - PivotTable Reporting
Duration:½ day
Standard:Intermediate
Prerequisites:none

Building on Module A05 - PivotTable Fundaments, this session explores advanced calculations and data analysis settings available in the PivotTable interface which are not utilised by many users, but which provide essential analytical outputs. It also demonstrates some useful solutions to common date-related issues in summarising and collating data by quarter, half year & financial year.

The extraction of data from PivotTables using the GETPIVOTDATA function is demonstrated, including how to make the references more dynamic, enabling the use of a standard block of formulas for a range of reporting needs. Custom sorting and formatting of the PivotTable outputs is demonstrated.

A07 - Introduction to Excel's BI Tools
Duration:½ day
Standard:Intermediate
Prerequisites:none

This hands-on session demonstrates the use of the Business Intelligence (BI) tools built into recent versions of Excel. Including, Power Query to import data, prepare and transform data for analysis in PowerPivot tables in Excel, making use of and introducing the Data Model. The data is then used to prepare a simple dashboard with 3D Map view.

For true mastery of these tools, we would recommend out Power BI Series, but this will give management and accounting staff new to this area a grasp of the new functionality and power available.