1 2 3 Future Income Projection CPI Values 2a Below, you will generate the projected income...

Transcribed Image Text:

1 2 3 Future Income Projection CPI Values 2a Below, you will generate the projected income 5 years in the future You will base your 6 projection on CPI values that you look up. Use this procedure to look up the CPI value: 7 20 3. Press the "Retrieve Data" button at the bottom of the list. This should take you to a CPI table for about the last 10 years. (You may need to change the values shown by using the "Change Output 2 Options" at the top of the page). 29 =0 20 3 4 First, fill in the CPI value for the given month (1 = Jan, 2 = Feb, etc.) and year. Then, fill in the CPI #1 25 values for the next 7 years, advancing exactly one year for each value. For example, if you start in 6 February 2011, your next CPI value will come from February 2012. Format your CPI values as 7 Numbers with 3 decimals of precision. 28 Month Number 2 #2 13 13 14 14 55 19 6 +7 Current Income 38 =9 10 1 12 12 13 1. Go to Bureau of Labor Statistics page link https://data.bls.gov/cgi-bin/surveymost?cu 2. Check the box to the left of text "U.S. city average, All items - CUUR0000SAO" 14 15 16 $105,536.00 9 9 9 9 9 9 9 9 9 Year 2014 2015 2015 2016 2016 2017 2018 2019 2020 2021 2022 CPI Value 238.031 237.945 241.428 246.819 252.439 256.759 260.28 274.31 296.808 Green Gold Any other color Enter a number Enter an Excel formula Make no changes 2b Now, from the year and CPI values, find the slope and y-intercept of the best-fit line for your CPI values. Format your values as Numbers with 3 decimals of precision. Slope (m) Y-Intercept (b) 6.445920103 1984.067595 2c Use your slope and y-intercept to project the CPI 5 years in the future based on the Year you are Projecting the Income to; calculate the 5- year Inflation Rate based on the last CPI value from the table in 2a and the Projected CPI Value; apply the 5-year Inflation Rate to your Current Income to find a 5-year Income Projection; and calculate the Projected Monthly Income. Use Excel formulas with cell references when appropriate for all gold cells. Format the Projected CPI Value as a Number with 3 decimal places and the 5-year Inflation Rate as a Percentage with 2 decimal places. Format Your Current Income, Your 5-year Income Projection, and Projected Monthly Income as Currency with 2 decimal places. Year you are Projecting your Income to (5 years after the last CPI value from 2a) Projected CPI Value for the Year you are Projecting to The 5-year Inflation Rate based on the last CPI value from 2a and the Projected CPI value Your Current Income (cell referenced from above)| Your 5-year Income Projection Projected Monthly Income 2027 Income and Projection - part 2c 1. 2. 3. 4. 5. 6. The Year you are projecting to represents 5 years after the last year in the CPI values table. Projected CPI Value for the Year you are Projecting to = slope*Year+y-intercept The five-year inflation rate is =(The projected CPI-last CPI value from 2a)/ last CPI value from 2a Cell reference the Current Income Your 5-year Income Proection inflation rate) current Projected Monthly Budget = Your 5-year Income Projection/12 ToJelleu LI I value as a univer with cumiar piaces and the Jaycan natin hale as a i cicilage will & uccinia nt Income, Your 5-year Income Projection, and Projected Monthly Income as Currency with 2 decimal places. Year you are Projecting your Income to (5 years after the last CPI value from 2a) Projected CPI Value for the Year you are Projecting to The 5-year Inflation Rate based on the last CPI value from 2a and the Projected CPI value Your Current Income (cell referenced from above) Your 5-year Income Projection Projected Monthly Income 2027 319.448 7.63% $60,736.00 $65,371.32 $5,447.61 1 2 3 Future Income Projection CPI Values 2a Below, you will generate the projected income 5 years in the future You will base your 6 projection on CPI values that you look up. Use this procedure to look up the CPI value: 7 20 3. Press the "Retrieve Data" button at the bottom of the list. This should take you to a CPI table for about the last 10 years. (You may need to change the values shown by using the "Change Output 2 Options" at the top of the page). 29 =0 20 3 4 First, fill in the CPI value for the given month (1 = Jan, 2 = Feb, etc.) and year. Then, fill in the CPI #1 25 values for the next 7 years, advancing exactly one year for each value. For example, if you start in 6 February 2011, your next CPI value will come from February 2012. Format your CPI values as 7 Numbers with 3 decimals of precision. 28 Month Number 2 #2 13 13 14 14 55 19 6 +7 Current Income 38 =9 10 1 12 12 13 1. Go to Bureau of Labor Statistics page link https://data.bls.gov/cgi-bin/surveymost?cu 2. Check the box to the left of text "U.S. city average, All items - CUUR0000SAO" 14 15 16 $105,536.00 9 9 9 9 9 9 9 9 9 Year 2014 2015 2015 2016 2016 2017 2018 2019 2020 2021 2022 CPI Value 238.031 237.945 241.428 246.819 252.439 256.759 260.28 274.31 296.808 Green Gold Any other color Enter a number Enter an Excel formula Make no changes 2b Now, from the year and CPI values, find the slope and y-intercept of the best-fit line for your CPI values. Format your values as Numbers with 3 decimals of precision. Slope (m) Y-Intercept (b) 6.445920103 1984.067595 2c Use your slope and y-intercept to project the CPI 5 years in the future based on the Year you are Projecting the Income to; calculate the 5- year Inflation Rate based on the last CPI value from the table in 2a and the Projected CPI Value; apply the 5-year Inflation Rate to your Current Income to find a 5-year Income Projection; and calculate the Projected Monthly Income. Use Excel formulas with cell references when appropriate for all gold cells. Format the Projected CPI Value as a Number with 3 decimal places and the 5-year Inflation Rate as a Percentage with 2 decimal places. Format Your Current Income, Your 5-year Income Projection, and Projected Monthly Income as Currency with 2 decimal places. Year you are Projecting your Income to (5 years after the last CPI value from 2a) Projected CPI Value for the Year you are Projecting to The 5-year Inflation Rate based on the last CPI value from 2a and the Projected CPI value Your Current Income (cell referenced from above)| Your 5-year Income Projection Projected Monthly Income 2027 Income and Projection - part 2c 1. 2. 3. 4. 5. 6. The Year you are projecting to represents 5 years after the last year in the CPI values table. Projected CPI Value for the Year you are Projecting to = slope*Year+y-intercept The five-year inflation rate is =(The projected CPI-last CPI value from 2a)/ last CPI value from 2a Cell reference the Current Income Your 5-year Income Proection inflation rate) current Projected Monthly Budget = Your 5-year Income Projection/12 ToJelleu LI I value as a univer with cumiar piaces and the Jaycan natin hale as a i cicilage will & uccinia nt Income, Your 5-year Income Projection, and Projected Monthly Income as Currency with 2 decimal places. Year you are Projecting your Income to (5 years after the last CPI value from 2a) Projected CPI Value for the Year you are Projecting to The 5-year Inflation Rate based on the last CPI value from 2a and the Projected CPI value Your Current Income (cell referenced from above) Your 5-year Income Projection Projected Monthly Income 2027 319.448 7.63% $60,736.00 $65,371.32 $5,447.61

Answer rating: 100% (QA)

Here are the steps to project income 5 years into the future based on CPI values 1 The last year in ... View the full answer