assignment np excel2013 t4 p1b

5
PROJECT DESCRIPTION Carolyn Allen is the coordinator of the Friends of Glasswater Park, a nonprofit organization that raises funds and organizes volunteers to maintain Glasswater Park in Springfield, Missouri. The organization is midway through a decade-long fundraising campaign to raise capital for improving and expanding the park facilities. Carolyn has started a workbook that summarizes the organization’s fundraising thus far for the new facilities. She would like you to modify the workbook by adding charts and graphs to depict the information visually, as well as complete a projection of fundraising amounts for the remaining period of the campaign. GETTING STARTED Download the following file from the SAM website: o NP_Excel2013_T4_P1b_FirstLastName_1.xlsx Open the file you just downloaded and save it with the name: o NP_Excel2013_T4_P1b_FirstLastName_2.xlsx o If you do not see the .xlsx file extension in the Save file dialog box, do not type it. Excel will add the file extension for you automatically. With the file NP_Excel2013_T4_P1b_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet. If cell B6 does not display your name, delete the file and download a new copy from the SAM website. PROJECT STEPS 1. On the 2012-2016 Funds Raised worksheet, add green gradient data bars to the range H4:H6. 2. Create Column Sparklines in the range G4:G7 based on the data in the range B4:F7. Change the style of the sparklines to Sparkline New Perspectives Excel 2013 Tutorial 4: SAM Project 1b Friends of Glasswater Park

Upload: deepak-baid

Post on 24-Dec-2015

127 views

Category:

Documents


0 download

DESCRIPTION

Question

TRANSCRIPT

Page 1: Assignment NP Excel2013 T4 P1b

PROJECT DESCRIPTION

Carolyn Allen is the coordinator of the Friends of Glasswater Park, a nonprofit organization that raises funds and organizes volunteers to maintain Glasswater Park in Springfield, Missouri. The organization is midway through a decade-long fundraising campaign to raise capital for improving and expanding the park facilities. Carolyn has started a workbook that summarizes the organization’s fundraising thus far for the new facilities. She would like you to modify the workbook by adding charts and graphs to depict the information visually, as well as complete a projection of fundraising amounts for the remaining period of the campaign.

GETTING STARTED

Download the following file from the SAM website:

o NP_Excel2013_T4_P1b_FirstLastName_1.xlsx

Open the file you just downloaded and save it with the name:

o NP_Excel2013_T4_P1b_FirstLastName_2.xlsx

o If you do not see the .xlsx file extension in the Save file dialog box, do not type it. Excel will add the file extension for you automatically.

With the file NP_Excel2013_T4_P1b_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet. If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

PROJECT STEPS

1. On the 2012-2016 Funds Raised worksheet, add green gradient data bars to the range H4:H6.

2. Create Column Sparklines in the range G4:G7 based on the data in the range B4:F7. Change the style of the sparklines to Sparkline Style Accent 6, Lighter 40% (6th column, 4th row of the Sparkline Style gallery).

3. Add the chart title Facilities Funds Raised 2012-2016 to the clustered column chart using the Above Chart title option.

4. Add the primary vertical axis title Amount Raised to the clustered column chart.

5. In the clustered column chart, make the following adjustments to the vertical axis:

New Perspectives Excel 2013

Tutorial 4: SAM Project 1b

Friends of Glasswater ParkCREATE A FUNDRAISING PLAN

Page 2: Assignment NP Excel2013 T4 P1b

New Perspectives Excel 2013| Tutorial 4: SAM Project 1b

a. Change the number format of the vertical axis to Accounting with 0 decimal places.

b. Change the major units value for the vertical axis to 150000.0

6. Add Primary Major Horizontal Gridlines to the chart.

7. Change the position of the clustered column chart’s legend using the Top legend option.

8. Create a 3-D Pie chart based on the data in the range H4:H6. Change the chart title to Shares Raised By Source and then move the chart so that the top left corner is in cell H9.

9. In the 3-D pie chart, modify the legend labels to use the label text from the range A4:A6.

10. In the 3-D pie chart, make the following adjustments:

a. Change the location of the chart legend to Top.

b. Add data labels to the chart and change the label options so that the data label contains only the Percentage.

11. Change the location of the data labels in the 3-D pie chart using the Outside End data label option.

12. On the 2017-2020 Projection worksheet, create Column Sparklines in the range F4:F7 based on the data in B4:E7. Change the Sparkline style to Sparkline Style Accent 6, Lighter 40% (6th column, 4th row of the Sparkline Style gallery).

13. Use the Quick Analysis tool on the range A3:E6 to insert a Clustered Column chart, using the first clustered column chart recommendation on the Charts tab. (Hint: The columns in this chart are grouped by year.) Move and resize the chart so that the top left corner is in cell A9 and the bottom right corner is in cell G26.

14. Change the chart title of the clustered column chart to Projected Facilities Fundraising 2017-2020. Then add the primary vertical axis title Amount Raised to this chart.

15. Change the major units value for the vertical axis to 150000.0.

16. Change the style of the clustered column chart to Style 7 (7th choice in the chart styles palette.)

17. In the 3-D pie chart, change the font size of the title to 16 pt.

18. Remove the legend from the 3-D pie chart.

Page 3: Assignment NP Excel2013 T4 P1b

New Perspectives Excel 2013| Tutorial 4: SAM Project 1b

19. In the 3-D pie chart, make the following adjustments to the data labels:

a. Change the position of the data labels using the Outside End data label option.

b. Change the label options so that the data label contains the Category Name and the Percentage.

c. Change the data label number option to Percentage Number style with 0 decimal places.

Your workbook should look like the Final Figure below. Save your changes, close the workbook, and exit Excel. Follow the directions on the SAM website to submit your completed project.

Final Figure 1: 2012 – 2016 Funds Raised Worksheet

Microsoft product screenshot reprinted with permission from Microsoft Incorporated. Copyright © 2014 Cengage Learning. All Rights Reserved.

Page 4: Assignment NP Excel2013 T4 P1b

New Perspectives Excel 2013| Tutorial 4: SAM Project 1b

Final Figure 2: 2017-2020 Projection Worksheet

Copyright © 2014 Cengage Learning. All Rights Reserved.