For many financial planners out there, budgeting, planning and forecasting equals spreadsheets. Dozens or hundreds of them – that’s just the reality. You’ve accepted the fact that come the end of the fiscal year, you and your cohorts will be chained to your desk piecing together various versions of spreadsheets from each department and hoping that after several weeks of this, you’re able to consolidate the numbers into a workable budget for the next year. And then you hit a sales roadblock halfway through the year and have to forecast the impact…and go through all of this again.
The sheer amount of work this process takes is not the only challenge you face, and you’re not alone. Let’s take a closer look at 4 of the common challenges that are undermining your ability to be truly productive and add value when it comes to budgeting, planning, and forecasting.
Have you ever tried driving at night without headlights? I haven’t, but I can tell it’s a bad idea. In the same vein, your visibility issues when it comes to budgeting and planning mean you might be driving blind and that’s a bad idea for organizations that want accurate budgets and forecasts for the year/6 months/quarter ahead. Can you relate to these complaints?
- I’m unable to get real-time data from IT.
- The data I do get is siloed.
- We need to cut our sales plan back 10% and I have no way to see the financial impact, let alone quickly notify budget managers of the change.
Visibility issues might vary in complexity but they all mean the same thing: making decisions with inadequate or outdated information can inject serious error into every process at your organization. If you have old data to plan with, your budgets may be unrealistic. If you can’t provide timely insight to your budget managers, they can’t make good decisions (as would be the case with #3 above).
And if you’re not providing value-added contributions to the budgeting and planning process, your role as a planner will be undermined.
The end of the fiscal year can often be a stressful time for businesses, which is why you need a sophisticated way of managing critical budgeting and planning processes. If you’re still using Excel and you’re bogged down with dozens (or hundreds) of spreadsheets, think about taking advantage of your company’s BI system to make this budgeting cycle a little less overwhelming. Or maybe it’s time to consider dedicated budgeting software that’s part of your company’s larger business intelligence (BI) strategy.
Maybe you don’t think that BI and planning go hand-in-hand, but trust me – after working with literally hundreds of customers who are using BI to manage their planning – they do. BI allows the finance department to provide timely consolidated budgets and forecasts that leverage data from a variety of sources so you can understand how your business may perform by modeling a range of scenarios.
Here are a few tips to consider when using BI for budgeting and forecasting.
1. Move on from spreadsheets and think about adopting dedicated software as part of a larger BI initiative. Organizations with revenues of $35M or more are typically at a stage when financial planners must deal with a plethora of spreadsheets during budgeting season (not to mention a number of versions of each spreadsheet). This amount of data quickly becomes unwieldy and prone to errors. By integrating dedicated budgeting software into your BI platform, you can perform real-time “what if” predictive modeling and monitor performance against a target to improve performance. Think about all the value you’ll add as a financial planner with up-to-date data – not plans that are outdated as soon as they’re formed.
2. Integrate BI software into existing budgeting processes. If you’re a smaller company, you likely don’t have the time or resources to reinvent the wheel. Use your company’s existing BI tool into your already-defined process. If you don’t currently have a tool, look for software that easily and flexibly integrates with your existing data sources, includes adjustable templates and offers your planners a Web interface for offline planning.
3. Create an organized workflow for your BI collection and analysis process. Logically order your planning tasks and keep an eye on where your team is in the planning cycle. This will help you better monitor, analyze and make tactical adjustments to the budgeting process throughout the year.
4. Collaborate within your organization for increased efficiency.
Regression analysis finds the relationship between two variables and is often used for projecting future data. It’s an extremely complicated formula that starts with…
Regression Equation(y) = a + bx
Slope(b) = (NΣXY – (ΣX)(ΣY)) / (NΣX2 – (ΣX)2)
Intercept(a) = (ΣY – b(ΣX)) / N
…but that’s just the beginning. You may want to leverage software to do the heavy analytical lifting for you.
In the example below (video), we’ll go over how regression analysis can predict 12 months of future sales data based on 3 years of historical data. arcplan Enterprise business intelligence software includes a linear regression formula that takes all the guesswork out of your calculations. You can change your assumptions with the click of a mouse and the system instantly delivers a new regression line that projects updated future data. Check it out and let me know what you think!