Скачать или смотреть New Perspectives Excel 365 | Module 4: SAM Project B | New Perspectives Excel 365 | Module 4

New Perspectives Excel 365 | Module 4: SAM Project B | New Perspectives Excel 365 | Module 4

Pacific Crest College
ANALYZE AND CHART FINANCIAL DATA

GETTING STARTED
• Save the file NP_EX365_4B_FirstLastName_1.xlsx as NP_EX365_4B_FirstLastName_2.xlsx
o Edit the file name by changing “1” to “2”.
o If you do not see the .xlsx file extension, do not type it. The file extension will be added for you automatically.
• With the file NP_EX365_4B_FirstLastName_2.xlsx open, ensure that your first and last name is displayed in cell B6 of the Documentation worksheet.
o If cell B6 does not display your name, delete the file and download a new copy.

PROJECT STEPS
1. Jalinda Larsen is an administrator at Pacific Crest College in Redding, California. She is compiling data about student enrollment for the college's Marketing Department and wants to use Excel to create charts to illustrate the data and emphasize trends.

Go to the Enrollment worksheet. This worksheet shows the number of students enrolled in programs in five areas of study over four years. Jalinda wants to compare the number of students enrolled in 2028 in each area of study.

In the range F5:F9, add Conditional Formatting to show Solid Fill Orange Data Bars.
2. Jalinda also wants to provide a general impression of the annual trend for each area of study and for the total students enrolled over four years.

In the range G5:G10, add Line sparklines based on the data in the range C5:F10.
3. Format the sparklines as follows to make them more prominent:
a. Apply the Orange, Accent 6, Darker 50% (10th column, 6th row in the Theme Colors palette) sparkline color to the range G5:G10.
b. Show the High Point and Low Point data markers.
4. Jalinda has created a pie chart representing the percentage of students enrolled in each area of study in 2028. She wants to allow more room for the plot area of the chart. Modify the chart in the range I2:O17 as follows:
a. Change the position of the data labels to Inside End.
b. Move the legend to the Right of the plot area.
5. Jalinda wants a similar pie chart showing the percentage of students enrolled in each area of study in 2027. Create the chart as follows:
a. Create a 2-D Pie chart based on the data in the range E5:E9 and using the category labels in the range B5:B9.
b. Resize and reposition the chart so that the upper-left corner is located within cell I19, and the lower-right corner is located within cell O34 to show that the two pie charts are related.
6. Customize the pie chart you just added as follows to match the appearance of the Enrollment by Area of Study: 2028 pie chart:
a. Enter Enrollment by Area of Study: 2027 as the chart title.
b. Apply Style 4 to the chart.
c. Change the colors to Monochromatic Palette 6.
d. Change the position of the data labels to Inside End.
e. Change the data label content to show a Percentage rather than a value.
f. Move the legend to the Right of the plot area.
7. Jalinda wants to insert a column chart that combines the enrollment data in a single column for each area of study. Create the chart as follows:
a. Create a Stacked Column chart based on the data in the range B5:F9. Add each series name (2025, 2026, etc.) based on the labels in cells C4:F4.
b. Resize and reposition the chart so that the upper-left corner is located within cell B12, and the lower-right corner is located within cell G34.
8. Customize the stacked column chart as follows to coordinate with the other charts and to make the data easier to interpret:
a. Enter Enrollment by Area of Study as the chart title.
b. Change the Maximum value to 4,000 on the vertical axis.
c. Change the colors to Monochromatic Palette 6.
9. Jalinda also wants a chart that compares the yearly and total enrollment of the first three areas of study. Create the chart as follows:
a. Create a Clustered Column – Line combination chart based on the data in the nonadjacent range B5:F7, B10:F10. Edit the Horizontal Axis labels to use the range C4:F4.