Project Description

Project Description

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 Points Possible 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. 0.000 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. 4.000 3 On the Invoice worksheet, use Evaluate Formula to determine what is wrong with the formula in cell F33. Fix the formula. 4.000 4 On the Invoice worksheet, identify and correct the circular reference. 4.000 5 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. 0.000 6 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. 4.000 7 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. 5.000 8 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 Enter the Error message box Future dates are not allowed. Select cell E6, type =TODAY() 4.000 9 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 10 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 11 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 Enter the Input message title Comments Enter the Input message Comments may not be more than 180 characters. Enter the Error Alert Title Error Enter the Error Alert message Must be 180 characters or less. 4.000 12 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 13 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)<=“5”,LEFT(E12,1)>=“1”,RIGHT(E12,3)>=“001”,RIGHT(E12,3)<=“140”,LEN(E12)=4) Enter the Input title Room number Enter the Input message Enter the 4-digit room number. Enter the Error Alter title Error Enter the Error message You must enter a 4-digit room number. In cell E12, type 3120. 6.000 14 In cell E4, type =IF(E6>0,TEXT(E6,”YYYYMMDD”),””)&” “&IF(E8>0,TEXT(E8,”HHMM”),””)&” “&IF(E10>0,VLOOKUP(E10,Therapists,2),””) 2.000 15 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. 5.000 16 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 change the font to Bold, size 14. Click cell B16, and then select Hair Coloring. Test the macro. 5.000 17 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. 5.000 18 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. 4.000 19 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). 3.000 20 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 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 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 100.000

Updated: 06/20/2017 1 Current_Instruction.docx

li_e04ch08_grader_pc_Spa.xlsx

Invoice

Turquoise Oasis Spa INVOICE
A Passion for Helping People Relax INVOICE #:
DATE:
TIME:
3356 Hemmingway Circle
Santa Fe, NM 87594 THERAPIST:
Phone: 505.555.SPA1
Fax: 505.555.SPAx ROOM:
DESCRIPTION CHARGE/HOUR HOURS AMOUNT
Mud Bath $ 100.00 1.00 $ 100.00
Reflexology $ 150.00 1.00 $ 150.00
COMMENTS: SUBTOTAL $ 150.00
TAX RATE 6.50% SALES TAX ERROR:#VALUE!
OTHER $ – 0
TOTAL ERROR:#VALUE!

Make all checks payable to Turquoise Oasis Spa. THANK YOU FOR YOUR BUSINESS!

Prices

Product Location Price Unit
Facial – Mud & Citrus Salon 100.00 Each Return to invoice
Facial – Steam Exfoliation Salon 75.00 Each
Hair Coloring Salon 100.00 Each
Hair Style & Cut – Men Salon 35.00 Each
Hair Style & Cut – Women Salon 50.00 Each
Makeup Consultation Salon 1100.00 Each
Manicure & Pedicure Package Salon 70.00 Each
Manicure & Polish Salon 45.00 Each
Pedicure & Polish Salon 45.00 Each
Waxing – Body Salon 75.00 Each
Waxing – Facial Salon 25.00 Each
Aroma & Sound Therapy Package Spa 100.00 Hour
Aroma Therapy Spa 75.00 Hour
Body Detox Electrotherapy Spa 150.00 Hour
Body Detox Steam Therapy Spa 100.00 Hour
Massage – Deep Tissue Spa 125.00 Hour
Massage – Deep Tissue, Couples Spa 112.50 Hour
Massage – Fusion Spa 150.00 Hour
Massage – Fusion, Couples Spa 137.50 Hour
Massage – Shiatsu Spa 150.00 Hour
Mud Bath Spa 100.00 Hour
Reflexology Spa 150.00 Hour
Sound Therapy Spa 75.00 Hour
Steam Bath Spa 65.00 Hour
Tibetan Reiki Therapy Spa 225.00 Hour

Products

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

THERAPISTS EMPLOYEE_NUMBER
Istas, Christy 3054
Mault, Kendra 1477
Niese, Jason 5901

Macro


Comments are closed.