Shelly CashmanExcel 2016 Module 1: SAM Project 1b

Kelly’s Part-Time Tutoring Job

CREATING A WORKSHEET AND A CHART

GETTING STARTED

Open the fleSC_EX16_1b_FirstLastName_1.xlsx, available For downloadFrom the SAM website.

Save the fle asSC_EX16_1b_FirstLastName_2.xlsxby changing the “1” to a“2”.oIF you do not see the.xlsxfle extension in the Save As dialog box, do nottype it. The program will add the fle extension For you automatically.

With the fleSC_EX16_1b_FirstLastName_2.xlsxstill open, ensure that yourfrst and last name is displayed in cell B6 oF the Documentation sheet.oIF cell B6 does not display your name, delete the fle and download a newcopy 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 thisjob during the frst three quarters oF the year.Open theJob Analysisworksheet, and then useAutoFitto adjust the width oFcolumn A.

2.Select cell A1, enter the textKelly's Part-Time Tutoring Job,and thencenterthe merged range A1:D1.

3.Apply theHeading 1cell style to the merged range A1:D1.

4.Enter the textRevenuein cell A2, and thenmergethe contents oF the rangeA2: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:D6CellB6C6D6Value180019502020

7.Using the ±ill Handle, copy the Formula in cell B7 into the range C7:D7.

8.Merge and centerthe range A9:D9.

9.Select the range B11:D14, and then apply theAccountingnumber format withtwodecimal places and$as the symbol.

10.The Expenses table shows Kelly’s expenses for her math, physics, andchemistry classes so far. She wants to calculate the total expenses that sheincurred over the three quarters by adding up the total expenses for eachquarter.In cell E14,create a formula using theSUMfunction to calculate Kelly’s totalexpenses over the last three quarters (B14:D14).

11.Update the formatting of the merged range A16:D16 as described below:

a.Boldthe contents of the merged range.

b.Change the font toCalibri.

c.Change the font size to13 pt.

d.Change the font color toGreen, Accent 6, Darker 25%(10thcolumn, 5throw of the Theme Colors palette).

12.Kelly was oFered another job recently. She has a strong passion for teaching,but she also wants to make a wise ±nancial decision. She will continue tutoringonly if she sees that her pro±t has increased every quarter.In cell B18, create a formula without using a function that calculates the totalpro±t earned by subtracting the total expenses in Q1 (cellB14) from the totalrevenue in Q1 (cellB7).Using the ²ill Handle, ±ll the range C18:D18 with the formula from cell B18.

13.Change the tab color of theJob Analysisworksheet toBlue(8thcolumn, 1strowof the Standard Colors palette).

14.Select the clustered column chart with the title Kelly’s Revenue. Update thechart as described below:

a.Change the chart style of the clustered column chart toStyle 2.

b.Move the chart to its own sheet. UseQ1-Q3 Revenueas the name forthe new chart sheet.

15.Switch back to theJob Analysisworksheet. Select the non-adjacent rangesA11:A13 and D11:D13, and then create a2-D Piechart. Update the chart asdescribed below:

a.In the title placeholder, enterExpenses in Q3as the chart title.

b.Change the chart style of the 2-D pie chart toStyle 3.

c.Move the chart to its own chart sheet, usingQ3 Expensesas the namefor the new chart sheet.

Your workbook should look like the ²inal ²igures on the following pages. Save yourchanges, close the workbook, and then exit Excel. ²ollow the directions on the SAMwebsite to submit your completed project.

Final Figure 1: Job Analysis Worksheet

Final Figure 2: Q1-Q3 Revenue Worksheet

Final Figure 3: Q3 Expenses Worksheet

pur-new-sol

Related Questions