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 {snippet ktls-link} 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

R02 - Understanding Spreadsheet Risks and Implementing Internal Strategies to Mitigate

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

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

c

The use of dynamic references, dynamic names, advanced conditional expressions, array formulas is canvassed in this session. Techniques to avoid redundant 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 data and structuring workbooks to facilitate these analyses.  Summary and cross-tab reports using dynamic array formulas are demonstrated.

Participants should be familiar with more complex formulas such as MATCH, INDEX, and SUMIF and have some knowledge of array formulas. Completion of one of the pre-requisite modules or courses is recommended.

T05 - Building Dynamic Charts

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 dynamic charts 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.  It also demonstrates useful techniques to manage the scales on charts and providing simple functionality to enable the end-user to control key elements in the charts.

T06 - Save Time - Build a Template for Reuse and Standardisation

A hallmark of well-designed models is the use of styles to manage the formatting and appearance key elements in 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.  This has an advantage as users become familiar with and are better able to interact with models, as the clear formatting effectively acts as a form of documentation.

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 theme is developed (you may use your own enterprise's colours and font preferences), and the issues around colour selecting and ordering are discussed.  Then a template is built using the underlying theme, and is populated with a useful set of standard styles.  The approach for maintaining and extending the style structure is discussed and explained.  Important components such as 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

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, COUNT, SUMIF, INDEX and XLOOKUP.

T08 - Array Formulas Reinvented

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 new operators added to Excel to handle array logic are also covered.  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, XLOOKUP, and SUMPRODUCT.

T09 - Using LET to Add Transparency to Complex Calculations

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, or XLOOKUP.

T10 - Advanced Formula Environment and LAMBDAs - A Revolution in Excel

This session demonstrates the use of the Advanced Formula Environment to manage and edit LAMBDA formulas in Excel.  It will demonstrate the development of simple introductory LAMBDA formulas in Excel worksheet, then more practical and useful ones, and finally introduce and demonstrate the use of the Advanced Formula Environment to create, edit and manage the LAMBDA formulas in a workbook project.  The participant will gain some very practical and useful functions that they can deploy in their own projects as well as learning how to use the LET and LAMBDA functions and the Advanced Formula Environment to develop solutions to common problems that otherwise require quite complex calculations in models and analyses.

Participants should be familiar with Excel and comfortable working with formulas and functions such as IF, INDEX, MATCH, XLOOKUP and familiarity with the use of the LET function is a distinct advantage.  If unfamiliar, we would recommend that participants complete module T09 first..

T11 - Structuring and Accessing Data in Models

The layout of data in models, such as tables of historical data, tables of inputs, conversion and indexing factors and how to effectively work with those tables in retrieving data for the use of calculations in forecasts is the topic covered in this module.  At one level, this is a straight-forward requirement, but as models become more complex, many users find they have difficulties and errors creeping in that make the management of the source data difficult and the use of the data in their forecasting formulas becomes challenging.

This module canvasses proven techniques for removing the headaches and demonstrates techniques to simplify the referencing and gathering of data based on input values or key values derived by calculation.  Dealing with multi-value keys, reducing the complexity in lookup and matching calculations to make the model easier to manage and audit is a key focus.  This module is designed to help the user solve real-world problems.

T12 - Charting Fundamentals

Charting to build summary reports, dashboards and to provide graphical presentation to be shared via PowerPoint are common tasks.  yet for many users, the process of getting a chart just right, is a somewhat hit and miss affair, especially if something out of the ordinary is required.

This session canvasses chart types, what they are useful for, how to layout the data to make building the charts as easy as possible and critical information on understanding how the charting engine works.  Solutions to issues when the data is not neatly arrayed is also presented and can save lots of lost time and pain.  A host of useful guidance on formatting, default options, advanced options and how to corral your charts is shared.  The use of Sparklines is covered,

And simple processes to organise and size charts, arrange them in sets and to copy or link them into PowerPoint are demonstrated.  This can save a lot of time and grief for users who struggle with these tasks on a weekly, monthly or less frequent cycle of reporting.

Users should have some basic familiarity with charts and be comfortable working with data in Excel.  Everything else you need to know will be taught in this lesson.

T91 - Making Practical Use of the Macro Recorder

vba

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

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 functions such as IF, INDEX, MATCH and XLOOKUP, basic numeric formatting and the use of data validation.

M02 - Implementing Complex Scenarios in Cash Flow Models

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 and the LET function would be an advantage.

M03 - Building Valuation Models with Less Complexity

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.

M05 - Dynamic Array Formulas in Forecasting

This session demonstrates the use of dynamic array formulas in the creation of forecasts.  It covers when you can and cannot use dynamic arrays in common tasks, and how to process calculations row-by-row and column-by-column in blocks of calculations.  It covers functions that will and those that won't work in dynamic array formulas, and the alternative calculations that let you process values effectively.  It discusses when and when not and how to retrieve data sets efficiently.  The LET function and LAMBDA functions are used, and more recent functions will be required including the array manipulation functions.The sample model built will include user-controlled settings that dynamically changes the number of periods forecast, the number of months per period, building a truly flexible forecasting tool with surprisingly few formulas!

It would be familiar with modelling concepts and be able to absorb the workings of new and unfamiliar functions.  Some familiarity with LET and LAMBDA would be an advantage, but their use will be covered in this module..

M06 - Dealing with Interest Circularity

A common problem in modelling tasks is handling the circularity in calculating interest in profit and loss and cash flow models.  The more interest earned, the greater the cash balance, leading to a higher interest, and so on.  Many people deal with this by building in circular references into the calculations, and applying brute force via iteration.  At one level, this is a perfectly valid solution, but it causes some other issues.  It can have an impact on model calculation speed and mask the presence of other problematic circular references.

This module canvasses alternative approaches to this task, demonstrating a common button-based macro-driven solution  that can temporarily implement circularity until a stable solution is found, then replace the formulas with the hard-wired values.Whilst it removes the circularity as a general rule, the user must remember to press the button or execute the macro when any changes occur.  This causes issues with scenario analysis but is a risk factor if the update is overlooked.

The approach to resolving these calculations with non-circular formulas is demonstrated, covering both interest on borrowing and cash at bank, and catering to the transition to or from overdraft to cash at bank.

Analytical & Reporting Subjects

A01 - Streamlining Performance Reporting in Excel

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, INDEX, MATCH and XLOOKUP, basic numeric formatting, the use of Data Validation and basic charting features in Excel.

A04 - Dashboarding in Excel, a Practical Example

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

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

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

analysis by pivot

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 our {snippet alias="pbi-link"}, but this will give management and accounting staff new to this area a grasp of the new functionality and power available.

Retired Modules

T03 - Something Old, Something New...

Withdrawn - no longer available

R01 - Understanding & Managing Spreadsheet Risk

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.

This module has been withdrawn from general offering, and is now available as a free module for subscribers and alumni.

M04 - Questions a CFO Should Ask

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.

T04 - Date Sensitive Forecasting & Reporting

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.

A02 - Streamlining Performance Reporting in Excel (Short Version)

Module A02 is a shortened version of Module A01 - 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

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.

This module has been retired and made available as a self-stud course for subscribers and alumni.