Project Assignment #2 – Using Spreadsheet Data to Create

Charts/Graphs

Step by Step Instructions:

Using Google Sheets’ chart-creating feature, you will create five separate graphs showing the relationship between the following grouped pairs.

1.       Nominal GDP and Real GDP (line chart)

2.       Nominal GDP Growth Rate and Real GDP Growth Rate (column chart)

3.       Real GDP Growth Rate and RGDP per capita Growth Rate (column chart)

4.       Real GDP per capita Growth Rate and Unemployment rate (column chart)

5.       RGDP per capita Growth Rate and Inflation Rate (line chart)

Before you begin:

•        Check your Assignment #1 grade in the gradebook. If you did not make a 100 on your spreadsheet, you had calculation or formatting mistakes that need correcting before you begin this assignment. You can view the feedback to see what you need to correct before starting this assignment.

•        You will use your spreadsheet for this assignment. Open the spreadsheet and replace the XXX on the 1980 row with the following values and then convert them to percentages. 

o   Nominal GDP growth rate .0875 o              GDP deflator % change .0905 o Real GDP growth rate   -.0027 o Population growth rate .0116 o Real GDP per capita growth rate  -.0142 o    Inflation rate .1350

•        Before creating your first graph, name the data sheet.  At the bottom of your spreadsheet on the tab labeled Sheet1, open the drop-down menu and click on Rename. Name this tab “Macro Data”.

Step 1: Create a Graph   

There is a video demonstrating how to create Graph #2.  These instructions will guide you through creating Graph #1. 

•        To create a graph/chart, you will select the data to be graphed by dragging your mouse through the entire column (including titles) of the desired data (highlight the Nominal GDP column, including the column title).

•        Once the entire column is highlighted, go to the Google Sheet menu at the top and find the Insert Chart icon and click on it.  A graph will immediately appear on the page. 

•        Your data for Chart 1 should show up as a line graph. If it isn’t, from the Chart Type menu in the Chart editor, choose one of the Line charts.

 

Step 2: Add Another Series

•        To add the rest of the data to your chart, in the Chart editor menu, find and click on Add Series (near the bottom of the menu). Click on the grid-looking box (windowpane).

•        Highlight the data to add. This will automatically fill in and populate your chart (highlight the entire Real GDP column, including the title).

•        Check to verify that a key has been generated at the top. If there isn’t one, at the bottom of the Chart Editor menu, check the box “Use row 4 as header”, making sure the  

o   If that did not add a key, you didn’t highlight the title when choosing the data. In that case, delete the data set from your graph and begin again. 

•        Note: If the Chart Editor isn’t open, click on the graph, then click on the three dots in the upper right-hand corner and choose Edit chart.

 Step 3: Add the X-Axis

•        In the Chart editor menu, under X axis choose Add X-axis. Click on the grid-looking box (windowpane).

•        In the Years column, highlight the Years column (include the title). That range automatically pops up and is added to the graph.

•        Before moving on to the next step make sure the boxes at the bottom that say, “Use row 4 as headers” and “Use column A as labels” are checked and “Treat labels as text” is NOT checked.

Step 4: Add Detail

•        You will continue to edit the chart by adding detail. In the menu above your chart, in your Chart Editor, at the top, choose Customize.

•        In the drop-down menu, click on Chart & axis titles. 

•        In the box below, make sure it says Chart title, then under Title text, type in the title of your chart.  This needs to reflect the information you are graphing. For example, Chart 1 title will be Nominal and Real GDP.

o   Next, click in the Chart title box and choose the Horizontal axis title, in the box below, type in YEARS.

o   Click on the Chart title box again and choose Vertical axis title, in the box below, type in whatever unit is being measured. This must be in the unit the variable is measured. For example, NGDP and RGDP are measured in Billions of U.S. Dollars, but NGDP and RGDP growth rates are measured as Growth Rates.  

•        You must have major & minor gridlines for both the vertical and horizontal axes. To add major or minor gridlines go to your Chart editor>Customize>Gridlines and Ticks (near the bottom of the menu). o Choose the Horizontal axis.

▪  Your years must be in five-year increments (example: 1985, 1990), if they are not do the following:

▪  Go to Major spacing type, choose Count, Under Major count, type in 7.

▪  Then under Minor spacing type, choose Count. Under Minor count, choose 4.

This will add a minor grid line for each year not labeled (1981, 1982…)

▪  Do this on all the graphs for the horizontal axis

▪  Next change the colors for both Major and Minor gridlines (the current color is white and when converted to a pdf cannot be seen). You can choose any color but:

▪  Choose one color for all major gridlines and one color for all minor gridlines.

▪  The colors should not be the same as the colors in the graph.

▪  The colors must be dark enough to show up, but they should not obscure the lines or columns on the chart.

▪  The major lines must be darker than the minor lines. o Choose the Vertical axis.

▪  Major lines for Chart 1 need to be in increments of $5,000. If that is not the current increment, under Major spacing choose Count; under Major Count choose 7.

▪  Minor lines need to be in increments of $1,000. If that is not the current increment, under Minor spacing choose Count; under Minor Count choose 4.  

▪  Follow the steps outlined in the horizontal axis instructions to change your gridline colors.

Step 5: Move Chart to Own Sheet

•        To move this chart to a separate page, click on the 3 dots in the upper right-hand corner of the chart and choose “MOVE TO OWN SHEET”.

•        There will be a new tab at the bottom of your page, Chart 1. Click the drop-down menu and choose Rename. Add the titles of the data columns you graphed. For example: 1. Nominal & Real GDP. You may abbreviate this – NGDP and RGDP

•        Check work to make sure everything on your graph is correct.

Step 6: Create Remaining Graphs.

•        Follow Steps 1-4 to create the next four graphs. The video at the end of this assignment demonstrates the creation of Graph #2

•        Note the following differences:

•        Charts 1 & 5 are Line charts.  Charts 2-4 are Column charts.  You can make this change after you have selected your data and created your chart. From the Chart Type menu choices, choose Column Chart. 

•        For the Vertical axis title, only the first graph is measured in Billions of U.S. dollars, graphs 2-5 are measured as Percentages.

•        You will need to change your gridlines on the vertical axis for Charts 2-5 to increments of 1 percent. The major count value will vary depending on the percentage range of the data shown. Experiment with the major count so that the uppermost range of data is clearly shown and there is at least one percentage point above it. I used 16 for the NGDP and RGDP Growth Rates, but you may need to use less or more. Experiment until you have it right. 

•        Change the minor count to one. (Your minor counts will indicate half percent)

You are now done with Assignment #2. Please submit it through Canvas as a PDF. To do that follow these steps:

•        On your Google Sheet, go to File>Download>PDF (pdf.)

•        When the pdf opens, in the EXPORT menu under the Export heading click on the down arrow>choose Workbook.

•        Click on EXPORT (this is saved in your download file).  

•        Go to this assignment in Canvas and submit.