I need an explanation for this Excel question to help me study.
I attached the work sheet and the instructions
Open the file NP_EX19_4a_FirstLastName_1.xlsx, available for download from the SAM website.
- Save the file as NP_EX19_4a_FirstLastName_2.xlsxby changing the “1” to a “2”.
- With the file NP_EX19_4a_FirstLastName_2.xlsxstill open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.
oIf you do not see the .xlsxfile extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
oIf cell B6 does not display your name, delete the file and download a new copy from the SAM website.
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 Spendingworksheet. In the range E5:E10, add Conditional Formatting to show Solid Blue Data Bars.
2.In the range F5:F10, add Linesparklines based on the data in the range B5:E10.
3.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:
- Enter 2018 Park Spending as Percentage of Totalas the chart title.
- Change the data labels to include the Category as well as the Percentage, and position the labels in the Outside Endlocation.
- Gudrun would like a pie chart representing the percentage of the parks’ budget that would go to each park in 2021.
- Create a 2-D Piechart based on the data in the range E5:E9 and using the category labels in the range A5:A9.
- Customize the chart in the range G21:O39 as follows:
- Enter 2021 Park Spending as Percentage of Totalas the chart title.
- Gudrun wants a stacked bar chart showing spending per park for each year.
- Create a Stacked Barchart based on the data in the range A4:E9.
- Customize the chart in the range A11:F39 as follows:
- Enter Spending per Park 2018-2021as the chart title.
- Change the Maximum value to$2,300,000on the horizontal axis.
- Update the Park Spending 2018-2021 combo chart in the range A40:G56 as follows:
- Move the chart to the Projectionworksheet.
- For the Park Spending 2018-2021 combo chart, update the chart area as follows:
- Apply the Monochromatic Palette 4color scheme to the chart.
- Add a secondary axis for the Total series. [Mac Hint: Select the Total series and use the format pane to add the axis.]
- Gudrun wants a clustered column chart comparing the costs of the bond options.
- Create a Clustered Columnchart based on the values in the range A9:D10.
- Customize the chart in the range A11:D28 as follows:
- Enter Bond Offering Cost Comparisonas the chart title.
c.Remove the Legendfrom the chart.
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.
b.Apply Style 3to the chart.
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.
c.Apply a shape fill of White, Background 1, Darker 5%(1st column, 2nd row in the Theme Colors palette) to the chart area.
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.
c.Add Axis Titlesto the chart, and then enter Spending per parkas the left vertical axis title, and then enter Total spendingas 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 Offeringworksheet. 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 Columnsparklines 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.
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.
b.Add Data Labelsto the chart.
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.