Never again! The pain of budgeting and forecasting in spreadsheets

Many of the customer solutions that we are currently building at Fidenda all started in the same way. What was once a clever Microsoft Excel based solution had “hit a wall”. And because of how a humble spreadsheet had become an integral and vital part of a critical process, senior management was starting to get concerned at the level of risk. One customer was about to invest in a new server with lots of RAM as a tactical solution to solve a performance problem whilst the other had created a temporary position to coordinate and manage a time consuming, largely manual Excel based process.

The safest road to hell is the gradual one. CS Lewis

Let’s just think about why Excel based solutions (or any other spreadsheet for that matter) are so pervasive within most organisations. And just to be clear, I’m talking about complex interconnected solutions and processes that rely on Excel such as annual budgeting, cost allocation, sales forecasting etc.

  • It’s already on every desktop so there are no additional IT costs and no installation is needed. And so it follows that Excel documents are easy to share because recipients can open them too.

  • It’s very easy to use and no / minimal training is required

  • It’s simple to bring in static data from a single or many sources, and then join together using the trusty VLOOKUP function

  • It can do pretty much anything. Repetitive tasks can be automated with macros. Data entered can be validated. There is a function for almost everything - 477 or so for Excel 2016, not to mention the impressive range of add-ins available

  • Pivot tables are often the go-to analytical solution for summarising and slicing line item data

  • The output tables and charts can easily be used in other documents such as reporting packs and presentations

  • Business teams can develop applications cheaply and quickly, without IT involvement

So Excel basically offers a perfect mix of unrestricted access, the ability to store data and manipulate data, a powerful analytical engine and a great presentation layer.

So what’s not to like?

Powerful and flexible as Excel is, you may be experiencing some or all of the following problems:

  • Lack of process visibility and control:

  • when multiple users are trying to update a single sheet. Who is currently editing the master? Has Bob made his updates?

  • when multiple users are completing multiple versions of the same sheet, as part of a budgeting process for example. Who has done their updates? Why has Bob used the old template? Bob you haven’t followed the instructions and you’ve entered your forecast in GBP not USD!

  • Process administration can be very time consuming. For example, what if there is a mistake in the template and you need to distribute a corrected version to many users? What about the impact on anyone who has already completed the version containing the mistake? And the consolidation of multiple spreadsheets into a single master version can take days out of a monthly process.

  • Spreadsheets can get very large and slow to work with. Which then makes them harder to share. Especially when there are multiple tabs, complex formulas (especially VLOOKUPs!) and millions of rows of data.

  • Spreadsheet security tends to be quite binary. Either you have access to the whole sheet, or you have no access. Unprotected spreadsheets are a big security risk with sensitive financial information. But securing them in a multi-user environment is time consuming and complex.

  • It’s really easy to make mistakes in spreadsheets. We’ve all done it. An error in a formula, a sum() not covering the entire range of values. And spreadsheets are rarely subjected to any independent testing so errors tend to persist or get covered up.

  • Getting data in and out of spreadsheets automatically can be a pain. Yes, it is possible to connect spreadsheets to other sources such as a database or a financials system, but it rarely happens. Flatfile upload / download or copy / paste remain the most common approaches in my experience.

  • Only the creator really understands what’s going on. Inheriting someone else’s spreadsheet is really rather unpleasant. There is normally no documentation. IT don’t know about it so there isn’t any support. So you spend time trying to understand and reverse engineer it. And then you spot things you don’t like or wouldn’t have done that way. Wouldn’t it just be easier to start again and rebuild it? There goes another week….

I’m guessing that you might have experienced some of these challenges but that you’re like most organisations and the flexibility and freedom of Excel outweighs the challenges. But what if I could show you that there is another way, that has the best of both worlds?

At Fidenda, we’ve worked with many clients to systemise their processes and solutions built on spreadsheets. We typically work with Anaplan because of how quickly we can provide a solution. We work with our customers to understand what they are trying to achieve, who is involved in the process, what the current spreadsheet solution does, where the data comes from etc.

We then build Anaplan models to replace the core logic of the spreadsheet. Workflows control the process. Master data (or Lists in Anaplan terminology) and actuals data can be loaded directly from source systems. The security model determines who can see what.

Some of the advantages of an Anaplan based solution are:

  • Central visibility of the process. This improves efficiency and follow-ups and chasers can be very targeted

  • Multiple users can edit and access the same data at the same time

  • Your data is stored securely and access to it is controlled and auditable. Both in terms of access but also in terms of changes. Yes that’s right. You can see a history of user changes at the cell level.

  • Anaplan is a highly performant, in-memory platform. We recently took an Excel workbook that was taking in excess of 4 hours to recalculate on server based hardware and modelled it in Anaplan. With the same data, we achieved almost instantaneous recalculation times. Anaplan didn’t even break a sweat.

  • Creating snapshots and new versions of financial plans is very easy and fast

  • Users enter data directly into the central model so no more copying, pasting or uploading

  • Anaplan models are typically built in an integrated way so that they all talk to each other and share data. Want to allocate overheads by cost centre headcount? Easy – just reference the headcount data from the Workforce Planning solution

  • Centralisation of key components such as foreign exchange rates and master data so that everybody is working from the same values

We agree that not everything belongs in a system. We love a good spreadsheet at Fidenda too. Our job is to help you move the slow, complicated, multi-step, multi-user spreadsheets onto Anaplan.

We also love a challenge. So if you have hit the limits of Excel and want us to show you what it could look like in Anaplan, then please get in touch. As yet, we haven’t found anything we can’t make better, faster or more secure.