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 1 (TP1)

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 cover 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. A sample cover page can be found here: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 that when you construct graphs and tables in MS Excel, you will sometimes be instructed  to include them in your working document unless otherwise specified. 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.

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.

1.   The changing cost of a university education in Canada

Go to Statistics Canada’s Data page and locate the annual "price indexes" for tuition fees for the 10 Canadian provinces and at the Canada level for the period 1991 to 2021. Although not mentioned in the Statistics Canada database, these series represent the costs for post-             secondary education (hint: these series are a part of the consumer price index series).

a.   Include these series in the Excel template that bears the name of Tuition.

b.   Add another series to the table which is that of the all-items consumer price index (CPI) for Canada.

c.   Plot a line chart (with colors) of all these series while highlighting (perhaps with a   bolder line) the all-items CPI series so that we can clearly identify it and distinguish it from the others. Be sure to include a title for the chart and properly label/identify each of the series and the axes of the chart.

d.   Calculate at the bottom of the table (yellow cells), the percentage change between the two extreme periods, i.e., 1991 and 2021 for each of the provinces and for the all-        items CPI for Canada.

e.   Identify, by coloring the cell in which the results in d) shows the province that      experienced the strongest growth in tuition fees over this period? Which province had the slowest growth rate?

f.    Briefly explain in your working document the analytical interest of adding the all- items CPI series for Canada to the Table.

g.   From this data, are you able to identify the province where students in 2021 pay the most for their tuition? Explain your answer in your working document.

2.   Creating a Database

The objective of this exercise is to create a database that will be analyzed in a subsequent assignment. For this exercise, please collect from the LCBO website (www.LCBO.com) or

the SAQ (www.SAQ.com) the information that is requested in the Scotch survey which can

be found here:https://fr.surveymonkey.ca/r/Y865P7Hor by QR code:

 

Choose 5 different scotch bottles whose volume is between 700ml to 750ml. Limit your      sample to bottles where the price falls in the range of $25 to $2000. Note that there are two main categories of Scotches on the market: “blended” and single malt” . These categories are clearly identified on the bottle and all things being equal, “single malts” are typically  more expensive than “blended” ones. The distinction is apparent by the description of the bottle where it is clearly identified if this scotch is a "blended" or a "single malt".

So, choose 5 single malt bottles only and complete the questions asked in the survey. If by    chance this information asked in the survey is not readily available for your chosen bottles   from the LCBO, then choose another bottle whose information is available. Note sometimes that you may need to visit the distiller’s website to retrieve the missing data. For single         malts, it is important to identify the region of origin where it is produced, as the taste and     price of Scotch are affected by this. For the purpose of this survey, the regions are limited to 3; note that there is a drop-down menu in this particular question that identifies the 3 region from which you will pick your bottles. For reference, the regions of Scotland where Single    Malt Scotches are produced can be found here:

https://en.wikipedia.org/wiki/List_of_whisky_distilleries_in_Scotland.

It is important to properly diversify your sample of 5 bottles to succeed in the analysis that  will follow. So, look for “single malt” Scotches from different regions, different price ranges, different distilleries, etc.

Do not include with your numbers that you submit in the survey question boxes. This            includes symbols such as "$" or units of measurement such as "ml" or "%". The questionnaire will be accessible until the deadline of this assignment.

 

Here is an example bottle:

3.   The pandemic and the economy

Extract the following data for Canada to construct 7 separate line charts that show the effect of the 2008 financial crisis and the pandemic on some key economic indicators. Put your       downloaded series on the Q3 calculations sheet and perform your calculations (if any) on   this sheet. The graphs should cover the first period going from 2005 (either the first month   or the first quarter of 2005 depending on the series that is requested because some series are produced monthly and others quarterly), to the last period for which the data are available.

Label your axes on the chart and include a title to the charts. Each graph should be saved on

its own separate sheet in your Excel file using this technique:https://www.excel-              easy.com/examples/chart-sheet.html. Give a name to the sheet that corresponds with the

series in question (for example for question a), name the sheet Chart 1).

a)   Chart 1: Quarterly % change of Canada’s real GDP (seasonally adjusted at the annual rate) by expenditure in chained dollars (2012).

b)   Graph 2: Monthly unemployment rate at the Canada level.

c)   Graph 3: Monthly employment rate at the Canadian level.

d)  Chart 4: Monthly Stock Index - S&P/TSX Composite Index.

e)   Chart 5: 12-month % change of quality adjusted of resale house prices. (There are two suppliers in Canada of such series, choose one from these two             suppliers). You may be required to register with one of the providers'              websites to download their series, however access to these data is free. Note: Statistics Canada produces a New House Price series; this is not one of the     two series sought.

f)    Chart 6: 12-month % change in the all-items CPI.

g)   Chart 7: The business confidence index (hint: not a Statistics Canada series).

4.   Student spending survey

Your responses from the Student Expenditure Survey will be used in a subsequent

assignment to calculate a consumer price index for the class. Go here to answer the survey:

https://www.surveymonkey.ca/r/Y8ZXGLQor use the QR code.

 

Note that you will potentially be assessed for completing the survey for which all responses are anonymous and will not be shared with other students. For now, thinking back, try to    estimate as best you can how much dollars you've spent in the last 12 months on the             different expense categories in the survey. Even if a third party (for example, a parent or a   relative) purchased or financed these purchases on your behalf, then assume that you are    the person who incurred the expense. If you made these purchases in a foreign country, it    still counts, but be sure to convert the equivalent amount into Canadian dollars. The             questionnaire will be accessible until 11:30 p.m. on the day of the deadline for this                 assignment.

5.   Correcting for seasonal effects A word about S.A.

Most economic data are discussed in seasonally adjusted form. For example, housing starts always rise in the spring. The seasonally adjusted number reported in the          press looks at whether starts rose more or less than typical seasonal patterns would   have dictated. But there’s one thing to keep in mind: a small decline in housing starts in January is no big deal; the same percentage decline in May has a much bigger         impact, because the normal May volume is so much greater than the normal January volume. This applies to many economic data series.

For the graphs in this problem, please save them as instructed above using this   technique:https://www.excel-easy.com/examples/chart-sheet.html. Also include with your graph a title and label both axis.

a.   Download from Statistics Canada the "not" seasonally adjusted time-series data for the monthly sales (in units) of New passenger cars in Canada for the period January 2001 to December 2019. The series can be found in Table 20-10-0001-01. Include these data in the sheet named Season in the Excel template file.

b.   Represent this series in a line chart with sales on the y-axis and time on the x-   axis. Name the sheet on which you have moved the graph Season 1. Also copy and paste as an image the graph à in your work document.

c.   Most sub-annual time series (e.g., monthly, or quarterly) that are published by   national statistical agencies such as Statistics Canada are often presented in their seasonal adjusted form. I would suggest that you read and understand the          concept of seasonal adjustment as presented under the following links:

https://www.ons.gov.uk/methodology/methodologytopicsandstatisticalconcepts /seasonaladjustment

https://www.statcan.gc.ca/eng/dai/btd/sad-faq

Wikipedia also has a good reference to seasonal adjustment:

https://en.wikipedia.org/wiki/Seasonal_adjustment

By examining the chart in b), do you notice any seasonal pattern in the data series? Briefly explain your answer (2 or 3 sentences) in your working document.

d.   Now redo the graph from part b), but now add two types of trendlines using the built-in function in Excel: a) Moving average trendline and b) a linear trendline   including its corresponding equation (also available as a built-in feature in           Excel). When creating a moving average trendline you will be provided with a   choice of periods (e.g., a 2 period trendline or 3 period trendline, etc.), this is the “order” of the moving average; as a rule, with monthly data characterized by a   seasonal pattern the length of the moving average should equal the seasonal       frequency: i.e., 12 months). So that your trendlines stand out in your graph,         please use dashes for the trendline and apply different colours . Name the sheet  on which you have moved the graph Season 2. Also copy and paste as an image the graph à in your work document.

If you are unsure as to how to create a trendline in Excel I would suggest you consult Google where tips and tricks on this topic abound.

e.   Go to the manual Quantitative Methods for Business and Management which is  available in the assignment space on Brightspace if you want to learn more about trends and moving averages. The relevant pages are 126 – 132. (Note that the       examples in this document use a quarterly series your car data are monthly, you will need adjust for this). Other sources of information are also available on the   Internet that you can consult to expand your knowledge about seasonal

adjustment. Here are a few examples:

i.  https://www.forbes.com/sites/billconerly/2014/12/17/how-to-adjust- your-business-data-for-seasonality/?sh=41213e41421c

ii.  https://www150.statcan.gc.ca/n1/dai-quo/btd-add/btd-add-eng.htm

iii.  https://www.youtube.com/watch?v=FhL8oTURO7Q

iv.  https://www.dallasfed.org/research/basics/seasonally.aspx

v.  http://web.vu.lt/mif/a.buteikis/wp-                                    content/uploads/2019/02/Lecture_03.pdf(pages 19-20)

In columns D and E on sheet Season in the Excel template, calculate the moving average (order = 12) and the trend for passenger cars and draw a line chart         including this trendline. How does the calculated trendline compare that one     calculated in Excel using the moving average method? Briefly explain this in      your working document in one sentence or two.

f.    Once again go to the manual Quantitative Methods for Business and                       Management which is available in this assignments space (pp. 134-139) and          construct a seasonally adjusted version of this car series. You will be constructing an additive model (and not the multiplicative model) for the purpose of                 seasonally adjusting the series. You will need to continue populating the Table     on sheet Season, which is like the one found on page 135 of the manual. Some      calculations will likely need to be done outside the Table which is fine since I am  only interested in the values that you will be populating the table with. As for the values to include in the Trend column of the Table, simply use those that you       calculated in part e) above.

g.   Draw a graph in which you will plot the seasonally adjusted new car series           against their raw (not S.A.) counterpart. Knowing that the S.A series is shorter      than the raw data series, use the shortest of the two series as the number of           periods on which to base your charts. Copy and paste the graph into your main   document but also save it as a separate sheet in Excel and name this sheet Season

3. Comment in your working document about how the S.A. series compares to the non S.A. series.

2.     Search on the Statistics Canada website and answer the following questions relating to the 2019 Survey of household spending:

By the way, most data from the Statistics Canada website are derived from              surveys. When using such data for your analysis or research, it is often useful to    have access to some background information about the survey such as how the      data were collected and the quality of the data. Statistics Canada will include         therefore what is called metadata with its data and this exercise will provide you a taste of what that metadata involves.

a. b. c. d. e. f.

.

.

What is the target population of the survey of household spending?

Is the survey mandatory or voluntary?

What is the sample size of the survey?

What is the reference period of the survey?

Under what condition are data supressed from the survey?

What is the standard error related to the survey? Also define the standard error and explain how it can help in understanding data quality.

What is the coefficient of variation related to the survey? Also define the  coefficient of variation and explain how it can help in understanding data quality.

What is a response rate? (You might need to explore outside the Statistics Canada website for the answer)

i.    What is the response rate for the Interview part of the survey of household        spending at the overall Canada level? Why would we be interested in knowing the response rate? (You might need to explore outside the Statistics Canada       website for the answer).

j.    Which province has the highest response rate for the interview part of the survey?

k.   Which province has the lowest response rate for the interview part of the survey?