Case: Efficiently reducing the asylum process backlog
Case description
From refugees to specialised knowledge migrants: the Immigration and Naturalisation Service (IND) assesses all applications from foreign nationals who want to live in the Netherlands or want to become Dutch citizens.
In recent years, the large number of applications has resulted in a backlog of thousands of asylum applications. This is troublesome, both for the asylum seekers who are waiting for their application review, as well as for the Immigration and Naturalisation Service, which has to pay penalties for long asylum processes. For that reason, the IND has setup a task force to reduce the backlog. This raises a question: how can this task force be used efficiently? That's where you come in to help the IND!
Case data
Asylum applications:
- 4000 asylum applications, also called cases
- Each case is from a different applicant
- Each applicant has one nationality (from a list of 10 nationalities)
- All cases are registered between August 31st 2024, and December 31st 2024
- Legal review period: 125 days
- The legal review period starts on the registration day
- After this review period, the asylum applicant is entitled to claim a compensation
Compensation:
- If an applicant claims a compensation, it is claimed until the asylum application is in review
- Compensation amount: €250 per day until the application is in review
- There are two moments in time that an applicant (a) may claim a compensation
- Time T1(a): Directly after the review period of applicant a is over
- Time T2(a): 30 days after the review period of applicant a is over
Not every applicant claims a compensation, instead, this is probabilistic.
- Probability P1(a): The probability that an applicant (a) claims a compensation on T1
- Probability P2(a): The probability that an applicant (a) claims a compensation on T2
- Both probabilities are determined as follows:
- P(T,a) = b*x(T,a) + c(T,a)
- b = 0.001
- x(T,a) = number of open cases at time T(a) (= date T1(a) or T2(a)) - cases that are registered, but not yet reviewed before this date - with the same nationality as the applicant’s nationality
- c(T,a) = parameter, which varies per nationality of applicant a, and for T1 and T2
Employees:
- 50 employees
- Employees can start working on the backlog from January 1st 2025 onwards
- Each employee can review at most 1 application per day
- Each employee has its own competencies: a set of nationalities (from a list of 10 nationalities) for which the employee can review applications
- Each employee can review at least one nationality
- Each employee has its own availability (work roster)
- Each employee has a fixed work roster of at least 1 day per week
- Employees only work on weekdays (Monday – Friday)
Nationalities:
- 10 nationalities
- Each case (applicant) has one nationality
- The probability that an applicant claims a compensation, varies per nationality
- Depending on the constant probabilities for T1 and T2 of that nationality
- Depending on the number of open cases of that nationality
- Each employee can review at least one nationality
Exercise
We want to know the order in which the applications should be reviewed.
Provide a sort order for all 4000 asylum applications.
- We are interested in a quantitatively substantiated sort order, however, it is also important to take ethics into account.
- What happens if you take an unethical approach?
- Use Excel to create a sort order
- Fill in the sort order in the Anago application
- You can copy and paste the sort order for all 4000 cases at once from Excel to the Anago application. Be aware that pasting can take about 30 seconds.
Data
Penalty per day: 250 euro
Legal review period: 125 days
| Data | Probability C1 | Probability C2 |
|---|---|---|
| Afghan | 0,2 | 0,27 |
| Chinese | 0,4 | 0,2 |
| Colombian | 0,3 | 0,16 |
| Egyptian | 0,1 | 0,08 |
| Eritrean | 0,2 | 0,31 |
| Gambian | 0,4 | 0,61 |
| Iraqi | 0,2 | 0,25 |
| Iranian | 0,3 | 0,15 |
| Syrian | 0,1 | 0,31 |
| Turkish | 0,1 | 0,34 |
Tip: Start with a simple approach and build on it.
To help you with problem we provide you with some Excel function that you can use. You are free to use other functions of course!
| Function | Description | More information |
|---|---|---|
| =SORT(array,[sort_index],[sort_order],[by_col]) | SORT returns a sorted array of the elements in an array. The returned array is the same shape as the provided array argument. | https://support.microsoft.com/en-us/office/sort-function-22f63bd0-ccc8-492f-953d-c20e8e44b86c |
| =SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2],…) | he SORTBY function sorts the contents of a range or array based on the values in a corresponding range or array | https://support.microsoft.com/en-us/office/sortby-function-cd2d7a62-1b93-435c-b561-d6a35134f28f |
| =FILTER(array,include,[if_empty]) | The FILTER function allows you to filter a range of data based on criteria you define | https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759 |
| =XLOOKUP(lookup_value, lookup_array, return_array,) | Use the XLOOKUP function to find things in a table or range by row | https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929 |
| =SUM(number1,[number2],...) | The SUM function adds values. You can add individual values, cell references or ranges or a mix of all three | https://support.microsoft.com/en-us/office/sum-function-043e1c7d-7726-4e80-8f32-07b23e057f89 |
| =SUMIF(range, criteria, [sum_range]) | Use the SUMIF function to sum the values in a range that meet criteria that you specify | https://support.microsoft.com/en-us/office/sumif-function-169b8c99-c05c-4483-a712-1697a653039b |
| =COUNT(value1, [value2], ...) | The COUNT function counts the number of cells that contain numbers, and counts numbers within the list of arguments | https://support.microsoft.com/en-us/office/count-function-a59cd7fc-b623-4d93-87a4-d23bf411294c |
| =COUNTIF(range, criteria) | Use COUNTIF to count the number of cells that meet a criterion | https://support.microsoft.com/en-us/office/countif-function-e0de10c6-f885-4e71-abb4-1f464816df34 |
| =MIN(number1, [number2], ...) | Returns the smallest number in a set of values. | https://support.microsoft.com/en-us/office/min-function-61635d12-920f-4ce2-a70f-96f202dcc152 |
| =MAX(number1, [number2], ...) | Returns the largest value in a set of values. | https://support.microsoft.com/en-us/office/max-function-e0012414-9ac8-4b34-9a47-73e662c08098 |
| =ABS(number) | Returns the absolute value of a number. | https://support.microsoft.com/en-us/office/abs-function-3420200f-5628-4e8c-99da-c99d7c87713c |
Case data
1. Go to 'Case and employee data'
2. Click on the file icon in the taskbar and choose export to csv