Are your models in excel Hell

Almost all companies do modelling of some sort in many parts of the business. Sales would probably model their Sales Forecasts and Budgets, Production their buying and stock needs, Services their manpower needs and such like, with the financial people modelling their Budgets, Forecasts and cash-flow needs. In most cases these models are done in Excel and are occasionally even linked, which is where some of the Hell part comes into the equation.

These models are never Version controlled, are single-user and each User is mainly unaware of the special needs and requirements of the other Users and their models. The end result is that the links between models are extremely hard to maintain and even harder to ascertain if they are correct. Most people are using these models as their own personal data warehouse and they are seldom linked back to accurate source data and, when they are, they result in complex Excel formulas to make some sense of them, even with the new Excel pivot functions.

The end result is that most Users tend to do their own model and rely on manual input into their model of, say, sales figures, material costs, stock levels required and so on. As a result they are completely unaware of changes other users may make to their source data and it is not unusual for arguments to ensue as to who sent an email to whom informing them of changed data. This can be very awkward for all concerned if these arguments take place in, say, a Management meeting to discuss the latest forecast. It is also often the cause of some very destructive internal politics and has often resulted in a high personnel turnover rate.

We have a very unique combination of systems that overcome 100% of all these issues. Imagine having a custom designed system that all runs from Internet Explorer (or similar) with a fully functional Workflow system whereby each step of the process is able to be monitored, approved (or rejected and sent back the User for revision, together with notes as to why) and then open the next step on the process based on these actions.

The source data comes from live real-time data from your system and our systems have the unique feature of full write-back capability by all approved users with internal programming features to enable the system to be modelled based on User-entered drivers. All this data is fully integrated and is all in real-time with results able to be monitored at any point and with the ability to easily add multiple versions for comparison purposes. The end reporting can then be seen via Internet Explorer or in a custom designed Excel report which can link your actual data to your model data, all in real time, for final reporting at all levels.

In our models we would normally start with extracts from General Ledger, Sales, Purchases, Debtors and Creditors so as to get accurate historical data to base the model on. Using user-entered drivers the Sales forecasts can be done at any level, down to Customer/Product if appropriate for your industry, with user driven factors such as expected growth, expected product price increases and so on. User entered drivers would then also control your COGS at any required level of Product or Product Group. This would then feed into the Purchasing module which determines purchases required to meet demand based on user drivers for Weeks Stock required etc.

These figures would then flow through to the financial model giving your trading profit, inclusive of any other factors you may need to include. Expenses, likewise, can be set at any level required with separate models to cover items like Payroll, Advertising and such like, all flowing through and also split with automated internal allocations and/or recoveries if required. Some factors like Superannuation can simply be modelled in at set user-driven percentages to automatically calculate.

All of these trading results have an impact on the Balance sheet and these can be catered for, giving Debtors, Creditors, Stock and any others required. Debtors and Creditors can be linked to individual customer payment terms or to a general driver of expected outstanding days, as can things like payments for Payroll Tax, Superannuation, GST and such like. The end result is a fully balanced Budget or Forecast including a detailed, and balanced, cash flow. There can be multiple versions of this maintained in the system and comparison between, say, Version 1 and Version 2 can be done with ease. Very few companies budget down to Balance Sheet level and the end result is an unbalanced budget which can lead to some shocks in the future.

Past experience has shown that this can cut the budgeting process down from Months to Weeks and, as a result, it is easy to introduce an ongoing forecasting system to continually provide accurate expected results.

As this system is linked to your live data, it is then very easy to simply integrate this data with Actuals to give up to date Management reporting linked to Budgets or Forecasts (as selected by the User) and also giving expected Annual results being a combination of past Actuals and future Budget/Forecast. This can be available in real-time either through Internet Explorer, or live data in Excel, with the ability to select any period in live mode and almost instantly see the results for that period. Likewise, Actual sales data, purchasing data and stock data can be compared to Budget/Forecast at the same level as used in the modelling process, either in an Excel report or through Internet Explorer. Authorised Users also have the ability to build manual Hierarchies in a few seconds which can be used in reporting and users can also be trained to easily do their own basic reports with ease. Due to the unique write-back abilities, budgets can also be set up for items not catered for in your ERP system. Integration within our systems also allow items such as Payroll from a separate system to be fully integrated with, say, sales data.

All of the above is what many companies are currently doing but it is taking a lot of time and manual effort and the end results are often dubious, due to multiple changes, and users are seldom able to report against actual data, except with a lot more time and effort, assuming they know which was the latest approved version of their model.

If this scenario represents a solution to your modelling hell please feel free to contact me via our website (BISolutions.com.au) and also do your colleagues a favour by clicking the Like button so that they have the opportunity to understand that there are solutions out there to their nightmare issues.

SUBSCRIBE

Your Name (required)

Your Email (required)