Can not reconcile Monthly Apps report with Indicative earnings.

Hi Sophie,

I have been using the indicative and period end report for some time now and it does take some time to find your groove and how you want your data to appear. 

I use Pivot tables to collate the data together in each, although some of the fields are different across the reports which doesn't help, you always need to keep coming back to year to date figures and take into consideration any co-investment (this will show on the actual but won't technically be in the totals paid, i.e. £1,000 due, £50 co-investment to collect, the actual will only show £950 paid).

I also keep a manual spreadsheet that I update monthly with the indicative totals per month versus the actual, this then highlights the difference and I can use it to keep a running total of co-investment etc. I save a new version each month and update so I always have last month as a back up if needed.

I also have a vlookup spreadsheet that looks learner by learner against both the indicative and actual reports and highlights any differences in the YTD funds for each, this allows me to them note co-investment or to investigate other potential issues on a learner by learner basis.

What I would say is not to solely rely on the ESFA reports, I had an apprentice move employer last year, we updated the ILR as required but to date have received no funds for the new employer, both the indicative and actual reports match and no issues highlighted on the data match report, so always worth periodically doing a full check of all learners to make sure you are receiving all funds due.

I hope this helps somewhat.

Kind Regards

Louise

Yeah, no, that isn't going to happen. The Indicative is a dumb, dumb, dumb report and will never reconcile to the actual money. Let me count the ways:

1. doesn't take off employer contribution, even for non-levy
2. includes completion even where you haven't been paid it 
3. can't know if a learner is DLOCKed
4. can't know if a levy learner fell out of funding and switched to 95/5

Also the monthly profile will be off because it's looking at this month's ILR as though it's gospel and everyone started/withdrew/went on a break on the date it says rather than, as is inevitable, that change not appearing in the ILR til the month after. The Apps Monthly Payment report is the real actual money that was generated/clawed back for each learner *in the month it actually happened*.

It's *not bad* at *roughly* forecasting future income for the learners currently on board, but that's about all.