Eco2117 Midterm Part 2 Winter 2022
Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit
Midterm Part 2
Eco2117 Winter 2022
Questions:
1. Open “hh” sheet, which consists of household level variables1 . Answer the following questions in the excel file. Note: I need to see the formula you used and not only a number. [5 marks]
i . How many variables are there? ______
ii. How many observations (households) are there? ______
2. There are four regions. Household characteristics may vary by region. Create and fill the following table in the excel file (Hint: you need to use an excel the appropriate command with an if condition). Note: I need to see the formula in excel and not only the result you submit in this word
document. Note that in the data Chittagong is written chittagon and Rajshahi ragfhahi. [20 marks]
|
Dhaka |
Chittagong |
Khulna |
Rajshahi |
Total number of households |
|
|
|
|
Total number of individuals (hint: looks at the size of the family) |
|
|
|
|
Percentage of households who have toilets (hint: review of to compute a percentage) |
|
|
|
|
Average distance to paved road |
|
|
|
|
3. Based on your calculations which of these four regions has the largest deprivation in terms of sanitation (toilets)? Explain? [5 marks]
Note: Once you have filled this table in the excel sheet with your formulas and you have the answer, copy the cells in which you have calculated the numbers for the table and the use pasteàspecialà “values”. Place these numbers below the actual tables you filled in the excel. It will look like a duplicate of the table and that is perfectly fine for me. This will allow me to read your work even if file gets corrupted.
4. Open the “ ind1” sheet. This file consists of information on household members at the individual
level (the previous file had information on households in general). Merge (i.e., link) this data with the data from the “ hh” sheet using hhcode (to identify to which household information an individual should be linked) as a way to link them (hint: (1) you can use vlookup and, (2) you only need to merge the variables that you need to produce the results below2 ). [5 marks]
List the variables that you decided to keep here: _________________________________________
Copy and paste your excel command here: ______________________________________________
5. Now open “ind1b” sheet and answer the following questions in the excel sheet and report your work in this word document as well :
i. For individuals who are 15 years and older Regional variation [10 marks]
|
Dhaka |
Chittagong |
Khulna |
Rajhshahi |
Average years of schooling |
|
|
|
|
Sex ratio (% of the household that is female) sex=1 if male |
|
|
|
|
|
|
|
|
|
Note: Once you have filled this table in the excel sheet with your formulas and you have the answer, copy the cells in which you have calculated the numbers for the table and the use pasteàspecialà “values”. Place these numbers below the actual tables you filled in the excel. It will look like a duplicate of the table and that is perfectly fine for me. This will allow me to read your work even if file gets corrupted.
ii. Gender differences [15 marks]
|
For Males |
For Females |
Average schooling years (ages>=5 years) |
|
|
Average school years age <15 |
|
|
Average working hours per month (All ages, include all working hours) Hint: Make sure you do not count empty cells |
|
|
Note: Once you have filled this table in the excel sheet with your formulas and you have the answer, copy the cells in which you have calculated the numbers for the table and the use pasteàspecialà “values”. Place these numbers below the actual tables you filled in the excel. It will look like a duplicate of the table and that is perfectly fine for me. This will allow me to read your work even if file gets corrupted.
6. Open “comsume” sheet It has household level consumption expenditure information. Merge it with “ hh” sheet (using hhcode). Create three new variables per capita food expenditure (pcfood), per capita ordinary non-food expenditure (call it pcnonfood) and per capita total expenditure (tpcexp) it is already in the file, so double check your calculations by comparing both. [5 marks].
7. Now open “Consumeb” sheet Compute in the excel sheet the Average per capita expenditure for the following (Also, complete the table this word document). [15 marks]
|
Pcfood |
Pcexp |
Head has some education |
|
|
Head has no education |
|
|
All individuals |
|
|
Note: Once you have filled this table in the excel sheet with your formulas and you have the answer, copy the cells in which you have calculated the numbers for the table and the use pasteàspecialà “values”. Place these numbers below the actual tables you filled in the excel. It will look like a duplicate of the table and that is perfectly fine for me. This will allow me to read your work even if file gets corrupted.
8. Now go back to using the “consume1” sheet file compute the headcount and the poverty gap (Show your work on the excel file and report your work in this word document) [20 marks]:
i . Assuming the poverty line is 4500.
ii. Assuming that the poverty line is 4000.
|
Poverty Line 4500 |
Poverty Line 4000 |
Headcount |
|
|
Poverty Gap |
|
|
iii. Interpret the headcount and the poverty gap and explain what happens when you decrease the poverty line threshold to these two measures.
Note: Once you have filled this table in the excel sheet with your formulas and you have the answer, copy the cells in which you have calculated the numbers for the table and the use pasteàspecialà “values”. Place these numbers below the actual tables you filled in the excel. It will look like a duplicate of the table and that is perfectly fine for me. This will allow me to read your work even if file gets corrupted.
Once you complete these questions save your file using the following structures
“Datafamilyname1_familyname2.xlsx”
9. Using the example.xlsx file that contains data on the consumption for three countries A, B and C with 10 residents in each country only. Show your work on the excel file and report your work in the table.
i . What is the average consumption in each country. [2 marks]
ii . Assuming the poverty line is 126 is the poverty line for each country.
|
Country A |
Country B |
Country C |
Headcount Index |
|
|
|
Povery Gap |
|
|
|
Which country has the highest poverty incidence? Explain your answer. [3 marks]
Note: Once you have filled this table in the excel sheet with your formulas and you have the answer, copy the cells in which you have calculated the numbers for the table and the use pasteàspecialà “values”. Place these numbers below the actual tables you filled in the excel. It will look like a duplicate of the table and that is perfectly fine for me. This will allow me to read your work even if file gets corrupted.
Save your work using “Ex_1familyname1_familyname2.xlsx”
2022-04-29