Sensei-san
Active Member
It looks beautiful.Calculating Quotas – A Step-to-Step Guide
I wanted to wait for the impending publication of FY 2014 LPR flow data but have decided to write a commentary to the calculations early for two reasons. First, you should be able to do that by yourself and, second, I have to make corrections to the DV-2014 and DV-2015 quotas.
Preparing DV-2016 I noticed a change in how the Visa Office treat Western Sahara. Up to fiscal year 2013 the disputed territory was listed as a dependent area of Spain and therefore charged to Europe. Now, as of FY 2014 the reports list it under Morocco. Looking up 9 FAM 42.12 Exhibit II about dependent areas (10/02/2014) confirms Western Sahara's chargeability to AF region. The corrections are minor but interesting and affect Africa and Europe only.
Have the following documents at hand:
– Report of the Visa Office 2014, Table VII
– 9 FAM 42.12 Exhibit I and II (foreign countries and dependent areas)
– DV-20## instructions (list of regional chargeability and eligibility)
– 9 FAM 42.33 (the law -- recommended reading for people suffering from insomnia)
– 9 FAM 42.33 Notes and Procedural Notes (interesting reading but not necessary)
I will calculate DV-2014 quotas.
The formula in 9 FAM 42.33 contains two ingredients, flow of lawful permament residents and population of eligible countries. I will show how to collect the data and how to aggregate it. But first:
Step 1 [MOST IMPORTANT]
Use 9 FAM 42.12 and the DV instructions and create an alphabetical "Master List" of all countries/dependent areas indicating proper regional chargeability and eligibility, as complete as possible:
So Anguilla is charged to Europe but being part of the U.K. it is ineligible. Northern Ireland is also part of the U.K. still Northern Ireland is eligible in the DV program (Instructions!). I keep Northern Ireland separated from the alphabetical list and use a 0/1 eligibility marker which turns into a weight factor later on.
Step 2 LPR Flow Data
DV-2014 needs the lawful permament resident flow of the "most recent 5 fiscal years", i.e. 2008-2012 at the time of the draw. You find these on http://www.dhs.gov/yearbook-immigration-statistics. If you go to the 2010 Yearbook | LPR Data Tables subpage you can open Table 10:
9 FAM 42.33 uses the Family sponsored, Employment based, and Immediate Relatives categories only. So just forget any totals or summaries in the table (wrong category and/or wrong regional assignment)!
Open an empty sheet LPR2010 and copy-paste your master list into it w/o eligibility marker and w/o Northern Ireland. Now copy-paste from Table 10 the range from Afghanistan down to Zimbabwe and the necessary LPR categories:
If the range doesn't align simply shift your master columns and assign the proper regional code. You will usually have to make four to five such corrections. If everything aligns properly, delete the master country column, add up the three categories countrywise and afterwards the regional flow with a command like SUMIFS(C$2:C$250,A$2:A$250,"=AF") for Africa:
Since you need these summaries for several fiscal years, keep record in a separate LPR Flow sheet:
For DV-2014 add up for each region the numbers for the fiscal years 2008 up to 2012. That's the first ingredient of the formula.
Step 3 Population of Eligible Countries
Go to http://www.census.gov/population/international/data/countryrank/rank.php and choose the year 2012 – the most recent real world population data at the time of the draw. Copy-paste the table into a sheet, get rid of the rank numbers and sort alphabetically. Open a new sheet and copy-paste your master list into it w/ Northern Ireland and w/ eligibility marker. Take the pulled population and align it to Afghanistan. No shifting should be necessary but check! Delete one country column:
For Northern Ireland head over to http://www.nisra.gov.uk/ where you find key-statistics about Northern Ireland like 2014 mid-year population which you need for DV-2016. For 2012 population go to the subpage http://www.nisra.gov.uk/demography/default.asp17.htm where you find a spreadsheet containing Historical Population Totals.
In our spreadsheet multiply the population column (C) with the eligibility column (A) to get column D of the eligible population. Using a SUMIFS command again we arrive at the summary, the second ingredient of the formula.
Step 4 Regional Quotas
Open a new sheet DV-2014 and copy-paste the two ingredients into it:
The LPR flow total ist 4255k. If the flow were uniform each region would account for 1/6 of the flow, i.e. 700k. Comparing the regions with this number one understands why AS/SA are deemed high admission regions, whereas AF/EU/OC/NA are low admission regions. For the high/low split we get 3465k/4255k = 0.81446 and 0.18554, respectively.
The DV program promotes the low admission regions by reversing the high/low split. So, if 100,000 DV visas were to be distributed AF/EU/OC/NA would get 81,446 of these and AS/SA the rest (red numbers, 3rd column).
Within each group the visas are now allocated according to the population split within the group. So AF regions gets 1074M/1953M*81,446 = 44,799 visas (blue numbers). In the same vein you get the quotas for the other regions.
Regional Quotas DV-2014 and DV-2015
As I said the changes are minute only but the comparison of DV-2014 with its target quotas is stunning:
The Visa Office's numerical workings in EU, AS and SA regions is nothing but pristine perfection.
DV Regional Quotas Template
So for 2015 you would use 2009-2013 flow and 2013 population?
