Mastering M2 Money Supply Analysis: A Step-by-Step Guide

Abstract

Assignment #00 Completion is required. While I expect each of you to be able to do each of these questions you are permitted to work in groups of between 1 and 5 students. Each student group is required to put their names in the single submitted Word file in the Sakai assignments page. If you do not complete Assignment #00 you will not be eligible to submit either of the other written assignments for the semester. Grades on Assignment 00 will reflect a level of effort rather than specific performance on any question. In other words its purpose is to force you to refresh yourself with EXCEL and begin thinking more analytically about things. The expectation is that you will have access to this assignment sheet as well as the data file ASSGN00.xlsx. The tabs (or worksheets) in ASSGN00.xlsx are labelled per the questions below. Use the data in the tab “M2BasicData.” You are asked to do some manipulations and calculations with the data on the M2 money supply. Specifically the result we are looking for is the Year over Year % change by month in M2 from January 2020 to April 2022. There are some instructions in the tab “M2BasicData” that are intended really to allow you to follow along. Either way, you can do the problem any way you want. The result is that you have the numbers to fill in the table below “Yearly % Change in M2 – by Month January 2020 – April 2022.” Yearly % Change in M2 - by Month January 2020 - April 2022                     Year.Month Yearly % Change in M2   Year.Month Yearly % Change in M2 2020.01 6.76%   2021.03 24.59% 2020.02 6.76%   2021.04 18.88% 2020.03   2021.06 2020.04   2021.07 2020.05   2021.08 2020.06   2021.09 2020.07   2021.10 2020.08   2021.11 2020.09   2021.12 2020.10   2022.01 2020.11   2022.02 2020.12   2022.03 2021.01   2022.04 8.47% 2021.02 26.78%       Again we are going to use the Data on M2, but a shortened version of it. Please look in the tab M2BasicData-Graphing. Here we have the same data but from January 2015 through April 2022. In columns H and I there is the yearly average of the M2 values for each month in 2015 through 2021. Those were obtained using a Pivot Table and while Pivot Tables are useful you don’t need to use one, or should I say “yet”. In any event what I want you to do is calculate the average % growth in yearly M2 and make a bar chart showing the percentage growth for 2016, 2017, 2018, 2019, 2020, and 2021. Place your Bar Chart below. Please format it accordingly. In the tab CPI-Irate you will find raw data on two items: The Consumer Price Index labelled CPI, and on the 10 Year US government constant maturity interest rate, labelled DGS10. The data is monthly, it begins in Jan. 2010 and it ends in April 22. Also shown is a “count” and various forms of the date. Notice the data has the latest version or latest values at the bottom of the file. This happens sometimes ! You can live with it or sort the data on the date column and reverse it. We need to calculate a year ago inflation rate. So in column I put in a label “Infl” and make it equal to the percentage change in the CPI, for each appropriate value from its value one year ago. The “answer” for January 2011 is 1.70% as the CPI changed from 217.488 to 221.187. Please find that in your “data file”. The inflation rate in February 2022 was _______ . The inflation rate in April 2022 was __________. We ultimately want to compare the values for DGS10 and Inflation. If you look at the numbers in the columns B through I you see that the “scale” of the calculated inflation rate is in % terms and the scale of DGS10 is in numeric terms; they are not really comparable, at least by size. So I want you to create a variable XDGS10. The variable XDGS10 will best be put in column J and its value should be equal to the original value shown by...