Project 3 – GDP

Steps:

Download the starting file from the Assignment Dropbox and save it on your computer.  Then open it to work on it.

  1. In the Documentation worksheet, enter your name in cell B3and the date you created this workbook in cell B4.
  2. Add a brief business purpose statement in cell B5. Wrap the text if needed.

 

Switch to the GDP Data worksheet. Use formulas with cell references to complete these steps.

  1. Enter formulas in Row 26 to calculate the totals for columns B to K.
  2. Enter formulas in L6:L25 to calculate the percentage change from 2008 to 2017. Percentage change is calculated as follows:

(newest minus oldest)/newest

  1. Enter formulas in Row 27 to calculate the averages for columns B to L.
  2. Formatting:
    1. Format data in columns B to Kwith Number style with 1 decimal place.
    2. Format data in column L as Percentage style with 1 decimal place.
  3. Create sparklines in column M for rows 6 to 25, using the line style. Set the marker color to dark orange.
  4. Format data in B6:K27 with the Number style, with 1 decimal place, and including the 1000s separator character (the comma). Adjust column widths if needed to ensure that the values are displayed.

Note: Your data on this worksheet should now match the data shown in Figure 1 below.

  1. On the GDP Data worksheet, set the Orientation to Landscape and the Margins to
  2. Insert a footer with the phrase “Prepared by “, followed by your name, in the left side of the footer. Put thedate you completed this worksheet in the right side of the footer.
  3. Use Print Preview to set the printing option to fit all columns on one page.
  4. Create a clustered column chart showing each industry segment for years 2015-2017 Format your chart so that its appearance matches the chart shown in Figure 2 below, which will include these features:
    1. Display major horizontal gridlines.
    2. Position the legend on the bottom of the chart.
    3. Add a chart title as shown.
    4. Add a primary vertical axis title of “Billions” as shown. Note: this may be called Vertical (Value) Axis Title instead of primary vertical axis, depending on your version of Excel.
    5. Add a primary horizontal axis title of “Industry Segment” as shown.Note: this may be called Horizontal (Category) Axis Title instead of primary horizontal axis, depending on your version of Excel.
  5. Move the chart to the Analysis worksheet and position it at B2:L23. (Hint: with the chart selected, use the Move Chart button on the Chart Tools – Design ribbon.)
  6. Create a line chart showing GDP Gross Output from 2008 to 2017 for these industry segments only: Agriculture, Mining, Utilities, Educational Services, and Arts, Entertainment and Recreation. (Hint: You must know how to select non-adjacent ranges to create this chart in one operation.) Format your chart so that its appearance matches the chart shown in Figure 3 below, which will include these features:
    1. Choose Style 3 (in Windows Excel) that provides a light gray gradient background in the plot area.
    2. Display major vertical gridlines.
    3. Position the legend on the bottom of the chart.
    4. Add a solid gray border to the legend.
    5. Modifythe chart title as shown.
  7. Position this chart on the Analysis worksheet at N2:U23.
  8. Create a 3-D pie chart for 2017 GDP for the same selected industry segments as the line chart in Step 14. Format your chart so that its appearance matches the chart shown in Figure 4 below, which will include these features:
    1. Position the legend on the right.
    2. Add a solid light gray border to the legend.
    3. Explode the Utilitiesslice of the pie.
    4. Position data labels on the outside edge.
    5. Format data labels as percentages with 1 decimal places.
    6. Add a chart title as shown.
  9. Position this chart on the Analysis worksheet at B25:O48.
  10. Create a clustered column chart for percent change for all sectors. Format your chart so that its appearance matches the chart shown in Figure 5 below, which will include these features:
    1. Display major horizontal gridlines.
    2. Adjust the range of the vertical axis to show from minus 30% to plus 40%, with a decimal place.
    3. Display the data labels as percentages with 2 decimal places
    4. Add a chart title as shown.
  11. Position this chart on the Analysis worksheet at B50:O70.
  12. Save your file, and submit into Canvas.

US GDP Gross Output 2008-2017

[in Billions of dollars]

INDUSTRY SEGMENT 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 % Change Trends
Agriculture, forestry, fishing, and hunting 374.7 340.4 368.8 432.5 452.6 488.5 500.9 455.7 428.2 435.8
Mining 614 404 499.3 601.2 613 662.9 743.8 492.9 382 487.9
Utilities 524.9 436.5 494.5 484.9 461.5 491.7 537.7 514.2 477.3 489.6
Construction 1275.8 1099 1016.7 1020.5 1074.6 1155.7 1261.1 1376.4 1478 1534.7
Manufacturing 5493.7 4511.1 5019.1 5592.9 5771.3 5939.3 6039.6 5714.7 5573 5889.5
Durable goods 2687.1 2152.1 2382.5 2599.1 2699.7 2789.6 2905.9 2900.3 2836.3 3004.5
Nondurable goods 2806.7 2359 2636.6 2993.7 3071.6 3149.8 3133.6 2814.3 2736.7 2885
Wholesale trade 1327.1 1154.8 1359.2 1530.8 1655 1734.8 1814.4 1829.6 1817.7 1928.1
Retail trade 1277.2 1207.7 1299.7 1335.9 1403.5 1480 1550.7 1648.6 1720.5 1792.5
Transportation and warehousing 906.8 782 856.3 954.2 1002 1036.8 1105 1111.7 1104.1 1155.5
Information 1254.9 1219.7 1268.9 1324.8 1382.5 1437.3 1510.1 1570 1659.3 1738.4
Finance, insurance, real estate 4653.3 4472.7 4587.5 4736.4 5036.8 5305.3 5635.5 5911.7 6200.9 6556.3
Real estate and rental and leasing 2660.6 2602.2 2679.3 2767.5 2896.2 3034.6 3208.1 3379.2 3573.2 3752.9
Professional and business services 2739.2 2593.6 2712.2 2868.6 3009 3095.1 3280.6 3421.2 3593.2 3796.6
Management of companies and enterprises 432.6 400.2 422.2 446.5 478.8 499 514 525.4 535.5 548.9
Educational services 257.3 273.5 292.9 306.4 309.4 319.8 330.8 341.3 349 357.3
Health care and social assistance 1641.3 1724.1 1798 1867.2 1956 1993.9 2070 2189.2 2307.1 2424.1
Arts, entertainment, and recreation 247.5 244.1 245.9 252.1 264.5 274.1 288.4 305.3 320.2 340.4
Accommodation and food services 730.9 711.1 733.3 777.9 828.9 865.3 924.8 991.8 1043.4 1071.5
Other services, except government 550.5 521.6 535 548.9 576.3 584.5 620.8 641.8 674.4 697
TOTALS
AVERAGES
Source:  Bureau of Economic Analysis