What is CloudModeller?
99% standard looking Excel Workbooks available on your desktop. The 1% difference in first appearance is …
A simple login toolbar appears
After logging in, CloudModeller connects securely to our Cloud Database
Now a list of Models – forecasts, budgets … can be seen and loaded from the database into your local Excel spreadsheet
The user can build their forecasts as they normally do in Excel but some additional features are now available to make life easier – see here
When any one user synchronises their changes, this pushes them up to our Cloud Database
Every user will now see one Single Version of the Truth.
What is CloudModeller used for?
Different professionals have specific names for their types of model, e.g. the CFO may build Forecasts and Budgets. HR may maintain a staff register with associated skillsets. Sales people will have a pipeline of opportunities graded by likelihood and close date. Regulators build capital adequacy models with which to benchmark Banks. Project Managers abandon inflexible software to manage large programmes with spreadsheet models.
Large organisations may call these different models EUC – end user computing. Whatever they are called, professionals build and maintain them because they have much more control over them than they would with rigid software applications.
Let’s look at the processes CloudModeller supports during the forecast model cycle:
Building and maintaining Forecasts
- Our CloudModeller Wizard makes it much easier to create the initial forecast
- Now the entire team can work in parallel on this with no data conflicts
- This often involves manually tweaking parameters to give different scenarios
- These multiple scenario versions will be named and stored in our Cloud Database so that they can be cleanly retrieved and worked on over time
Constantly updating rolling Forecasts
- Operations teams are often in different locations and may need to make simple revisions of their view of their forecasts as actuals come in
- Because each member of the operations team has their own CloudModeller login they can update their local forecast numbers with their revised view of the future
- HQ will then always see one Single Version of the Truth across all of the distributed operations team’s forecasts – no more collating, copy and pasting
- (Show screenshot)
Generating Group Consolidated Financial Statements
- Our Cloud CFO product reads in Trial Balance and ledger data from multiple Accounts Production software systems to produce the Primary Financial Statements – P&L, Balance Sheet and Cashflows
- (Show screenshot)
- The CloudModeller module can load different Forecast and Budget scenarios into Cloud CFO which can then generate forward looking consolidated forecast P&L, Balance Sheets and Cashflows
- (Show screenshot)
System administration: audit, security, user and version management
- CloudModeller supports all of the administration features that any mature Accounts Production system offers – Excel now becomes a proper enterprise application
- (Show screenshot)
- Many of our customers used to have huge Excel forecast models spanning dozens of linked workbooks totalling hundreds of megabytes.
- Our approach with CloudModeller can simplify this hugely – see here
- Key to this is the notion of Business Rules – these can be embedded by name in the Excel interface but will do their work in our Cloud Database
- The benefit of this approach is that these cloud systems can scale to very high performance and data volumes very inexpensively – huge creaking spreadsheets are now eliminated
What Excel problems does CloudModeller solve?
- Excel is an excellent single user desktop tool but …
- Multiple users cannot work on the same workbook in parallel so …
- Multiple versions on everyone’s desktops proliferate so …
- Much work is spent in copying and pasting everyone’s copies together
- Excel does not scale to large volumes and multiple versions
- Data spread over multiple worksheets and workbooks leads to broken links …
- Storing different workbooks in different file locations means problems in trying to recreate the right version.
- Large complex models become very hard for anyone other than their original author to comprehend – even then it can be difficult …
- There is a long list of fundamental problems that Excel cannot overcome.
- However, for over 40 years, commercial databases have solved all of these problems.
- The database acronym RASSIM encapsulates these proven capabilities – Reliability, Auditability, Security, Scalability, Integrity and Manageability.
- CloudModeller leverages all RASSIM capabilities.
Why connect Excel to a database?
Let’s look at some of the key RASSIM details that have immediate benefits:
- For low level details on the how please read our technical FAQ.
- In practical terms, having multiple people working on the same model in parallel means less elapsed time to get the job done.
- It also reduces the effort time (man days) by elimination of all of the cutting and pasting normally needed to collate data back together again.
Audit and Security
- (Show screenshot)
- Every person using CloudModeller must login to the system first – just as you do with MS Outlook.
- This means we have a complete track of who logged in when and what they did, at a cell by cell level of granularity
- It also means we can control what they can see and what they can modify
- (Show screenshot)
- Commercial databases do not need ‘file save as’ functions – this disappeared 40 years ago.
- CloudModeller users are therefore able to just load multiple different versions and scenarios of their models from the Synapse database with no worry about where the data lives
- All data is of course backed up, secure and can be expanded to huge volumes accessible from anywhere in the world.
- (Show screenshot)
- Databases have support for a far richer set of validation rules to maintain integrity e.g. keys that enforce uniqueness, logic that says any employee must have a department they are assigned to.
Beyond database support, what new features does CloudModeller have that Excel does not?
- Limitations of Excel
- Excel Formulae are simple statements of logic that are very powerful when linked together.
- If we create a forecast with 5 years of monthly financial projections it will have 60 columns in a worksheet.
- If the forecast has 200 rows we will end up with 12,000 cells with each one generally containing a copy and paste replicated formula.
- Cell References and ‘Links’
- Many of these Formulae are of the form (+c2 * d5) etc and so if a row or column is deleted the formula may be broken.
- This problem is exacerbated when formulae are linked to another worksheet and especially so if linked to another workbook.
- Introducing CloudModeller Business Rules
- These problems do not exist in databases – they use Business Rules.
- Database Business rules use ‘names of things’ e.g. ‘sales = ‘cost per unit’ * ‘number of units sold’ – there are no breakable cell references.
- Database Business rules are written in software languages – e.g. SQL – which are far more powerful and faster than Excel Formulae
- Most importantly, one Business rule could write out or print all of the 12000 cells we describe above.
- CloudModeller supports the maintenance of a small library of business rules that will replace hundreds of thousands of fragile and hard to maintain formulae.
Building a CloudModeller forecastStep by step screen walk through
Let us introduce some key concepts in CloudModeller which enhance how we can build and maintain Excel Models.
Note that the Synapse team are database veterans, which means that many of the concepts that follow are well known, tried and tested in millions of commercial database systems throughout the world – we have simply grafted the best of these on to Excel to remove its limitations.
If we look at the simple forecast below, we can see that the first row is highlighted from A1:M1.
By default, CloudModeller looks at this structure and declares that A1:M1 is a table with the name Period and 12 values which are of the datatype ‘date’.
40 years ago when Dr Ted Codd invented relational databases he mandated ‘locational transparency’ i.e. hard coded file locations such as c:\mydesktop\sample.xls:A1:m1 were abolished and replaced with tables.
Tables store the data logically and hide the physical file system implementation.
Our ‘Period’ table is like an Excel Named Range but we can extend its power by giving it optional properties such as ‘must be unique, must not be null, must …’
Most importantly we synchronise this data to our Cloud Database so it becomes accessible to any member of the team without their having any need to know where the source spreadsheet might live.
Note that CloudModeller has also defined vertical tables so ‘Mar’ is a table of 5 data values which are £ currency amounts.
Business rules – enhanced versions of Formulae
Let’s look at the Period Table again. Essentially this just says replicate the value in B1 to the right-hand side for another 11 cells, incrementing the value one month at a time.
One of our customers with Wind Turbines makes Met Office projections of wind speed on a seasonal basis for 40 years into the future – this means a lot of formula replication and spreadsheet bloat.
In databases, Business Rules are statements of logic which are defined in one place but which act over millions of records/cells. In Excel, formulae are defined in one place and replicated over millions of records/cells. This is an intuitive way to build a model but problematic to maintain.
The simple Syn$Project Business Rule we see below ‘sits’ in one worksheet called Model Definition (it could be anywhere you choose) and says, in effect “write out the values in a table called Period, to a new worksheet called Model, start at the top left corner and Project the table name followed by 12 values Jan:Dec across the top”
We can do a similar thing for the secondarow i.e. Syn$Project(Model, Sales,A2,£100,000) and now that row with all of its values will be generated and written to the model sheet.
Now let’s look at CostOfSales which is simply 60% of Sales projected across all the periods.
We want to get rid of ‘C2’ and other fixed cell references for the reasons discussed previously.
Remembering that each of Jan, Feb … Dec are vertical tables in the same way that Sales … NetProfit are horizontal tables we can use this syntax:
Syn$Project(Model, CostOfSales, A2, Sales * 60%, Jan:Dec)
In fact, as we will see later, we can optimise this syntax because at Model setup time we will have told CloudModeller the name of the output worksheet, where to start the rows and columns, how many periods are involved so the syntax will look more like:
Syn$Project(CostOfSales, Sales * 60%)
This does leave one inelegant problem – the hard-coded statement of 60%. Many experienced Excel Model builders will already use the approach we define below:
Syn$Project(CostOfSales, Sales * CostOfSales%)
We use this approach internally in CloudModeller to store general parameters that describe one particular model, e.g. all of the following are tables with single values:
- NameofCreator = Claudio.M
- CreationDate = 1 Jan 2020
- Scenario# = 1
- PeriodTypes = Month
- StartLocation = A1
- OutputWorksheet = Model
Because all of this data (the text and number and date values in cells) and metadata (definitions of tables etc.) is synchronised to our Cloud Database we now have the opportunity for any model builder to access any model data or metadata from the models built by their peers anywhere in the world – they can ‘call’ their models from within the model they are building. For example, we know where the horizontal table ‘Sales’ lives and ditto for the vertical table ‘Jan’.
We can see from the previous screenshot that cell B2 = £100,000 and now we can reference that by this syntax Syn$Value(Sales, Jan) = £100,000.
Let’s say our model builder Claudio has created two scenarios – optimistic and pessimistic.
They are virtually identical except that the pessimistic value of Syn$Project(CostOfSales, Sales * 60%) delivers less profit than the optimistic Syn$Project(CostOfSales, Sales * 40%).
Rather than the normal Excel modelling approach of having two giant, near identical sets of worksheets to flip between, Cloud Model just stores the two different parameters. Whilst we could allow model builders to address (i.e. point to the value they want) the relevant parameter with a long-winded value reference, e.g.:
Syn$Project(ThisModel.ThisScenario(CostOfSales, Sales * CostOfSales)) …
This is not a way to win friends with ease of use! (note that we store the metadata etc. in our database this way which makes sense for advanced users wishing to write sophisticated business rules). Instead of this advanced approach we have a GUI Wizard that makes the process much more digestible – see here and see here
Screenshot – CloudModeller Wizard 1
Screenshot – CloudModeller Wizard 2
Summary of the differences in this approach
We abolish hard coded file and cell references and replace them with soft coded logical references with meaningful names.
These latter can be used across models e.g. Model1.Sales can reference Model2.Costs very easily which in turn reduces the number and size of duplicate models.
Although the Synapse software architecture supports (our biggest Banking customer has 1000 concurrent users) multiple users of the same logical spreadsheet very well, this partitioning approach for metadata etc. improves things even further.
For example, having a logical worksheet for just parameter values means that perhaps members of the operations team can tweak that for their own departments very easily and see the results. Having one tidy repository for Business Rules means that perhaps a more senior expert can maintain this and other staff are precluded by our role management permissions system from seeing or modifying these rules.
Manageability – Audit, Security, User access control etc.
This means that the Model team can add and delete users, give different team members different permissions to see and modify different models etc.
There is a full audit history for all events.