Generating Multiple Scenarios in Budgeting and Forecasting: A Game Changer for CFOs and FP&A

In the dynamic world of finance, the ability to anticipate and plan for various future states is critical. Financial Planning and Analysis (FP&A) Managers and their CFOs not only need to create comprehensive budgets and rolling forecasts, they also need the ability to rapidly create What If-style analysis.

This capability is not just a luxury—it is a critical component of strategic financial management. The creation of full scenario copies, especially those incorporating detailed models for revenue, direct costs, and employee costs, offers a suite of benefits that can significantly enhance decision-making processes. Moreover, integrating all relevant data into a single Finance Data Warehouse simplifies these operations, making scenario generation efficient and comprehensive.

Brydens BI works with various private and listed companies, where finance users routinely make full working scenario copies. We do this primarily with SQL Server and often use Calumo as the front-end tool, as it works in Excel and has excellent online finance-style reporting.

Why is being able to create Multiple Budget and Forecast Scenarios Important?

Several years ago (as COVID started to impact everything), a frantic CFO called me; I had designed, built and continued to support their budgeting and forecasting solution.  I did not normally have much to do with the CFO, as the business was large, he was busy, and his FP&A manager was really good.  The solution was reasonably sophisticated, with a driver-based revenue model (updated from their internal data warehouse) and an employee model (updated from their payroll system). A small capital and funding model was also used, and other expenses were updated via direct writeback.   The whole model was also 3-way in that it auto-generated most balance sheet and cash entries based on a range of cash and GST rules.  Anyway, they had recently finalised their budget and were in the middle of a leadership strategic offsite. The FP&A Manager was holidaying overseas.  The discussion at the offsite was all about potential COVID impacts, and the CFO was tasked with generating a range of different budget outcomes based on changes in key operations.  As I said, he was frantic and breathlessly asked how long it would take to create three new budget scenarios.  I thought for a second and replied… “Probably 3 to 5”… he cut me off.  “No”, he said, “3 days is too long… I need this tomorrow.”   I took a quick breath and said, “Sure… I was going to say 3 to 5 minutes”.  He went silent, confirmed he had heard me right and was a bit stunned.  To cut a long story short, I showed the team how to create the copies, discussed with the CFO what the differences between the scenarios should be and oversaw the relevant updates.  That evening, the CFO reviewed them and, the next morning was able to present the draft budget options.   A very happy client.

The above hit home for me. As a former CFO who has stepped in to help businesses in tough times, I understood the power of rapidly generating what-if scenarios, modelling outcomes, and proving numbers with underlying data. Obviously, this is not trivial to do in a rolling forecast scenario unless things are set up well.  This is a key reason I gravitated toward solutions that use SQL Server and have an Excel and Online front-end. Below are five (of the many) advantages of setting up a Finance Data Warehouse in SQL Server and using a tool like Calumo for Budgeting and Forecasting, management reporting and automation, with a focus on Multiple Scenarios.

Five key advantages of using a tool like SQL Server with Calumo for Budgeting and Forecasting with Multiple Scenarios:

  1. Enhanced Decision-Making Agility
    Being able to quickly generate and compare multiple financial scenarios gives CFOs, FP&A and business leadership the agility to make informed decisions in response to changing market conditions. This agility is crucial for maintaining competitive advantage and financial health.
  2. Improved Risk Management
    Multiple scenarios allow for thoroughly examining potential risks and opportunities and their financial implications. A better understanding of impacts can help develop more robust risk mitigation strategies.
  3. Strategic Allocation of Resources
    With detailed scenarios that include revenue models based on operational data and employee cost models based on staff information, organisations can strategically allocate resources to optimise outcomes. This level of precision in planning helps to prioritise investments and provides Executives with much clearer guideposts.
  4. Increased Forecast Accuracy
    Using sophisticated models that draw on a wide range of operational and employee data leads to more accurate forecasts. Rapidly showing what-if scenarios, especially when M&A is discussed, or a new product or business line is being contemplated, in the context of real operational data helps build trust and promote rapid decisions.
  5. Streamlined Planning Processes
    Centralising all relevant financial data in a single Finance Data Warehouse not only makes the creation of scenario copies relatively straightforward but also significantly streamlines the planning process. This efficiency can lead to cost savings and more time for value add and, of course, helps lay the foundation for the adoption of AI. If you have read this far, I’m sure you wondered when AI would be mentioned.

A Finance Data Warehouse that includes planning models and the ability to generate multiple scenarios quickly and without issue revolutionises how companies approach budgeting and forecasting.  This paradigm shift enables a more proactive, data-driven strategy for financial planning and analysis. Tools like Calumo make using these intuitive and are ideal for many Finance reporting and automation needs. At the same time, data can also be easily curated for use in reporting-focused tools, such as Power BI to remove many of the data-related issues typical when using these tools.

In conclusion, the capacity to generate multiple scenario copies rapidly, especially when integrated with detailed operational and employee cost models, is transforming the landscape of financial planning and analysis. For CFOs and FP&A Leaders, embracing these capabilities can lead to more informed decision-making, enhanced strategic planning, and, ultimately, greater organisational success.

If you are interested in learning more, please reach out to us.

Top 3 Frequently Asked Questions on Scenario Planning

1. How does scenario planning improve strategic decision-making?

Scenario planning allows organisations to explore multiple potential futures, providing a clearer understanding of opportunities and threats.  In turn, this foresight and the ease of generating multiple ‘what-ifs’ gives senior leadership additional, timely information to inform a strategic pivot in response to market or other relevant changes.

2. What are the key elements of a Finance Data Warehouse that facilitate scenario planning?

A Finance Data Warehouse that supports effective scenario planning should include comprehensive, up-to-date data on all aspects of the organisation’s operations, including sales, costs, and human resources. Integration capabilities with other business systems for real-time data updates, along with powerful analytics and scenario generation tools, are also crucial.

3. Can small and medium-sized enterprises (SMEs) also benefit from generating multiple scenarios?

Absolutely. While the scale might differ, the principles and advantages of scenario planning apply equally to SMEs. Of course, the definition of SME is pretty broad, and good solutions like Calumo come with a price tag, so basic cost benefit is important.  Having said that, it definitely suits fast-growing businesses, and Brydens BI has several listed clients and larger private businesses that many would classify as SMEs.  By adopting scalable financial planning and analysis tools, SMEs can leverage scenario planning to navigate uncertainty, manage risks, and make more informed decisions, ultimately supporting their growth and resilience. 

Maximising Finance Data Warehouse Potential with Calumo and Power BI

In today’s finance landscape, fully owning your Finance Data Warehouse is paramount. This centralised hub contains core financials, budgets, forecasts, and operational data tailored explicitly for finance needs. However, establishing an effective Finance Data Warehouse can be complex and slow, especially as many IT teams have limited Finance experience. Thankfully, tools like Calumo, with support from dedicated consultants, streamline implementation, enabling finance teams to leverage data-driven insights, informed decision-making, and efficient automation from Calumo and related tools like Power BI and Excel.

Understanding the Finance Data Warehouse:

At its core, a Finance Data Warehouse serves as the nerve centre of financial data management. It acts as a centralised repository, aggregating financial data from various sources such as General Ledger systems, budgeting and forecasting tools, operational databases, relevant external sources, and manual input and commentary.  By consolidating data at a granular level, the Finance Data Warehouse enhances the contextual understanding of financial information, facilitating automation and comprehensive reporting and analysis.

The Role of Calumo and Power BI:

Calumo transcends traditional reporting and budgeting tools by offering a comprehensive solution that includes creating a tailored Finance Data Warehouse in Microsoft SQL Server using a dimensional model approach. This Finance Data Warehouse is then expanded over time to further empower finance.

Calumo uses Cubes, which, like large pivot tables, can easily be viewed and queried online for rapid and self-service analysis.  These cubes and direct links to the Finance Data Warehouse also form the basis of Calumo’s intuitive and flexible reporting capabilities.  Reports are authored in Excel, and there is no end to the types of reports that are possible; writeback can also be enabled across different cubes and situations, commentary captured directly, and the full power of SQL Server (and Excel) can be used for modelling, automation or creation of Finance specific applications to generate complex allocation journals, commission calculations, eliminations or any of the hundreds of other things that a well-built Finance Data Warehouse with a friendly interface allows.

Calumo also has charting capabilities and can leverage Excel charts. Still, it does not have the purpose-built and specialised visualisations of Power BI.  Fortunately, it can provide tailored (and updatable) datasets for Power BI, unlocking powerful visualisation capabilities. This integration empowers finance professionals to explore and analyse data effortlessly, driving informed decision-making and strategic insights.  It also means that finance uses a single source of truth and does not waste time wrangling data or DAX (the formula language used in Power BI).

Challenges in Establishing a Finance Data Warehouse:

Establishing and maintaining a finance-centric data warehouse poses unique challenges, especially for IT teams who may not be deeply familiar with the specific needs and nuances of the finance domain, may have limited or no prior experience in developing and managing finance-specific data warehouses, and, like any good IT team, may have many competing priorities.  This knowledge gap underscores the importance of partnering with a specialised consultancy like Brydens BI (yes, this is a plug!). With a wealth of experience in developing and managing complex Finance Data Warehouses, Brydens BI is a trusted ally, leading and guiding organisations through every implementation stage, always being available and providing ongoing support as an extension of the Finance team.

A good Finance Data Warehouse is data source agnostic.  Over the course of several years, Brydens BI has had many clients change source systems, change staff, buy and sell businesses, and work through reorganisations.  The only constant is the Finance Data Warehouse, with Calumo as its key interface.  In fact, it is not unusual when a CFO changes businesses for Calumo and the principal consultant they trust to be very high on their list of priorities.

Synergy Between Calumo, Power BI, Excel and Brydens BI:

The synergy between Calumo, Power BI, and Brydens BI offers unparalleled benefits for finance teams. Calumo’s robust Finance Data Warehouse and finance-friendly reporting and modelling serve as the foundation, while Power BI enhances visualisation and ad-hoc once-off capabilities, bringing data to life. Calumo also directly links to Excel and uses Excel for report authoring to capitalise on existing Excel skills. With Brydens BI’s expertise and support, organisations can be up and running quickly, maximising the value derived from these tools, unlocking new insights, driving efficiency, and empowering informed decision-making.

Frequently Asked Questions:

How does Calumo differentiate itself from traditional budgeting tools?

Calumo goes beyond conventional budgeting tools by offering a comprehensive solution that includes creating a tailored Finance Data Warehouse and dynamic online reporting authored in Excel. Unlike traditional tools that focus solely on budgeting, Calumo effectively serves as a centralised hub or platform for financial and relevant operational data, enabling deeper insights and analysis. Furthermore, Calumo integrates seamlessly with Excel, and the Finance Data Warehouse it links to is built using Microsoft SQL Server using a Dimensional model approach perfectly suited for Power BI. This means that tailored correctly formed datasets can be generated for Power BI to fully unlock its potential without becoming bogged down in data wrangling and complex data transformations that many Finance staff may not have the time or background to achieve.

   

What specific advantages does linking Power BI with Calumo offer finance teams?

Many staff will have experience with Excel and Power BI.  Linking Power BI with tailored datasets from the Finance Data Warehouse that underpins Calumo ensures a single source of truth removing key data sourcing and data transform issues associated with Power BI. It also means Finance teams get the best of both worlds, including the enhanced visualisation capabilities of Power BI and the data management, modelling and finance-friendly reporting capabilities of Calumo.

How does Brydens BI support clients with their Finance Data Warehouse and Finance needs?

Brydens BI offers specialised expertise in designing, implementing, and supporting Finance Data Warehouse and analytics capabilities, drawing from its extensive experience with a range of large private and listed companies. Given its Finance and IT knowledge combined with its depth of experience, a typical implementation requires only limited input from client-side resources.

Collaborating closely with clients, Brydens BI actively provides options, suggests best practise and delivers tailored solutions that address unique needs and challenges.  As client businesses grow and change, Brydens BI remains available to account for change, allowing the Finance team to focus on adding value and drive efficiency, accuracy, and strategic decision-making.

Beyond Spreadsheets: Why Every Modern CFO Needs a Finance Data Warehouse

In today’s fast-evolving business environment, the role of CFO has transformed dramatically. No longer confined to traditional bookkeeping or fiscal management, the modern CFO is at the helm of strategic decision-making, harnessing the power of data analytics to drive the business forward. One tool, in particular, stands out in this new era of financial management: the finance-specific data warehouse. In our view, beyond the familiarity of spreadsheets lies a tool that offers a magnitude of benefits tailored for the contemporary CFO.

1. Finance Specific Data

Central to successful financial decision-making is the availability and integration of relevant financial and operational data. Although spreadsheets were apt for a time when data existed in isolated silos, contemporary businesses thrive in a profoundly interconnected environment. Having critical financial and operation information tailored for finance in an intuitive and easy-to-use central data warehouse removes significant risks, makes any reporting and/or ad-hoc analysis faster, and means the Finance function is positioned to add value.


Apart from the obvious General Ledger, Finance often need broader business data for specific Finance use cases and/or to answer particular Finance style questions such as regulatory reporting or forecasting. Finance must control their data and directly influence how it is structured and maintained. Although broad business-wide datalakes or data warehouses are to be applauded and are often a very useful data source, they should not negate the need for a tailored finance-specific data warehouse whose primary purpose is to support Finance reporting, forecasting and efficiency.

2. Scalability and Performance

As organizations grow, so does their financial and related operational data volume. Spreadsheets, albeit convenient, have limitations in managing vast quantities of data, often leading to performance issues. A finance data warehouse, however, is inherently designed to handle large datasets, ensuring that CFOs can retrieve and analyze information rapidly, irrespective of its volume. Importantly, if the data warehouse is finance-centric and well-designed, the enormous amount of time wasting associated with using Excel to ‘format’ or ‘pivot’ the data also becomes redundant.

It also means that other popular tools become more effective. Tools like Power BI become far more useful when the underlying data sets are clean, trusted and designed for Finance.

3. Enhanced Data Accuracy and Reliability

In the world of finance, precision is non-negotiable. Manual data entries on spreadsheets are prone to human error, potentially leading to significant financial discrepancies. Data warehouses automate much of the data aggregation process and can include their own controls and reconciliations. This dramatically reduces the risk of errors and enhances data integrity.

In addition, as businesses evolve and new accounts get added or new cost centres are introduced, a well-designed finance-specific data warehouse (and associated reporting) will automatically handle these changes. If ‘manual’ management accounting adjustments, eliminations or allocations are required, then having these set and centralised in the Finance data warehouse and fully controlled by Finance will save significant time, reduce risk and increase accuracy. For instance, it is not uncommon for Brydens BI clients to have their CALUMO solution auto-generate cost allocations based on rules they define and have these form part of their management reporting with the option to generate an actual journal also available.

Similarly, reports set up in Power BI are far more effective if the team trusts the underlying data and knows that the underlying database is dynamically updating.

4. Timely Financial Insights and Forecasts

Businesses are moving faster. Waiting for month-end reports or quarterly analyses no longer suffices. Modern companies demand up-to-date data, and timely insights to respond swiftly to market dynamics. A finance-specific data warehouse that is connected to critical finance and operational data sources can provide essential reporting and advanced analytics in near-real time. This allows CFOs to oversee agile and informed decision-making. It also means that rolling forecasts become a standard tool, underpinned and automatically informed by models tied to actual financial and operational data.

Many Brydens BI clients have monthly rolling forecasts as standard. As we have implemented rolling forecasts and then continue to support them, it becomes clear that these businesses become much more forward-looking, and Finance becomes far more central to decision-making,

5. Advanced Analytical Capabilities and Automation

Beyond mere data storage, finance data warehouses are equipped with powerful analytical tools and centralise things like mapping to avoid the tedious use of VLOOKUPs often repeated across Excel workbooks. Brydens BI has designed, set up and continues to support dozens of Finance-specific data warehouses. By leveraging code and the power of databases, we see these being leveraged to run complex financial models, predictive analyses, and the automation of once time-consuming processes like allocations, consolidations, and eliminations. We also see CFOs continue to push the boundaries as they leverage their finance-specific data warehouse to answer challenges or key process challenges specific to their business.

This approach also lends itself to adopting Artificial Intelligence and Machine Learning, with several Bryden’s BI clients now using these technologies to understand better what might happen next and/or identify areas that need greater scrutiny.

Whether using sophisticated tools like Calumo, reporting in Power BI or even exposing data in Excel, ensuring the Finance team (and broader business) is using the same, trusted (and enhanced) finance data from a central finance-centric data warehouse removes significant risk from the business, saves time and unlocks much potential.

6. Data Security

In an era where data breaches can spell monumental crises, ensuring the security of key financial and operational data is paramount. Data warehouses can leverage robust security protocols to minimise the risk of unauthorized access or breaches.

Closing Thoughts

The digital transformation wave has left no stone unturned, and the finance function is no exception. As businesses hurtle towards an increasingly data-driven future, the tools of yesteryears — no matter how cherished — must give way to more advanced, efficient, and strategic solutions. This does not mean that broad business-wide data warehouses are unnecessary, nor does it mean that Excel will die; instead, how they are used will evolve. For the modern CFO tasked with steering the financial ship amidst volatile waters, the finance data warehouse is not just a tool; it’s an imperative.

Incorporating a finance data warehouse into the strategic toolkit is the forward leap that today’s CFOs must take to ensure their organizations remain competitive, resilient, and ever-evolving. Brydens BI supports businesses on this journey, primarily with CALUMO, Power BI and Excel. This includes an SQL Server-based data warehouse designed and supported by us (ex-finance staff) tailored to each client and their specific needs.

The journey beyond spreadsheets is not just a technological upgrade; it’s a paradigm shift in financial leadership.

Scenario Planning Enhanced:  Leveraging the Finance Data Warehouse for Robust Employee Forecasting

In today’s complex and rapidly evolving business landscape, financial leaders are not just number crunchers but strategic decision-makers.  Central to this transformation has been the rise of the Finance Data Warehouse (or finance data mart) designed for and owned by Finance.  A centralised repository for detailed financial data and relevant operational data that underpins analytics, decision-making, and future planning.

One compelling application of the Finance Data Warehouse is in the domain of employee forecasting.  In nearly all businesses, employee costs are significant.  Understanding and accurately forecasting these costs and changes in FTE and Headcount becomes vital.

Employee Forecasting made easy.

Nearly all Brydens BI clients have a sophisticated Employee Planning model that is informed daily or monthly of actual changes via automated (API) connections or monthly file uploads, along with a simple interface to manage all future forecast changes.  This level of automation and control becomes critical as clients reach hundreds or thousands of employees. Due to its ease of writeback, Calumo is typically the front-end tool used by clients who no longer have dozens of Excel files with employee data. Being able to rely on a central source of truth that they control reduces risk, saves time and makes Finance more relevant. In addition, with central, clean and trusted data, reporting with other tools, such as Power BI or Excel, becomes easier and more useful. Several Brydens BI clients leverage the Finance Data Warehouse that underpins Calumo to generate specific datasets tailor-made for Power BI.

 

The Finance Data Warehouse automatically updates rolling forecasts with new employees, terminations, and salary or cost centre changes. This automation saves significant time and reduces risk. Importantly, it also provides key business users with up-to-date, by-employee data they can discuss with Finance and use to make decisions. Being able to discuss variances and quickly drill to employees makes everything easier.

Using the Finance Data Warehouse to budget and forecast employees and all related costs, such as salaries, superannuation, and payroll taxes, simplifies core forecasting challenges. In addition, and where relevant, headcount or new starters can be used to model other relevant costs automatically.  This also means that what-if style forecasting is straightforward.

Employee Forecasting is just the beginning.

In addition to Employee forecasting, once the General Ledger, HR Systems, and other relevant operational systems are linked to the Finance Data Warehouse, more possibilities are unlocked. For some clients, Brydens BI calculates and reports commissions, provides workforce planning capability, automates allocations (based on FTE and other metrics), and generates employee analytics and related visualisations through Calumo or Power BI.

In conclusion, the Finance Data Warehouse is not just a tool; it’s a game changer. By harnessing its capabilities, finance leaders can ensure more robust, accurate, and strategic employee forecasting while empowering their teams, removing significant unproductive work and providing the foundation for additional value-add. A finance-centric data warehouse also means monthly report packs and annual reporting, especially when linked to a tool like CDM (Certent Disclosure Management), becomes faster with less risk.

Brydens BI works with clients around Australia and worldwide. Because of our extensive finance experience, we often become an extension of the finance team and provide advice and support as required. Feel free to reach out if you want to know more.

Strategic Financial Planning: Navigating Change with Annual Budgets and Rolling Forecasts

The annual budget and the rolling forecast are critical deliverables for Finance. Historically, executing these effectively has posed a considerable challenge for many organisations. Some businesses would take months to produce a budget without even attempting monthly rolling forecasts. However, with bespoke solutions and adept support, this daunting task transitions into an opportunity, amplifying business value and redefining finance department significance.

Brydens BI works with many organisations and has helped them transition to rolling forecasts and far less painful annual budget processes.

The annual budget and rolling forecasts serve distinct yet complementary purposes, offering organisations the benefits of stability and adaptability in a dynamic business environment.

The Annual Budget: Setting the Course for the Year Ahead

The annual budget acts as an organisation’s fiscal compass. It remains static, instils fiscal discipline, establishes benchmarks for performance assessment, and helps define critical capital allocation decisions.

For many enterprises, the annual budget only focuses on the Profit and loss (P&L) statement. Yet others adopt a comprehensive three-way methodology, incorporating Balance Sheet and Cash Flow. At Brydens BI, we champion both approaches and have developed methodologies to streamline and tailor them to each client.

The Monthly Rolling Forecast: Adapting and Looking Further Ahead

Contrasting the static nature of the annual budget, the monthly rolling forecast thrives on adaptability, continuously adjusting in response to internal shifts and external influences. Brydens BI recommends setting up core models around employees, revenues and direct costs and would typically leverage CALUMO and its underlying Finance Data Warehouse to achieve this.

Each model, uniquely crafted to fit individual business needs, syncs seamlessly with pivotal operational data. This synchronisation ensures that when a month “rolls,” updates to the models mirror both operational and financial realities, enhancing the forecast’s pertinence while eliminating redundant tasks like copying and pasting data or updating a range of Excel workbooks. Brydens BI has clients who have built various complex models, including within Funds Management (with all the complexities around costs and hurdles by Fund), Equipment Lease and Term Loan Models, and Construction and Development Models. Clients have full control in all cases and typically extend and modify models as their businesses evolve (always with our support, if needed). These models become central to forecasting, especially as they are linked to real operational data and automatically update monthly or in line with business needs.

The strength of the monthly rolling forecast is its continual evolution and ability to inform business strategy.  In our experience, Rolling Forecasts typically extend for three to five financial years (although we manage some that extend further). When done well, Rolling Forecasts equip businesses to anticipate challenges and spot burgeoning opportunities.  Importantly, Finance and the company can discuss these as they evolve.

Combining Annual Budgets and Rolling Forecasts

An effective and timely annual budget and monthly rolling forecast equips organisations to define, evaluate, and recalibrate their fiscal goals. This dynamic allows for a harmonious blend of setting foundational expectations and making agile, informed strategy modifications in response to live data and emerging trends.

Notably, a high-quality monthly Rolling Forecast is the starting point of the Annual Budget process. In some cases, Brydens BI has seen clients reduce time spent on Annual Budgets from close to three months to under two weeks. Finance teams, who start with a rolling forecast informed by up-to-date operational data that Executives are familiar with, effectively have a draft Annual Budget (even multi-year) when they start.

Equipped with the right tools and support, focusing on insightful analysis rather than mere execution elevates the finance function’s stature.  Brydens BI partners with numerous finance teams to set up and support monthly rolling forecasts and annual budgets. This is achieved through years of experience designing, implementing and managing robust Finance Data Warehouses that support Finance in these and other related tasks. To learn more, simply reach out.