Funding dashboard calculations and data filters

Overview

This page has the details of the Funding dashboard calculations. Navigate to the sections linked below to see the relevant calculation details.

• Headline
  
  • Page Filters
  • Monthly values
    
    • Programme Funding
    • Learning Support
    • Math & English Programme Funding
    • Additional Payments for Providers
    • Additional Payments for Employers
    • Advanced Loans Bursary Funding
    • Achievement payment
    • Projected Achievement
    • Balancing payment
    • Uplift payments
  • YTD
  • Year Total
• Year to Date
  
  • Page Filters
  • YTD Summaries
  • Potential outcome payments for live learners past planned end date
  • Total YTD
  • Monthly funding values
• Funding Data
  
  • Page Filters
  • On programme funding
  • Total YTD
  • Potential outcome payments for live learners past planned end date
  • Total funding by month
  • Page Filters
  • Total learners including leavers
  • Count of aims from total learners
  • Number of live learners
  • Number of live aims from live learners
  • Number of live aims excluded from batch
  • Count of live learners unfunded / past end
  • Number on learning break
  • Live learners with aim type 1
  • Live without open aim type 1
• Funding Detail
  
  • Page Filters
  • Claim component breakdown by month (current and next three months)
  • Current Month
  • Funding revenue by month
• PMR/TNP Report
  
  • Page Filters
  • TNP total this month
  • PMR total this month
  • TNP total last month
  • PMR total last month
• Missing Aims, Weighting & Errors
  
  • Page Filters
  • Data Filters
    
    • ILRAims
    • Funding
    • Episodes

Headline

Page Filters

No additional filters

Monthly values

Programme Funding

• Monthly on programme payment - OPP (In Month Programme Funding/In Month OPP/FundingInMonth)
  • Funding model is 36 (Apprenticeship)
  • Aim duration at least 1 day.
  • The aim type is 1.
  • On programme in the month
    • Achieved in month.
      OR
    • Finished on last day of month.
      OR
    • In learning in month.
  • 80% * Post code weighting * Total negotiated price (TNP1 per month value)
    ADDED TO
  • End point assessment price (TNP2 per month value)
  • If there has been a change in employer then the residual monthly values (TNP3 & TNP4) replace TNP1 & TNP2 values from the date the change happened.
• Monthly Trainee/Study programme (In Month Programme Funding/In Month Trainee StudyP/FundinginMonth_nonApp)
  • Learner has achieved in the month or finished on the last day of the month or is still in learning.
  • Planned end date is equal to or after the end of the month.
  • Start date is equal to or after the end of the month.
  • Traineeship 16-19 in month
    • Funding model is 25 and Programme Type is 24 (16-19 Traineeship)
    • Aim name does not contain English or Mathematics and Functional Skills or GCSE
    • Learner is in learning or has completed and passed their qualifying period.
    • Provider ESFA weighting * Traineeship 16-18 funding amount * Month duration
      OR
  • Traineeship 19-24 in month
    • Funding model is 35 and Programme Type is 24 (19-24 Traineeship)
    • Aim name does not contain English or Mathematics and Functional Skills or GCSE
    • 80% * Traineeship 19-24 funding amount * Month duration
      OR
  • Study programme 16-19 in month
    • Funding model is 25, Programme Type is empty and Source of Funding is 107 (16-19 Study Programme)
    • Aim name does not contain English or Mathematics and Functional Skills or GCSE
    • Learner has achieved in the month or finished on the last day of the month or is still in learning.
    • Planned end date is equal to or after the end of the month.
    • Start date is equal to or after the end of the month.
    • Learner is in learning or has completed and passed their qualifying period.
    • 80% * Provider ESFA weighting * Study Programme 16-19 funding amount * Month duration
      OR
  • Study programme 19-24 in month
    • Funding model is 25, Programme Type is empty and Source of Funding is 105 (19-24 Study Programme)
    • Aim name does not contain English or Mathematics and Functional Skills or GCSE
    • Learner has achieved in the month or finished on the last day of the month or is still in learning.
    • Planned end date is equal to or after the end of the month.
    • Start date is equal to or after the end of the month.
    • 80% * Provider ESFA weighting * Study Programme 19-24 funding amount * Month duration
      ADD
  • Functional skills funding in month
    • Funding model is 25, Programme Type is empty and Source of Funding is 105 (19-24 Study Programme) or Funding model is 35 and Programme Type is 24 (19-24 Traineeship)
    • Aim name contains English or Mathematics and Functional Skills or GCSE
    • Learner has achieved in the month or finished on the last day of the month or is still in learning.
    • Planned end date is equal to or after the end of the month.
    • Start date is equal to or after the end of the month.
    • 80% * Weighted Aim funding value * Month duration
• Monthly AEB (In Month Programme Funding/In Month AEB/Adult Skills inMonth)
  • Funding model is 35 and Programme Type is not 24 (Adult Skills)
  • Learner has achieved in the month or finished on the last day of the month or is still in learning.
  • Planned end date is equal to or after the end of the month.
  • Start date is equal to or after the end of the month.
  • 80% * Adult skills funding amount * Month duration.

Learning Support

• ALS funding amount as provided by UK Government (In Month LSA/ALS_payment)

Math & English Programme Funding

• English funding in month (In Month Maths & English Programme Funding/In Month English/Eng_FundinginMonth)
  • Aim name contains English and Functional Skills or GCSE
  • Funding model is 36 (Apprenticeship)
  • Learner has achieved in the month or finished on the last day of the month or is still in learning.
  • Planned end date is equal to or after the end of the month.
  • Start date is equal to or after the end of the month.
  • Planned end date is after Start date.
  • Functional skills funding amount as provided by UK Government divided by number of months between start date and planned end date.
• Maths funding in month (In Month Maths & English Programme Funding/In Month Maths/Mth_FundinginMonth)
  • Aim name contains Mathematics and Functional Skills or GCSE
  • Funding model is 36 (Apprenticeship)
  • Learner has achieved in the month or finished on the last day of the month or is still in learning.
  • Planned end date is equal to or after the end of the month.
  • Start date is equal to or after the end of the month.
  • Planned end date is after Start date.
  • Functional skills funding amount as provided by UK Government divided by number of months between start date and planned end date.
• Functional skills in month -Trainee/Study programme (In Month Maths & English Programme Funding/In Month Func Skills (Trainee/StudyP)/FS_inMonth_nonApp)
  • Funding model is 35 and Programme Type is 24 (19-24 Traineeship) or
  • Funding model is 25, Programme Type is empty and Source of Funding is 105 (19-24 Study Programme)
  • Aim name contains English or Mathematics and Functional Skills or GCSE
  • Learner has achieved in the month or finished on the last day of the month or is still in learning.
  • Planned end date is equal to or after the end of the month.
  • Start date is equal to or after the end of the month.
  • 80% * Weighted Aim funding value * Month duration

Additional Payments for Providers

• In Month 16-18 Provider Incentive/FundingDates
  • It is month 4 or if duration is 12 months then month 12 else month 13.
  • A Standard
  • Aim type is 1.
  • Learner has achieved in the month or finished on the last day of the month or is still in learning.
  • Planned end date is equal to or after the end of the month.
  • Start date is equal to or after the end of the month.
  • Age at start of academic year is 18 or less.
  • 50 % * Incentive payment.

Additional Payments for Employers

• In Month 16-18 Employer Incentive/FundingDates
  • It is month 4 or if duration is 12 months then month 12 else month 13.
  • A Standard
  • Aim type is 1.
  • Learner has achieved in the month or finished on the last day of the month or is still in learning.
  • Planned end date is equal to or after the end of the month.
  • Start date is equal to or after the end of the month.
  • Age at start of academic year is 18 or less.
  • 50 % * Incentive payment.

Advanced Loans Bursary Funding

• In Month ADL/FundingDates
  • Funding Model is 99
  • Programme type is blank.
  • ADL is 1
  • Weighted Aim funding value * Postcode weighting * SFA area cost factor for the delivery postcode / prior learning funding adjustment % / number of instalments

Achievement payment

• In Month outcome/FundingDates
  • Achievement date, if not available uses actual end date, is in this month.
  • Learner outcome is achieved.
  • 20% of Aim value

Projected Achievement

• In Month projected outcome/FundingDates
  • Actual end date is empty.
  • Planned end is after today and before end the month.
  • 20% of Aim value.

Balancing payment

• In Month Balancing payment/FundingDates
  • Achievement date, if not available uses actual end date, is in this month.
  • Learner outcome is achieved.
  • Actual end date is before planned end date.
  • Monthly amount * number of months remaining.

Uplift payments

• In Month 16-18 uplift/FundingDates
  • Learner has achieved in the month or finished on the last day of the month or is still in learning.
  • Planned end date is equal to or after the end of the month.
  • Start date is equal to or after the end of the month.
  • Aim type is 1.
  • Frameworks only.
  • Age at start of academic year is 18 or less.
  • 20% of
    • Funding model is 36 (Apprenticeship)
    • Aim duration at least 1 day.
    • The aim type is 1.
    • On programme in the month
      • Achieved in month.
        OR
      • Finished on last day of month.
        OR
      • In learning in month.
    • 80% * Post code weighting * Total negotiated price (TNP1 per month value)
      ADDED TO
    • End point assessment price (TNP2 per month value)
    • If there has been a change in employer then the residual monthly values (TNP3 & TNP4) replace TNP1 & TNP2 values from the date the change happened.

YTD

• Sum of monthly payments by funding line up to the current month.

Year Total

• Sum of all monthly payments by funding line for the current academic year.

Year to Date

Page Filters

• Category is not Other Non-ESFA Funding and is not Non Funded

• flg_current_acytd is 1

YTD Summaries

• YTD Total = Sum of monthly payments up to the current month
• YTD OPP (app) = Monthly on programme payment
• YTD Maths (app) = Maths funding in month
• YTD English (app) = English funding in month
• YTD LSA (app) = ALS funding amount as provided by UK Government
• YTD 16-18 Framework uplift = Uplift payments
• YTD 16-18 employer incentive (std) = Additional Payments for Employers
• YTD 16-18 provider incentive (std) = Additional Payments for Providers
• YTD Outcome (app) = Achievement payment
• YTD projected outcome (app) = Projected Achievement
• YTD trainee / study programme = Monthly Trainee/Study programme
• YTD func skill (trainee / study programme) = Functional skills in month -Trainee/Study programme
• YTD AEB skills = Monthly AEB
• YTD ADL (app) = Advanced Loans Bursary Funding
• YTD balancing payments = Balancing payment

Potential outcome payments for live learners past planned end date

• Learner has no actual end date.
• Planned end date is on or before today and before end the month.
• Category not 16-19 Traineeship.
• 20% of Aim value.

Total YTD

• Sum of monthly payments up to the current month.

Monthly funding values

• Headline monthly values in graphical form, showing each funding line.

Funding Data

Page Filters

• Subscriptions status is not cancelled or expired.
• User's full name does not contain “test” or “Do not use”.
• User is of type “user”.

On programme funding

• Count learners with funding.
  • Learner has achieved in the month or finished on the last day of the month or is still in learning.
  • Planned end date is equal to or after the end of the month.
  • Start date is equal to or after the end of the month.
• Current month
  • Sum of monthly payments for the current month.
• Next month
  • Sum of monthly payments for the next month.

Total YTD

• Sum of monthly payments up to the current month.

Potential outcome payments for live learners past planned end date

• Learner has no actual end date.
• Planned end date is on or before today and before end the month.
• Category not 16-19 Traineeship.
• 20% of Aim value.

Total learners including leavers

• Count of distinct learners from ILRAims OData feed where
  • Aim is not excluded from batch
  • ILR status is not blank, excluded or unknown

Count of aims from total learners

• Count of the Id from ILRAims OData feed

Number of live learners

• Count of distinct learners from ILRAims OData feed where
  • Aim is not excluded from batch
  • ILR status is not blank, excluded or unknown
  • Actual end date is blank

Number of live aims from live learners

• Count of the Id from ILRAims OData feed where
  • Aim is not excluded from batch
  • ILR status is not blank, excluded or unknown
  • Aim status is 1

Number of live aims excluded from batch

• Count of the Id from ILRAims OData feed where
  • Aim is excluded from batch
  • Aim status is 1

Count of live learners unfunded / past end

• Count of distinct learners from ILRAims OData feed where
  • Aim is not excluded from batch
  • ILR status is not blank, excluded or unknown
  • Actual end date is blank
  • Planned end is on or before today

Number on learning break

• Count of distinct learners from ILRAims OData feed where:
  • Aim is not excluded from batch
  • ILR status is not blank, excluded or unknown
  • Aim with the latest start date, for the learner, has a status 6

Live learners with aim type 1

• Count of distinct learners from ILRAims OData feed
  • No additional filters (only the page filters are applied)

Live without open aim type 1

• The difference between
  • Count of distinct learners from ILRAims OData feed where
    • Aim is not excluded from batch
    • ILR status is not blank, excluded or unknown
    • Actual end date is blank
    • Aim type = 1
      AND
  • Count of distinct learners from ILRAims OData feed where
    • Aim is not excluded from batch
    • ILR status is not blank, excluded or unknown
    • Actual end date is blank

Total funding by month

• Headline monthly values in graphical form, showing total only.

 

Funding Detail 

Page Filters

• Subscriptions status is not cancelled or expired

• Users fullname does not contain “test” or “Do not use”

• User is a type “user”

Claim component breakdown by month (current and next three months)

• Graph showing the monthly payments by funding line over 4 months.

Current Month

• Total amount for all funding lines in the current month.

Funding revenue by month

• No additional calculations. Breaks down the monthly payments for the current academic year by funding line per standard, can be drilled down to learner.

 

Calculations and Page Filters

Page Filters (across all pages)

• Users fullname does not contain “test” or “Do not use”
• User is a type “user”
• AimDuration is greater than 0
• EpisodeStatus is not Unknown or Blank
• ULN is not 9999999999

A new Subscription status filter that has been add to all pages. The new filter will allow users to add learners that have a subscription status of Cancelled or Expired to be displayed within the dashboard, by default they are excluded.

 

PMR/TNP Report

Page Filters

• Subscriptions status is not cancelled or expired.
• User's full name does not contain “test” or “Do not use”.
• User is of type “user”.
• Learner’s name does not contain “test” or “DO NOT USE”.
• Learner’s ULN is not blank or “9999999999”.
• Learner’s ILR status in not “Unknown” or “Excluded”.
• Aim is not excluded from batch.

TNP total this month

• Sum of Amount from ApprenticeshipFinancialRecords OData feed where:
  • Aim is not excluded from batch.
  • ILR status is not excluded or unknown.
  • User's full name does not contain “test” or “Do not use”.
  • Learner’s ULN is not blank or “9999999999”.
  • Type = TNP.
  • Month of date is in the month of today.

PMR total this month

• Sum of Amount from ApprenticeshipFinancialRecords OData feed where:
  • Aim is not excluded from batch.
  • ILR status is not excluded or unknown.
  • User's full name does not contain “test” or “Do not use”.
  • Learner’s ULN is not blank or “9999999999”.
  • Type = PMR.
  • Month of date is in the month of today.

TNP total last month

• Sum of Amount from ApprenticeshipFinancialRecords OData feed where:
  • Aim is not excluded from batch.
  • ILR status is not excluded or unknown.
  • User's full name does not contain “test” or “Do not use”.
  • Learner’s ULN is not blank or “9999999999”.
  • Type = TNP.
  • Month of date is in the month before today.

PMR total last month

• Sum of Amount from ApprenticeshipFinancialRecords OData feed where:
  • Aim is not excluded from batch.
  • ILR status is not excluded or unknown.
  • User's full name does not contain “test” or “Do not use”.
  • Learner’s ULN is not blank or “9999999999”.
  • Type = PMR.
  • Month of date is in the month before today.

 

Missing aims, Weighting & Errors

Page Filters

• Subscriptions status is not cancelled or expired
• Users fullname does not contain “test” or “Do not use”
• User is a type “user”

There are no calculations on this page

 

Data Filters

ILRAims

• Programme type is 2, 3, 20, 21, 22, 23, 24, 25 or empty
• Aim start date and end date and different

Funding

• All funding calculations are for the current accademic year.

Episodes

• ILR status is not blank, Excluded or Unknown
• Aim is not excluded from batch
• Learner’s name does not contain “test” or “DO NOT USE”