XLCubed was asked to present at the Acounting and Finance Virtual Summit to show how to easily build IBCS reports and charts within Excel. XLCubed can connect to most datasources directly and can also use data held in Excel ranges. 

We felt that aligning a financial business scenario to the current pandemic was appropriate, and clearly this was of interest to the community as close to 5,000 attendees signed up!

Below is one of the reports created for the final board communication.
IBCS charts waterfall and variance charts built in Excel with XLCubed

The Business Scenario

ACME Ltd is a software and services business with direction operations in the UK, France and Germany, delivering business intelligence software and projects to a large cross section of industries including multi-national Fortune 500 organisations.

It is headquartered in the UK and, like many high tech organisations, has grown rapidly with some of its back office processes not keeping pace with the growth (the words "spreadsheet hell" has been used in the past!).

A new CFO was hired at the beginning of 2020 with a goal to modernize the financial back office through digital transformation. He came from a large German Enterprise that was running SAP as the ERP and was a big fan of XLCubed which connected to the ERP through a data warehouse.

Alongside XLCubed, he introduced IBCS to his prior company which proved very succesful. This used the cMore Message tool from pmOne which XLCubed acquired in 2019.

His CEO wants a new set of forecasts so he can compare the impacts of COVID-19 in multiple scenarios vs the 2020 plan. He specifically wants to look at individual country financials and a full legal consolidated view of the P&L, Balance Sheet and Cash Flows.

The forecasts need to take into account potential cost savings from the respective government furlough schemes and assess how capacity of staff reductions impact inter-company trading. He wants the first update within 1 week!

Solution Design

With a rapid turnaround required and an abundance of Excel and XLCubed skills performing the financial modelling, using Excel was the obvious choice.

The model needed to provide high level numbers on a series of  scenarios to enable easy comparison, and facilitate a discussion with HR to determine the employee actions.

Using existing 2020 Plans as the start point for the individual company forecasts, building in Excel assumptions to perform full financial consolidation on the detail forecast scenarios was somewhat more challenging.

We contacted our partner Solitwork to discuss using the AFC software platform to achieve what we wanted. AFC provides a world class legal consolidation platform which scales from small companies to large international conglomerates. AFC provides direct integration with many ERPs but also has a robust Excel data collection mechanism which we felt would lend itself to the scenario. 

AFC allowed us to model the business operation, defining the business dimensions we needed and produces a cube which XLCubed can use to interrogate facilitating P&L, Balance Sheet and, importantly in a COVID world, cash flow statements.  For the scenario we were looking at, we are only using a very small percentage of AFC consolidation rules engine just to eliminate intercompany services. 

AFC handles automatic consolidation for Actuals, Plans and Forecast so for our needs where Forecasts were the focus it more than does what we need. Being able to do this in a week would require good planning, good quality data and some long days but it was a challenge Solitwork felt confident about.  

The net result was they took on the challenge to consume our scenarios and load up AFC from scratch within a week – delivering us the Financial cube to write the reports (more on that process later).

Based on positive feedback we are planning to release a multipart series of explanations of what we did and how we did it.

 

Part 1 : Constructing the Covid Furlough Modelling Scenarios

Part 2: Setting up the overall forecast  Revenue and Cost models

Part 3: Setting up AFC Environment

Part 4: Linking the models to AFC

Part 5: Building the IBCS Reports

Part 6: Presenting the Lead Case to the Board

Part 7: Next Steps

Subscribe to the XLCubed newsletter.
  • Latest features
  • Version releases
  • News and events
Privacy Policy