70.2K

Verified Solution

Link Copied!

1. 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 C1S:F17 of the Caravella Springs worksheet are replaced with static values. 2. 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 workshents, change the font size in the merged range B2:G2 to 18 point. Bold the values in the range B6:89. Resize the column width of columns C:C to 12. Do not ungroup the worksheets. 3. With the worksheets still grouped, change the text in cetl B2 to read: City of Bradford Pools In cell 89 , change the text "Other" to read: Supplies Do not ungroup the worksheets. 4. With the worksheets still grouped, enter a formuta in cell C10 using the Sum function that totals the expenses for 2021. Copy the formula to the range 010:F10. Ungroup the worksheets and then check to confirm that the formatting and formulas from Steps 2-3 are present in all three worksheets. 5. Enrique wants to create a copy of the forrnatted 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 New Perpectives Eucel 365/2021 | Madule 5: End of Modue Project2 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. Switch to the Combined Experises 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 B 6 in the Westover worksheet. Copy the formula from cell B6 to the range B7:B9 without copying the formatting. 7. 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 narme. 8. Create names from the range B6:F9 using the values shown in the left column. 9. Apply the defined names Expenses.2021, Expenses_2022, Expenses_2023, and Expenses 2024 to the forrnulas in the range C10:F10. 11. Delete the defined name Total Expenses (cell H10) because it will not be used in formulas. 12. Enrique wants to compare 2024 expense totals to the projected expense totals for 2025 and needs to add the 2025 data to the Combined Expenses worksheet. Open the file Support_EX365_2021_EOM5-2 Projections_2025.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_EX365_2021_EOM5-2_Projections 2025.xisx workbook. Link each cell in the range H7:H10 in the Combined Expenses worksheet to the corresponding cell in the Support_EX365-2021 EOMS. 2_Projections 2025. xlsx workbook. Do not break the links. Close the Support_EX365_2021 EOM5-2_Projections_2025.xlsx workbook. Documentation Caravella Springs Galway Park Newton Oak Combined Expenses Documentation Caravella Springs Galway Park Newton Oak Combined Expenses City of Bradford Pools Combined Expenses \begin{tabular}{|rrrrrr|r|} \hline & 2021 & 2022 & 2023 & 2024 & Total & 2025 Total \\ \hline & $1,910 & $4,603 & $4,598 & $4,867 & $15,978 & \\ & $4,997 & $5.527 & $7,403 & $11,164 & $29,091 & \\ & $22,079 & $25.217 & $27,067 & $27,448 & $101,810 & \\ & $2,974 & $3,899 & $5,238 & $4,283 & $16,395 & \end{tabular} 1. 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 C1S:F17 of the Caravella Springs worksheet are replaced with static values. 2. 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 workshents, change the font size in the merged range B2:G2 to 18 point. Bold the values in the range B6:89. Resize the column width of columns C:C to 12. Do not ungroup the worksheets. 3. With the worksheets still grouped, change the text in cetl B2 to read: City of Bradford Pools In cell 89 , change the text "Other" to read: Supplies Do not ungroup the worksheets. 4. With the worksheets still grouped, enter a formuta in cell C10 using the Sum function that totals the expenses for 2021. Copy the formula to the range 010:F10. Ungroup the worksheets and then check to confirm that the formatting and formulas from Steps 2-3 are present in all three worksheets. 5. Enrique wants to create a copy of the forrnatted 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 New Perpectives Eucel 365/2021 | Madule 5: End of Modue Project2 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. Switch to the Combined Experises 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 B 6 in the Westover worksheet. Copy the formula from cell B6 to the range B7:B9 without copying the formatting. 7. 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 narme. 8. Create names from the range B6:F9 using the values shown in the left column. 9. Apply the defined names Expenses.2021, Expenses_2022, Expenses_2023, and Expenses 2024 to the forrnulas in the range C10:F10. 11. Delete the defined name Total Expenses (cell H10) because it will not be used in formulas. 12. Enrique wants to compare 2024 expense totals to the projected expense totals for 2025 and needs to add the 2025 data to the Combined Expenses worksheet. Open the file Support_EX365_2021_EOM5-2 Projections_2025.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_EX365_2021_EOM5-2_Projections 2025.xisx workbook. Link each cell in the range H7:H10 in the Combined Expenses worksheet to the corresponding cell in the Support_EX365-2021 EOMS. 2_Projections 2025. xlsx workbook. Do not break the links. Close the Support_EX365_2021 EOM5-2_Projections_2025.xlsx workbook. Documentation Caravella Springs Galway Park Newton Oak Combined Expenses Documentation Caravella Springs Galway Park Newton Oak Combined Expenses City of Bradford Pools Combined Expenses \begin{tabular}{|rrrrrr|r|} \hline & 2021 & 2022 & 2023 & 2024 & Total & 2025 Total \\ \hline & $1,910 & $4,603 & $4,598 & $4,867 & $15,978 & \\ & $4,997 & $5.527 & $7,403 & $11,164 & $29,091 & \\ & $22,079 & $25.217 & $27,067 & $27,448 & $101,810 & \\ & $2,974 & $3,899 & $5,238 & $4,283 & $16,395 & \end{tabular}

Answer & Explanation Solved by verified expert

See Answer