Module 1 Class Statistics Worksheet
|PAPER TYPE||EXCEL PROJECT|
|# OF SOURCES||N/A|
|# OF WORKSHEETS||5|
Excel 2016 | Modules 1–3: SAM Capstone Project 1a
Open the Class Statistics worksheet. Modify the column widths and row heights as described below:
Change the row height of row 1 to 00 pts.
Change the widths of columns B through M to 25 characters.
Change the font to Tw Cen MT and the font size to 26 pt.
Change the font color to Turquoise, Accent 3, Darker 50% (7th column, 6th row of the Theme Colors palette).
Change the fill color of the cell to Turquoise, Accent 3, Lighter 80% (7th column, 2nd row of the Theme Colors palette).
Table 1: Data for Range C4:G5
Perform a Goal Seek analysis to determine the number of hip-hop class attendees needed in January (F10) to change the value in cell F14 to 400. (Hint: Cell F10 will be the changing cell.) Keep the results of the Goal Seek Analysis as the new value for cell F10.
In cell B18, enter a formula that multiples the value in cell B12 by the value in cell F5. Copy the formula from B18 to the range C18:M18.
In cell B19, enter a formula that multiples the value in cell B13 by the value in cell G5. Copy that formula from B19 to the range C19:M19.
If this condition is true, the cell value should be set to Over (Hint: For the value if true, use “Over”).
If this condition is false, the cell value should be set to Under (Hint: For the value if false, use “Under”).
Copy the formula created in cell B26 to the range C26:M26.
Change the orientation to Landscape.
Change the worksheet margins to Narrow.
Insert a header in the center section with the text 2018 Class Statistics.
Scale the worksheet so that it prints on one page.
Switch back to Normal View.
Create a Line chart from the nonadjacent ranges B8:M8 and B20:M20. Move the chart you just created to its own chart sheet. Use Monthly Revenue Chart as the name of the new chart sheet.
Change the chart title to Total Revenue by Month.
Format the data labels using the Above positioning option.
Change the chart style to Chart Style 2.
Change the chart title to Fall Semester Class Attendance.
Add Number of Attendees as the primary vertical axis title.
Add Fall Semester Months as the primary horizontal axis title.
Go to the Personal Trainers worksheet and make the following formatting changes:
Rotate the labels in the range B3:B17 to 0 degrees.
Copy the range C3:C5, then paste it into the range C6:C17. Use the paste option that pastes the values, but not the cell formatting.
Set the fill color in cell C3 and the range D6:D8 to No Fill.
In cell E4, type Mon-11:1-LoWa. Select the range E3:E17, and use Flash Fill to fill the values in the range. [Mac Hint: Flash Fill is not available in Excel 2016 for Mac, so refer to the Final Figures to enter the text.]
Your workbook should look like the Final Figures on the following pages. (The value in cell F10 generated by the Goal Seek analysis has intentionally been blurred out in Final Figure 3.) Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed Module 1 Class Statistics Worksheet project.
Final Figure 1: Monthly Revenue Chart Worksheet
Final Figure 2: Fall Attendance Chart Worksheet
Final Figure 3: Class Statistics Worksheet
Final Figure 4: Personal Trainers Worksheet
Final Figure 5: Physical Therapists Worksheet