File Home Insert Page Layout Formulas Data Review View Help Share Con Split View Side by Side Default Ruler Formula Bar Normal Page Break Page Custom New Arrange Freeze Hide IDI Synchronous Scrolling Switch Macros H Keep Exit New Options Zoom 100% Zoom to Preview Layout Views Gridlines Headings Selection Window All Panes Unhide Reset Window Position Windows Sheet View Workbook Views Show Zoom Window Macros B26 X v fx A B C D E IN 3 Project Description: 4 In this problem, you will investigate the average annual CO2 concentration from 1959 to 2018. You will also provide the results as scatter charts. UT 6 Steps to Perform: Points Step Instructions Possible 60 00 - Start Excel. Download and open the workbook named: C Chapter_5-5_CO2_Start 10 2 In cell 08, determine in what season the high point will occur in the Northern Hemisphere. 2 11 3 In cell D8, calculate the total change of CO2 concentration in 1960 compared to 1959. Fill cell D8 down the column to cell D66. Format cells D8:D66 as Number with 2 decimal places. 12 4 In cell E8, calculate the annual percent change of CO2 in 1960. Fill cell E8 down the column to cell E66. Format cells E8:E66 as Percentage with 2 decimal places. 6 In cells G23:J36, insert a Scatter Chart to show the average annual CO2 concentration over years. Select range B7:C66. On the Insert tab, click Recommended Charts, then open the All Charts tab, and then choose Scatter. Apply Style 1 on the Design tab. Add a chart title and choose the Above Chart option. Replace Chart Title with Average Annual CO2 Concentration (ppm). Add vertical axis title. Replace Axis Title for the vertical axis with parts per million. To add a linear trendline to the data on the chart, select any point on the chart and right click on it. Select Add Trendline. In the Trendline Options window select Linear with automatic trendline 13 5 name and check the "Display equation on chart". Drag it to any place on the chart so that it is more visible to read. 14 15 Note: The category y-axis labels should be from 300 to 420 with increment 20. 16 6 In cell G43, determine the slope of the curve from the equation on the chart. NN 17 In cell H43, determine the y-intercept of the curve from the equation on the chart. 18 8 In cell H47, calculate the average annual CO2 concentration (ppm) in 2050 using values from part (c). Format cell H47 as Number with 0 decimal places. 19 In cells H52, H54, H55, and H57, complete the sentence by using the slope value and choosing the words from a drop-down list. NNA 20 10 In cell H59, determine whether CO2 has been increasing linearly over the last millennium. 21 11 In cell H63, determine what happens with the constant $5 deposit as a percentage of the linear monthly balance. In cells C68:F82, insert a Scatter Chart to show the annual percent change over time of the CO2 concentration. Select ranges B8:B66 and E8:E66. On the Insert tab, click Recommended Charts, then open the All Charts 12 tab, and then choose Scatter. Apply Style 1 on the Design tab. Add a chart title and choose the Above Chart option. Replace Chart Title with Annual % Change of CO2. To add a linear trendline to the data on the chart, select any point on the chart and right click on it. Select Add Trendline. In the Trendline Options window select Linear with automatic trendline name and check the "Display equation on chart". Drag it to any place on the 22 chart so that it is more visible to read. 23 13 In cell H68, determine whether the growth of CO2 will be linear or non-linear if the annual percent change is increasing. IN 24 14 Save your file and submit for grading. 0 Instructions Questions +File Home Insert Page Layout Formulas Data Review View Help 15 Share Comments Default Ruler Formula Bar Split DO View Side by Side Hide IDI Synchronous Scrolling H Keep Exit New Options Normal Page Break Page Custom Switch Preview Layout Views Gridlines Headings Zoom 100% Zoom to New Arrange Freeze Macros Selection Window All Panes Unhide Reset Window Position Windows Sheet View Workbook Views Show Zoom Window Macros P21 X V fx A B D E G H K M N O P Q R S T U V VA Un A Climate change has received a lot of attention lately. The first graphic shows the rise of Carbon Dioxide (CO2) concentration in b. ) b.) the atmosphere as measured at the Mauna Loa Observatory in parts per million (ppm). A measurement of 400 ppm means for Year Average Annual CO2 Total every 1,000,000 parts of a quantity of air (about 78% nitrogen and 20% oxygen) 400 of those parts are CO2 molecules. Percent Concentration (ppm) Change Change 1959 315.97 Co 1960 316.91 0.94 0.30% a.) The rise and fall of the line in the graphic is due to seasonal activity related to the activity of plants in the Northern a.) Spring 1961 317.64 0.73 0.23% Hemisphere. Plants absorb CO2, so would the high point of each seasonal cycle occur during spring or fall in the Northern 1962 318.45 0.81 0.26% Hemisphere? Atmospheric CO, at Mauna Loa Observatory 1963 318.99 0.54 0.17% 400 1964 319.62 0.63 Scripps Institution of Oceanography 0.20% b.) Data in a spreadsheet from http://co2now.org/current-co2/co2-now/annual-co2.html gives the annual concentration of NOAA Earth System Research Laboratory 1965 320.04 0.42 0.13% carbon dioxide in parts per million (ppm). Compute the annual total change of CO, concentration (format as number to 2 380 1966 321.38 1.34 0.42% decimal places) and the annual percent change (format as percentage to 2 decimal places). 1967 322.16 0.78 0.24% 360 1968 323.04 0.88 0.27% PARTS PER MILLION 1969 324.62 1.58 0.49% c.) Create a well-labeled scatterplot of the average annual CO, concentration over years and insert trendline and display 18 1970 325.68 1.06 0.33% equation. Format the vertical scale to start at 300. Type in the slope and y-intercept from the equation with as many decimal 340 19 1971 326.32 0.64 0.20% places as given. Axis label and the title should be chosen from the list: 20 1972 327.45 1.13 0.35% a. Average Annual CO2 Concentration (ppm) 320 June 2014 1973 329.68 2.23 0.68% b. parts per million 22 1974 330.18 0.5 0.15% 1960 1970 1980 1990 2000 2010 23 1975 331.08 0.9 0.27% C.) YEAR 1976 332.05 0.97 0.29% Average Annual CO2 Concentration (ppm) 1977 333.78 1.73 0.52% 420 y = 1.5611x - 2749.9 26 1978 335.41 1.63 0.49% 400 1979 336.78 1.37 0.41% 1980 338.68 1.9 0.56% 9 38 Recent History of Atmospheric CO2 1981 340.1 1.42 0.42% E 30 1982 341.44 1.34 0.39% 36 1983 343.0 1.59 300 0.47% parts p 360 9 340 Atmospheric 32 1984 344.58 1.55 0.45% CO2 (ppm) Emissions (GtC/yr) 340 PML Fossil Fuel Mana Lo 33 1985 346.04 1.46 0.42% 320 34 1986 347.3 1.35 0.39% ice core mospheric COz (ppm) 320 35 1987 349.16 1.77 0.51% 300 290 36 1950 1960 1970 1980 1990 2000 2010 2020 2030 Calender Year 1988 351.56 2.4 0.69% 300 27 10on Instructions Questions +File Home Insert Page Layout Formulas Data Review View Help Share Comments DO View Side by Side Default Ruler Formula Bar Split New Arrange Freeze Hide IDI Synchronous Scrolling Zoom 100% Zoom to Switch Macros H Keep Exit New Options Normal Page Break Page Custom Preview Layout Views Gridlines Headings Selection Window All Panes Unhide Reset Window Position Windows Sheet View Workbook Views Show Zoom Window Macros P21 X v fx M N Q R S U V B D E H 42 1994 358.82 1.75 0.49% Slope: Intercept: 1000 1200 1400 1600 1800 2000 43 1995 360.8 1.98 0.55% C.) From Equation: Calendar Year 44 1996 362.59 1.79 0.50% 45 1997 363.71 1.12 0.31% 46 1998 366.65 2.94 0.81%; 47 1999 368.33 1.68 0.46% d.) 2050 Estimate: d.) Enter a formula that uses the values from part (c) to predict the average annual CO, concentration (ppm) in 2050. 48 2000 369.52 1.19 0.32% Format as number to 0 decimal places. 49 2001 371.13 1.61 0.44% 2002 373.22 2.09 0.56% 2003 375.77 2.55 0.68% e.) The e.) Interpret the SLOPE value in a sentence by filling in the blanks in the sentence below. 2004 377.49 1.72 0.46% The is changing by _ ii_ iii per iv 2005 379.8 2.31 0.61% is changing by 54 2006 381.9 2.1 0.55% 55 2007 383.76 1.86 0.49% iii 56 2008 385.59 1.83 0.48% per 57 2009 387.37 1.78 0.46%! IV 58 2010 389.85 2.48 0.64% f.) Your scatterplot seems to be indicating a linear increase in CO2. Tacking this line onto ice core records of CO, over 59 2011 391.63 1.78 0.46% f. ) the last 1000 years gives us a new perspective in the second graphic above. Is it accurate to say CO, has been 60 2012 393.82 2.19 0.56% increasing linearly over the last millennium? 61 2013 396.48 2.66 0.68% 62 2014 398.61 2.13 0.54% 63 2015 400.83 2.22 0.56% g. ) g.) Another way to check for linear growth is to consider the annual percent change. For example, if you start with $20 2016 402.52 1.69 0.42% in a savings account and add $5 to it every month, the balance will grow linearly. But the $5 which is originally 25% of 64 the $20 will only be 20% of the $25 balance in month two. Is the constant $5 deposit as a percentage of the linear 65 2017 406.13 3.61 0.90% 66 monthly balance going to increase or decrease? 2018 407.98 1.85 0.46% 67 68 h. ) h.) h.) Create a scatterplot showing the annual percent change over time of the CO, concentration, insert trendline and 69 display equation. Does the fact that the annual percent change is increasing indicate that the growth of COz 70 concentration from 1959 to 2018 is linear or non-linear? The title should be: Annual % Change of CO2. 71 72 73 75 76 Instructions Questions +