How to Use Excel for Budgeting with Effective Results
By Clinton Jones on Dec 2, 2013
In today’s business climate, Finance departments are expected to place an increasing amount of effort into the budgeting process. The increasing pressure to manage rolling budgets and preliminary results are indicated from our Winshuttle ACCA survey. Almost 90%, of more than 1500 respondents, continue to use Excel for the data gathering process for cyclical budgeting.
Accountants and business users like to use Excel because of the ease of use and the two dimensional aspects of working with spreadsheets. Report formatting is relatively easy and the most recent updates of Excel enable basic data analysis when the data is used in conjunction with pivot tables.
What Excel lacks of course is relationships in other dimensions and building conditional logic into cell results can become incredibly complex and go horribly wrong when used with Excel is stretched to limits beyond reasonableness.
Excel has no inherent intelligence or rules – everything has to be built by hand and maintained accordingly. Although the structure of sheets in Excel and the added capability of multi-sheet workbooks suggests an ability to be used in a similar fashion to database, it is not a database and the structure of the data does not align with the capabilities of databases. Furthermore although many businesses continue to rely heavily on Microsoft Office Access and Excel for analytics and secondary data manipulation and gathering, Microsoft Office is a sub-optimal reporting and data management environment. By their very nature, Excel workbooks and Access databases are relatively fragile technologies for large scale data gathering and collation – they are susceptible to corruption. Even if they manage to retain their integrity, they don’t usually form part of the landscape of true enterprise knowledge repositories. Given the passion that users have for using them and their lack of functionality-what are your options?
In this post I will highlight some critical measures that you can apply to your budgeting process. The goal is to facilitate the continued use of Microsoft Office in your budgeting tasks and to retain the overall robustness you need for the process as a whole.
Is budgeting critical…?
During the budget season, an optimized and consistent budgeting process becomes a key focus for finance and business leaders alike. Budgeting helps to determine resource allocation for the forthcoming period(s) and represents how you will numerically support the corporate strategy. It can be represented by monetary or statistical values. Often, with limited resources, the choice of the two-dimensional spreadsheet becomes an easy choice.
Yet, consider where the numbers ultimately land – usually they should land in the ERP, typically SAP or one of the other leading systems. While Excel or Access are great ways to pre-stage or collate the numbers the final budget analysis should happen in an environment where the implications of the budget can be felt to best effect. Constant referral back to the content of the spreadsheet or access database should be avoided as much as possible due to the lack of rules and logic in these staging environments.
By leveraging your ERP, you get the benefit of the account structure, the organization structure, the financial periods, versioning, currency and currency translation as well as all the master data such as employee information, and the asset item master. Your ERP system is likely also configured to accommodate influencing variables, apply spreading algorithms as well as all the annotations and supplementary rules that you are likely to need. Most importantly, it has the ability to do the periodic roll-ups and comparisons with actual figures and then also provide the audit trails and reports.
Your Excel workbook or access database will not provide most of these in an integrated and easy to digest form. If you wanted to continue to use a workbook after the data gathering process then you have the challenge of needing to maintain it and build the rules. Maintenance and rule building around the workbook or database should be avoided. You have the all the rules and data in the ERP all you need is an easier way to maintain that ERP data with Winshuttle’s approach to Budgeting and Planning.
Budget logic increases user flexibility and at the same time dramatically improves the financial controls and integrity of the process; your ERP is already built and requires no additional programming to get it to work for your company.
Looking at SAP as your system of record, during the annual budgeting process, managers of non-operational cost centers such as sales, marketing, and administrative, plan the costs for various cost types/elements for their respective cost centers. The usual starting point for development of these plans is the actual data for the current/previous year.
To ease this process, SAP allows you to take the previous year’s actual expenses for the cost centers and copy them into an AOP (annual operative planning) budget version in cost center accounting. While this is great for a budget that changes on a little from year to year, this is not ideal for growing organizations that may have many changes to institute.
As an alternative, the previous year’s budget data can also be used as a starting point for a whole recompile of the budget. The data is often downloaded into spreadsheets by each cost element and cost center; planned depreciation on fixed assets is transferred separately to the cost center plan version.
SAP calculates planned depreciation on assets by cost center based on the asset values posted on the cost centers. The data in the spreadsheets is reviewed by respective cost center managers and then manually updated in the spreadsheet with values according to projected requirements and plans. The problem with this approach is three-fold:
- There is no oversight of the numbers by anyone other than the person maintaining the spreadsheet and the person loading the file
- Loading the file requires saving it as a CSV or tab delimited file which may not be done properly and may cause the data load to fail.
- There is no guarantee that the data loaded is actually the data agreed.
Although loading the numbers back into SAP is not the final step. The plans in SAP still need to be reviewed and finalized; changing the numbers again requires the same multi-step process. For most users this is a little tricky and prone to problems and errors. Excel is great for applying formulas to existing data but when you export the Excel data you also lose the rationale and detail of how the numbers were arrived at.
So how can you improve this process while retaining the flexibility that Excel affords?
Using Winshuttle Studio, you have the option to use a transaction recording, a standard SAP API or a query, to extract the budget numbers. You can perform this extraction on demand, via a scheduled job or better still, by way of self service. Instead of one person being the hub for the data distribution process, Winshuttle enables you to embed any one of these extraction methods into the Excel template and distribute it via your preferred method, whether it be by email or by placing it on SharePoint or a shared drive.
Budget review and approval
If you prefer to use a hub and spoke approach to starting the budgeting process you can start a Winshuttle workflow process that is wrapped around either web form data or an Excel Workbook. The workflow process you leverage would be designed by you or your organization to specifically address the way you prefer to have people participate in the budgeting process. Although Winshuttle provides some standard workflow processes out-of-the-box, often we find that it is more appropriate for companies to consider a workflow that specifically addresses their own organizational hierarchy. They may be bound up in workflow rules based on factors like delta percentages relative to the original numbers.
Using a workflow process brings the added benefit of helping you to know where everyone is in the budgeting process. Managers and controllers can sign off on budgets using their own credentials by simply approving submitted numbers or reject them with the option for the submitter to resubmit them after adjustment. Winshuttle supports parallel and serial workflow approval processes according to your preferences. Numbers can even be auto approved if they conform to the rules you define for the workflow.
Instant Upload to SAP
Staffs don’t budget every day so navigating the SAP screens directly can be a challenge. However, using an Excel template or a web form is often quite intuitive and the requirements and steps are often self-evident to work with. Excel allows business users in a way that they are comfortable with. More importantly they want to be sure that the numbers that are approved become the numbers that are used. The best way to achieve this is to create tight integration between the data staging environment (Excel or a web form) and the system of record, in this instance – SAP. Winshuttle allows you to do this.
Only users who have the requisite transaction or data object access in SAP can make the necessary updates. SAP keeps track of versioning and you have the audit trail of who requested the numbers, who approved them and who posted them to SAP.
This flexibility in the data maintenance approach encourages participation and ownership which provides more accurate numbers, at a quicker pace. You can repeat this process an infinite number of times without losing track of the process and without needing to build custom programs or specialized processes that rely on IT. Using Winshuttle as one of the arsenal of tools for the periodic budgeting process enables you to maintain budgets with enhanced oversight, secure integration and increased business agility.
You can see the extraction and upload process in this short video in which we’ve enhanced the process a little further by leveraging a partner technology from Boardwalktech.
If you enjoyed this article-please pass it along to others and through your social networks!
About the author
The Winshuttle blog is written by professional thought leaders who are dedicated to providing content on a variety of topics, including industry news, best practices, software updates, continued education, tips and techniques, and much more.
Questions or comments about this article?
Tweet @Winshuttle to continue the conversation!