KTLS - Current Module Listing

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.

Function & Formula Fundamentals
Lecture #01
Lecture TitleFunction & Formula Fundamentals
Duration½ day
FormatInstructor-led hands-on workshop
StandardIntermediate
Prerequisitesnone
Outline...

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 & that they understand the subject area it covers. But most users cannot properly explain exactly how formulas & functions work in Excel; the order of precedence of operators; identify all operators; nor describe the data types and conversion rules. Often, they cannot properly explain how conditional expressions operate; how arguments are passed & resolved in functions; nor how Excel resolves ambiguous references.

They might still be doing quite advanced work and working quite effectively with Excel, but this lack of key technical knowledge means that tasks are often over-complicated; too many steps are included in calculations; and formulas become needlessly redundant and complex.

This session is designed to correct those misconceptions, as a necessary step to building simpler, more effective formulas for forecasting and analysis. 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, ISERROR.

Fundamentals of Model Design & Development in Excel
Lecture #02
Lecture TitleFundamentals of Model Design & Development in Excel
Duration½ day
FormatInstructor-led hands-on workshop
StandardIntermediate
Prerequisitesnone
Outline...

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.

Streamlining Performance Reporting in Excel
Lecture #03
Lecture TitleStreamlining Performance Reporting in Excel
Duration½ day
FormatInstructor-led hands-on workshop
StandardIntermediate
Prerequisitesnone
Outline...

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.

Streamlining Performance Reporting in Excel (Short Version)
Lecture #04
Lecture TitleStreamlining Performance Reporting in Excel (Short Version)
Duration2 hours
FormatInstructor-led hands-on workshop
StandardIntermediate
Prerequisitesnone
Outline...

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.

Something Old, Something New...
Lecture #05
Lecture TitleSomething Old, Something New...
Duration1 hour
FormatLecture only with presentation
StandardAll Audiences
Prerequisitesnone
Outline...

For finance professionals who are making the transition or have recently made the transition to 2013 or 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, it has been updated to cover the latest generation of changes.

Questions a CFO Should Ask
Lecture #06
Lecture TitleQuestions a CFO Should Ask
Duration1 hour
FormatLecture only with presentation
StandardAll Audiences
Prerequisitesnone
Outline...

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.

Formatting & Rounding to Simplify Reporting
Lecture #07
Lecture TitleFormatting & Rounding to Simplify Reporting
Duration1 hour
FormatInstructor-led hands-on workshop
StandardAll Audiences
Prerequisitesnone
Outline...

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.

Advanced Analytic Formulas
Lecture #08
Lecture TitleAdvanced Analytic Formulas
Duration1 hour
FormatInstructor-led hands-on workshop
StandardAdvanced
PrerequisitesLecture 02 or 03 or 04 or FAC
Outline...

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 03/04 Streamlining Reporting Performance in Excel and 02 Function and Formula Fundamentals or prior attendance at the Financial Analysis Certificate is recommended.

Charting Workshop
Lecture #09
Lecture TitleCharting Workshop
Duration1 day
FormatInstructor-led hands-on workshop
StandardIntermediate / Advanced
Prerequisitesnone
Outline...

A unique and practical one-day hands-on master class designed to improve the reporting and presentation of key performance & financial information in reports, including introduction to dashboards. It is a cut-down version of the Charting & Dashboarding Master Class to make it suitable for inclusion in the Key Topic Lecture Series. It is also possible to have the full two-day Charting & Dashboarding Master Class included in a Key Topic course if desired.

It appeals to those in finance or management who need to increase the effectiveness and accuracy of the reports they generate and reduce the amount of effort required to generate them.

This course delivers: practical guidance on the layout and structuring of data to facilitate & vastly simplify charting; methods to save on chart building time & to allow for re-use of regularly created charts; techniques that allow for the automatic changing of chart settings without resorting to VBA; techniques to facilitate the re-use of charts with minimal editing, and creating templates; a thorough grounding in the features of the Excel charting interface; techniques to design and construct reporting dashboards, using the chart types presented; a wealth of practical information on the design and creation of advanced chart types. It delivers measurable improvements in the accuracy and productivity of attendees.

Implementing Complex Scenarios in Cash Flows
Lecture #10
Lecture TitleImplementing Complex Scenarios in Cash Flows
Duration1 hour
FormatInstructor-led hands-on workshop
StandardAdvanced
Prerequisites02 or 03/04 or FAC
Outline...

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.

Building More Effective Models with Less Complexity
Lecture #11
Lecture TitleBuilding More Effective Models with Less Complexity
Duration1 day
FormatInstructor-led hands-on workshop
StandardAdvanced
Prerequisitesnone
Outline...

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.

Formula Auditing & Error Tracing & Handling
Lecture #12
Lecture TitleFormula Auditing & Error Tracing & Handling
Duration½ day
FormatInstructor-led hands-on workshop
StandardIntermediate
Prerequisitesnone
Outline...

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.

Date Sensitive Forecasting & Reporting
Lecture #13
Lecture TitleDate Sensitive Forecasting & Reporting
Duration½ day
FormatInstructor-led hands-on workshop
StandardAdvanced
Prerequisitesnone
Outline...

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.

Making Practical Use of the Macro Recorder
Lecture #14
Lecture TitleMaking Practical Use of the Macro Recorder
Duration½ day
FormatInstructor-led hands-on workshop
StandardIntermediate
Prerequisitesnone
Outline...

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.

Understanding & Managing Spreadsheet Risk
Lecture #15
Lecture TitleUnderstanding & Managing Spreadsheet Risk
Duration½ day
FormatLecture only with presentation
StandardAll Audiences
Prerequisitesnone
Outline...

This session 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.

Building Workbooks with Style
Lecture #16
Lecture TitleBuilding Workbooks with Style
Duration½ day
FormatInstructor-led hands-on workshop
StandardIntermediate
Prerequisitesnone
Outline...

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.

PivotTable Fundamentals
Lecture #17
Lecture TitlePivotTable Fundamentals
Duration½ day
FormatInstructor-led hands-on workshop
StandardIntermediate
Prerequisitesnone
Outline...

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.

PivotTable Reporting
Lecture #18
Lecture TitlePivotTable Reporting
Duration½ day
FormatInstructor-led hands-on workshop
StandardAdvanced
Prerequisites17 or FAC
Outline...

Building on Module 17 - 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.

New Features Put to Work
Lecture #19
Lecture TitleNew Features Put to Work
Duration1 hour
FormatLecture only with presentation
StandardAll Audiences
Prerequisitesnone
Outline...

This lecture reviews new tools and features added in Excel 2007, 2010 & 2013, showing how they can be quickly and effectively deployed to solve analytical and reporting tasks. It covers key new formulas and how they can significantly reduce complexity in common modelling and analytical tasks.

Building a Dynamic Chart
Lecture #20
Lecture TitleBuilding a Dynamic Chart
Duration1 hour
FormatInstructor-led hands-on workshop
StandardAdvanced
Prerequisites03 or 04
Outline...

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.

Dashboarding in Excel, a Practical Example
Lecture #21
Lecture TitleDashboarding in Excel, a Practical Example
Duration½ day
FormatInstructor-led hands-on workshop
StandardIntermediate / Advanced
Prerequisites08 or 20 or FAC
Outline...

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.

Dashboarding in Excel, a Practical Example (Shorter Version)
Lecture #22
Lecture TitleDashboarding in Excel, a Practical Example (Shorter Version)
Duration½ day
FormatInstructor-led hands-on workshop
StandardIntermediate / Advanced
Prerequisites08 or 20 or FAC
Outline...

Module 22 is a shortened version of Module 21 - Dashboarding in Excel, a Practical Example, 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 dashboard design & implementation & manages to effectively communicate & demonstrate the key elements of the lesson.

Understanding Spreadsheet Risks and Implementing Internal Strategies to Mitigate
Lecture #23
Lecture TitleUnderstanding Spreadsheet Risks and Implementing Internal Strategies to Mitigate
Duration½ day
FormatInstructor-led hands-on workshop
StandardIntermediate / Advanced
Prerequisites08 or 20 or FAC
Outline...

This session begins with a lecture based on module 15 - 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.

Introduction to Excel's BI Tools
Lecture #24
Lecture TitleIntroduction to Excel BI Tools
Duration½ day
FormatInstructor-led hands-on workshop
StandardIntermediate / Advanced
Prerequisitesnone
Outline...

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

The features demonstrated in this session are only available from Excel 2013 onwards, and so this session requires Excel 2013, Excel 2016 or Excel 365. Excel 2016 is recommended.