Consolidate forecasts

Using Power Query

South West Yorkshire Partnership NHS Foundation Trust, North East and Yorkshire, Mental Health

The problem

The team need to quickly consolidate the forecasts from each individual care group to meet both internal and external deadlines. Otherwise, time would be spent on manual processes rather than value added activities, such as review and challenge.

The challenge

How might we reduce the time spent manually consolidating our detailed forecasts so that more time can be spent reviewing and validating the assumptions with budget holders?

The outputs

The outputs are:

  • a consolidated forecast supported by detailed assumptions
  • detailed dataset that can be used to support decision-making
  • detailed workforce information in the same data set and format as the financial information
  • time saved by automating previously manual processes.

The outcomes

  • Time previously spent by senior finance managers producing the forecast is now spent reviewing outputs and challenging the assumptions. This also allows for quicker internal reporting and decision-making.
  • The financial forecast is now supported by consistent WTE information. This makes internal reporting more relevant for non-financial colleagues and allows more meaningful discussions with budget holders.
  • Consistency in naming conventions and formatting has made it easier to identify and correct spreadsheet errors. Checks have been built into the process to ensure the data matches the ledger and give assurances on data quality.

The Innovation

Read the full innovation on consolidating forecasts

All the documents and resources relating to this innovation can be found in the downloads section at the bottom of the page.

Read now

What the peer-reviewers said

“By successfully implementing the automation and standardisation of an overly manual process, this organisation has enabled their finance team to offer a better service to the rest of the organisation and used a tool that could be applied elsewhere in the trust.”

“Use of software that is available to all trusts. This can be rolled out quickly and adapted”

“This is a no brainer, power query is the best of excel and everyone should making use of it, especially when consolidating multiple data sources”

Get Involved Today

Downloads

Here are all of the documents and resources related to this innovation

Innovation O4

Using Power Query to consolidate Excel forecasts

Download