Alex Morrison

MI reports - advice please



I have always used the Indicative earnings and monthly payment reports from the ESFA to produce month end reports (prior to that it was the occupancy report). Recently I've had Breaks in learning, Price changes, returning apprentices which produce multiple lines on the Indicative earnings. There doesn't appear to be a consistent rule I can apply to de-duplicate the report. Aim Sequence number works to a certain extent but not always. I use Excel to produce the reports so end up writing IF statements or manually de-duping the sheet to work out numbers In-Learning, those on breaks, those returned from breaks, withdrawn, completions.

The issue really is the breaks in learning. When an apprentice returns a new row is produced but the completion status 6 row remains so a pivot table will either show it as 2 count or if I use a count unique then it will show them as status 6.

What do others use to report:-

Numbers on programme

Funding by apprentice - YTD and across all years?

Thanks for any help or advice you can offer. I've feel like I'm in a bit of a reporting rut at the moment having done the same thing for years.





No one has replied to this post.

Louise Tomkins

Hi Alex,

I also use the indicative and actual month end reports to reconcile my apprentices and funding, I use a mix of pivot tables in the ESFA reports themselves to look at headline finances as well as breakdowns for Co-investment (actual only) and by apprentice.

I then use a separate spreadsheet and use a vlookup against my own occupancy as well as the indicative pivot and the actual pivot, to make sure all of the apprentices appear on the report and to compare the indicative values with the actual, it then highlights the difference for each individual apprentice so I can see where funds have been withheld between the indicative and actual reports.

I have a further report that looks at reconciling the overall funding between the two reports across the academic year and a different tab for each year so it is technically all in one place but allows me to refer back if needed and I make monthly notes of any discrepancies to refer back to.

I also use reconciliation reports from our MIS (PICS) to reconcile the funding apprentice by apprentice and that highlights to me if I have any discrepancies between what my MIS thinks we should have verses both the indicative and the actual reports so I can interrogate specific students if needed.

Not sure if this helps much but I don't tend to worry when reconciling ESFA funding reports about who is on a break or who's TNP has changed as this is more in my ILR data audit checks for accuracy rather than my month end reconciliations.


Paul Taylor

Hi Alex

It seems to me that you are trying to produce summarised income reports and also produce MI from a report which is only designed to give you one of these things.  

You can do both but it may be easier to split these tasks up. An obvious question to ask whether or not your MI this can be produced from your MIS?

The purpose of the indicative funding report to produce funding info. We use templates that we drop the indicative reports into. One containing last month's month end report, and one containing this month's version. Each contains a series of look ups that go and fetch the YTD totals from the monthly income columns to the right so that for R05 we will total the August to December income, and then take this from the previous month's version to give you the net in month income total. For Apprenticeship provision you should use the period end reports as opposed to the indicative reports as they will include the impact of any Apps Service mismatches and also helpfully gives you your net income amount within the report.

If you need to use the indicative report for MI, and excel is your only reporting solution, then again you could create a template that counts starts / funded in learning / leavers / achievers using a series of IF, IFS and AND statements and summary columns;

- create a column which creates start month from the learning start date and use this in a pivot to summarise starts, reporting only on zprog aims and excluding restarts

- create 12 period columns to count your funded in learning per month (August = start date <01/09/20, planned end date >=30/09/20, end date >=30/09/20 or is null etc). Again only report on zprogs. 

Something like thus may help but hopefully you should have a reporting solution in your MIS. Use google to find formula solutions and if stuck get a consultant in to build a few reports for you.

Christina Marfleet


I don't know if this well help, as your excel skills sound above my knowledge and it may not even apply but there is a known issue with the how the reports are dealing with return to learning after a break.

Submit Learner Data known issues - GOV.UK (

Alex Morrison

Thanks for all your responses.

It does appear as if I'm doing similar things to the rest of you which is reassuring.