Spreadsheet and Monthly Worksheets
NEW PERSPECTIVES EXCEL 2010
TUTORIAL 6: ASSIGNMENT
WINTER CREEK APARTMENTS
PROJECT OVERVIEW
W inter Creek Apartments manages large apartment complexes in four cities in the Midwest: Kansas City,
Des Moines, Omaha, and St. Louis. At the corporate level, Gordon Rockwell uses Excel to summarize monthly maintenance expenses submitted by each complex manager. He asks you to total the maintenance costs of each apartment complex’ for each month, and then format each worksheet. Gordon also needs you to add another worksheet to calculate summary costs for each city and maintenance category. STUDENT START FILE
Download Excel_T6_Data_File.xlsx from Moodle.
Instructions
1. Open the file Excel_T6_Data_File.xlsx and save the file as T6_LastName_FirstName.xlsx before you move to the next step. Enter your name in cell B4 of the Documentation sheet.
2. Group the three monthly worksheets. For each month, calculate the maintenance category totals in the range B9:F9 and calculate the apartment complex totals in the range G5:G8. In cell G9, enter a grand total that adds up the amounts in range G5:G8.
3. Improve the look of the monthly worksheets by formatting the ranges A4:G4 and A8:G8 to have a bottom border. Format the range B5:G9 to Accounting Number Format, and then Decrease
Decimal so no decimal places are shown. Ungroup the worksheets.
4. Make a copy of one of the quarterly worksheets, rename the new worksheet as Summary, and then position the Summary sheet between the Documentation and Jan worksheets.
5. In the Summary worksheet, in the range B5:F8, delete the existing values and insert formulas that add the sales in the corresponding cells of the three monthly worksheets. Use 3-D references in the formulas. (Hint: Reapply the bottom border to cells B8:G8 if needed.)
6. Set up the Summary and three monthly worksheets for printing.
Each worksheet should:
a) be centered horizontally,
b) fit on one page,
c) display the name of the worksheet
TUTORIAL 6: ASSIGNMENT
WINTER CREEK APARTMENTS
PROJECT OVERVIEW
W inter Creek Apartments manages large apartment complexes in four cities in the Midwest: Kansas City,
Des Moines, Omaha, and St. Louis. At the corporate level, Gordon Rockwell uses Excel to summarize monthly maintenance expenses submitted by each complex manager. He asks you to total the maintenance costs of each apartment complex’ for each month, and then format each worksheet. Gordon also needs you to add another worksheet to calculate summary costs for each city and maintenance category. STUDENT START FILE
Download Excel_T6_Data_File.xlsx from Moodle.
Instructions
1. Open the file Excel_T6_Data_File.xlsx and save the file as T6_LastName_FirstName.xlsx before you move to the next step. Enter your name in cell B4 of the Documentation sheet.
2. Group the three monthly worksheets. For each month, calculate the maintenance category totals in the range B9:F9 and calculate the apartment complex totals in the range G5:G8. In cell G9, enter a grand total that adds up the amounts in range G5:G8.
3. Improve the look of the monthly worksheets by formatting the ranges A4:G4 and A8:G8 to have a bottom border. Format the range B5:G9 to Accounting Number Format, and then Decrease
Decimal so no decimal places are shown. Ungroup the worksheets.
4. Make a copy of one of the quarterly worksheets, rename the new worksheet as Summary, and then position the Summary sheet between the Documentation and Jan worksheets.
5. In the Summary worksheet, in the range B5:F8, delete the existing values and insert formulas that add the sales in the corresponding cells of the three monthly worksheets. Use 3-D references in the formulas. (Hint: Reapply the bottom border to cells B8:G8 if needed.)
6. Set up the Summary and three monthly worksheets for printing.
Each worksheet should:
a) be centered horizontally,
b) fit on one page,
c) display the name of the worksheet