New Perspectives Excel 2019 | Module 5: End of Module Project 2 | City of Bradford Pools (2025)

City of Bradford Pools
GENERATING REPORTS FROM MULTIPLE WORKBOOKS
GETTING STARTED




Open the file NP_EX19_EOM5-2_FirstLastName_1.xlsx, available for download from
the SAM website.
Save the file as NP_EX19_EOM5-2_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.
To complete this SAM Project, you will also need to download and save the following
data files from the SAM website onto your computer:
o
o
Support_EX19_EOM5-2_Caravella.xlsx
Support_EX19_EOM5-2_Projections_2022.xlsx
With the file NP_EX19_EOM5-2_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.
2.
3.
4.
5.
Enrique Vaquero is a city planner for the city of Bradford in northeast Colorado. The
Recreation Department is studying whether to open another pool in the city and needs
an overview of pool expenses. Enrique is using an Excel workbook with multiple
worksheets to summarize four years of these expenses. He asks for your help in
completing the expense information.
Switch to the Caravella Springs worksheet. Break the external link in the worksheet so
that the formulas in the range C15:F17 of the Caravella Springs worksheet are replaced
with static values.
Enrique wants to apply consistent formatting to the worksheets he collected from
separate workbooks. Group the Caravella Springs, Galway Park, and Newton Oak
worksheets together. In the grouped worksheets, change the font size in the merged
range B2:G2 to 18 point. Bold the values in the range B6:B9. Resize the column width of
columns C:G to 12. Do not ungroup the worksheets.
With the worksheets still grouped, change the text in cell B2 to read:
City of Bradford Pools
In cell B9, change the text "Other" to read:
Supplies
Do not ungroup the worksheets.
With the worksheets still grouped, enter a formula in cell C10 using the SUM function
that totals the expenses for 2018. Copy the formula to the range D10:F10. Ungroup the
worksheets and then check to confirm that the formatting and formulas from Steps 2-3
are present in all three worksheets.
Enrique wants to create a copy of the formatted Newton Oak worksheet to use for
expense data for a pool on the outskirts of Bradford, which the city might incorporate
into their Recreation Department. Create a copy of the Newton Oak worksheet between
the Newton Oak worksheet and the Combined Expenses worksheet, and then change the
worksheet name to Westover for the copied worksheet. Edit the text to read Westover
in the merged range B3:G3. Clear the contents of the range C6:F9. (Hint: Ignore errors
because Enrique will enter the expense data later.)
6.
7.
8.
9.
Switch to the Combined Expenses worksheet. Enrique wants to combine the expense
data from each of the current city pools. In cell B6, enter a formula without using a
function that references cell B6 in the Westover worksheet. Copy the formula from cell
B6 to the range B7:B9 without copying the formatting.
Enrique started to create named ranges in the worksheet and has asked you to complete
the work. Create a defined name for the range C10:F10 using Expenses as the range
name.
Create names from the range B6:F9 using the values shown in the left column.
Apply the defined names Expenses_2018, Expenses_2019, Expenses_2020, and
Expenses_2021 to the formulas in the range C10:F10.
10. Change the defined name to Total_Expenses_2022 for the range H6:H9. [Mac Hint:
Delete the existing defined name "_2022" and add the new defined name.]
11. Delete the defined name Total_Expenses (cell H10) because it will not be used in
formulas.
12. Enrique wants to compare 2021 expense totals to the projected expense totals for 2022
and needs to add the 2022 data to the Combined Expenses worksheet. Open the file
Support_EX19_EOM5-2_Projections_2022.xlsx. Switch back to the original
workbook and go to the Combined Expenses worksheet. Create an external reference by
linking cell H6 in the Combined Expenses worksheet to cell C6 in the Projected Expenses
worksheet in the Support_EX19_EOM5-2_Projections_2022.xlsx workbook. Link
each cell in the range H7:H10 in the Combined Expenses worksheet to the
corresponding cell in the Support_EX19_EOM5-2_Projections_2022.xlsx workbook.
Do not break the links. Close the Support_EX19_EOM5-2_Projections_2022.xlsx
workbook

#MicrosoftExcel
#Excel
#NewPerspectivesExcel