PROJECT STEPS 1. Gudrun Duplessis is a manager in the Alanis Parks Department. Gudrun is compiling...

Transcribed Image Text:

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. 2. 3. 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. Final Figure 1: Spending Worksheet 5 Cerver 6Fem-Foley $ 7 Oleander 11 Pestocene S Sartoris E 10 Total 12 16 IMG 37 IN A (130 40 Nam FOR cher Melinday E GARUMO C 6 Alanis Parks Department Spending on Parks, 2018-2021 M 172.331 S 225.200 SHUNKY 302.212 200.000 552638 S 1.452.567 $ WHOHING 178.000 $ 241 R$ 302.805 207,832 580 845 S 1.500.000 $ 180524 247.033 350.247 221,167 575230 1.534,311 Spending per Park 2018-2021 www.m WDER IS SANJAN 189.794 212,853 340800 237810 585 124 20 SUMM Decumentation Spending Projection bond Offering 13 Se 0 H 2018 Park Spending as Percentage of Total Caram 12% fartura DIN Pla 145 WI 2021 Park Spending as Percentage of Total 8.926 12% 15% SIN Olender SIN 0 Carn mremtide Concer masticate Flames Final Figure 2: Projection Worksheet 123 56789 10 11 12 13 14 15 87888497 16 17 18 19 20 21 22 A Spending per park B $700,000 $600,000 $500,000 $400,000 $300,000 5200,000 $100,000 C 2018 Carvoeit D E F G Alanis Parks Department Park Spending Projection Park Spending 2018-2021 Fern-Foley 2019 Dieander Documentation Spending Projection Bond Ciffering 2020 Pleistocene H 2021 Sartoris I J $1,600,000 $1,550,000 $1,500,000 $1,450,000 $1,400,000 $1,350,000 Total spending K 1 23 A 4 5 Total Bond Revenue 6 Quarterly Interest Rate # of payments 8 9 Quarterly Payments 10 Annual Payments 11 12 13 14 15 ($10,000.00) 16 17 ($20,000.00) 18 19 ($30,000.00) 20 ($40,000.00) 21 22 ($50,000.00) 23 ($60,000.00) ($70,000.00) 24 25 26 27 28 29 30 5.1 $0.00 B Alanis Parks Department Bond Offering Projection ($15,314.08 Option A ($61,256.31) $575,000 0.0169 60 ($15,314.08) ($61,256.31) C Quarterly Payments Option B $575,000 0.0175 60 Bond Offering Cost Comparison 2 ($15,555.68 ($15,799.25 117 ($62,222.73) D ($15,799.25) ($15,555.68) ($62,222.73) ($63,197.00) Annual Payments Option C $575,000 0.0181 60 Documentation Spending Projection Bond Offering ($63,197.00) (+) E 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. 2. 3. 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. Final Figure 1: Spending Worksheet 5 Cerver 6Fem-Foley $ 7 Oleander 11 Pestocene S Sartoris E 10 Total 12 16 IMG 37 IN A (130 40 Nam FOR cher Melinday E GARUMO C 6 Alanis Parks Department Spending on Parks, 2018-2021 M 172.331 S 225.200 SHUNKY 302.212 200.000 552638 S 1.452.567 $ WHOHING 178.000 $ 241 R$ 302.805 207,832 580 845 S 1.500.000 $ 180524 247.033 350.247 221,167 575230 1.534,311 Spending per Park 2018-2021 www.m WDER IS SANJAN 189.794 212,853 340800 237810 585 124 20 SUMM Decumentation Spending Projection bond Offering 13 Se 0 H 2018 Park Spending as Percentage of Total Caram 12% fartura DIN Pla 145 WI 2021 Park Spending as Percentage of Total 8.926 12% 15% SIN Olender SIN 0 Carn mremtide Concer masticate Flames Final Figure 2: Projection Worksheet 123 56789 10 11 12 13 14 15 87888497 16 17 18 19 20 21 22 A Spending per park B $700,000 $600,000 $500,000 $400,000 $300,000 5200,000 $100,000 C 2018 Carvoeit D E F G Alanis Parks Department Park Spending Projection Park Spending 2018-2021 Fern-Foley 2019 Dieander Documentation Spending Projection Bond Ciffering 2020 Pleistocene H 2021 Sartoris I J $1,600,000 $1,550,000 $1,500,000 $1,450,000 $1,400,000 $1,350,000 Total spending K 1 23 A 4 5 Total Bond Revenue 6 Quarterly Interest Rate # of payments 8 9 Quarterly Payments 10 Annual Payments 11 12 13 14 15 ($10,000.00) 16 17 ($20,000.00) 18 19 ($30,000.00) 20 ($40,000.00) 21 22 ($50,000.00) 23 ($60,000.00) ($70,000.00) 24 25 26 27 28 29 30 5.1 $0.00 B Alanis Parks Department Bond Offering Projection ($15,314.08 Option A ($61,256.31) $575,000 0.0169 60 ($15,314.08) ($61,256.31) C Quarterly Payments Option B $575,000 0.0175 60 Bond Offering Cost Comparison 2 ($15,555.68 ($15,799.25 117 ($62,222.73) D ($15,799.25) ($15,555.68) ($62,222.73) ($63,197.00) Annual Payments Option C $575,000 0.0181 60 Documentation Spending Projection Bond Offering ($63,197.00) (+) E