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.

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

(newest minus oldest)/newest

5. Enter formulas in Row 27 to calculate the averages for columns B to L.
6. 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.
7. Create sparklines in column M for rows 6 to 25, using the line style. Set the marker color to dark orange.
8. 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.

9. On the GDP Data worksheet, set the Orientation to Landscape and the Margins to
10. 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.
11. Use Print Preview to set the printing option to fit all columns on one page.
12. 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.
13. 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.)
14. 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.
15. Position this chart on the Analysis worksheet at N2:U23.
16. 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.
17. Position this chart on the Analysis worksheet at B25:O48.
18. 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.
19. Position this chart on the Analysis worksheet at B50:O70.
20. Save your file, and submit into Canvas.

US GDP Gross Output 2008-2017

[in Billions of dollars]

Last Updated on February 10, 2019