Today I am specifically analyzing the Doing Business Report 2011 in Eastern Africa. I am aware only yesterday (27th June 2012) the World Bank released it's World "Doing Business Report for 2012". But I will be using the 2011 data. I will be doing a comparative analysis in my next posting.
During the 2011 period 47 countries were surveyed in the Sub Sahara Africa (SSA). In the world, 183 economies were ranked. Rankings can be read here.
Back to what I want to post about. My analysis is specifically for Eastern Africa Community (Kenya, Uganda, Rwanda, Burundi and Tanzania) - I hear South Sudan and Somalia have applied for membership though they were not among those economies surveyed.
Of the 47 economies, I would like to extract and rank the 5 Eastern Africa states using VLOOKUP, HLOOKUP and RANK.
Step1: Naming the data: On the"Rankings Data" sheet, I gave a name called "data" to all the economies starting A1:L47
Step2: The sheet "EAC Analysis", I would like to extract the data for Kenya on cell B3 using the formula: =VLOOKUP($B$2,data,2,0)
Where B2 (with dollar signs to reference it absolutely) referring to Kenya, "data" is the named ranged for the 47 economies, 2 is the column referencing the data "Ease of Doing Business Rank" in column B (Here VLOOKUP counts from left)
When pulled down (please not you will have to edit the columns to 3,4,5,... depending on the column you want to reference.
The table will look like this:
| World 2011 Doing Business Report - Eastern Africa | |||||
| Metric | Kenya | Uganda | Rwanda | Tanzania | Burundi |
| Ease of Doing Business Rank | 9 | 12 | 3 | 14 | 35 |
| Starting a Business | 25 | 27 | 1 | 20 | 14 |
| Dealing with Construction Permits | 2 | 21 | 13 | 45 | 39 |
| Getting Electricity | 19 | 27 | 2 | 8 | 34 |
| Registering Property | 27 | 22 | 5 | 37 | 17 |
| Getting Credit | 2 | 6 | 2 | 12 | 39 |
| Protecting Investors | 16 | 23 | 3 | 16 | 5 |
| Paying Taxes | 36 | 14 | 3 | 25 | 23 |
| Trading Across Borders | 20 | 34 | 31 | 7 | 42 |
| Enforcing Contracts | 23 | 20 | 3 | 1 | 41 |
| Resolving Insolvency | 12 | 3 | 36 | 19 | 38 |
Now from the table above, I would like to extract the rankings on row 2 using HLOOKUP ("Ease of Doing Business Rank" and using the RANK formula to get the rank in Eastern Africa:
The synstax will go like this: =HLOOKUP(A17,$A$2:$F$13,2,0) - Here HLOOKUP looks for data in a row, by looking for A17 (The country) in the data A2:F13 (The table above) and giving us what is row number 2 and "0" for "Exact match"
Next we use the RANK formula to rank the economies based on the ranking displayed by the formula above (HLOOKUP) in column B17:B21.
The sytax for RANK is =RANK(number, ref,[order]). Here the number is the actual number you want to rank, ref - is the array, while Order - is either descending "0" or ascending - "1"
The formula on column C "rank" will appear like this: =RANK(B17,$B$17:$B$21,1)
Here I am using 1 as the order since I want the country with the biggest number to appear as a last performing in terms of doing business.
Conclusion: in 2011 Rwanda was ranked first in doing business report, ranking 3rd in Sub Sahara Africa. Mauritius and South Africa came first and second respectively while Kenya came a distant 9th.
-----------------------------
Download the detailed analysis here
Want more analysis email me
0 comments:
Post a Comment