# 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 Title Function & Formula Fundamentals Duration ½ day Format Instructor-led hands-on workshop Standard Intermediate Prerequisites none 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 Title Fundamentals of Model Design & Development in Excel Duration ½ day Format Instructor-led hands-on workshop Standard Intermediate Prerequisites none 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 Title Streamlining Performance Reporting in Excel Duration ½ day Format Instructor-led hands-on workshop Standard Intermediate Prerequisites none 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 Title Streamlining Performance Reporting in Excel (Short Version) Duration 2 hours Format Instructor-led hands-on workshop Standard Intermediate Prerequisites none 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 Title Something Old, Something New... Duration 1 hour Format Lecture only with presentation Standard All Audiences Prerequisites none 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 Title Questions a CFO Should Ask Duration 1 hour Format Lecture only with presentation Standard All Audiences Prerequisites none 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 Title Formatting & Rounding to Simplify Reporting Duration 1 hour Format Instructor-led hands-on workshop Standard All Audiences Prerequisites none 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.

 Lecture # 08 Lecture Title Advanced Analytic Formulas Duration 1 hour Format Instructor-led hands-on workshop Standard Advanced Prerequisites Lecture 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 Title Charting Workshop Duration 1 day Format Instructor-led hands-on workshop Standard Intermediate / Advanced Prerequisites none 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 Title Implementing Complex Scenarios in Cash Flows Duration 1 hour Format Instructor-led hands-on workshop Standard Advanced Prerequisites 02 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 Title Building More Effective Models with Less Complexity Duration 1 day Format Instructor-led hands-on workshop Standard Advanced Prerequisites none 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 Title Formula Auditing & Error Tracing & Handling Duration ½ day Format Instructor-led hands-on workshop Standard Intermediate Prerequisites none 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 Title Date Sensitive Forecasting & Reporting Duration ½ day Format Instructor-led hands-on workshop Standard Advanced Prerequisites none 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 Title Making Practical Use of the Macro Recorder Duration ½ day Format Instructor-led hands-on workshop Standard Intermediate Prerequisites none 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 Title Understanding & Managing Spreadsheet Risk Duration ½ day Format Lecture only with presentation Standard All Audiences Prerequisites none 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 Title Building Workbooks with Style Duration ½ day Format Instructor-led hands-on workshop Standard Intermediate Prerequisites none 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 Title PivotTable Fundamentals Duration ½ day Format Instructor-led hands-on workshop Standard Intermediate Prerequisites none 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 Title PivotTable Reporting Duration ½ day Format Instructor-led hands-on workshop Standard Advanced Prerequisites 17 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 Title New Features Put to Work Duration 1 hour Format Lecture only with presentation Standard All Audiences Prerequisites none 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 Title Building a Dynamic Chart Duration 1 hour Format Instructor-led hands-on workshop Standard Advanced Prerequisites 03 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 Title Dashboarding in Excel, a Practical Example Duration ½ day Format Instructor-led hands-on workshop Standard Intermediate / Advanced Prerequisites 08 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 Title Dashboarding in Excel, a Practical Example (Shorter Version) Duration ½ day Format Instructor-led hands-on workshop Standard Intermediate / Advanced Prerequisites 08 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 Title Understanding Spreadsheet Risks and Implementing Internal Strategies to Mitigate Duration ½ day Format Instructor-led hands-on workshop Standard Intermediate / Advanced Prerequisites 08 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 Title Introduction to Excel BI Tools Duration ½ day Format Instructor-led hands-on workshop Standard Intermediate / Advanced Prerequisites none 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.