Question

The data below is the total spending (in millions of dollars) on drugs and other non-durable products for your assigned state (or DC). You need to convert this data to spending per capita in constant 2019 dollars.

Go to the FRED database at https://fred.stlouisfed.org/

Search for the PCEPI. Change the frequency to annual. Using that price index (this is a national index; there isn't a PCE index for each state), convert the following to 2019Q3 dollars.

Again using the FRED database, find the population for your state. The symbol is usually the two letter abbreviation for the state and POP. New York, for example, would be NYPOP.

Using this information, covert the spending below into spending per capita, in 2019Q3 dollars. Keep in mind that the values below are in millions of dollars and you want your answers in dollars.

Enter your results for every even-numbered year in the answer

Your assigned state:

Alaska

Year	Total spending on drugs and other non-durable products (millions of dollars)
1991	142
1992	149
1993	153
1994	162
1995	156
1996	179
1997	209
1998	229
1999	262
2000	290
2001	317
2002	358
2003	411
2004	425
2005	455
2006	499
2007	531
2008	524
2009	503
2010	485
2011	480
2012	468
2013	430
2014	471

Answer 1

Steps:

1. Populate year and Total spending on drugs and other non-durable products (millions of dollars) data in column A and B respectively.

2. Download and update PCEPI data fill 2018 and 2019Q3 as shown in column C (base year = 2012)

3. Convert the PCEPI into 2019Q3 base by multiplying downloaded series by 100 and dividing by 2019Q3 number (i.e., 109.927)

4. Convert total spending on drugs and other non-durable products (millions of dollars) into 2019Q3 dollars by multiplying spending for each year with corresponding PCEPI and dividing by 100 (since 2019Q3 is converted as 100).

5. Download population data for Alaska (expressed in thousands) as shown in column F

6. Calculate per capita expenditure on drugs and other non-durable products by multiplying column E by 1000 and dividing by column F, since spending on drugs and other non-durable is in million dollars and population in thousands.

The required table for even-numbered years is as follows:

The formula view of the spreadsheet is as follows: