ECO 2147 ANALYSIS OF ECONOMIC AND SOCIAL DATA SUMMER 2022 ASSIGNMENT 2
Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit
ANALYSIS OF ECONOMIC AND SOCIAL DATA
ECO 2147
SUMMER 2022
ASSIGNMENT 2 (TP2)
Instructions: Please respect to avoid serious penalties
This is an individual assignment. You can however consult each other to work on the assignment but you must return your own individual copy. Under no circumstances can you submit your assignment by email; you must submit it on Brightspace. Tardiness will be penalized at the rate of a 10% deduction per day up to a maximum of 3 days after which time no assignment will be accepted. Include with your working document a typed title page that is produced with word processing software such as MS Word or Pages; do not submit a handwritten cover page. Your last name (family name) should be in caps so that we can best
identify you (do not capitalize your given name.) A sample title page can be found in this document on page 18:https://socialsciences.uottawa.ca/public-international- affairs/sites/socialsciences.uottawa.ca.public-international-affairs/files/guidelines-
20110217/WritingandStyleGuide2010-2011_001.pdf
Which is a sub section of this very good writing guide which I think every university-level student should read.
I would also like to point out that a subset of the exercises will be corrected and not the entire assignment due to lack of resources. However, you must attempt all the exercises, otherwise points will be deducted for incomplete work.
Note for some targeted exercises, the graphs and tables that you construct in MS Excel will need to be included in your working document; these exercises will be identified as such. There are several ways to go about transferring charts and tables from an Excel file to the working document. Here's an example (the same approach is used if you're using Pages):
https://support.microsoft.com/en-us/office/insert-a-chart-from-an-excel-spreadsheet-into-word-
0b4d40a5-3544-4dcd-b28f-ba82a9b9f1e1
You can also with these methods to resize your graphs and tables so that they can fit on the page of your document. Always include labels on your charts to identify axes and series and a title. Tables should always include a title.
Note that tables and graphs should include a title with and the axes of the charts should be labelled.
One of the goals of these exercises is to assess your ability to find data, so please don't ask me where the data is, as that defeats the purpose of the exercise. If it proves impossible to find the requested data, then simply skip the question.
With every assignment you will be asked to submit in Brightspace the following:
1. A copy of your working document, which is typically produced in MS Word (or Pages) and then saved as a PDF file. You submit the PDF file.
2. Your MS Excel file.
Additional guidelines for submitting the assignment
Please observe the following advice/instructions to avoid point deductions:
1) Work submitted by email will not be graded.
2) Do not return your documents in a ZIP file.
3) When you are asked to do calculations in the Excel file, you must use the formulas in MS Excel to perform them. You should never just write a result in a cell without it having been calculated in Excel.
4) When you apply an Excel formula, you must use the cell addresses in the formula and not use the numbers taken directly from the table, for example.
5) You have a grace period of two days after the deadline to return the assignment but with penalties. Delays will be penalized at the rate of 10% per day (maximum tolerated of 2 days). After this date, the window for returning the work will be closed and it will be impossible to
submit the TP regardless of the motivation.
6) Back up your files to one of the many services that exist in the cloud since there are no exceptions to deadlines in case you have technical problems that prevent you from submitting the work on time.
7) If you have any questions, please ask them in the forum for a timelier response. Do not communicate by email any questions about the assignment.
8) It is advised to start the assignment earlier than later. That way, if you have any questions, they have a better chance of being addressed before the deadline.
1. Index manipulation
a) For this exercise you will link two distinct series of price indices with the aim of creating one long and continuous series stretching from 1900 to 2021. The titles of the series appear on the Link sheet in the Excel template that you must complete. The link period is year 1914, i.e., the first year that is common to both series. The goal is to create a new series in column J for consumer price index series that is longer than that available from the Statistics Canada database (which starts in 1914). We will use this information in b) to make a revealing calculation.
a. The hourly wage of a master electrician in Toronto in 2021 is $40.00; in contrast, the comparable wage in 1901 was $1.00 (both figures are in nominal dollars). Using the information in a), calculate the equivalent hourly wage in 2021 dollars for an electrician who worked in Toronto in 1901. Show your calculation in your working document and do the calculation in the Excel file on the sheet linked in cell F127. Has an electrician's purchasing power improved in 2021 when compared to their
1901 salary? Explain the logic of your answer in your working document.
2. Shifting base
a. Complete the Table on sheet Tuition which involves changing the base year of the indices in the completed table to the new base year of 1991 = 100.
b. Draw a chart that shows the indices under the new base year and save it on a separate Excel sheet as per these instructions: https://www.excel- easy.com/examples/chart-sheet.html.
3. Price Indices: Complete the index calculations on the Indices sheet in the Excel template. The formulas for these indices appear in the course notes and/or are available on the Internet. All calculated indices should be on a 100 base.
4. Minimum wage
a. Fill in the table in the Excel sheet titled Wages. Since the Ontario version of the CPI (the ideal choice) is not available back to 1965, the values for Canada are used instead.
b. Calculate, on line 64, the percentage change between these two extreme years: 1965 and 2021 for each of the columns. Show these results in your working document.
c. Plot a line graph showing what happened to the inflation-adjusted (real) minimum wage over the period from 1965 to 2021. Copy and paste this graph into your working paper. Also save it on a separate Excel sheet as per these instructions:
https://www.excel- easy.com/examples/chart-sheet.html.
d. What can you conclude from the variation in purchasing power since 1965 to the present day (2021) of people who work at this salary? Answer in your working document.
5. Tests
a. Go to the Excel sheet TESTS and calculate the three price indices that are shown on the sheet.
b. Do an Internet search to learn about the test approach to choosing the best index formula and determine which one satisfies the factor reversal test. You can do your index calculations in Excel but show your steps and logic in your working document.
c. Now determine which one satisfies the Time reversal test. You can do your index calculations in Excel but show your steps and logic in your working document.
6. Class CPI
This exercise uses data from the expenditure survey you completed in assignment 1. Go to the CPI Class worksheet in the Excel template. Take note of the difference in the weights between the official CPI (the entire Canadian population) and those derived from the survey (starting in column R). You will find a table with the price indexes that correspond to the various spending categories drawn from the official CPI. Because we cannot go out and collect prices ourselves (past and present) but we will assume that the price changes for the students of this class are the same as those of the population at large (perhaps a bit of a stretch but good enough for our purposes); these indices therefore reflect how prices for these different categories have changed on an annual basis over time. I also left in row 1 (starting from column T) the weights that were derived from a previous student survey for information purposes only: thought you might be interested in seeing how the spending patterns of previous cohorts compare to yours.
A) Calculate in the yellow area of the table, the overall (all-items) CPI for students. To do this, you need to calculate the weighted average of the price indices that are in columns E to O, using the weights derived from your expenditures (columns R to AB - row 4). This exercise is repeated for each year. Here is an example: for the 2002 index, we made (97.5 x ?) + (96.9 x ?) + … + … = CPI all-items for students in 2002. Then repeat for 2003 using the same weights (Laspeyres formulas) but this time you use the price indices for
2003 and so on.
B) Draw a line chart that shows each of the two CPI data series (official vs students). For this graph. To put a little more emphasis on the disparity between the two indices,
adjust the beginning of the y-axis of the chart to the value 90 (as opposed to the default value of 0).
C) In cells P28 and AC28 calculate the % change between 2002 and 2021 for the class CPI and the overall population CPI.
D) Which group has experienced the highest inflation rate over the 2002 to 2021 period ?
7. Hedonic regression
This exercise will use the Scotch data you collected in assignment 1.
Do not be put off by the econometrics in this exercise which is the source of hedonic modeling. The basic principles and the spirit behind linear regressions and hedonic modeling are quite intuitive. For example, Wikipedia.org offers a good presentation on the principles of linear regression.
https://en.wikipedia.org/wiki/Linear_regression(Read the introduction and some of the examples of applications in different fields, further down the page . Do not stay stuck on the formulas).
A hedonic regression is a regression where the independent variable is the price of the product and the characteristics (number of bedrooms in the case of houses, age of Scotch in the case of Scotch, RAM in the case of a computer, etc.). Hedonic models are commonly used by statistical agencies and researchers in different contexts. These models are very useful when you want to know the contribution of a characteristic of a
product to its final price. See here for more on hedonic regressions:
https://en.wikipedia.org/wiki/Hedonic_regression
Hedonic modeling is also increasingly used for quality adjustments of certain products in the CPI.
The following video explains hedonic models very well:
https://www.youtube.com/watch?v=xu0prYu5e-c&t=266s
The following hedonic model for the case of Scotches is a first to my knowledge (however, these models have been applied in the field of wines). As for regressions, you
must run them in STATA. There are several YouTube videos with introductions to Stata. Take your pick.
Exercises:
a) Produce a table of summary statistics in Stata from the Scotches database. Paste/copy this table into your working document. The data for this are found on the Scotch sheet of the Excel file or in a Stata file (Scotch 2022.dta). It is up to you to choose whether to use the Scotch 2022.dta file directly in Stata or to import the data from the
Excel file into Stata. To Copy/Paste the results of Stata in a Word document, consult pages 17-18 inhttp://xtophe.bontemps.free.fr/Cours/stata/CoursStata.pdf
b) Plot a scatter plot in Stata with the following two variables: Price (y-axis) and Age (x- axis). Add a title to your chart. Paste/copy this graphic into your working document. See how from page 15 inhttp://xtophe.bontemps.free.fr/Cours/stata/CoursStata.pdf
c) Run a linear hedonic regression on the Scotches database. The dependent variable is price and the independent variable is age.
d) Repeat c), but this time add the independent variable degree of alcohol to your regression. Copy and paste your regression results into your working document.
e) What is the effect on your results (when compared to your results in c)) of adding the degree of alcohol as an independent variable? Explain and answer in your working document.
f) Interpret the following results from the results of the linear regression in c):
i) R2
ii) Fisher's test (F test) (yes, it's the same Fisher as the index that bears his name).
iii) The value of the coefficients.
iv) Student's t statistic
v) The confidence intervals.
g) Repeat d) but this time use the new transformed variable for the dependent variable, which is the natural logarithm of the Price which you will name LNP. Copy and paste the regression results into your working document.
h) What is the effect on your results (when compared with those in d)) of using the natural log of price in your regression? Explain and answer in your working document.
i) Repeat g) but this time add the regions as a new independent variable. This requires the use of “dummy” variables (also known as “dichotomous or binary variables”) to represent qualitative variables such as region in a regression. To this end, this document could help you:https://stats.idre.ucla.edu/other/mult-
pkg/faq/general/faqwhat-is-dummy-coding/ Other are also available.
j) Copy and paste the results of this new regression in i) into your working document.
2022-06-13