NP_EX19_4a_AvivLevy_Report_2

New Perspectives Excel 2019 | Module 4: SAM Project 1a Aviv Levy GE ver. 12.0.0-rc0000 1. 7/7 Apply a conditional formatting rule that uses data bars. Change the fill color of conditional formatting data bars. 2. 7/7 Add sparklines to cells. 3. 7/7 Set the color of a sparkline. 4. 7/7 Insert a chart title. Change the data label options. Change the position of the data labels. Remove a legend from a chart. 5. 8/8 Insert a chart. Resize and reposition a chart. 6. 8/8 Insert a chart title. Change the chart style. 7. 8/8 Insert a chart. Resize and reposition a chart. 8. 8/8 Insert a chart title. Change the maximum bounds of the horizontal axis. Change the fill color of a chart area. 9. 8/8 Change a chart's worksheet. Resize and reposition a chart. 10. 2/8 Change the chart colors scheme. Add an axis to a chart. In the Projection worksheet, the combo chart's Total series should be on a secondary vertical axis. Add an axis title to a chart. Add an axis title to a chart. In the Projection worksheet, the combo chart's Total series should be on a secondary vertical axis. Remove an axis title from a chart. In the Projection worksheet, the combo chart's primary horizontal axis title should not be displayed. 11. 8/8 Add sparklines to cells. Set a scale of a sparkline axis to a custom value. Set a scale of a sparkline axis to a custom value. 12. 4/8 Insert a chart. Resize and reposition a chart. 13. 8/8 Insert a chart title. Add a data labels to a chart. SUBMISSION #2 | SCORE IS: 90 OUT OF 100 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. In the range E5:E10, add Conditional Formatting to show Solid Blue Data Bars . In the range F5:F10, add Line sparklines based on the data in the range B5:E10. Apply the Blue, Accent 1, Darker 25% (5th column, 5th row in the Theme Colors palette) sparkline color to the range F5:F10. Gudrun has created a pie chart representing the percentage of the parks' budget that went to each park in 2018. Modify the chart in the range G2:O20 as follows: a. Enter 2018 Park Spending as Percentage of Total as the chart title. b. Change the data labels to include the Category as well as the Percentage, and position the labels in the Outside End location. c. Remove the Legend from the chart. Gudrun would like a pie chart representing the percentage of the parks' budget that would go to each park in 2021. a. Create a 2-D Pie chart based on the data in the range E5:E9 and using the category labels in the range A5:A9. b. Resize and reposition the chart so that the upper-left corner is located within cell G21 and the lower- right corner is located within cell O39, and then left-align the chart with the pie chart above it in the range G2:O20. Customize the chart in the range G21:O39 as follows: a. Enter 2021 Park Spending as Percentage of Total as the chart title. b. Apply Style 3 to the chart. Gudrun wants a stacked bar chart showing spending per park for each year. a. Create a Stacked Bar chart based on the data in the range A4:E9. b. Resize and reposition the chart so that the upper-left corner is located within cell A11 and the lower- right corner is located within cell F39. Customize the chart in the range A11:F39 as follows: a. Enter Spending per Park 2018-2021 as the chart title. b. Change the Maximum value to $2,300,000 on the horizontal axis. c. Apply a shape fill of White, Background 1, Darker 5% (1st column, 2nd row in the Theme Colors palette) to the chart area. Update the Park Spending 2018-2021 combo chart in the range A40:G56 as follows: a. Move the chart to the Projection worksheet. b. Resize and reposition the chart so that the upper-left corner is located within cell A4 and the lower- right corner is located within cell K22. For the Park Spending 2018-2021 combo chart, update the chart area as follows: a. Apply the Monochromatic Palette 4 color scheme to the chart. b. Add a secondary axis for the Total series. [Mac Hint: Select the Total series and use the format pane to add the axis.] c. Add Axis Titles to the chart, and then enter Spending per park as the left vertical axis title, and then enter Total spending as the right vertical axis title. Finally, delete the horizontal axis title placeholder. In the Projection worksheet, the combo chart's primary vertical axis title should be "Spending per park". 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 range B9:D10 to calculate the quarterly and annual payments for each option. In the range E9:E10, add Column sparklines using the data in the range B9:D10. Ungroup the sparklines, and then set the vertical axis maximum value to -15000 for cell E9 and to -60000 for cell E10. Gudrun wants a clustered column chart comparing the costs of the bond options. a. Create a Clustered Column chart based on the values in the range A9:D10. b. Resize and reposition the chart so that the upper-left corner is located within cell A11 and the lower- right corner is located within cell D28. In the Bond Offering worksheet, the clustered column chart should be repositioned and resized so that the upper-left corner is located within cell A11 and the lower-right corner is located within cell D28. Customize the chart in the range A11:D28 as follows: a. Enter Bond Offering Cost Comparison as the chart title. b. Add Data Labels to the chart.

Alanis Parks Department ANALYZE AND CHART FINANCIAL DATA Author: Aviv Levy New Perspectives Excel 2019 | Module 4: SAM Project 1a Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy the file from the SAM website.

Alanis Parks Department Spending on Parks, 2018-2021 Park 2018 2019 2020 2021 Trend Carver $ 172,331 $ 178,606 $ 180,624 $ 189,794 Fern-Foley $ 225,280 $ 241,898 $ 247,033 $ 252,853 Oleander $ 302,212 $ 302,805 $ 310,247 $ 319,880 Pleistocene $ 200,098 $ 207,832 $ 221,167 $ 237,810 Sartoris $ 552,636 $ 569,846 $ 575,239 $ 586,124 Total $ 1,452,557 $ 1,500,988 $ 1,534,311 $ 1,586,460 Carver Fern-Foley Oleander Pleistocene Sartoris -$200,000 $300,000 $800,000 $1,300,000 $1,800,000 $2,300,000 Spending per Park 2018-2021 2018 2019 2020 2021

Carver; 11.86% Fern-Foley; 15.51% Oleander; 20.81% Pleistocene; 13.78% Sartoris; 38.05% 2018 Park Spending as Percentage of Total 11.96% 15.94% 20.16% 14.99% 36.95% 2021 Park Spending as Percentage of Total Carver Fern-Foley Oleander Pleistocene Sartoris

Alanis Parks Department Bond Offering Projection Option A Option B Option C Total Bond Revenue $575,000 $575,000 $575,000 Quarterly Interest Rate 0.0169 0.0175 0.0181 # of payments 60 60 60 Quarterly Payments ($15,314.08) ($15,555.68) ($15,799.25) Annual Payments ($61,256.31) ($62,222.73) ($63,197.00) 1 2 3 ($70,000.00) ($60,000.00) ($50,000.00) ($40,000.00) ($30,000.00) ($20,000.00) ($10,000.00) $0.00 Bond Offering Cost Comparison Quarterly Payments Annual Payments