References Mailings Review View Help careful--files from the Internet can contain viruses. Unless you need to edit it's safer to stay in Protected View. emmy davis ED Enable Editing a. a. a. 7. Gudrun also wants a stacked bar chart showing the spending per park for each year. Create a stacked bar chart based on the individual park spending data from all years shown, using the park names as the category labels. b. Size the chart so that it covers the range A11:F39. 8. Customize the stacked bar chart as follows: Enter Spending per Park 2018-2021 as the chart title. b. Change the Maximum value of the horizontal axis to $2,300,000. C. 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: Move the chart to the Projection worksheet. b. 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: 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, 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. Crasto a cluctorad column chart haced on the Quarterly and Annual naumont Focus on 8-54 F File nom Home Search Insert Draw Page Layout Formulas Data Review View Help Power Pivot emmy dams Arla 10 - === Paste Wrap Text X Cut Copy Fomiat Painter Clipboard General BLU- A Sun 28 0 Merge & Center $ % 22:28 H Conditional Format as Cell Fomatting Table Styles Styles TEX Incert Delete Format Font Autosur F Sort & Fied Coal File Select Editing HD DIS Analys Data Alignment R32 Number Ex Cells Ay E F G H 1 K M N o P a R Chart Title Tiend 2021 189,794 252,853 319,880 237,810 586,124 1,586,460 12% Carver 38% 15% Forn.Fate Cleander Pleistocene 21% Surt 14% B C D 1 Alanis Parks Department 2 Spending on Parks, 2018-2021 3 4 Park 2018 2019 2020 5 Carver S 172.331 $ 178,606 S 180 624 S 6 Fern-Foley $ 225,280 $ 241,898 $ 247,033 S 7 Oleander S 302.212 5 302.805 $ 310,247 S 8 Pleistocene $ 200.098 207.832 $ 221,167 $ 9 Sartoris $ 552 636 $ 569,846 5 575,239 $ 10 Total $ 1,452,557 $ 1,500,988 $ 1,534,311 $ 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 28 Park Spending 2018-2021 27 $2,000,000 28 29 $1,500,000 30 31 $2,000,000 32 33 $500,000 34 35 5 36 2012 2018 2010 37 Axis Title 38 39 Carver Lern Foley Oleander Pleistocene 40 41 42 43 44 45 46 47 Documentation Spending + Spending Projection Bond Offering Ready Calculate 2021 Sartus Total w x 78F Type here to search ca File Home Insert Draw Page Layout Formulas mydi Data Review View Help Power Pivot por X cut In Copy - Paste Arial Be Wrap Text 10 AAE EE AE General AutoSum FI 2 O HD ) BIU - Format Painter Merge & Center $ % 8 % Call Insert Delete Format Conditional Format as Formatting Table Styles Styles Cear Sort & Find & Filter Select Analys Data Clipboard Fant E Alignment Number 1 Cells Tid ting MI Andals $ fic A B J K L M N 0 P Q R s T U D E F G Alanis Parks Department Park Spending Projection 2 3 4 5 6 7 B 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 38 37 38 39 40 41 42 43 44 45 46 47 Documentation Spending Prolection Bond Offering File Home Insert Draw Page Layout Formulas Data Review View Help Power Pivot Arial 2 Wrap Text X Cut Ib Copy Format Painter HI General 10 M A A SEE a. A- AutoSum DEN LX A O Paste BIU Fill E 3 Merge & Center $ % 588 Insert Delete Format Conditional Format as Cell Formatting Table Styles Styles Clear Sort & Find & Filter Select Clipboard Font Alignment Number Cells Editing G1 X D E F G H 1 J K L M N 1 B Alanis Parks Department Bond Offering Projection 2 3 - Option A $575,000 0.0169 Option B $575,000 0.0175 60 Option C $575,000 0.0181 60 60 ($15,314.08) ($61,256.31) ($15,555.68) ($62,222.73) ($15,799.25) ($63,197.00) 4 5 Total Bond Revenue 6 Quarterly Interest Rate 7 # of payments 8 9 Quarterly Payments 10 Annual Payments 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 References Mailings Review View Help careful--files from the Internet can contain viruses. Unless you need to edit it's safer to stay in Protected View. emmy davis ED Enable Editing a. a. a. 7. Gudrun also wants a stacked bar chart showing the spending per park for each year. Create a stacked bar chart based on the individual park spending data from all years shown, using the park names as the category labels. b. Size the chart so that it covers the range A11:F39. 8. Customize the stacked bar chart as follows: Enter Spending per Park 2018-2021 as the chart title. b. Change the Maximum value of the horizontal axis to $2,300,000. C. 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: Move the chart to the Projection worksheet. b. 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: 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, 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. Crasto a cluctorad column chart haced on the Quarterly and Annual naumont Focus on 8-54 F File nom Home Search Insert Draw Page Layout Formulas Data Review View Help Power Pivot emmy dams Arla 10 - === Paste Wrap Text X Cut Copy Fomiat Painter Clipboard General BLU- A Sun 28 0 Merge & Center $ % 22:28 H Conditional Format as Cell Fomatting Table Styles Styles TEX Incert Delete Format Font Autosur F Sort & Fied Coal File Select Editing HD DIS Analys Data Alignment R32 Number Ex Cells Ay E F G H 1 K M N o P a R Chart Title Tiend 2021 189,794 252,853 319,880 237,810 586,124 1,586,460 12% Carver 38% 15% Forn.Fate Cleander Pleistocene 21% Surt 14% B C D 1 Alanis Parks Department 2 Spending on Parks, 2018-2021 3 4 Park 2018 2019 2020 5 Carver S 172.331 $ 178,606 S 180 624 S 6 Fern-Foley $ 225,280 $ 241,898 $ 247,033 S 7 Oleander S 302.212 5 302.805 $ 310,247 S 8 Pleistocene $ 200.098 207.832 $ 221,167 $ 9 Sartoris $ 552 636 $ 569,846 5 575,239 $ 10 Total $ 1,452,557 $ 1,500,988 $ 1,534,311 $ 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 28 Park Spending 2018-2021 27 $2,000,000 28 29 $1,500,000 30 31 $2,000,000 32 33 $500,000 34 35 5 36 2012 2018 2010 37 Axis Title 38 39 Carver Lern Foley Oleander Pleistocene 40 41 42 43 44 45 46 47 Documentation Spending + Spending Projection Bond Offering Ready Calculate 2021 Sartus Total w x 78F Type here to search ca File Home Insert Draw Page Layout Formulas mydi Data Review View Help Power Pivot por X cut In Copy - Paste Arial Be Wrap Text 10 AAE EE AE General AutoSum FI 2 O HD ) BIU - Format Painter Merge & Center $ % 8 % Call Insert Delete Format Conditional Format as Formatting Table Styles Styles Cear Sort & Find & Filter Select Analys Data Clipboard Fant E Alignment Number 1 Cells Tid ting MI Andals $ fic A B J K L M N 0 P Q R s T U D E F G Alanis Parks Department Park Spending Projection 2 3 4 5 6 7 B 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 38 37 38 39 40 41 42 43 44 45 46 47 Documentation Spending Prolection Bond Offering File Home Insert Draw Page Layout Formulas Data Review View Help Power Pivot Arial 2 Wrap Text X Cut Ib Copy Format Painter HI General 10 M A A SEE a. A- AutoSum DEN LX A O Paste BIU Fill E 3 Merge & Center $ % 588 Insert Delete Format Conditional Format as Cell Formatting Table Styles Styles Clear Sort & Find & Filter Select Clipboard Font Alignment Number Cells Editing G1 X D E F G H 1 J K L M N 1 B Alanis Parks Department Bond Offering Projection 2 3 - Option A $575,000 0.0169 Option B $575,000 0.0175 60 Option C $575,000 0.0181 60 60 ($15,314.08) ($61,256.31) ($15,555.68) ($62,222.73) ($15,799.25) ($63,197.00) 4 5 Total Bond Revenue 6 Quarterly Interest Rate 7 # of payments 8 9 Quarterly Payments 10 Annual Payments 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45