Office 2016 – myitlab:grader – Instructions
Excel Project
YO16_XL_CH08_GRADER_PC_HW - Spa 1.3
Project Description:
Meda Rodate, manager of the Turquoise Oasis Spa, wants to improve the layout of the existing spa invoice and
automate the invoice process as much as possible to ensure data accuracy and consistency. The invoice currently
has formulas in the Charge/Hour and Amount columns, but they often get deleted by mistake. The Therapist
name is often misspelled, the room number is often wrong, and Meda thinks the subtotal amount may not be
calculating correctly. Another problem arises when the description of the service is not entered correctly, and
then the charge/hour cannot be found in the lookup table.
Instructions:
For the purpose of grading the project you are required to perform the following tasks:
Step
Instructions
1
Open the downloaded file e04ch08_grader_pc_Spa.xlsx. Save the file as
e04ch08_grader_pc_Spa_LastFirst, using your last and first name. If a Security Warning
message displays, click the Enable Editing button.
2
On the Invoice worksheet, use trace precedents and trace dependents to look at the formulas to
make sure they are constructed properly. Fix any errors you find.
3
4
5
6
7
8
On the Invoice worksheet, use Evaluate Formula to determine what is wrong with the formula in
cell F33. Fix the formula.
On the Invoice worksheet, identify and correct the circular reference.
On the Invoice worksheet, select cell E10 and add a list Data Validation using cell range A2:A4
from the Therapist worksheet as the source.
Enter the Input message title Select a therapist
Enter the Input message Select the therapist who delivered the services listed.
Enter the Error Alert title Not a valid name
Enter the Error message The name you entered is not a valid name. Please select a name
from the list.
Click the filter arrow next to cell E10, and then select Istas, Christy.
Enter a Decimal data validation for cell range E15:E30 to allow for decimals less than or equal to 2.
Enter the Input message title Hours
Enter the Input message Enter the number of service hours.
Enter a Warning alert. Enter the title Invalid value
Enter the Error message The hours you entered exceed the maximum recommended.
On the Invoice worksheet, in cell E6 enter a Date validation that verifies the date is less than or
equal to =TODAY()
Enter the Input title Invoice Date
Enter the Input message Enter the date in the following format: MM/DD/YYYY.
Enter the Error alert title Error
1
0.000
4.000
4.000
4.000
Note, Mac users can skip this step. On the Invoice worksheet, add the Watch Window. Add watch
for cells F31, F33, and F35.
Move the Watch Window to the top of the worksheet window.
Select cell E15, type 2. Notice that all the cells in the Watch Window changed.
Close the Watch Window.
Updated: 06/20/2017
Points
Possible
0.000
4.000
5.000
4.000
Current_Instruction.docx
Office 2016 – myitlab:grader – Instructions
Step
Excel Project
Points
Possible
Instructions
Enter the Error message box Future dates are not allowed.
Select cell E6, type =TODAY()
9
10
On the Invoice worksheet, in cell E8, enter a Time data validation that will limit entry to start time
of 8:00 AM and end time 4:30 PM.
Enter the Input title, Appointment time
Enter the Input message Enter the appointment time as HH:MM AM/PM.
Enter the Error Alert Title Error
Enter the Error message The time must be between 8:00 AM and 4:30 PM.
Select cell E8, type 2:30 PM
4.000
On the Invoice worksheet, in cell E12, enter a whole number Data Validation that will allow
numbers between 1001 and 5140
Select cell E12, type 1001
0.000
On the Invoice worksheet, in cell C31, enter a text length Data Validation that limits the text length
to less than or equal to 180
11
12
Enter
Enter
Enter
Enter
the
the
the
the
Input message title Comments
Input message Comments may not be more than 180 characters.
Error Alert Title Error
Error Alert message Must be 180 characters or less.
4.000
On the Invoice worksheet, in cell D33, enter a data validation that will display data entry prompts.
Enter the Input message Tax rate
Enter the Input message All items and services require sales tax.
2.000
On the Invoice worksheet, in cell E12, enter a custom data validation. Select the existing text, and
then replace it with
=AND(LEFT(E12,1)=“1”,RIGHT(E12,3)>=“001”,RIGHT(E12,3)0,TEXT(E6,"YYYYMMDD"),"")&"
"&IF(E8>0,TEXT(E8,"HHMM"),"")&" "&IF(E10>0,VLOOKUP(E10,Therapists,2),"")
If necessary, add the Developer tab. Create a macro named ClearCells with a shortcut key of C
and the description To clear contents from cells.
The macro will clear the contents from cells E6, E8, E10, E12, and the cell ranges C31:D32,
B15:B30, and E15:E30. The macro will make cell E6 the active cell after it is ran.
Save the workbook as a macro-enabled workbook with the filename
e04ch08_grader_pc_Spa_LastFirst
In cell E6, enter today’s date. In cell E8, enter 2:30 PM. In cell E10, select Istas, Christy. Test the
Macro.
On the Invoice worksheet, in cell B15, choose Facial - Mud & Citrus. In cell E15, type 1.
Select cell B15.
Create a Relative References Macro named HighlightItem with the shortcut key h and the
description To highlight an invoice special. The macro will select cells B15:F15 and then
Updated: 06/20/2017
2
2.000
5.000
5.000
Current_Instruction.docx
Office 2016 – myitlab:grader – Instructions
Step
Excel Project
Points
Possible
Instructions
change the font to Bold, size 14.
Click cell B16, and then select Hair Coloring. Test the macro.
17
18
19
20
On the Invoice worksheet, add a Button (Form Control) in the top left corner of cell G2, and then
drag to the bottom right corner of cell H3 to draw the button.
Assign the ClearCells macro to the button. Edit the button text to Clear Invoice.
Test the button.
Create an absolute reference macro named ClearFormatting with the shortcut key k and the
description To clear special highlighting from the invoice. The macro will select the cell
range B15:F30, remove the bold font, change the font size back to 9, and then click cell B15.
Launch Visual Basic for Applications (VBA) to view the actual code for the macros you recorded. All
macros you have recorded will show in the window, separated from one another by a horizontal
line.
Scroll if necessary to see the VBA code for the ClearFormatting macro.
Copy the text that starts with Range (“B15:F30”) and ends with Range (“B15:C15”).Select.
Scroll to the top of the VBA window to see the ClearCells macro.
Place your insertion point after the line Range (“E6”).Select. Select Paste.
Close and Return to Microsoft Excel.
Click cell B15, and select Facial - Mud & Citrus. Run the HighlightItem macro.
Click the Clear Invoice button. If necessary, click the Home tab, and verify that the formatting in
row 15 was changed back to size 9, not bold.
On the Invoice worksheet, if necessary, click the Developer tab. In the Code group, click Macros. In
the Macro dialog box, in the Macro name list, select ClearCells, and then click Edit.
A new Visual Basic for Applications (VBA) window opens with the actual code for the macros you
recorded. All macros you have recorded will show in the window, separated from one another by a
horizontal line. Copy all the code from the ClearCells macro, beginning with Sub ClearCells() and
ending with End Sub. Click the Macro worksheet tab, and then paste that code beginning in cell A1.
Return to the VBA window and copy all of the code from the HighlightItem macro, beginning with
Sub HighlightItem() and ending with End Sub. Click the Macro worksheet tab and paste that code
beginning in cell H1. Return to the VBA window and copy all of the code from the ClearFormatting
macro, beginning with Sub ClearFormatting() and ending with End Sub. Click the Macro worksheet
tab and paste that code beginning in cell M1.
Save the workbook as an Excel workbook (.xlsx).
On the Invoice worksheet, in cell D14, insert a hyperlink to cell F2 on the Prices worksheet. Add
the screentip, Go to Prices worksheet
On the Prices worksheet, in cell F2, insert a hyperlink to cell D14 on the Invoice worksheet. Add
the ScreenTip Return to Invoice worksheet
5.000
4.000
3.000
5.000
21
Hide all worksheets except Invoice.
8.000
22
On the Invoice worksheet, unlock cells E6, E8, E10, E12, D14, C31, F34, and cell ranges B15:B30
and E15:E30.
Protect the Invoice worksheet and do not allow for locked cells to be selected.
Press HOME.
5.000
23
On the Invoice worksheet, remove the Gridlines.
4.000
Updated: 06/20/2017
3
Current_Instruction.docx
Office 2016 – myitlab:grader – Instructions
Excel Project
Step
Instructions
Points
Possible
24
Unprotect the Invoice worksheet. On the Invoice worksheet, hide all formulas, and then protect the
sheet again.
5.000
25
On the Invoice worksheet, hide the horizontal scroll bar.
3.000
26
Protect the workbook structure. Do not add a password.
5.000
27
Close the workbook, exit Excel, and then submit your file as directed by your instructor.
0.000
Total Points
Updated: 06/20/2017
4
100.000
Current_Instruction.docx
INVOICE
Turquoise Oasis Spa
A Passion for Helping People Relax
INVOICE #:
DATE:
TIME:
3356 Hemmingway Circle
Santa Fe, NM 87594
Phone: 505.555.SPA1
Fax: 505.555.SPAx
THERAPIST:
ROOM:
DESCRIPTION
CHARGE/HOUR
HOURS
AMOUNT
Mud Bath
$
100.00
1.00 $
100.00
Reflexology
$
150.00
1.00 $
150.00
COMMENTS:
Make all checks payable to Turquoise Oasis Spa.
THANK YOU FOR YOUR BUSINESS!
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
SUBTOTAL
TAX RATE
$
6.50% SALES TAX
OTHER
TOTAL
150.00
#VALUE!
$
#VALUE!
Product
Location Price
Unit
Facial - Mud & Citrus
Facial - Steam Exfoliation
Hair Coloring
Hair Style & Cut - Men
Hair Style & Cut - Women
Makeup Consultation
Manicure & Pedicure Package
Manicure & Polish
Pedicure & Polish
Waxing - Body
Waxing - Facial
Aroma & Sound Therapy Package
Aroma Therapy
Body Detox Electrotherapy
Body Detox Steam Therapy
Massage - Deep Tissue
Massage - Deep Tissue, Couples
Massage - Fusion
Massage - Fusion, Couples
Massage - Shiatsu
Mud Bath
Reflexology
Sound Therapy
Steam Bath
Tibetan Reiki Therapy
Salon
Salon
Salon
Salon
Salon
Salon
Salon
Salon
Salon
Salon
Salon
Spa
Spa
Spa
Spa
Spa
Spa
Spa
Spa
Spa
Spa
Spa
Spa
Spa
Spa
Each
Each
Each
Each
Each
Each
Each
Each
Each
Each
Each
Hour
Hour
Hour
Hour
Hour
Hour
Hour
Hour
Hour
Hour
Hour
Hour
Hour
Hour
100.00
75.00
100.00
35.00
50.00
1100.00
70.00
45.00
45.00
75.00
25.00
100.00
75.00
150.00
100.00
125.00
112.50
150.00
137.50
150.00
100.00
150.00
75.00
65.00
225.00
Return to invoice
PRODUCTS
Aroma & Sound Therapy Package
Aroma Therapy
Body Detox Electrotherapy
Body Detox Steam Therapy
Massage - Deep Tissue
Massage - Deep Tissue, Couples
Massage - Fusion
Massage - Fusion, Couples
Massage - Shiatsu
Mud Bath
Reflexology
Sound Therapy
Steam Bath
Tibetan Reiki Therapy
THERAPISTS
Istas, Christy
Mault, Kendra
Niese, Jason
EMPLOYEE_NUMBER
3054
1477
5901
Purchase answer to see full
attachment