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.

Retired Lectures

The following listing shows the retired lectures formerly offered as part of this series, which have been replaced, or retired as the content is no longer applicable to modern Excel..

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.

T03 - Something Old, Something New...

Withdrawn - no longer available

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.