Cleansing Inventory Data
YO16XLCH09GRADERPS2AS_-_Cleansing_Inventory_Data_13_Instructions.docx
Office 2016 – myitlab:grader – Instructions Excel Project
YO16_XL_CH09_GRADER_PS2_AS – Cleansing Inventory Data 1.3
Project Description: You currently work part-time in an automotive parts store. Because word of your knowledge of Excel has spread, you have been contacted by the district manager. The database used for keeping track of inventory has been corrupted, causing some issues with the inventory data. You have been asked to use your expertise of Excel to clean the inventory data.
Instructions: For the purpose of grading the project you are required to perform the following tasks: Step Instructions Points Possible 1 Start Excel. Open the downloaded workbook named e05ch09_grader_a1_Inventory.xlsx. Save the file with the name e05ch09_grader_a1_Inventory_LastFirst.xlsx, using your last and first name. If necessary, enable the content. 0.000 2 Remove any duplicates that exist in the Inventory worksheet. Duplicate records are any records with the same InventoryCode and ItemNumber. 15.000 3 Enter the text Step 1 in cell G1. 5.000 4 The data in column C has nonprintable characters before and after the data contained in each cell. In cell G2, enter a formula to remove any nonprintable characters from column C. Copy the formula down the column. 12.000 5 Enter the text Step 2 in cell H1. 5.000 6 There are several spaces before and after the data in column G that need to be removed. In cell H2, enter a formula to remove any extra spaces in the data from column G. Copy the formula down the column. 12.000 7 The category and manufacturer should be in two separate columns. Enter the text Category in cell I1. 5.000 8 Enter the text Manufacturer in cell J1. 5.000 9 Use Flash Fill to place the category data from column H in proper case in column I. Use Flash Fill to place the Manufacturer data from column H into the new column J. Owing to the nature of the data, Flash Fill will need to be invoked from the Data tab. Mac users will need to use the Text to Columns feature. Select the range H2:H24. On the Data tab, click Text to Columns. Select Space as the Delimiter and =$I$2 as the Destination. 21.000 10 Enter the text InvCode in cell K1. In cell K2, enter a formula to display the inventory code from column A as all uppercase letters. Copy the formula down the column. 10.000 11 Enter the text ItemCode in cell L1. The ItemCode is a combination of the inventory code in all uppercase letters, with the item number appended to it. For example, the first ItemCode should be RLXF920569. Use the appropriate function in column L to create this new ItemCode and AutoFill the function down to L20. 10.000 12 Save the workbook. Close the workbook. Exit Excel. Submit the workbook as directed. 0.000 Total Points 100.000
Updated: 05/01/2017 1 Current_Instruction.docx
li_e05ch09_grader_a1_Inventory.xlsx
Inventory
| InventoryCode | ItemNumber | Category/Manufacturer | SellingPrice | WholeSale | Units/Description |
| rLXf | 920569 | � BRAKES NAPA � | 44.99 | 41.94 | 4 pads |
| lJDf | 400632 | � TOOLS NAPA� | 16.25 | 12.34 | 4 grinding wheels |
| lJDf | 400632 | � TOOLS NAPA� | 16.25 | 12.34 | 4 grinding wheels |
| bCDx | 914314 | � Paint 3M � | 18.4 | 14.48 | 3 spray gun filters |
| kRBa | 933521 | � EQUIPMENT Balkamp � | 9.99 | 7.89 | 3 antifreeze testers |
| lQSa | 402860 | � CHEMICALS NAPA� | 48.49 | 35.18 | 5 gallons RV antifreeze |
| bLMv | 009225 | � Paint Sharpe � | 6 | 4.62 | 1 spray gun service kit |
| fOXt | 030594 | � SHOCKS KYB� | 89.99 | 75.00 | 2 rear shocks |
| nXWq | 041141 | � CHEMICALS Altrom Chemicals � | 37.99 | 25.51 | 6 cans fuel injector cleaner |
| tNDk | 741102 | � OIL Pennzoil� | 5.25 | 3.94 | 1 qt |
| eSZw | 009216 | � WELDERS Vise Grip � | 22.19 | 13.53 | 3 rolls metal solder |
| qKOm | 895850 | � Paint Evercraft � | 9.99 | 6.19 | 5 spray gun nozzle gaskets |
| mTHa | 091160 | � EQUIPMENT Viper � | 12.49 | 10.62 | 3 antifreeze filters |
| lQSa | 402860 | � CHEMICALS K&W� | 18.49 | 13.18 | 5 cans radiator stop leak |
| eWHp | 500129 | � OIL Quaker State � | 3.99 | 3.31 | 1 qt |
| eCEz | 009256 | � ACCESSORIES NAPA � | 8.5 | 6.35 | 1 universal license plate light |
| sYKp | 424076 | � TOOLS DeWalt � | 249.49 | 203.65 | 1 grinder |
| nQUq | 874000 | � SHOCKS NAPA � | 13.09 | 11.55 | 2 shock mate boot kits |
| gLYh | 930777 | � BRAKES McGard � | 10.79 | 6.14 | 4 locks |
| eCEz | 009256 | �ACCESSORIES Balkamp � | 3.5 | 2.35 | 2 yellow round side markers |
| bLMv | 009225 | � Paint 3M � | 26 | 24.62 | 6 rolls masking tape |
| aWHw | 873700 | � WELDERS Balkamp � | 26.49 | 20.09 | 4 soldering gun tips |
| uIRb | 883466 | � SEALANTS Permatex � | 16.49 | 12.00 | 6 tubes gasket sealant |
| rLXf | 920569 | � BRAKES Balkamp � | 5.45 | 2.94 | 1 spindle lock nut kit |
&F
YO16XLCH08GRADERPCHW_-_Spa_13_Instructions.docx
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 |