You are the office manager for Jim’s Auto Shop. A new body shop just opened a few blocks away, and your boss, Jim Love, is concerned that sales have declined. He has started a workbook of services offered at the auto shop and included the last six month’s sales. He has asked that you complete the workbook by creating formulas to calculate the total sales of each service. Jim also wants you to determine the largest-selling service at the auto shop and to call attention to these services by formatting them appropriately. From this completed workbook, Jim will determine whether he should continue to offer all services or focus on fewer services to stay competitive.
For the purpose of grading the project you are required to perform the following tasks:
Start Excel. Open the downloaded file named e01_grader_h2_AutoBody.xlsx. Save the file with the name e01_grader_h2_AutoBody_LastFirst, using your last and first name.
Rename Sheet1 Sales. Rename Sheet2 Documentation.
On the Sales worksheet, set the horizontal alignment for A1:H1 and then A2:H2 to Center Across Selection.
Increase the height of row 1 to 30. Hide row 3.
Click cell H4, and type Total. Click in cell A15, and type Total.
Select cell B4. Use AutoFill to fill the months through G4. Apply the Angle Counterclockwise orientation to cell range B4:H4.
In cell range B15:G15, use a function to calculate the monthly totals.
In cell range H5:H15, use functions to calculate the total of each service offered.
Select cell range A4:H4, and then apply the Heading 3 style to the cell range. Select cell A1, and then apply the Title style. Select cell A2, and then apply Heading 4 style.
Select cell ranges B5:H5 and B15:H15, and then apply the Accounting Number Format.
Select cell range B6:H14, then apply the Comma style format.
Select cell range B15:H15, and then apply the Total style.
Select cell ranges B5:H5 and B15:H15, and then decrease the decimals to zero decimal places.
Select cell range B6:H14, and then decrease the decimals to zero decimal places.
In cells A17, A18, and A19, enter the labels Average, Largest, and Smallest respectively. Right-align cell range A17:A19.
In cell B17, use a function to determine the average sales for January.
In cell B18, use a function to determine the largest sale for January.
In cell B19, use a function to determine the smallest sale for January.
Copy the formulas in cell range B17:B19 to cell range C17:G19. If necessary, decrease the decimals to zero decimal places.
Select cell range H5:H14. Apply the Gradient Fill Light Blue Data Bar to the range of cells.
Select cell range B5:G14, and apply a conditional format to highlight any sales less than $1,000 with Light Red Fill.
Hide the Gridlines of the Sales worksheet.
Click the Documentation worksheet. In cell A8, type today’s date. In cell B8, type your name in the Firstname Lastname format. In cell C8, type Calculated totals for the services and products for the past six months (no period). Wrap the text of cell C8. In cell B20, type Sales.
Enter the file name code in the left footer of both worksheets. Check the spelling of both worksheets, and make any necessary corrections.
Change the scaling of the Sales worksheet to Fit all Columns to One Page. Change both worksheets to Landscape orientation.
Save your workbook, exit Excel, and then submit your file as directed by your instructor.