Rolling Forecasts with Excel

Pre-requisites

The course requires at least an Intermediate knowledge of Excel. It is not suitable for inexperienced Excel users and is primarily aimed at Finance staff.

Delivery

The format will encompass both on screen work by the tutor and exercises in building parts of the model by the participants with two way interactivity throughout.

Who Will Benefit?

Anyone who has to prepare and or evaluate budgets and forecasts
Anyone who has need for rapid hands-on planning in their organisation or on behalf of clients.

Summary outline

Features of the day will include:
Design of fully integrated model (self balancing etc.)
High level of automation of model based on a case study approach.
Macro usage for basic assumption setting (e.g. dates covered), automation and report generation.
Hierarchical reporting structure from monthly detailed to quarterly top level reporting.
Reconciliation between the need for the model to transcend the financial year while simultaneously respecting year end cut-off (functions and macro).
Loading historical data (opening trial balance) automatically and accurately
Model accuracy verification technique.
Formulas to distinguish history from forecast
Graphical outputs and key reports including use of conditional formatting and other techniques in KPI's etc.
Sensitivity Analysis within the model as an inherent part of same.

Short-term cashflow model

The second and much shorter part of the day will entail the creation of a 13 week rolling cash flow forecast.
This will not be an integrated model, but will operate as standalone cash flow forecast at the tactical level i.e. at the creditor, debtor, sales order, individual payment etc.
The purpose will be to gather below trial balance level information (e.g. aged debtors) and incorporate these, using macros and other techniques into feeder documents to produce the 13 week rolling cash flow.