Shelly CashmanExcel 2016| Module 1: SAM Project 1b

Shelly Cashman Excel 2016| Module1: SAM Project 1b

Kelly’s Part-Time Tutoring Job

creating a worksheet and achart

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.xlsxby 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 inmath, 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.

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

Table 1: Data for the Range B6:D6

Cell / B6 / C6 / D6
Value / 1800 / 1950 / 2020
  1. Using the Fill Handle, copy the formula in cell B7 into the rangeC7:D7.
  2. Merge and center the range A9:D9.
  3. Select the range B11:D14,and then apply the Accounting number format with two decimal places.
  4. 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).

  1. Update the formatting of the merged range A16:D16 as described below:
  2. Bold the contents of the merged range.
  3. Change the font to Calibri.
  4. Change the font size to 13 pt.
  5. Change the font color to Green, Accent 6, Darker 25% (10th column, 5th row of the Theme Colors palette).
  6. 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 increasedevery 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 rangeC18:D18 with the formula from cell B18.

  1. Change the tab color of the Job Analysis worksheet to Blue (8th column, 1st row of the Standard Colors palette).
  2. Select the clustered column chart with the title Kelly’s Revenue. Update the chart as described below:
  3. Change the chart style of the clustered column chart to Style 2.
  4. Move the chart to its own sheet. Use Q1-Q3 Revenueas the name for the new chart sheet.
  5. Switch back to the Job Analysis worksheet. Select the non-adjacent rangesA11:A13 and D11:D13, and then create a 2-D Pie chart. Update the chart as described below:
  6. In the title placeholder, enter Expenses in Q3 as the chart title.
  7. Change the chart style of the 2-D pie chart to Style 3.
  8. Move the chart to its own chart sheet, usingQ3 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 Figure2: Q1-Q3 Revenue Worksheet

Final Figure3: Q3 Expenses Worksheet