are you prepared
for what's next?
budgeting beyond excel
By: Rob Gandee
VP, Business Development
are you prepared for what's next?
budgeting beyond excel
By: Rob Gandee
VP, Business Development
Introduction
Excel has been a valuable tool in many aspects of business for many years. Getting started is easy, many users can reach a level of competency relatively quickly, and those with a higher level of aptitude can work magic with their data.
Over the last 10 years, however, improvements in network speeds, data storage, and coding structures have made it possible to build platforms that offer a choice for managers in various businesses.
This paper will examine some of the strengths and weaknesses of spreadsheets, and the platforms that are replacing them, with a particular focus on the budgeting and forecasting functions performed by the finance team in most organizations.
Spreadsheets have many strengths, not least of which is that many people have grown up using them in both their personal and business lives, and they have an in-built familiarity with how they work.
We all know how to create a file, name it, store it, manage multiple tabs, and build simple formulas. Formatting our data to make it presentable is a simple task, and as we spend more time working on our files, we delve into the multitude of formula types, the charting functions, sorting data, pivot tables, lookups, and more. Master users can perform some truly impressive feats of data manipulation and create works of art with their tabs.
Spreadsheets have many strengths, not least of which is that many people have grown up using them in both their personal and business lives, so have an in-built familiarity with how they work.
We all know how to create a file, name it, store it, manage multiple tabs, and build simple formulas. Formatting our data to make it presentable is a simple task, and as we spend more time working on our files, we delve into the multitude of formula types, the charting functions, sorting data, pivot tables, vlookups and more. Master users can perform some truly impressive feats of data manipulation and create works of art with their tabs.
Introduction
Excel has been a valuable tool in many aspects of business for many years. Getting started is easy, many users can reach a level of competency relatively quickly, a those with a higher level of aptitude can work magic with their data.
Over the last 10 years, however, improvements in network speeds, data storage, and coding structures have made if possible to build platforms that offer a choice for managers in various businesses.
This paper will examine some of the strengths and weaknesses of spreadsheets, and the platforms that are replacing them, with a particular focus on the budgeting and forecasting functions performed by the finance team in most organizations.
Spreadsheets have many strengths, not least of which is that many people have grown up using them in both their personal and business lives, so have an in-built familiarity with how they work.
We all know how to create a file, name it, store it, manage multiple tabs, and build simple formulas. Formatting our data to make it presentable is a simple task, and as we spend more time working on our files, we delve into the multitude of formula types, the charting functions, sorting data, pivot tables, vlookups and more. Master users can perform some truly impressive feats of data manipulation and create works of art with their tabs.
the drawbacks of excel
- Cells can easily be overwritten. A complex formula can vanish in a second. If there is only one person using the spreadsheet and they notice it immediately, this can be fixed. But if not, the error may propagate through the file, resulting in incorrect results, and there are numerous horror stories of money lost due to simple spreadsheet errors (see appendix).
- Providing team access not only increases the chances of the error just noted, but not all users will have the same level of aptitude. And if you’re not using a cloud-based spreadsheet, you may need to collate files from different contributors who have altered your formatting, entered data in different places, creating more work for the finance team.
- Version control is a challenge. If you’re sending and receiving budget spreadsheets, and your process involves multiple iterations or regular updates, how can you be sure that the files you receive back are the right version?
- Security can be an issue. How do you disseminate your spreadsheet templates to your users? By email? By secure file sharing platform? Can you control who is seeing the file once you have sent it?
- Change tracking and workflow is next to impossible. There is no way in Excel to track who made changes and when, nor is there a function that offers the ability for a user to request approval for a change.
Depending on the size and structure of your organization, you may have experienced some or all of these problems, and because of this, there are a large number of different platforms that have appeared to answer them in a variety of ways.
A budget is a financial plan for a defined period
A financial forecast is an estimate of future financial outcomes
The two have some similarities, but different approaches are often used to create them.
So what steps do we typically consider when building a budget?
considerations for building a budget
#1 First, what is our structure? Are we building a single annual budget for the organization, or does each department, location, cost center create their own standalone budget which we then consolidate into the total.
#2 Next, we build out our Revenues. Maybe they will be based on a previous year’s data, or perhaps we build them from the ground up. Are we deriving the Revenues from some assumptions like Unit Sales x Unit Price, or Billable Hours x Billable Rate?
#3 Our Cost of Sales and Expenses might be driven by our Revenues. What percentage are we using as a multiplier, does it vary by department.
#4 Payroll needs to be included in our Expenses, are we budgeting salary for each individual, or approximating for the workforce as a whole? Do we try to calculate payroll taxes and benefits accurately, or use a single percentage that covers them all?
#5 And once you’ve built all the relevant data into the budget, you’ll want to run a comparison to your previous year’s budget or actuals, to make sure nothing is missing, and the changes to the previous year are reasonable, and as intended.
The extra/alternative steps that might be used to build a forecast include:
- Overwriting budget data with Actuals month by month as the year progresses.
- Extrapolating the actual data to create a trend in the forecast months.
- Modelling growth rates into future years to create a 2,3, or 5 year forecast.
All of these actions can be performed in spreadsheets, and have been for years, so what benefits can be provided by a software platform specifically designed for budgeting and forecasting?
Related: Budgyt's 2020 Business Budgeting Software Reviews & Rankings
Come see why Budgyt leads the pack in overall value, ease of use and functionality. Other reviewed platforms include: Adaptive Insights, Vena Solutions, Jirav, Planful, Budgeta, Hyperion and many more!
considerations for building a budget
#1 First, what is our structure? Are we building a single annual budget for the organization, or does each department, location, cost center create their own standalone budget which we then consolidate into the total.
#2 Next, we build out our Revenues. Maybe they will be based on a previous year’s data, or perhaps we build them from the ground up. Are we deriving the Revenues from some assumptions like Unit Sales x Unit Price, or Billable Hours x Billable Rate?
#3 Our Cost of Sales and Expenses might be driven by our Revenues. What percentage are we using as a multiplier, does it vary by department.
#4 Payroll needs to be included in our Expenses, are we budgeting salary for each individual, or approximating for the workforce as a whole? Do we try to calculate payroll taxes and benefits accurately, or use a single percentage that covers them all?
#5 And once you’ve built all the relevant data into the budget, you’ll want to run a comparison to your previous year’s budget or actuals, to make sure nothing is missing, and the changes to the previous year are reasonable, and as intended.
The extra/alternative steps that might be used to build a forecast include:
- Overwriting budget data with Actuals month by month as the year progresses.
- Extrapolating the actual data to create a trend in the forecast months.
- Modelling growth rates into future years to create a 2,3, or 5 year forecast.
All of these actions can be performed in spreadsheets, and have been for years, so what benefits can be provided by a software platform specifically designed for budgeting and forecasting?
Budgeting Software Pros and Cons
There are many options to choose from when looking for a new budgeting software, with many different capabilities, but most of the better ones share a number of advantages over spreadsheets. Let’s walk through our budgeting steps listed above, and see the comparison at each stage.
Depending on the size and structure of your organization, you may have experienced some or all of these problems, and because of this, there are a large number of different platforms that have appeared to answer them in a variety of ways.
structure
- Spreadsheet: you need to create a separate tab for each business unit, make sure the chart of accounts is on each tab (and if you later add an account, ensure you add it in the same place on each tab), and build all the linking formulas that consolidate your units.
- Software: business units may be added with a simple form, grouped into divisions or regions, and consolidation is automatic. The chart of accounts only needs to be entered once, and when adding an account, it will appear in all units that use that account.
Revenues
- Spreadsheet: create an additional tab for your assumptions, build formulas and links to create your revenues (on each tab that needs them), protect the cells that contain the formulas, remember your password, enter the data for your drivers.
- Software: assumptions can be input directly, formulas built away from the data, and available to all business units. User permissions may be restricted so they are unable to edit (or accidentally delete) formulas and links.
COGS
- Spreadsheet: apply formulas separately in each tab, protect the cells (don’t forget to do this), remember the password, enter data.
- Software: formulas may be built to apply to all units, using different percentages in each unit. Formulas are listed and labeled so are easy to audit and amend
Payroll
- Spreadsheet: Create a separate section or tab to list your employees, define the salary for each, create formulas for benefits, payroll taxes, pay raises, link the employees to the right business unit tabs, and try to create some sort of protection so that only certain users see certain parts of the payroll.
- Software: custom designed Payroll section has all the formula types built in, allows employee payroll to be allocated across different business units, targeted to different accounts, spread according to your pay periods or accrual schedule, and user permissions can be set so that all this sensitive data is only visible to your managers that need to see it.
Comparisons
- Spreadsheet: create a new tab for each comparison, or add complexity to your current tabs. Copy and Paste the data that you need to compare, build formulas for each account for each comparison, and for consolidations, hope you didn’t miss a formula.
- Software: import your comparison data, if you don’t’ already have it in the system (you’ll be able to store multiple data sets). Go to the Budget to Actual comparison screen and choose the data sets you wish to compare. All comparison formulas are already built. View your comparisons at the business unit, group, and account levels, for any period with just a few clicks.
Forecasting
- Spreadsheet: make a copy of your spreadsheet, copy and paste to overwrite the data where necessary, build additional formulas to extrapolate your data, on each tab, check your formulas and links, then go through the comparison process again to see how your new forecast relates to the original budget.
- Software: create a new version of your budget with a few clicks. Import data where needed, use in-built tools to copy, grow, extrapolate data into the future as required. Your new version will be stored, and you can run a comparison to any other versions you may have created – Actuals, Budgets, What If Scenarios, Forecast Iterations etc.
As you can see from the comparisons we just walked through, in most areas there are significant advantages to using a budgeting platform:
- Time savings
- Data integrity
- Multi user access with varied permissions
- In built structures for Payroll, Comparisons and Formulas
- Uniform templates
But what about the negatives,
surely this isn’t a completely one-sided conversation?
There are indeed a number of considerations to take into account when deciding whether to move away from budgeting in spreadsheets:
- Cost – Spreadsheets are basically free, and just as there are a wide variety of platforms available, there is a wide range of prices, from $50 per month up to $50,000 a year or higher.
- In making a determination as to the value of using a new platform, time saved is an important part of the calculation, and a lot of packages will more than pay for themselves on this metric. But also, what value do you place on data integrity, and the knowledge that when you present the budget to the board, your numbers will be correct?
- Necessity – If your business only has one business unit, you will be the only user, and there is limited complexity in your budget data, your best bet probably is to work with spreadsheets. They may be perfectly adequate for your needs.
- Complexity – A lot of your budget contributors will have at least a working knowledge of spreadsheets, and although they may not be huge fans, they know how the budget works. Any new software will obviously have a learning curve, and you need to choose something that not only will be easy to learn and use for your non-admin as well as your admin users, but has a solid training and support function so that everyone can be confident they have backup. As with pricing, there is a wide range of complexity across the universe of budgeting software.
Budgeting Software Pros and Cons
There are many options to choose from when looking for a new budgeting software, with many different capabilities, but most of the better ones share a number of advantages over spreadsheets. Let’s walk through our budgeting steps listed above, and see the comparison at each stage.
Depending on the size and structure of your organization, you may have experienced some or all of these problems, and because of this, there are a large number of different platforms that have appeared to answer them in a variety of ways.
structure
- Spreadsheet: you need to create a separate tab for each business unit, make sure the chart of accounts is on each tab (and if you later add an account, ensure you add it in the same place on each tab), and build all the linking formulas that consolidate your units.
- Software: business units may be added with a simple form, grouped into divisions or regions, and consolidation is automatic. The chart of accounts only needs to be entered once, and when adding an account, it will appear in all units that use that account.
Revenues
- Spreadsheet: create an additional tab for your assumptions, build formulas and links to create your revenues (on each tab that needs them), protect the cells that contain the formulas, remember your password, enter the data for your drivers.
- Software: assumptions can be input directly, formulas built away from the data, and available to all business units. User permissions may be restricted so they are unable to edit (or accidentally delete) formulas and links.
COGS
- Spreadsheet: apply formulas separately in each tab, protect the cells (don’t forget to do this), remember the password, enter data.
- Software: formulas may be built to apply to all units, using different percentages in each unit. Formulas are listed and labeled so are easy to audit and amend
Payroll
- Spreadsheet: Create a separate section or tab to list your employees, define the salary for each, create formulas for benefits, payroll taxes, pay raises, link the employees to the right business unit tabs, and try to create some sort of protection so that only certain users see certain parts of the payroll.
- Software: custom designed Payroll section has all the formula types built in, allows employee payroll to be allocated across different business units, targeted to different accounts, spread according to your pay periods or accrual schedule, and user permissions can be set so that all this sensitive data is only visible to your managers that need to see it.
Comparisons
- Spreadsheet: create a new tab for each comparison, or add complexity to your current tabs. Copy and Paste the data that you need to compare, build formulas for each account for each comparison, and for consolidations, hope you didn’t miss a formula.
- Software: import your comparison data, if you don’t’ already have it in the system (you’ll be able to store multiple data sets). Go to the Budget to Actual comparison screen and choose the data sets you wish to compare. All comparison formulas are already built. View your comparisons at the business unit, group, and account levels, for any period with just a few clicks.
Forecasting
- Spreadsheet: make a copy of your spreadsheet, copy and paste to overwrite the data where necessary, build additional formulas to extrapolate your data, on each tab, check your formulas and links, then go through the comparison process again to see how your new forecast relates to the original budget.
- Software: create a new version of your budget with a few clicks. Import data where needed, use in-built tools to copy, grow, extrapolate data into the future as required. Your new version will be stored, and you can run a comparison to any other versions you may have created – Actuals, Budgets, What If Scenarios, Forecast Iterations etc.
As you can see from the comparisons we just walked through, in most areas there are significant advantages to using a budgeting platform:
- Time savings
- Data integrity
- Multi user access with varied permissions
- In built structures for Payroll, Comparisons and Formulas
- Uniform templates
But what about the negatives,
surely this isn’t a completely one-sided conversation?
There are indeed a number of considerations to take into account when deciding whether to move away from budgeting in spreadsheets:
- Cost – Spreadsheets are basically free, and just as there are a wide variety of platforms available, there is a wide range of prices, from $50 per month up to $50,000 a year or higher.
- In making a determination as to the value of using a new platform, time saved is an important part of the calculation, and a lot of packages will more than pay for themselves on this metric. But also, what value do you place on data integrity, and the knowledge that when you present the budget to the board, your numbers will be correct?
- Necessity – If your business only has one business unit, you will be the only user, and there is limited complexity in your budget data, your best bet probably is to work with spreadsheets. They may be perfectly adequate for your needs.
- Complexity – A lot of your budget contributors will have at least a working knowledge of spreadsheets, and although they may not be huge fans, they know how the budget works. Any new software will obviously have a learning curve, and you need to choose something that not only will be easy to learn and use for your non-admin as well as your admin users, but has a solid training and support function so that everyone can be confident they have backup. As with pricing, there is a wide range of complexity across the universe of budgeting software.
CONCLUSIONS
So what have we learned from this discussion? Spreadsheets have issues, and for a lot of organizations it makes sense to at least consider using a budgeting platform to replace their spreadsheets.
Time savings and data integrity will probably be the major considerations when making this decision, but also user permissions, ease of use, the size of your team, and the future growth of your organization.
There are a wide range of options to choose from which can be bewildering, but you can go to comparison sites such as Capterra, Software Advice, Software Connect and others to start your search.
The world is moving towards a time when most of our software packages reside on the Cloud, and it makes sense to embrace this trend for many of our operations, to increase the efficiency and flexibility of our organizations.
CONCLUSIONS
So what have we learned from this discussion? Spreadsheets have issues, and for a lot of organizations it makes sense to at least consider using a budgeting platform to replace their spreadsheets.
Time savings and data integrity will probably be the major considerations when making this decision, but also user permissions, ease of use, the size of your team, and the future growth of your organization.
There are a wide range of options to choose from which can be bewildering, but you can go to comparison sites such as Capterra, Software Advice, Software Connect and others to start your search.
The world is moving towards a time when most of our software packages reside on the Cloud, and it makes sense to embrace this trend for many of our operations, to increase the efficiency and flexibility of our organizations.
- understand the hype -
- understand
the hype -
our latest thoughts
for what comes next
beyond excel
for what's next?
the CFO's toolkit
financial applications
work harder for you