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 MUST submit the PDF file, otherwise we might not be able to read your file.

2.    Your MS Excel file.

Additional guidelines for submitting the assignment:

Please observe the following advice/instructions to avoid point deductions (note there is some repetition from above):

1)    Do not return your documents in a ZIP file.

2)   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 number in a cell which requires a calculation. You need to calculate the result using the Excel formula.

3)   When you apply an Excel formula, you must use the cell addresses in the formula and not use the numbers taken directly from a cell in the table.

4)   You have a grace period of 2 days after the deadline to return the assignment but with penalties. Delays will be penalized at the rate of 10% per day. 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.

5)    Back up your files regularly 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.

6)    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. Note that your question was perhaps already asked in the forum and has been answered. Therefore, please peruse the forum before asking your question.

7)   Also be clear in the subject heading of the Forum posting when asking your question, e.g., Problem

2 B: How to calculate such and such a result.

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 in the forum before the deadline. This will also avoid penalties for late submissions in case of ill health. Recall that there are no extensions past the 2- day grace period in case you are sick during this time or for any other reason.

9)    In addition to saving your graphs in your working document (unless otherwise specified), all graphs constructed in Excel should be saved on a separate sheet within your Excel file as per this method: https://www.youtube.com/watch?v=mD28breAR2g  (about 1 minute into the video)

10) In the MS Excel a template is provided, do not move or change the layout (the way the page is set up) that appear in this file. Simply fill in the blanks without moving or changing the set up or layouts of these various sheets. Include your answers in the cells that have been reserved for this purpose (look for the yellow highlighted cells).

11) In your calculations, limit the number of digits for your answers after the decimal to one (e.g., 12.1 and not 12. 1234) unless otherwise specified.

12) Note that you are new to MS Excel, there are several YouTube videos on how to use this tool.

13) Use the following naming conventions for all your files: Family name_student number.pdf. For example: Prudhomme_11111111.xls

14) Failure to respect these instructions will result in grade deductions.

To learn more about Statistics Canada data and how to download the data please look at these links:

https://www.statcan.gc.ca/eng/developers/csv/user-guide

https://www.statcan.gc.ca/eng/about/website-faq

https://www.youtube.com/watch?v=8u1I8AWL-Q0

https://www.youtube.com/watch?v=4jdlqFu6Yak

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 2022. Although not specifically mentioned in the Statistics Canada database, these series represent the cost for    post-secondary education (hint: these series are a part of the consumer price index series).

a.   Include these series in the Excel template named 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 (line 43), the percentage change between the two extreme periods, i.e., 1991 and 2022 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 2022 pay the most for their tuition? Explain your answer in your working document.

2.   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 (there is no template) 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). Given that the series     starts in the first quarter 2005, the first quarterly percent change can only be   calculated starting with the second quarter 2005.

b)   Chart 2: Monthly unemployment rate not seasonally adjusted at the Canada level. (See note at the end of this assignment)

c)   Chart 3: Monthly employment rate not seasonally adjusted at the Canadian level. (See note at the end of this assignment)

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 (this is the percent change for a given month compared to the corresponding month of the previous   year). Given that the series starts in January 2005, the first annual percent  change can only be calculated starting in January 2006.

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

3.   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 at least in Canada. 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 shown here: https://www.excel-easy.com/examples/chart-sheet.html. Also include with your graph a title and label both axes.

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 2012 to December 2019. The series can be found in Table 20-10-0001-01. Include these data in the sheet named Season (column B) in the Excel template  file. Note that I have limited the data series to no later than year 2019 because    after that year the pandemic has really influenced car sales and would therefore make it impossible to achieve good results.

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 Chart 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 work document.

d.   Now redo the graph from part b), but now add two different trendlines using the built-in function for creating trendlines in Excel: a) Moving average trendline       and b) a linear trendline. Include the corresponding trendline 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 but for this exercise we will use 13 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 Chart Season 2. 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. To learn more a time series,     trends and moving averages you can consult pages 122 to 138 in the manual.       (Note that the examples in this manual use a quarterly series while your car data are monthly, you will need to 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 column C on sheet Season in the Excel template, calculate the trend for          passenger cars. For this you will apply method C on page 128 of the manual.     Note however that you now have from part d) above, the equation for the          trendline, so no need to compute the various elements of the formulas on pages

128-130.

f.    Once again go to the manual Quantitative Methods for Business and                  Management and use this information to complete columns D to F in the Excel template (sheet Season). Remember we are using the multiplicative model for  this exercise.

.

For the next assignment we will learn how to use the above information to “predict” future car sales, just like many economists do.

4.   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.   What is the target population of the survey of household spending?

b.   Is the survey mandatory or voluntary?

c.   What is the sample size of the survey?

d.   What is the reference period of the survey?

.

.

.

h.

i.

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)

What is the response rate for the Interview part of the survey of household spending at the overall Canada level?

j.    Why would we be interested in knowing the response rate? (You might need to explore outside the Statistics Canada website for the answer).

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

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

Note about unemployment rate data: If you search under the Statcan ACCESS OUR DATA         (CANSIM) tab for the unemployment rate and the employment rate, you might have some         difficulty finding data going back to 2005. Not sure why Statcan makes finding this series so       difficult but that is the way it is. However, if you click on the Unemployment rate tab in the Key