Calculation Excel Work Project work, with some calculations needed to be done on excel. Project 3 Instructions Starting File: Project 3 – GDP – Student.xls

Calculation Excel Work Project work, with some calculations needed to be done on excel. Project 3 Instructions
Starting File: Project 3 – GDP – Student.xlsx
You are developing an analysis of U.S. GDP for various industry segments. Follow the steps to produce the results shown
in Figure 1 at the end of this document.
Notes:
• Remember: Project work must be your own individual work – no group work allowed!
• This workbook has 3 worksheets: the Documentation worksheet (for the history of this workbook), The GDP
Data worksheet, and the Analysis worksheet. Be sure to work on the correct worksheet as indicated in the
instructions.
• You should use cell references in all formulas – do not re-enter data.
• If your version of Excel does not have the same names for the colors and styles referenced here, select a color
and style that gives an equivalent appearance (use the image at the end of this document to verify).
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 B3 and 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:
a. Format data in columns B to K with Number style with 1 decimal place.
b. 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 Narrow.
10. Insert a footer with the phrase “Prepared by “, followed by your name, in the left side of the footer. Put the date
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 only. Format your chart so
that its appearance matches the chart shown in Figure 2 below, which will include these features:
a. Display major horizontal gridlines.
b. Position the legend on the bottom of the chart.
c. Add a chart title as shown.
d. 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.
Copyright © 2019 by P. McDermott-Wells – All Rights Reserved.
May not be posted on any internet site except FIU Canvas.
CGS 2518 – Rev. 01/26/2019
13.
14.
15.
16.
17.
18.
19.
20.
e. 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.
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.)
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:
a. Choose Style 3 (in Windows Excel) that provides a light gray gradient background in the plot area.
b. Display major vertical gridlines.
c. Position the legend on the bottom of the chart.
d. Add a solid gray border to the legend.
e. Modify the chart title as shown.
Position this chart on the Analysis worksheet at N2:U23.
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:
a. Position the legend on the right.
b. Add a solid light gray border to the legend.
c. Explode the Utilities slice of the pie.
d. Position data labels on the outside edge.
e. Format data labels as percentages with 1 decimal places.
f. Add a chart title as shown.
Position this chart on the Analysis worksheet at B25:O48.
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:
a. Display major horizontal gridlines.
b. Adjust the range of the vertical axis to show from minus 30% to plus 40%, with a decimal place.
c. Display the data labels as percentages with 2 decimal places
d. Add a chart title as shown.
Position this chart on the Analysis worksheet at B50:O70.
Save your file, and submit into Canvas.
Copyright © 2019 by P. McDermott-Wells – All Rights Reserved.
May not be posted on any internet site except FIU Canvas.
CGS 2518 – Rev. 01/26/2019
Figure 1
Copyright © 2019 by P. McDermott-Wells – All Rights Reserved.
May not be posted on any internet site except FIU Canvas.
CGS 2518 – Rev. 01/26/2019
Figure 2
Copyright © 2019 by P. McDermott-Wells – All Rights Reserved.
May not be posted on any internet site except FIU Canvas.
CGS 2518 – Rev. 01/26/2019
Figure 3
Figure 4
Copyright © 2019 by P. McDermott-Wells – All Rights Reserved.
May not be posted on any internet site except FIU Canvas.
CGS 2518 – Rev. 01/26/2019
Figure 5
Copyright © 2019 by P. McDermott-Wells – All Rights Reserved.
May not be posted on any internet site except FIU Canvas.
CGS 2518 – Rev. 01/26/2019
US GDP Gross Output 2008-2017
Author
Date
Purpose
US GDP Gross Output 2008-2017
[in Billions of dollars]
INDUSTRY SEGMENT
Agriculture, forestry, fishing, and hunting
Mining
Utilities
Construction
Manufacturing
Durable goods
Nondurable goods
Wholesale trade
Retail trade
Transportation and warehousing
Information
Finance, insurance, real estate
Real estate and rental and leasing
Professional and business services
Management of companies and enterprises
Educational services
Health care and social assistance
Arts, entertainment, and recreation
Accommodation and food services
Other services, except government
TOTALS
AVERAGES
Source: Bureau of Economic Analysis
2008
374,7
614
524,9
1275,8
5493,7
2687,1
2806,7
1327,1
1277,2
906,8
1254,9
4653,3
2660,6
2739,2
432,6
257,3
1641,3
247,5
730,9
550,5
2009
340,4
404
436,5
1099
4511,1
2152,1
2359
1154,8
1207,7
782
1219,7
4472,7
2602,2
2593,6
400,2
273,5
1724,1
244,1
711,1
521,6
2010
368,8
499,3
494,5
1016,7
5019,1
2382,5
2636,6
1359,2
1299,7
856,3
1268,9
4587,5
2679,3
2712,2
422,2
292,9
1798
245,9
733,3
535
2011
432,5
601,2
484,9
1020,5
5592,9
2599,1
2993,7
1530,8
1335,9
954,2
1324,8
4736,4
2767,5
2868,6
446,5
306,4
1867,2
252,1
777,9
548,9
2012
452,6
613
461,5
1074,6
5771,3
2699,7
3071,6
1655
1403,5
1002
1382,5
5036,8
2896,2
3009
478,8
309,4
1956
264,5
828,9
576,3
t 2008-2017
lars]
2013
488,5
662,9
491,7
1155,7
5939,3
2789,6
3149,8
1734,8
1480
1036,8
1437,3
5305,3
3034,6
3095,1
499
319,8
1993,9
274,1
865,3
584,5
2014
500,9
743,8
537,7
1261,1
6039,6
2905,9
3133,6
1814,4
1550,7
1105
1510,1
5635,5
3208,1
3280,6
514
330,8
2070
288,4
924,8
620,8
2015
455,7
492,9
514,2
1376,4
5714,7
2900,3
2814,3
1829,6
1648,6
1111,7
1570
5911,7
3379,2
3421,2
525,4
341,3
2189,2
305,3
991,8
641,8
2016
428,2
382
477,3
1478
5573
2836,3
2736,7
1817,7
1720,5
1104,1
1659,3
6200,9
3573,2
3593,2
535,5
349
2307,1
320,2
1043,4
674,4
2017
% Change
435,8
487,9
489,6
1534,7
5889,5
3004,5
2885
1928,1
1792,5
1155,5
1738,4
6556,3
3752,9
3796,6
548,9
357,3
2424,1
340,4
1071,5
697
Trends

Purchase answer to see full
attachment