Final figure 1: monthly revenue chart worksheet
Shelly Cashman Excel 2016 | Modules 1–3: SAM Capstone Project 1a
Campus Fitness Center
GETTING STARTED
With the file SC_EX16_CS1-3a_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.
If cell B6 does not display your name, delete the file and download a new copy from the SAM website.
PROJECT STEPS
Change the widths of columns B through M to 10.25 characters.
In the merged range A1:M1, apply the formatting options described below:
Enter the contents in bold shown in Table 1 below into the range C4:G5.
Table 1: Data for Range C4:G5
| C | D | E | F | G | |
|---|---|---|---|---|---|
| 4 | Cardio | Hip-Hop | Spinning | Yoga | Zumba |
| 5 | 100 | 150 | 120 | 200 | 150 |
Format the cells in the range C5:G5 with the Currency number format with two decimal places and $ as the symbol.
Apply the All Borders border style to the range B4:G5.
Move the content of cell H6 to cell G7 and then apply the formatting options described below:
In January, the hip-hop class is the center’s least attended class, but Stella’s manager hopes to boost attendance so that they can reach their budget goals.
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.
Format the values in the range B9:M14 with the Comma Style number format with zero decimal places. Format the range B15:M20 with the Accounting number format with two decimal places.
To help see where the most profitable months are, apply a new conditional formatting rule to the range B20:M20. The rule should format only cells that contain cell values greater than $62,000 with Light Green fill color (5th column, 1st row of the Standard Colors palette) and Dark Blue font color (9th column, 1st row of the Standard Colors palette).
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”).
Change the orientation to Landscape.
Change the worksheet margins to Narrow.
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.
On the Monthly Revenue Chart worksheet, format the line chart with the following options:
Change the chart style to Chart Style 2.
Change the chart title to Fall Semester Class Attendance.
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.
Select the Class Statistics, Personal Trainers, and Physical Therapists worksheets and then change the color of the sheet tabs to Turquoise, Accent 3 (7th column, 1st row of the Theme Colors palette).
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 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
