Data Analytics in Economics – lamontblack.com

This is module 8 of 10 for the Business Analytics course.

Readings

Self-Directed Learning

Search the internet for articles, other resources, and examples of how business analytics is used in economics. See what you can learn from what others are saying. Economics is an important application of analytics for studying personal and corporate economic issues as well as for studying data on the “economy.”

Online Discussion of Module Topic

Write down your thoughts and experiences while you’re learning. For this module, this should include writing about your experience of learning about the use of data analytics in economics. Here are some prompts to get you started for writing about the module topic:

Provide links to any useful and relevant resources about data analytics in economics that you can find on the internet.
Do you have any experience in using analytics in economics? Maybe in a course or a job?
How can companies use data to do economic analysis on their industry and competition?
What data can be used to measure an economy and evaluate its performance?
How can predictive analytics be applied to forecasting the direction of the economy?
What is “nowcasting” and how does the growing availability of data make this possible?
Why is data analytics so important to economic policy makers?
What do you think is the future of data analytics in economics?

Online Discussion of Module Submission

For this module, the discussion of the submission should include writing about your experience of working on the submission. You should write about your experience working with the specific data, but also try to think about how this approach could be applied to other datasets. Here are some prompts to get you started for writing about it:

Why is economic data so readily available?
How can tools like Excel be useful for summarizing economic data?
What was your experience of downloading multiple data sets and organizing them to work together?
This exercise combines IF, PivotTable, and VLOOKUP all into one process. What did you think?
How did your data visualization highlight the key issue?
What is a “regression” and why is this such a powerful tool for studying the influence of factors on outcomes?
What did you conclude from your analysis?
How do you feel about the potential of this type of analysis for making policy decisions?

Module Submission

This assignment can be done in groups of up to 3 people. You will be submitting your work under the Module 8 Submission Folder. You will need to join a group in D2L in order to view the folder. The submission should be done by pasting a link to your Excel workbook in OneDrive.

The goal of the module exercise is to familiarize you with a range of economic data and how it can be used to analyze important economic issues. You are going to download data from multiple government agencies and then organize that data to study the relationship between COVID-19 cases and unemployment.

Suppose that you are an analyst working for a state government. Your goal is to answer this question:

How do rising COVID cases influence unemployment?

Before you begin the exercise, you might want to form your own hypothesis. Is the relationship positive, negative, or zero? In this exercise, you will use a “regression” to test your hypothesis based on data between September 2020 and September 2021. Your analysis will use state-level COVID cases during that period as your “factor” and then look at the influence on state-level unemployment over that period as your “outcome.” A scatterplot will be your key visualization to show the data and the regression equation.

Here are the steps for working on the module submission:

Step 1. Go to the U.S. Center for Disease Control (CDC) site for United States COVID-19 Cases and Deaths by State over Time. Click on the Export button on the right and then click on the “CSV for Excel” button (not the Europe one). This will download a csv file. Save this file as the first worksheet in an Excel workbook (.xlsx).

Step 2. Go the U.S. Census site for State Population Totals and Components of Change: 2010-2019. Scroll down and click on the link for “Annual Estimates of the Resident Population for the United States, Regions, States, and Puerto Rico: April 1, 2010 to July 1 2019 (NST-EST2019-01).” This will download a file called “nst-est2019-01.xlsx.” Open this file and copy it (in Excel: Edit -> Sheet -> Move or Copy Sheet) as a second worksheet in the Excel file you created in step 1.

Step 3. Go to the U.S. Bureau of Labor Statistics (BLS) site for Over-the-Year Change in Unemployment Rates for States. This will show state unemployment rates for September 2020 and September 2021. You are going to copy this table into Excel. Create a third worksheet in your workbook and label it “Unemployment.” On the website, click with your mouse right before the H in Hawaii and drag your mouse to the bottom right of the table after the number 51. Copy the selected data into your clipboard. Then in cell A2 of your Unemployment worksheet, paste in the contents of your clipboard. The data should now be in Excel. Label the four columns as “State Name,” “UR Sep2020,” “UR Sep2021,” “YoY UR Change,” and “UR Change Rank.” The UR refers to unemployment rate and YoY is year-over-year.

Step 4. Copy the Unemployment worksheet as a fourth worksheet after the other three. Label this new worksheet “State Data.” Sort the State Data into alphabetical order by state name. Notice that there are 51 states. In the F column, add a column header “Population 2019.” Now copy the 2019 population from the worksheet with the census data and paste this data into this column. The data should line up so that there is one population number for each state.

Step 5. You need state codes (e.g., “AL” for “Alabama”) so that you can combine the state data with the COVID data. Go the website https://www.infoplease.com/us/postal-information/state-abbreviations-and-state-postal-codes. Click your mouse just before the A in Alabama and drag your mouse just after the WY in Wyoming. Copy the information into your clipboard. Go back to the State Data worksheet in your workbook and click on cell G2. Paste the data into the worksheet. This should line up one state code for each state. Delete columns G and H because they will not be needed. For the state code column, add the header “State.” This will be our unique identifier. For the last step, move the “State” column to the beginning of the State Data (i.e., move the “State” column so that it is in column A using insert column and cut and paste).

Step 6. Copy the first worksheet on COVID cases into a fifth worksheet labeled “Working Cases Data.” Insert a column after the new_case field. Label this new field “new_case_Sep20_Sep21.” This new field is intended to only show new cases between September 1, 2020 and September 1, 2021 so that we can compare them to the changes in the unemployment rates. We will do this transformation using an IF function. The first input in your IF function will be the following

AND(A2>=DATEVALUE(“9/1/2020”),A2<DATEVALUE(“9/1/2021”))

This is an AND condition inside the IF function that will only be true for dates between 9/1/2020 and 9/1/2021. You will use A2 in the second row, A3 in the third row, etc. Your IF function should return the value of new_case on that date if the condition is true and the value of 0 otherwise.

Step 7. Create a sixth worksheet labeled “Total Cases” with a PivotTable based on the Working Cases Data. The PivotTable should have the states in the rows and the sums of new_case_Sep20_Sep21. Sort the table descending on new_case_Sep20_Sep21. In a statement below the table, explain why it makes sense why the states at the top are highest.

Step 8. In the State Data worksheet, insert two columns after the “UR Sep2021” field. In the first column (column E), label the new field “tot_case_Sep20_Sep21.” The values in this field should be the values from your PivotTable in Step 7. Use VLOOKUP with State (e.g., AK) as the lookup value to search the PivotTable and pull in the summed values. (Hint: Make sure to anchor the table array reference by putting a $ before the cell number reference.)

Step 9. In the second new column in the State Data worksheet (column F), label the new field “percap_case_Sep20_Sep21.” The values in this field should be the per capita values of total COVID cases. This can be calculated as total state cases divided by state population. Recall that state population is already in the last column of the worksheet.

Step 10. Create a seventh worksheet labeled “Chart” that has a scatterplot relating total COVID cases and changes in unemployment. You can create the scatterplot by selecting the data as the two columns in the State Data worksheet with per capita cases (column F) and YoY unemployment rate change (column G). Once you have the scatterplot, select “Add Chart Element” and add a linear trend line. Double click on the trend line, which will bring up the window for formatting the trend line. Select the box for “Display Equation on chart.” Once the formula appears, move it to a lower area in the chart and increase the font size to make it more readable.

Step 11. Write a statement under the scatterplot that is your economic interpretation of the trend line equation. The x is the x-axis variable and the y is the y-axis variable. This is a regression equation in which x is the independent variable that potentially causes changes in the y variable. The number in front of x is similar to a correlation coefficient. A positive value suggests that increases in x lead to increases in y and a negative value suggests that increases in x lead to decreases in y. Look at the number in your equation. What does this number tell us about the influence of COVID cases on unemployment? What do you think state policy makers should do with this key takeaway?

Step 12. If you would like to show any additional analysis with the data, you are welcome to add additional worksheets to your workbook!