MyITLab Office Excel for Statistics Chapter Project Solutions.

User Generated

Fbpprexvq5421

Business Finance

Description

I need a completed Chapter 8 Excel spreadsheet for an assignment out of MyITLabs. All required files are attached below.

Unformatted Attachment Preview

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
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

The soluti...


Anonymous
Super useful! Studypool never disappoints.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Related Tags