Homework 7: American River Cycling Club (ARCC) | ISMG 2050: Business Problem Solving Tools

Key Topics & Core Concepts

Grouped Worksheet Operations: Formatting multiple identically structured state worksheets simultaneously to maintain design consistency across workbooks.
Static 3D Data Consolidation: Utilizing Excel's Consolidate tool with the Sum function to aggregate participation counts across independent state sheets.
Header Graphics & Page Setup: Inserting and sizing organizational logo graphics (&[Picture]) inside page headers, centering worksheets horizontally, and configuring print footers.
SmartArt Business Graphics: Constructing 3-D process flows with custom styles, accent colors, and landscape orientation without display gridlines.

Assignment Overview & Deliverables

The American River Cycling Club (ARCC) collects participation figures across state chapters during National Race Day. You will group and format state worksheets, execute a static 3D data consolidation into a master summary tab, embed the club logo into a print header, and illustrate race preparation guidelines using a styled 3-D SmartArt process diagram.

Starter Workbook: AmRiverCycling-05.xlsx
Image Asset: ARCC.png
Required Submission Name: [your initials] ACC.xlsx
Target Worksheets: Illinois, Massachusetts, Texas, Washington, Static, and Race Prep

Step-by-Step Instructions

Step 1: Open Starter & Group State Worksheets

Open AmRiverCycling-05.xlsx and save it as [your initials] ACC.xlsx.
Click the Illinois sheet tab and inspect the table structure.
Click each of the remaining three state sheets (Massachusetts, Texas, and Washington) to confirm that all four follow the identical row and column layout.
Group all four state worksheets: click the Illinois tab, hold Shift, and click the Washington tab. (Verify that [Group] appears in the title bar).

Step 2: Format Grouped Worksheets

With the four state sheets grouped, type American River Cycling Club in cell A1 and set the font size to 20 pt.
Type National Race Day Participation in cell A2 and set the font size to 18 pt.
Select cells A1:F2 and apply Center Across Selection (via Alignment dialog).
Select cells A3:F12 and apply All Borders.
Select cell C3 and turn on Wrap Text.
Set the column width of columns A:B to 16. Set the width of columns C:F to 10. Then adjust the width of column C to 12.
Set the row height of row 3 to 30. Format all column labels in row 3 as Bold and Centered.
Open Page Setup, switch to the Margins tab, and check Horizontally under Center on page.
Switch to the Header/Footer tab, click Custom Header, and insert the file name code (&[File]) in the Left section. Click OK.
Click cell A1 and right-click any sheet tab to select Ungroup Sheets.

Step 3: Consolidate Three Columns of Participation Data

Click the Static sheet tab and select cell A5. (Consolidated figures begin here; do not pre-select an entire range).
On the Data tab, in the Data Tools group, click Consolidate.
Ensure the Function is set to Sum. Add the data ranges from each state worksheet:
- Illinois!$D$4:$F$12
- Massachusetts!$D$4:$F$12
- Texas!$D$4:$F$12
- Washington!$D$4:$F$12
Ensure top row and left column labels are not checked as labels if selecting numeric bodies, then click OK.
Apply All Borders to cells A5:C13.
Open Page Setup and center the worksheet Horizontally on the page.
Add a custom footer displaying the file name (&[File]) in the Left section.

Step 4: Embed Club Logo into Page Header

On the Static sheet, delete the text in cell A1.
Switch to Page Layout view (via the status bar or the View tab) and click inside the Center Header section.
On the Header & Footer contextual tab, click Picture and browse to insert ARCC.png.
Click Format Picture and set the image height to 0.75 inches. (Mac users may retain default image scaling).
Cut or move the label in cell A2 down to cell A3.
Select cells A3:C3 and click Merge & Center.
Click any worksheet body cell and switch back to Normal view. Preview the worksheet in Print Preview to confirm both the logo header and the table are centered horizontally.

Step 5: Build, Format & Style SmartArt Diagram

Insert a new worksheet tab and name it Race Prep.
On the Insert tab, in the Illustrations group, click SmartArt. Choose the Process category and insert a Basic Process diagram.
On the SmartArt Design tab, click Add Shape to expand the diagram to exactly four sequential boxes.
Enter the following labels into each process shape without adding trailing periods or line returns:
- Box 1: Get enough sleep
- Box 2: Keep hydration and nutrition goals
- Box 3: Service and maintain your bike
- Box 4: Encourage friends to join you
Click the outer frame of the SmartArt graphic. Apply the Cartoon style from the 3-D style palette.
Click Change Colors and select Colorful – Accent Colors.
Drag the graphic so its top-left handle anchors in cell A1. Resize the bottom-right selection handle so the frame extends to cell L25.
Select cell A30. Change page orientation to Landscape and uncheck Gridlines > View on the Page Layout tab to turn off gridline display.
Save the workbook as [your initials] ACC.xlsx. Test via the autograder pre-flight portal at /teaching/ISMG-2050/submit.html, then submit the finalized workbook directly to Canvas before the cutoff.

Refer to Canvas for official submission cutoff and penalty policies.

Key Skills Evaluated

Worksheet grouping for simultaneous multi-sheet formatting and setup
Static 3D multi-range consolidation using the Sum tool
Graphic header insertion, image height constraint, and print alignment
SmartArt 3-D process modeling, color mapping, and clean print presentation