Turquoise Oasis Spa Schedule
Project Description:
Irene Kai, manager of the Turquoise Oasis Spa, has exported sales data from a database program into an Excel workbook to facilitate the analysis of services received by a client during a visit to the spa. The intent is to track the treatments performed on an individual client during their stay at the resort, the consultant that performed each service, and the treatments that seem most popular. This will allow the staff to review spa usage in a visually appealing layout, notice trends in treatment choices, and improve the scheduling of consultants.
Instructions:
For the purpose of grading the project you are required to perform the following tasks:
Instructions
1 Start Excel. Open the downloaded file named e01ws02_grader_a1.xlsx. Save it ase01ws02_grader_a1_YourOffice.
2 Make Schedule by Date the name of Sheet1. Make Consultants the name of Sheet2.
3 Open the e01ws02ADoc.xlsx file. Copy the Documentation worksheet as the end worksheet in workbook e01ws02_grader_a1_YourOffice.xlsx. Close e01ws02ADoc.xlsx.
4 In the Documentation worksheet, type 12/31/2015 into cells A6, A8, A20, and A21. Type Irene Kia in cells B6, C20, and C21. Type Spa client and consultant schedule in cell C6. Type Your Office in cell B8. Type Completed the work begun by Ms. Kia in cell C8.
5 Type Schedule by Date in cell B20. Type Consultants in cell B21. Apply the Savon theme to the workbook.
6 In the Schedule by Date worksheet, apply Merge & Center and Cell Style Title to cell range A1:J1. Set the Fill Color of cell ranges A1:J1 and A4:J4 to Turquoise, Accent 3, Lighter 80%. Apply Center Across Selection and Cell Style Heading 4 to cell range A2:J2. Apply Bold and Italic to cell range A4:J4.
7 Insert a hard return after the word Client's in cells D4 and E4.
Insert a hard return after the word Payment in cell H4.
8 Use the Format Painter to copy the date format of cell A6 to the rest of the dates in column A. Format the Price values in column I as Currency with two decimal places. Change the format of the Time values in column C so that seconds are not displayed.
9 Enter a SUM function in cell J10 to calculate the total service charges for Tuesday. Enter a SUM function in cells J13, J18, J21 and J24 to calculate service charges for Wednesday, Thursday, and Friday respectively.
10 In the Schedule by Date worksheet, hide column H.
11 In the center section of the Print Header, type 2/18 to 2/22.
12 In cell C26, enter a formula to display the MAX daily sales total from cell range J6:J24.
In cell C27, enter a formula to display the MIN daily sales total from cell range J6:J24.
Apply Merge Cells, Align Right and cell style Heading 4 to cell ranges A26:B26 and A27:B27.
13 Apply Format as Table with headers to cell range A4:J24, Table Style Medium 2, include a Total Row. Format cells C27, C28, and J25 as Currency with two decimal places.
14 Apply conditional formatting to the totals in J10, J13, J18, J21, and J24 to format any total greater than $250.00 to appear Bold and in Green text.
15 Apply AutoFit Column Width to columns A:J, except column H.
16 In Backstage View, set the Orientation of the Schedule by Date worksheet to Landscape Orientation and change No Scaling to Fit All Columns on One Page.
17 In the Consultant worksheet, use Auto Fill to complete the weekday lists in cell ranges C16:C22, C24:C30. Apply Merge & Center to cell range A2:D2.
Apply Bold, Fill Color Turquoise, Accent 3, Lighter 80%, and Angle Clockwise to cell range A6:D6.
18 Apply Bold, Merge & Center, Vertical Text, and Middle Align to cell range A8:A14.
Use the Format Painter to apply the formatting from cell range A8:A14 to cell ranges B8:B14, A16:A22, B16:B22, A24:A30 and B24:B30.
19 Save the workbook and then exit Excel. Submit the workbook as directed.
Attachment:- m_e01ws02_grader_a1.rar