Excel Modelling Approach Nick Ogden ACA Dr Josef Baker 06/09/2018
Nick Ogden: Background to the Problem The first problem I wanted to solve was that of building and maintaining the integrity of Forecasts and Budgets Building the forecast initially is never too much of a problem, but when a handful of colleagues at HQ all make their own changes then there is lengthy work in collating their versions back together again to get to a Single Version of the Truth. There never seems to be a final version and even then we do want to have multiple scenarios which should be mirrors of the base forecast but with just a few drivers and assumptions changed. When we distribute the Forecasts to the teams in our different entities around the world with their different currencies and skills, we often have to remove hard coded changes to the templates and can never be sure we have the right data and logic sent back The technical problems are version control and data integrity which means we are constantly concerned about the risk of unreliable numbers – the management problem is it takes too much of our valuable time!
Getting Some Help I had worked with Josef Baker for some years to automate our spreadsheet based Group Consolidation system I wanted to get his Computer Science insight into how we could fix these problems at a fundamental level rather than just by fiddling with read only templates – an approach which people always seem to defeat. We came to the conclusion that there was a need to restructure both the spreadsheets and the processes we have been using. Josef had been working with an engineering company to generate cost forecasts on the fly i.e. put some variable drivers into one worksheet and the whole forecast is printed into another output worksheet straight away. We thought we would formalise this by having a worksheet for all of our parameters (drivers) stored in a tidy way so we could have different scenarios printed on demand. Eventually we realised that the structure and layout of the whole forecast could all be stored as parameters and that we could generate the formulae, but there was one much bigger problem left.
The Bigger Problem Having a tidy mechanism to generate forecasts doesn’t solve the problem of distributing them to remote entities, collating the returned data and ensuring data integrity – nor the lengthy turnaround time The work we have done with Josef’s team over the last 3 years was to automate our manual Group Consolidation spreadsheet processes so that multiple remote users could work on a single version of the accounts at the same time. This is all based on a Cloud Database I will let Josef explain but it means we can get rid of links between spreadsheet files and other problems as well as getting audit histories of all changes to cells etc. So the next step is to put this modelling tool on top of the Cloud Database so that we can control the versions of forecasts and budgets distributed to the teams and ensure they cannot change the structure of the models we have carefully built! I will let Josef show you the approach we have taken so far – the only constraint from my team was that it had to be plain Excel with nothing complicated involved that needed learning
Josef Baker: Concepts in a Nutshell: Structure
Parameters
Business Rules
Scenarios
Connecting Excel to a Cloud Database
Summary & Next Steps for Those Interested Synapse would like to engage interested parties who would be willing to provide feedback on what features are needed to make this into a valuable product – please feel free to chat to Josef The summary of our goals with this development is: - To make it faster and easier to build and maintain models of any type - To reduce the effort and risk involved in the forecasting and budgeting processes - By integrating with our Consolidated Financials module, to provide the forecast data that drive the business rules to produce the full Board Pack including P&L, Balance Sheet, Cashflows and captured KPI data (currently this is semi manual)
Contact Synapse Josef Baker Cloud CFO Product Manager josefbaker@synapseinformation.com
Additional Slides describing the step by step assembly of a Model 06/09/2018
Logging In
Getting Started
Configure Global Parameters
Define Structure
Define Structure
Define Business Rules
Define Business Rules
Define Parameters
Generate Draft Forecast
Update Formatting
Save Formatting
Add Additional Scenarios
Generate New Forecasts
Create Different Scenarios
Securely Audited
Contact Synapse Josef Baker Cloud CFO Product Manager josefbaker@synapseinformation.com
Recommend
More recommend
