Budget Tracker
The Budget Tracker records detailed costs and funding on staff posts (pay) and development
items (non-pay), and provides financial reports on this information.
Processes
The Budget Tracker supports the following processes:
- Supports WTE-based, Activity-based, and Bed-based funding by HSE
- Automated lookup of annual earnings based on approved post
- Records developments for pay (posts) and non-pay items (equipment etc)
- Records details required for cost tracking, incl start & end dates
- Records relevant details from HSE funding letters
- Automated calculation of Costs (YTD and EOY)
- Automated calculation of Budget Due (YTD and EOY)
- Automated calculation of HSE Predictor and incremental budget
- Automated projection of future costs by month
- Reports and dashboards with Costs vs Funding analysis
- API-enabled for data integration with other systems
Update ▸ Developments
When you open this screen, you can optionally select from a number of filters. If you leave a
selection emtpy, all information will be retrieved. It is recommended to use selections, so you
don't have too much data on screen. Once the data is on the screen, you can use the column
filters or the Find function (via Ctrl+F or via the Actions menu) to find records.
You can also select a Period for YTD, which is being used for the calculated YTD amounts
that are shown on screen. You can select any period, it has no impact on how you enter or update
the information itself.
You can enter all relevant details on staff posts and development items, as well as relevant
details on the associated HSE funding letters. Click the SAVE button (or Ctrl+S) when
done.
If you want to delete a record, choose DELETE as the funding driver.
These are the most important fields:
- Funding driver - select Activity, Beds, or WTE. This determines
how the item is being treated in terms of HSE funding. Select DELETE if you want to
permanently delete a record.
- Predictor Category - select the relevant HSE category for each post or item.
- Source - select the relevant HSE category for each post or item.
- Group/Hospital - select the relevant hospital for each post or item.
- Programme - select the relevant programme or activity for each post or item.
- Our Reference - enter the internal reference for each post or item.
- Approved Post - select a post from the PCC list, or one of the "Non-pay" items.
- Grade Earnings - this will automatically show the gross salary of the selected post.
- Earnings - only enter an amount if you want to deviate from the gross salary.
- WTE - enter the "Whole Time Equivalent" factor (between 0.00 and 1.00).
- P/NP - select whether this is a Pay post, or a Non-pay item.
- Recurring - select whether this post or item is recurring (Recurring) or NR
multi-month, or one-off (NR In-month).
- Start date - enter the estimated start date of each post or item. This can still be
confirmed in the field "Start Date Confirmed".
- HSE End Date - enter the end date as listed on the HSE funding letter.
- End Date - enter the real expected end date (a post or item may run longer than
listed on the HSE funding letter).
- Date Approval Letter - enter the date of the HSE funding letter, for reference.
- Primary Notification Number - enter the number on the HSE funding letter, for
reference.
- Allocated This Year - enter the (cumulative) amount that HSE has already approved for
this year.
All other fields are for reference only, and are not used in the calculation of amounts.
Note: The fields Funding driver, Predictor Category, Source, and Group/Hospital are
mandatory, and records without all these 4 fields filled in are considered incomplete and
will not be saved.
Click on the icon in the 1st column to edit a record, to insert a new record, or to delete a
record. Click on the SAVE button (or Ctrl+S) to save your changes.
A red, orange, or green icon on each row indicates validation:
◉ - item has an HSE End date but no End
date
◉ - item has a Start date which is later than
the HSE End date or End date
◉ - item has Recurring = "NR In-month" but Start
date differs from End date
◉ - item has no HSE End date or End
date
◉ - item has plausible start dates and end
dates
Update ▸ Activities & beds
Staff posts and development items that have a funding driver WTE are funded by HSE on an
individual level, can you can record these in the Developments screen.
Activities and bed openings are funded by HSE on a total basis, per programme, and the
calculations work differently for that reason.
In this screen you can enter the programmes for activities and beds (each on a separate tab) with
the associated start and end dates, and activity targets. The Budget Tracker will combine the
costs of the staff posts and development items (by programme) with the HSE funding of that
programme.
You can update all relevant details and add or delete records. Click the SAVE button (or
Ctrl+S) when done.
Update ▸ Create a Version
This option enables you to create (or refresh) a full copy (a Version) of all staff posts
and development items. Versions are saved per the selected month. If you run this multiple times
for the same month, the last version always replaces any previous versions for that same month.
All reports contain a field Version, which contains either "NOW" or the month in which
the version was copied.
Calculations
Based on the information, the Budget Tracker automatically calculates a number of financial
amounts, and makes these visible across multiple screens and reports. The amounts can not be
manually changed, they are calculated from the information that is recorded for staff posts and
development items. The amounts are the following:
- Annual Cost - the full annualised cost of an item if it was carried for a full year.
If the item has no start date, or if its End date is in a previous
year, the annual cost is set to zero.
- Cost YTD - the cost for this year, up to the selected period, based on the
start date and End date.
- Cost This Year - the full cost for the entire year, based on the start
date and End date.
- Budget Due YTD - the budget that is due from HSE, up to the selected period,
based on the start date and HSE end date.
- Budget Due EOY - the full budget that is due from HSE for the entire year,
based on the start date and HSE end date.
- Predictor - the predicted amount for the full year, based on extrapolating
the costs in the last 3 months. This amount is always extrapolated up to the end of the
year, ignoring any end date (thus replicating the calculation made by the HSE
Predictor system itself). The Predictor is set to ZERO for items without Start Date
or with an HSE End date in the previous calendar year.
- Incremental HSE - the net difference between Cost This Year, Cost YTD,
and Predictor.
See this section for a detailed overview of all formulas.
Please note:
- Items with an End date in a previous year are not included in Annual
Cost, Cost YTD, and Cost This Year.
- Items with an HSE End date in a previous year are not included in Budget Due
YTD, Budget Due EOY, and Predictor.
Reports
When you run reports, you can optionally select from a number of filters. If you leave a
filter emtpy, all information will be retrieved.
You can also select a Period for YTD, which is being used for the calculated YTD amounts
that are shown in the report. These are calculated in realtime.
Once the report is on the screen, you can use the Find function (via Ctrl+F or via the
Actions menu) to find records.
Reports ▸ Developments
This report shows the costs and funding of all staff posts and development items, for all
funding drivers.
The report has multiple dynamic layouts, which you can edit (if your user permissions allow
that). To edit a layout, select Actions ▸ Change layout. To export a report to Excel or
PDF, select Actions ▸ Export or print.
Reports ▸ Development History
This report shows the current data ("NOW") side-by-side with the selected VERSION
of the costs and funding of all staff posts and development items.
Reports ▸ Activity & bed report
This report shows the costs and funding of programmes for activities and beds (each on a
separate tab) with the associated start and end dates, and activity targets. The report combines
the costs of the staff posts and development items (by programme) with the HSE funding of that
programme.
Reports ▸ Predictor Upload
This report generates export data for upload into the HSE Predictor System (v4.7 for 2023). It
calculates Incremental amounts by month for the current year, and Annual Cost amounts by month
for the following year, either for the current data or for any saved version. To export this
data to an Excel or CSV file, select Actions ▸ Export or print.
Support
XLReporting uses the latest technologies (HTML5/CSS3) and works on any modern web browser such as
Chrome, Safari, Firefox, Opera, Edge. Chrome offers the best performance, but the choice is
yours. Internet Explorer is not supported, as it has known security weaknesses and (as of 2022)
is no longer supported by Microsoft. Safari version 8 and older is also no longer
supported.
To work efficiently with XLReporting, please note that:
- Your computer needs an internet connection
- Your screen must be at least 1024px wide
- Your computer needs at least 2Gb memory
- Your browser needs to allow javascript
The Budget Tracker uses the following formulas: (these can be read in Excel-style)
Annual Cost
IF(OR([Start Date] = "", PERIODS([End Date], SETPERIOD([Period for YTD]),
1)) > 0), 0, IFZERO([Earnings], [Grade Earnings]) * IFZERO([WTE], 1))
Annual HSE
IF(OR([Start Date] = "", PERIODS([HSE End Date], SETPERIOD([Period for
YTD], 1)) > 0), 0, IFZERO([Earnings], [Grade Earnings]) * IFZERO([WTE], 1))
Cost YTD
[Annual Cost] * IF([Recurring]= "NR In-month", IF(PERIODS([End Date], [Period
for YTD]) >= 0, 1, 0), PERIODSWITHIN(YEAR([Period for YTD]), [Period for YTD], [Start Date],
[End Date]) / 12)
Cost This Year
[Annual Cost] * IF([Recurring]= "NR In-month", 1,
PERIODSWITHIN(YEAR([Period for YTD]), YEAR([Period for YTD]), [Start Date], [End Date]) /
12)
Budget Due YTD (for WTE-based funding)
([Annual HSE] - [Allocated This Year]) * IF([Recurring]= "NR In-month",
IF(PERIODS([HSE End Date], [Period for YTD]) >= 0, 1, 0), PERIODSWITHIN(YEAR([Period for YTD]),
[Period for YTD], [Start Date], [HSE End Date]) / 12)
Budget Due EOY (for WTE-based funding)
([Annual HSE] * IF([Recurring] = "NR In-month", 1,
PERIODSWITHIN(YEAR([Period for YTD]), YEAR([Period for YTD]), [Start Date], [HSE End Date]) /
12)) - [Allocated This Year]
Budget Due YTD (for activity-based funding)
Budget Due EOY (for activity-based funding)
Predictor
IF([Recurring] = "NR In-month", 0, PREDICT([Annual
HSE] / 12, [Period for YTD], [Start Date]))
Incremental
IF([Recurring] = "NR In-month", IF(PERIODS([Period for YTD], [Start Date]) >
0, [Cost This Year], 0), MAX(0, [Cost This Year] - [Cost YTD] - [Predictor]))
Recommended reading:
Back to top