Hi story

Using Spreadsheets to Gather and Formulate Macroeconomic Data (Submit assignment as a PDF) PDF available for printing

There are three parts to this assignment (A, B & C). After each set of

instructions, a video demonstration is provided.

Getting Started:

● This assignment utilizes Google Drive and Google Spreadsheets. If you do not have Google Drive on your desktop, please follow the How to Download Google Drive instructions before continuing.

● If you use Excel regularly, you may convert the Google spreadsheet to an Excel spreadsheet.

● Please note that the directions refer to Google commands on a Chromebook or PC, which may differ from those in Excel or on a MacBook. Your use of either assumes that you understand and can use those functions in those formats.

● Once you are in this assignment, open the Macro Data Spreadsheet that has been shared with you.

● STOP AND MAKE A COPY of the Google Spreadsheet before changing or adding anything to the spreadsheet.

○ To make a copy, go to File>Make a Copy. ○ At the top of your new spreadsheet, below the title, add your name - "first name

last name”. ● You can make changes to your spreadsheet by opening it in Google Drive. ● Once you have submitted the assignment, any changes you make will require you to

resubmit it.

A. Gathering Data, Step-by-Step Instructions: You will gather data for all columns on the spreadsheet in black font. You will formulate

data for all columns in blue font. Instructions are clustered below by websites. The data

links are embedded in the step-by-step instructions and listed below.

1.Nominal GDP/billion

1. GDP Deflator Index

2. Population/thousands

3. Unemployment rate

4. Consumer Price Index

Nominal GDP and GDP Deflator

● Open the Nominal GDP link.

● When the web page opens, above the chart on the right-hand side, change the

given dates to range from 1980-01-01 to 2024-01-01.

● Click Download in the upper right corner> Excel (data)

● When Excel opens, at the bottom of the sheet, click ANNUAL to see the data.

Then click Enable Editing (at the top of the page), select all the data (not the

titles) in both columns (dates and GDPA),and copy it.

● Open your Google Sheet template, select the first cell under YEAR, and paste

the copied data. Use the Special Paste option>paste values only.

● Open the GDP Deflator link

● When the web page opens, above the chart on the right-hand side, change the

date range to 1980-01-01 to 2024-01-01.

● Click on Edit Graph, then in Modify frequency > Annual and in Aggregation

method > Average.

● Follow the remaining steps for NGDP above, except copy only the deflator

data, not the years. Use the Special Paste option>paste values only

Population

● Open the Population link.

● For Population, you will follow the instructions for Nominal GDP and GDP

Deflator EXCEPT once you have downloaded the data to Excel (#1-3), BEFORE

you copy and paste it on your sheet (#4), you must convert the data from millions

to billions. (We are doing this now to make calculating Real GDP per capita

easier)

○ If using a Chromebook, go to File>Save as a Google Sheet before

continuing.

○ BEFORE transferring this data to your Google sheet, in the column next to

the data on the first row of the data, type in = (this tells Google Sheets

that you are inserting a formula), then type the following formula as-is:

b12/1000000 (6 zeroes). Please note that"b12" identifies the first cell in

your Nominal GDP column.

○ Hit Enter. The converted population number will appear.

○ Hover the mouse over that cell to find the black+ symbol in the bottom

right corner of that cell and drag to the bottom of the column to copy the

formula and populate data in all the cells.

● Highlight and copy the last column (the one you just formulated).

● Go to your Google Sheet and paste it in the Population column. Use the Special

Paste option>paste values only.

Unemployment

● Open the Unemployment link.

● At the top of the page, change the dates to 1980 to 2024, uncheck “Include

Graphs,” and check “Include Annual Averages.” Click GO.

● Scroll down to data and above the schedule, click "Download.xslx".

● When the spreadsheet opens, click on Enable editing.

● You will not copy all of this into your spreadsheet. You only need the annual

average. Since those did not self-populate, you will have Excel calculate those

for you.

○ If using a Chromebook, go to File>Save as a Google Sheet before

continuing.

● In the first cell (1980 data) of the last column under Annual, type in the following

formula as-is: =Average(b13:m13). Hit Enter, and the yearly average of the

monthly unemployment rates will be entered into the cell. (Note: Instead of typing

in the letter and number of the cell, you can click on the cell that will be used for

the calculation.)

● In the bottom right corner of the cell with the new data, hover over it until you find

the black + symbol, then drag the black + symbol to the bottom of the column

to copy the formula and populate the data.

● BEFORE transferring this data to your Google sheet, in the cell to the left of the

first row of the Annual column, type in the following formula:=n13/100. This

converts a percentage to a decimal (to standardize how the data is formatted).

● In the bottom right corner of the cell, find the black + symbol, then drag it to the

bottom of the column to copy the formula and populate the data.

● Highlight and copy only the data you just created.

● Go to your Google Sheet, and paste in the first cell in the Unemployment Annual

Average Rate column. Use the Special Paste option>paste values only.

Consumer Price Index

● Open the Consumer Price Index link,

● Click on "More Formatting Options".

● Leave everything in the "Select view of the data" box the same.

● In the "Select the time frame for your data" box, set the years to 1980-2024.

● Click on "Select one time period" and choose Annual Data.

● Uncheck "Include graphs".

● Leave everything else the same and click on "Retrieve Data".

● Highlight and copy the Annual column, then paste it into the Consumer Price

Index column. Use the Special Paste option>paste values only.

. Using Formulas to Create New Data, Step-by-Step Instructions:

● You are now ready to use the data to formulate data for your blue columns. You

will use the formula feature of your Google Sheet to do the calculations for you.

However, you must tell it what to calculate by inserting a correct formula.

○ You will use your “Macroeconomic Formulas” handout for the basic

formulas.

Follow these directions to calculate the NGDP growth rate and use this example for the

formula function for your other calculations that ask for a % change.

● Starting in the NGDP growth rate column, in the second row (1981), below the

title type =(this tells Google Sheets that you are inserting a formula). Then tell

Google Sheets the formula to use, replacing values with cell letters and numbers

(see example below). Hit Enter to populate the cell.

○ On your Macroeconomic Formula sheet, you have formulas for Economic

Growth Rate and Percent Change. These are the same formulas. You will

use these steps for any data set that is a " % change".

● To calculate the NGDP Growth Rate for 1981, use the formula: (Current year

GDP - Previous year GDP) / Previous year GDP. Do NOT multiply by 100.

Instead of converting the decimal values to percentages, we will have the

spreadsheet perform the conversion during Cleanup.

● Start on the row for 1981 in the NGDP Growth Rate column. (Note: We cannot

calculate the growth rate for 1980 because we do not have data for 1979.)

● Using the data cells on your sheet, type in =(b6-b5)/b5. These letters and

numbers reflect the second & first cells in your Nominal GDP column. (Note:

Instead of typing in the letter and number of the cell, you can click on the cell that

will be used for the calculation.)

● Populate the entire column by finding the black + symbol in the bottom right

corner of the cell you formulated and dragging it down to the bottom of the

column. This will copy the formula.

● At this point, you have used the formula feature for several calculations. Repeat

those steps for all required calculations using the appropriate formulas from your

formula sheet with the appropriate data on your sheet.

● The first row of any rate-of-change/growth-rate column will NOT be calculated,

as there is no previous data to calculate it. You will start with the 2nd row on all

of these.

● To calculate Real GDP, use the formula from your formula sheet that employs the

GDP Deflator.

● If you are still unsure about doing this, watch the video demonstration.

● All columns, including the titles, should be formatted from the right margin.

● Adjust the cell size so the column titles are legible.

● The font type and size should be the same throughout.

○ Highlight all your data. Choose a font size and type.

● Check that you have data for every year listed except the "% Change" columns

for 1980.

● Check that you do not have a value of 100 at the bottom of any of your growth

rate or rate of change columns. If you do, it is because you did not skip the first

row of those columns. To correct, cut and paste everything in that column, one

row down.

●