Rolling Forecasts with Excel 2010

Request more details:

Description:
This course is designed to equip participants with the concepts and tools from which a model can be prepared which can be changed easily and automatically as business change requirements dictate. Changing external economic conditions can be reflected in KPI’s easily and quickly and this process iterated continuously (e.g. every quarter). The ‘rolling’ aspect implies that the model produced during the day (an 18 month rolling forecast) is quickly adaptable to changing business circumstances and can be run as often as required.
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 organsiation 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.