Can someone please help me with this exercise? Exercise 4: Using Excel to Calculate Linear Population Projections PUP 424Planning Methods Assignment Overview This assignment gives you the chance to practice calculating linear population projections utilizing Excel. Open the file titled "Exercise 4 Data" on Blackboard found under the "Exercises" tab. In addition, generate a Word document on which you will answer the questions listed below (in question #10). Your Excel spreadsheet and Word doc are due via Blackboard.

Data

https://docs.google.com/spreadsheets/d/1lMF9ZlS_ElxosuvnrY7DQTIwIBTyegP6/edit?usp=sharing&ouid=117319736495943181499&rtpof=true&sd=true

Assignment Steps Open the Excel file and examine its contents. This table provides estimated population totals for the cities of Phoenix and Tempe for each year from 2000 through 2013. Follow the steps outlined below to calculate the linear population projections for both cities from 2014 through 2020. Fill in all of the highlighted cells on the spreadsheet. All other cells are locked and cannot be altered. 1. First you need to determine the total sum of the observed populations for each year from 2000 to 2013, as well as the total sum of index numbers (T) representing these years. In the Total Observed Population cell (B18) type =SUM(B4:B17) to add the observed populations for the 14 years. In the Total Index Numbers cell (D18) type =SUM(D4:D17) to add the index numbers representing the 14 years. 2. Next you need to determine the mean (average) observed population and index numbers for the 14-year period. In the Mean Observed Population cell (B19) type =B18/14 and in the Mean Index Number cell (D19) type =D18/14 to determine these averages. 3. Now you need to calculate the deviations from the mean for each of the observed populations and each of the index numbers. Under the Population p column, in cell E4 type =B4-B$19. Copy and paste this cell into the remaining cells in the column (E5 through E17). Note that the $ sign in the formula allows you to cut and paste the formula, without altering the selected line (19) on which the mean calculation is located. It is always good practice to review the formulas after pasting to ensure that they still reflect the cells you intended. After you have calculated the mean for the observed population, calculate the deviations from the mean for the index numbers by typing =D4-D$19 in cell F4. Cut and paste this formula in the remaining cells in the column (F5 through F17). If you sum the deviations from the mean for both columns by typing =SUM(E4:E17) in cell E18 and by typing =SUM(F4:F17) in cell F18, the totals should equal zero (or in the case of column E you may see an extremely small number listed in scientific notation...this is fine.) 4. Next you need to calculate the necessary cross-products for determining slope and intercept of the linear equation. So, in the column labeled p*t, type =E4*F4 in cell G4, and then cut and paste this formula into the remaining cells G5 through G17. For the column labeled t, type =F4*F4 in cell H4, and then cut and paste this formula into the remaining cells F5 through F17. To finish both columns, include a total in cell G18 by typing =SUM(G4:G17), and one in cell H18 by typing =SUM(H4:H17). (Hint: You should show "3175310" in G18 and "227.5" in H18. If not, recheck your spreadsheet formulas and correct any mistakes you may have made before continuing to the next step.) 5. Now you are ready to determine the slope and intercept of the linear equation. As per the formula for the slope (), you will divide the total sum of p*t (in cell G18) by the total sum of the t values (found in cell H18). So, in cell J4 type =G18/H18. 6. To determine the intercept (), follow the formula provided by subtracting the slope you just calculated in J4 from the mean of the population (cell B19) and then multiplying by the mean of the index numbers T (listed in cell D19). So, in cell K4 type =B19-J4*D19. 7. To finish the spreadsheet info for Phoenix, you now need to calculate the linear population projections for 2014 through 2020 by using the slope and intercept calculated in 5 and 6 above. Utilizing the given formula, in cell B23 add the slope (cell J4) with the intercept (cell K4) and multiply by the index year T (cell D23). So type =K$4+J$4*D23 in cell B23 (the dollar signs will lock the slope and intercept cells in the formula) and cut and paste the results into the rest of the column (cells B24 through B29). These totals are your annual linear population projections for Phoenix through 2020. 8. Finally, you need to generate a line chart of the observed and projected Phoenix population from years 2000 through 2020. So, hold the control key (Command for Mac) and highlight all of the population counts for these years (cells B4 through B17 and B23 through B29). Next, look under the "Insert" tab and click on the line chart option. A line chart should now appear with the population counts and projections shown. Under the "Design" tab, choose the "Select Data" option. Under the "Horizontal Axis Labels" select the edit option, and then highlight all of the cells containing the year names (cells C4 through C17 and C23 through C29) and click OK. You should now see the correct year labels shown along the horizontal axis. Finally, under the "Design" tab, select a chart layout that includes a main title and enter "Phoenix Linear Population Projections" as the title for the chart. 9. Now complete the same steps 1 through 8 above, but for the Tempe data shown in the bottom portion of the Excel spreadsheet. Utilizing the correct formulas (you will need to adjust them appropriately), complete these steps to calculate the linear population projections using Tempe's population data. generate a chart (as outlined in step 8) to visualize these projections and include the title "Tempe Linear Population Projections." 10. Once you have completed the population projections, review the line charts you created and answer the following questions: a. Which city appears most likely to surpass its pre-recession population peak sometime before 2020? Do the projections for this city appear to make sense in your opinion, or do you feel they under- or over-project the population change likely to be experienced the rest of this decade? Explain. b. Which city appears unlikely to surpass its pre-recession population peak sometime before 2020? Do you agree with these projections? Explain. c. How well do you feel the linear projection model produced reasonable population projections for these two cities? Do you feel a different projection method discussed in class (geometric, parabolic, share of growth, or shift-share) would be more appropriate for projecting population in Phoenix and Tempe? Explain your reasoning why or why not.

Reference:

Wang & von Hofe text: Research Methods in Urban and Regional Planning

Chapter 3 Chapter 8