Month-end review and forecast tool

Innovation B3

University Hospitals of Morecambe Bay NHS Foundation Trust

North West, Acute

This innovation has had a clear and quantifiable impact on the time spent by staff.

Peer Reviewer

Power query is a really powerful and easily accessible tool.

Peer Reviewer

An excellent, shining example of what you can do in Excel to produce a wide variety of benefits.

Peer Reviewer

The Problem

The financial management team needed to streamline and automate the forecasting process to meet the faster month-end reporting deadlines and improve the quality and integrity of data. Otherwise, deadlines would be missed and earlier engagement with budget holders would not be achievable.

The Challenge

How might we improve the way we forecast, so that we can spend more time reviewing and influencing financial information with our senior finance colleagues and budget holders?

The Outputs

The outputs are:

  • the forecast care group template spreadsheet and trust summary forecast
  • more timely information to budget holders and decision makers
  • the significant amount of time saved by this automated process.

The Outcomes

  • The primary benefit of the new files is more accurate and timely forecasting. The files can be updated and refreshed by a macro and built-in checks ensure the data balances.
  • Some of the tools and processes used in the files will be built into other files to streamline and simplify other manual and time-consuming work. They will also form part of future standard operating procedures and best practice.
  • The innovation is a platform to build on, there are plans in place to develop the budget holder reports to include the forecasts and to report the forecast at a business unit level and then at a cost centre level.
  • It has been estimated that 47 hours per month of senior manager time has been saved. This has been re-invested into understanding the position with care groups which has led to quicker action to rectify adverse positions and quicker decision making.