New Perspectives Excel 2019 | Module 4: SAM Critical Thinking Project 1c Alanis Parks Department   ANALYZE AND CHART FINANCIAL DATA GETTING STARTED Open the file NP_EX19_CT4c_FirstLastName_1

Subject:MS ExcelPrice:19.99 Bought3

Share With

New Perspectives Excel 2019 | Module 4: SAM Critical Thinking Project 1c

Alanis Parks Department

 

ANALYZE AND CHART FINANCIAL DATA

GETTING STARTED

• Open the file NP_EX19_CT4c_FirstLastName_1.xlsx, available for download from the SAM website.
• Save the file as NP_EX19_CT4c_FirstLastName_2.xlsx by changing the “1” to a “2”.
  • If you do not see the .xlsx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
• With the file NP_EX19_CT4c_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. Gudrun Duplessis is a manager in the Alanis Parks Department. Gudrun is compiling spending data in preparation for a city bond offering for park funding. She wants to use Excel to create charts to illustrate some of her data and to apply a function to calculate the potential monthly and yearly costs to the city of different bond scenarios.
   Switch to the Spending worksheet. Gudrun wants to add some visuals to supplement the table data. Add solid blue Data Bars to the 2021 park spending column.
2. To quickly show how the data has changed over time, add line sparklines to the trend column based on all of the table data.
3. Change the sparkline color to Blue, Accent 1, Darker 25%.
4. Gudrun has created a pie chart representing the percentage of the parks' budget that went to each park in 2018. Modify the pie chart as follows:
   1. Enter 2018 Park Spending as Percentage of Total as the chart title.
   2. Change the data labels to include the category as well as the percentage, and move the labels to the outside of the pie chart.
   3. Remove the legend from the chart.
5. Gudrun would like a pie chart representing the percentage of the parks' budget that would go to each park in 2021.
   1. Create a 2-D pie chart based on the individual park spending data for 2021, using the park names as the category labels.
   2. Size the chart so that it covers the range G21:O39, and then left-align the chart with the pie chart above it.
6. Customize the pie chart for 2021 as follows:
   1. Enter 2021 Park Spending as Percentage of Total as the chart title.
   2. Apply the same chart style as the 2018 chart.
7. Gudrun also wants a stacked bar chart showing the spending per park for each year.
   1. Create a stacked bar chart based on the individual park spending data from all years shown, using the park names as the category labels.
   2. Size the chart so that it covers the range A11:F39.
8. Customize the stacked bar chart as follows:
   1. Enter Spending per Park 2018-2021 as the chart title.
   2. Change the Maximum value of the horizontal axis to $2,300,000.
   3. Change the fill color of the chart area to White, Background 1, Darker 5%.
9. Gudrun wants the combo chart to have its own worksheet. Update the Park Spending 2018-2021 combo chart as follows:
   1. Move the chart to the Projection worksheet.
   2. Size the chart so it covers the range A4:K22.
10. The chart could use some changes, as the individual park data is hard to read because of the scale of the total data. Update the combo chart area as follows:
    1. Apply the Monochromatic Palette 4 color scheme to the chart.
    2. Add a secondary axis for the Total series. [Mac Hint: Select the Total series and use the format pane to add the axis.]
    3. Add axis titles to the chart, with Spending per park as the left vertical axis title, and Total spending as the right vertical axis title. Delete the horizontal axis title placeholder.
11. Gudrun wants to compare future payments for three different scenarios for a park's bond offering.
    Switch to the Bond Offering worksheet. She has already entered formulas in the table to calculate the quarterly and annual payments for each option. Add column sparklines to the right of the Quarterly and Annual payments using the data from all of the payment options. Ungroup the sparklines, and then set the vertical axis maximum value to -15000 for the quarterly payments sparkline, and to -60000 for the annual payments sparkline.
12. Finally, Gudrun wants a clustered column chart comparing the costs of the bond options.
    1. Create a clustered column chart based on the Quarterly and Annual payment options. Include Quarterly Payments and Annual Payments as the series names.
    2. Size the chart so that it covers the range A11:D28.
13. Customize the clustered column chart as follows:
    1. Enter Bond Offering Cost Comparison as the chart title.
    2. Add a data table with legend keys to the chart.
    3. Apply a 2 point border to the chart area, and then change the border color to match the fill color of the table's headings.

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

Final Figure 1: Spending Worksheet

Final Figure 2: Projection Worksheet

Final Figure 3: Bond Offering Worksheet