Alanis Parks Department

ANALYZE AND CHART FINANCIAL DATA

GETTING STARTED

$$ Open the file NP$\_$EX$19\_4$a$\_$FirstLastName$\_1.$xlsx$,$ available for download from the SAM website.

$$ Save the file as NP$\_$EX$19\_4$a$\_$FirstLastName$\_2.$xlsx by changing the $1$ to a $2.$

o 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$\_$EX$19\_4$a$\_$FirstLastName$\_2.$xlsx still open, ensure that your first and last name is displayed in cell B$6$ of the Documentation sheet.

o If cell B$6$ 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. In the range E$5$:E$10,$ add Conditional Formatting to show Solid Blue Data Bars.

$2.$ In the range F$5$:F$10,$ add Line sparklines based on the data in the range B$5$:E$10.$

$3.$ Apply the Blue, Accent $1,$ Darker $25\%(5$th column, $5$th row in the Theme Colors palette$)$ sparkline color to the range F$5$:F$10.$

$4.$ 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 G$2$:O$20$ 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.

$5.$ 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 E$5$:E$9$ and using the category labels in the range A$5$:A$9.$

b$.$ Resize and reposition the chart so that the upper$-$left corner is located within cell G$21$ and the lower$-$right corner is located within cell O$39,$ and then left$-$align the chart with the pie chart above it in the range G$2$:O$20.$

$6.$ Customize the chart in the range G$21$:O$39$ as follows:

a$.$ Enter $2021$ Park Spending as Percentage of Total as the chart title.

b$.$ Apply Style $3$ to the chart.

$7.$ 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 A$4$:E$9.$

b$.$ Resize and reposition the chart so that the upper$-$left corner is located within cell A$11$ and the lower$-$right corner is located within cell F$39.$

$8.$ Customize the chart in the range A$11$:F$39$ 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\%(1$st column, $2$nd row in the Theme Colors palette$)$ to the chart area.

$9.$ Update the Park Spending $2018-2021$ combo chart in the range A$40$:G$56$ 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 A$4$ and the lower$-$right corner is located within cell K$22.$

$10.$ 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.

$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 range B$9$:D$10$ to calculate the quarterly and annual payments for each option.

In the range E$9$:E$10,$ add Column sparklines using the data in the range B$9$:D$10.$ Ungroup the sparklines, and then set the vertical axis maximum value to $-15000$ for cell E$9$ and to $-60000$ for cell E$10.$

$12.$ 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 A$9$:D$10.$

b$.$ Resize and reposition the chart so that the upper$-$left corner is located within cell A$11$ and the lower$-$right corner is located within cell D$28.$

$13.$ Customize the chart in the range A$11$:D$28$ as follows:

a$.$ Enter Bond Offering Cost Comparison as the chart title.

b$.$ Add Data Labels to the chart.