3 Ways to Improve Your Business Planning & Forecasting with Excel

When budgeting time rolls around again, what do you use as your preferred choice for collecting and consolidating the submissions from all stakeholders? You could turn to that cumbersome enterprise-grade FP&A system that sits in the background, unwieldy, difficult to use, and loved by no one or you could just simply do the whole thing in Excel.

Everyone knows Excel, everyone can use it, and it should get the job done in no-time, right?

Well actually, no. And if you’re reading this having just completed your latest round of plans, budgets, and forecasts in an Excel-based system, you’ll know just how long this took, how many errors you had to track-down and eliminate, and just how complex the whole process was to manage.

In this article, we’re going to take a look at 3 ways you can improve your entire planning, budgeting, and forecasting process directly from an existing Excel-based system and then automate that process in an easy-to-use, familiar, user interface that will provide you with a complete, finance-led Corporate Performance Management solution. Sounds too good to be true? Let’s lift the covers and look a little closer.

Excel Based Systems

Excel is fundamentally a personal productivity tool. It was designed for this purpose and does it so well that, in the Finance Department and beyond, it is used to plan and analyse just about every business process that you can imagine. This ultimate flexibility is its greatest asset, but it can also mean that it is often used in ways for which it was never intended.

Comprehensive, single user business planning applications can without doubt be built in Excel. Issues arise however, when such an application is shared for company-wide plans, budgets, and forecasts. In this scenario, Excel very quickly hits its limitations. Version control and data integrity issues at this stage inevitably require complex and time-consuming manual aggregations and data-cleansing routines, leading to greater costs, delays and increased demands on everyone’s time which could be better used elsewhere.

There’s no doubt though, that the familiarity and ease of use of Excel entice many companies along this path.

Custom built specialist FP&A Applications

There’s also no doubt that this option is an improvement over a pure Excel based solution. Purpose-built applications, available from all the major software vendors, work from a centralised database of cleansed data and deliver one single version of the truth across all company departments, integrating multiple business systems. They provide a genuinely collaborative and easily managed environment from which to manage, request, and deliver reports, budgets, and business forecasts securely and without recourse to an ever-growing email chain - with all the challenges that in itself incurs.

However, as good as these applications appear on paper, they can have drawbacks. Choose the wrong product for your requirements and you can face long development times, increasing associated costs. Some can also be quite rigid in that simply adding another reporting entity – eg a new business unit – setting up new business structures or changing the calculation logic means you’ll need to call-in a specialist or a team of developers to implement. More to the point can be training your users on the new system. Some applications are notoriously cumbersome to use which leads to low user adoption. In addition they often depend on centralised calculations and business rules which are not relevant or suitable for all business units.

In situations like these, many companies simply end-up reverting back to Excel for many tasks, which although understandable, rather defeats the objective. So, is there a solution that truly offers the best of both worlds? Thankfully, yes.

Vena Solutions

As outlined above, many companies will have developed their own in-house Excel-based planning, budgeting, and forecasting or FP&A systems. Although these have the previously mentioned drawbacks, they usually are the result of lengthy development, are familiar with end-users, and have taken a lot of investment by many stakeholders. This in-house development is not going to be thrown-out easily – even if it does require high maintenance and long month-ends and budgeting cycles. So, a solution which has native access to Excel and can easily transform your existing Excel-based system into a complete CPM solution has to be worth considering.

Vena Solutions provides a fully integrated business planning system which Excel users are instantly familiar with; it can also be easily integrated with virtually all of your existing company data platforms and systems. If you already maintain an Excel-based Financial Planning & Analysis system it can be easily re-created within Vena so that you can transform it to a fully centralised, secure, automated system which saves costly monthly spreadsheet maintenance and error checking routines while delivering improved productivity in 3 ways.

1. Centralised Database Mapping

Excel can be tough to manage at scale. When more than one person needs to collaborate on a spreadsheet, maintaining the integrity of the data used and even the calculated fields and formulas can very quickly go awry. The more people that share the same spreadsheet, the more likely the opportunity for errors to creep-in. On top of that, there’s the whole issue of emailing the latest version back and forth between all stakeholders. Version control then almost instantly becomes a very big issue.

Let’s look at an example. Following your latest round of budgeting approvals, a major deal comes in which means that you need to re-work all of your hard work to account for increased income, associated operating costs and more. Your previously finalised budget plans are now left in tatters. From Excel, you now need to:

• Go back to all departments budget sheets and manually re-key in all the changes to individual line-items (more opportunity for errors to creep-in) affected by the revised income and costs.
  
  
• Return to your balance sheets, profit & loss statements, and cash flow statements and update them all accordingly – an arduous task to say the least!
  
  
• Check and re-check all of the updates across the entire spreadsheet environment and re-confirm all of the calculated fields still work correctly.
  
  
• Go through the whole emailing and approval process once again and secure final approval.

It’s a familiar scenario which can take many days or even weeks to complete. And it goes without saying that the whole error-checking and email based version control can throw-up many issues along the way to trip the whole process up.

To solve these problems, Vena Solutions provides a centralised database which maps natively to your existing Excel spreadsheets. This now means that whenever a spreadsheet is altered, the changes automatically apply across your entire dataset for all stakeholders, solving data integrity and version control issues at a stroke. Vena becomes the single point of the truth while still enabling users to work straight from Excel.

Returning to our original example, using Vena, the process would be:

• Adjust the line items for your department using Excel.
  
  
• Balance sheets, profit & loss statements, cash flow statements, budgets, plans, and forecasts all update automatically based on your new inputs.
  
  
• Stakeholders and administrators are instantly notified of the changes, which they can then review and approve following a full audit trail function which enables them to see who changed what, together with any comments highlighting why the changes were made.

The additional capabilities enabled by the Vena centralised database represent a significant time and cost-saving over the previous time consuming processes required by a pure spreadsheet based system.

2. Integrates All Business Systems

Context can be of vital importance when analysing financial and operating performance. Numbers without context can raise more questions than they provide answers. You need to go further.

It’s often the case that, when analysing your figures, you need to see the underlying data to understand how the top-level information was arrived at. Without an integrated system, the only way to do this is typically to return to Excel once again and create yet another spreadsheet which is based on the data that you may be able to obtain from your GL, ERP, CRM, or HR systems (to name but a few).

Trying to integrate all of this additional information into a spreadsheet often results in huge, cumbersome spreadsheets and multiple workbooks containing many individually-tabbed worksheets. Linking all this together in a way which provides easy analysis and context can be achieved, but again, this is time-consuming and very prone to error. And of course, if an author of the spreadsheet leaves the company, someone has to try and recreate all that work without having the background knowledge of how and why it was created!

Vena provides an ever-growing list of connectors which enable you to interface with and integrate all of your source systems. This means that you don’t have to create additional analytical spreadsheets in order to get the detail. The data in the connected system is fully accessible from your Vena user interface (your familiar spreadsheet). Simply click on the number in Excel you’re interested in to drill-down to greater detail and obtain the context that you need for better and more productive analysis.

3. Templated Excel Solutions

How do we create a new spreadsheet for a specific business purpose?

Judging by the sheer number of searches performed on Google for spreadsheet templates for any number of business-related needs (try it out – you’ll be surprised how many are available!) a significant percentage of people start with a pre-created template and adapt it.

This approach has the appeal of being perceived as saving time, but in reality, this approach almost always means that you end up making significant changes to the template used. And once again, this leads to calculated field errors, formula changes and myriad other issues.

Think about the number of people you have in your company and how many might take this approach to producing very similar spreadsheets for the same purpose. The intended objective might be the same but the spreadsheets can be very different. A centralised, but flexible, approach is required.

Vena not only provides a wide range of highly useable template solutions, but you can adapt and modify these for specific purposes and make them available across your company infrastructure to ensure that everyone uses the same spreadsheet for the same purpose. And of course, if you wish to use existing spreadsheets, or create a new spreadsheet entirely from scratch, that’s fine too – just add them to your inventory for everyone to use.

Vena saves time and reduces errors by providing a controlled business environment using a system with which everyone is familiar and knows how to use without formal training.

Conclusion

Many purpose-built FP&A systems have attempted to do-away with the need to use spreadsheets over the years, but nearly all have realised the need to provide an Excel add-in for their users. This has produced sometimes ‘clunky’ solutions that are not always compatible with why people need to use Excel in the first place.

Vena takes a modern standpoint and embraces Excel to the point where it provides the user interface for the whole application. In taking this approach, Vena have created a system which enables faster implementations, wider user adoption, and greater overall productivity and flexibility than almost all other systems that we have encountered. Excel is here to stay, and Vena embraces that fact to great effect.

With a centralised database, automated aggregations, ultimate flexibility, and drill-through straight to analysis of the detail right from Excel, there’s a lot to recommend it. Why not join us for our webinar on Thursday 23rd September and see Vena in action.