download the file down below andfollow the directionsexcel filesthank you>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>New Perspectives Excel 2019 | Module 4: SAM Project 1a
Alanis Parks Department
ANALYZE AND CHART FINANCIAL DATA
GETTING STARTED
•
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.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_EX19_4a_FirstLastName_2.xlsx still open, ensure that your first
and last name is displayed in cell B6 of the Documentation sheet.
o
If cell B6 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 E5:E10, add Conditional Formatting to
show Solid Blue Data Bars.
2.
In the range F5:F10, add Line sparklines 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.
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 G2:O20 as follows:
5.
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.
New Perspectives Excel 2019 | Module 4: SAM Project 1a
6.
7.
8.
9.
10.
11.
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 $1,600,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.
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.
12.
13.
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.
Customize the chart in the range A11:D28 as follows:
a.
Enter Bond Offering Cost Comparison as the chart title.
b.
Add a Data Table With Legend Keys to the chart.
c.
Apply a 2 point border to the chart area, and then change the border color to
Gold, Accent 4 (8th column, 1st row in the Theme Colors palette).
New Perspectives Excel 2019 | Module 4: SAM Project 1a
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.
New Perspectives Excel 2019 | Module 4: SAM Project 1a
Final Figure 1: Spending Worksheet
Final Figure 2: Projection Worksheet
New Perspectives Excel 2019 | Module 4: SAM Project 1a
Final Figure 3: Bond Offering Worksheet
New Perspectives Excel 2019 | Module 4: SAM Project 1a
Alanis Parks Department
ANALYZE AND CHART FINANCIAL DATA
Author:
Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy of
the file from the SAM website.
new copy of
Alanis Parks Department
Spending on Parks, 2018-2021
Park
Carver
Fern-Foley
Oleander
Pleistocene
Sartoris
Total
$
$
$
$
$
$
2018
172,331
225,280
302,212
200,098
552,636
1,452,557
$
$
$
$
$
$
2019
178,606
241,898
302,805
207,832
569,846
1,500,988
$
$
$
$
$
$
2020
180,624
247,033
310,247
221,167
575,239
1,534,311
$
$
$
$
$
$
2021
189,794
252,853
319,880
237,810
586,124
1,586,460
Trend
Park Spending 2018-2021
$2,000,000
$1,500,000
$1,000,000
$500,000
$2018
2019
2020
2021
Axis Title
Carver
Fern-Foley
Oleander
Pleistocene
Sartoris
Total
Carver
Fern-Foley
Oleander
Pleistocene
Sartoris
Total
Chart Title
12%
38%
Carver
15%
Fern-Foley
Oleander
Pleistocene
21%
14%
Sartoris
Alanis Parks Department
Park Spending Projection
Alanis Parks Department
Bond Offering Projection
Total Bond Revenue
Quarterly Interest Rate
# of payments
Quarterly Payments
Annual Payments
Option A
$575,000
0.0169
60
Option B
$575,000
0.0175
60
Option C
$575,000
0.0181
60
($15,314.08)
($61,256.31)
($15,555.68)
($62,222.73)
($15,799.25)
($63,197.00)
Purchase answer to see full
attachment
Why Choose Us
- 100% non-plagiarized Papers
- 24/7 /365 Service Available
- Affordable Prices
- Any Paper, Urgency, and Subject
- Will complete your papers in 6 hours
- On-time Delivery
- Money-back and Privacy guarantees
- Unlimited Amendments upon request
- Satisfaction guarantee
How it Works
- Click on the “Place Order” tab at the top menu or “Order Now” icon at the bottom and a new page will appear with an order form to be filled.
- Fill in your paper’s requirements in the "PAPER DETAILS" section.
- Fill in your paper’s academic level, deadline, and the required number of pages from the drop-down menus.
- Click “CREATE ACCOUNT & SIGN IN” to enter your registration details and get an account with us for record-keeping and then, click on “PROCEED TO CHECKOUT” at the bottom of the page.
- From there, the payment sections will show, follow the guided payment process and your order will be available for our writing team to work on it.