Shelly Cashman Excel 2016 | Module 1: SAM Project 1b Kelly’s Part-Time Tutoring Job creating a worksheet and a chart   GETTING STARTED Open the file SC_EX16_1b_FirstLastName_1

Subject:MS ExcelPrice:20.99 Bought12

Share With

Shelly Cashman Excel 2016 | Module 1: SAM Project 1b

Kelly’s Part-Time Tutoring Job

creating a worksheet and a chart

 

GETTING STARTED

• Open the file SC_EX16_1b_FirstLastName_1.xlsx, available for download from the SAM website.
• Save the file as SC_EX16_1b_FirstLastName_2.xlsx by changing the “1” to a “2”.
  • If you do not see the .xlsx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
• With the file SC_EX16_1b_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

1. Kelly is a senior in college and has a part-time job where she tutors students in math, physics and chemistry. She is interested in analyzing her earnings for this job during the first three quarters of the year.

Open the Job Analysis worksheet, and then use AutoFit to adjust the width of column A.

2. Select cell A1, enter the text Kelly's Part-Time Tutoring Job, and then center the merged range A1:D1.
3. Apply the Heading 1 cell style to the merged range A1:D1.
4. Enter the text Revenue in cell A2, and then merge the contents of the range A2:D2.
5. Clear the contents of cell E3.
6. Enter the data shown in Table 1 below into the range B6:D6.

Table 1: Data for the Range B6:D6

7. Using the Fill Handle, copy the formula in cell B7 into the range C7:D7.
8. Merge and center the range A9:D9.
9. Select the range B11:D14, and then apply the Accounting number format with two decimal places and $ as the symbol.
10. The Expenses table shows Kelly’s expenses for her math, physics, and chemistry classes so far. She wants to calculate the total expenses that she incurred over the three quarters by adding up the total expenses for each quarter.

In cell E14, create a formula using the SUM function to calculate Kelly’s total expenses over the last three quarters (B14:D14).

11. Update the formatting of the merged range A16:D16 as described below:
    1. Bold the contents of the merged range.
    2. Change the font to Calibri.
    3. Change the font size to 13 pt.
    4. Change the font color to Green, Accent 6, Darker 25% (10^th column, 5^th row of the Theme Colors palette).
12. Kelly was offered another job recently. She has a strong passion for teaching, but she also wants to make a wise financial decision. She will continue tutoring only if she sees that her profit has increased every quarter.

In cell B18, create a formula without using a function that calculates the total profit earned by subtracting the total expenses in Q1 (cell B14) from the total revenue in Q1 (cell B7).

Using the Fill Handle, fill the range C18:D18 with the formula from cell B18.

13. Change the tab color of the Job Analysis worksheet to Blue (8^th column, 1^st row of the Standard Colors palette).
14. Select the clustered column chart with the title Kelly’s Revenue. Update the chart as described below:
    1. Change the chart style of the clustered column chart to Style 2.
    2. Move the chart to its own sheet. Use Q1-Q3 Revenue as the name for the new chart sheet.
15. Switch back to the Job Analysis worksheet. Select the non-adjacent ranges A11:A13 and D11:D13, and then create a 2-D Pie chart. Update the chart as described below:
    1. In the title placeholder, enter Expenses in Q3 as the chart title.
    2. Change the chart style of the 2-D pie chart to Style 3.
    3. Move the chart to its own chart sheet, using Q3 Expenses as the name for the new chart sheet.

Your workbook should look like the Final Figures on the following pages. 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: Job Analysis Worksheet

Final Figure 2: Q1-Q3 Revenue Worksheet

Final Figure 3: Q3 Expenses Worksheet