Assessment 1 1. Open RSRServRpt.xlsx. 2. Save the workbook with the name 1-RSRServRpt 3. Apply the following formatting changes to the Sep worksheet: a. Format the values in the range C6:C23 as fractions using the As quarters (2/4) option in the Type list box. b. Format the rate codes in the range D6:D23 with the 3 Traffic Lights (Rimmed) icon set (second column, first row of the Shapes section.) c. Use the New Rules option to format the parts values in the range F6:F22 with the light red fill color (sixth column, second row) for those cells with values that are equal to 0. d. Format the total invoice values in the range G6:G22 using the red data bars. (Use the Red Data Bar option in the Gradient Fill section at the drop down gallery.) 4. Save and then close 1-RSRServRpt. Assessment 2 1. Open 1-RSRServRpt. 2. Save the workbook with the name 1-RSRServRpt-2. 3. Create and apply the following custom number formats to the Sep worksheet: a. Create a custom number format that displays hrs one space after each value in the range C6:C23. Hint: After selecting Custom in the Category list box, click after the existing format codes in the Type text box and then add the required entry (Do not delete what is already in the Type text box.) b. Create a custom number format that displays RSR- in front of each work order number in the range B6:B22. 4. Save and then close 1-RSRServRpt-2. Assessment 3 1. Open 1-RSRServRpt-2. 2. Save the workbook with the name 1-RSRServRpt-3. 3. Make the following changes to the Sep worksheet: a. Select the range A5:G22 and turn on the Filter feature. b. Using the filter arrow at the top of the Hours Billed column, display those invoices for which the hours billed are between 1.75 and 3.75. Hint: When typing in the values, type 1.75 and 3.75. Do not select 1 3/4 hrs and 3 3/4 hrs from the drop-down lists in the Custom AutoFilter dialog box. 4. Make the following changes to the Oct worksheet: a. Select the range A5:G22 and then turn on the Filter feature. b. Filter the Parts column by color to show only those invoices for which no parts were billed. 5. Make the following changes to the Nov worksheet: a. Clear the filter from the Date column. b. Filter the worksheet by the icon associated with rate code 3. 6. Make the following changes to the Dec worksheet: a. Remove the filter arrows from the worksheet. b. Make any cell within the range A5:G22 active. c. Open the Sort dialog box. d. Define three sort levels as follows: Sort by Rate code Rate code Rate code Sort on cell icon cell icon cell icon Order Red traffic light (on top) Yellow traffic light (on top) Green traffic light (on top) 7. Save and then close 1-RSRServRpt-3. Assessment 4 1. Open VRPay-Oct30.xlsx. 2. Save the workbook with the name 1-VRPay-Oct30 3. Create and apply two conditional formatting rules for the values in the Pay Rate column as follows: a. Apply a light blue fill color (fifth column, third row) to values between 11.50 and 13.00. b. Apply a light green fill color (seventh column, third row) to values greater than 13.00. 4. Create a conditional formatting rule for the Gross Pay column that will format the values in the dark red font color (first option in the Standard Colors section) for those employees that have worked overtime hours. Hint: When entering the formula use relative referencing (K6:K23) in the logical test. 5. Use the Quick Analysis button to find the top 10 percent among values in the Gross Pay column. 6. Edit the formatting rule for Cell Value < 11.5 in the Pay Rate column by applying the light purple fill color (eighth column, third row). 7. Use the Quick Analysis button to delete the formatting rule for Overtime Hours column. 8. Save and then close 1-VRPay-Oct30 Visual Benchmark 1. Open Billings0ct4to8.xlsx. 2. Save the workbook with the name 1-Billings0ct4to8 3. Format the worksheet to match the one shown in Figure WB-1.1 using the following information: • The data in the Billing Code column has been custom formatted to add the text Amicus#- in the standard blue font color in front of the code number. • Icon sets have been used in the Attorney Code column and the same icon set should be applied in the Attorney Code Table section of the worksheet. • The data bars added to the values in the Legal Fees column have been formatted with the Turquoise, Accent 3 gradient fill (seventh column, first row in the Theme Colors section). Hint: Select More Rules in the Data Bars side menu. • Values below 1500.00 in the Total Due column have been conditionally formatted in bold dark red text and the worksheet has been sorted by the font color used for the conditional format. 4. Save and then close 1-Billings0ct4to8 RSR Computer Services Service Division Report Work Order Number Date 9/1/2021 65012 9/3/2021 65013 9/7/2021 65014 9/7/2021 65015 9/10/2021 65016 9/13/2021 65017 9/14/2021 65018 9/17/2021 65019 9/17/2021 65020 9/20/2021 65021 9/22/2021 65022 9/22/2021 65023 9/23/2021 65024 9/27/2021 65025 9/28/2021 65026 9/28/2021 65027 9/30/2021 65028 TOTAL SERVICE HOURS BILLED: September Hours Rate Billed Code 2.25 1 3.25 3 2.25 1 1.75 3 1.5 3 2.25 1 4.25 1 3.75 1 1.75 2 2 3 1.5 3 2.25 1 3.25 1 3.5 3 2.75 3 3.5 2 4.5 1 46.25 Extended Labor 101.25 227.50 101.25 122.50 105.00 101.25 191.25 168.75 105.00 140.00 105.00 101.25 146.25 245.00 192.50 210.00 202.50 2,566.25 Parts 22.50 75.85 28.95 25.75 45.15 55.85 22.50 18.50 75.85 187.50 110.50 35.95 47.66 752.51 Total Invoice 123.75 303.35 130.20 122.50 130.75 146.40 247.10 191.25 105.00 140.00 123.50 177.10 146.25 432.50 303.00 245.95 250.16 3,318.76 20220107034703rsrservrpt Sep Rate Code Chart Description Regular Evening Weekend Code 1 2 3 Rate $ 45.00 $ 60.00 $ 70.00 RSR Computer Services Service Division Report Work Order Number Date 10/4/2021 65029 10/5/2021 65030 10/7/2021 65031 10/7/2021 65032 10/11/2021 65033 10/12/2021 65034 12/13/2021 65035 10/18/2021 65036 10/18/2021 65037 10/19/2021 65038 10/21/2021 65039 10/22/2021 65040 10/25/2021 65041 10/25/2021 65042 10/27/2021 65043 10/28/2021 65044 10/28/2021 65045 TOTAL SERVICE HOURS BILLED: October Hours Rate Billed Code 2 1/4 1 3 1/4 2 2 1/4 1 1 3/4 1 1 1/2 1 2 1/4 3 1 3/4 3 3 3/4 1 1 3/4 2 2 1 1 1/2 2 2 1/4 1 3 1/4 3 3 1/2 1 2 3/4 2 1 1/2 2 4 1/2 3 41 3/4 Extended Labor 101.25 195.00 101.25 78.75 67.50 157.50 122.50 168.75 105.00 90.00 90.00 101.25 227.50 157.50 165.00 90.00 315.00 2,333.75 Parts 22.50 75.85 28.95 25.75 45.15 56.85 22.50 18.50 75.85 112.50 187.50 110.50 47.66 830.06 Total Invoice 123.75 270.85 130.20 78.75 93.25 202.65 179.35 191.25 105.00 90.00 108.50 177.10 340.00 345.00 275.50 90.00 362.66 3,163.81 20220107034703rsrservrpt Oct Rate Code Chart Description Regular Evening Weekend Code 1 2 3 Rate $ 45.00 $ 60.00 $ 70.00 RSR Computer Services Service Division Report Date 11/8/2021 11/8/2021 11/9/2021 11/12/2021 11/15/2021 11/15/2021 11/17/2021 Work Order Number 65049 65050 65051 65052 65053 65054 65055 November Hours Rate Billed Code 1 3/4 1 1 1/2 1 2 1/4 3 4 1/4 3 3 3/4 1 1 3/4 2 2 1 Extended Labor 78.75 67.50 157.50 297.50 168.75 105.00 90.00 Parts 25.75 45.15 55.85 22.50 - Total Invoice 78.75 93.25 202.65 353.35 191.25 105.00 90.00 20220107034703rsrservrpt Nov Rate Code Chart Weekend 3 $ 70.00 RSR Computer Services Service Division Report Work Order Number Date 12/1/2021 65063 12/2/2021 65064 12/6/2021 65065 12/6/2021 65066 12/9/2021 65067 12/10/2021 65068 12/14/2021 65069 12/16/2021 65070 12/16/2021 65071 12/17/2021 65072 12/20/2021 65073 12/21/2021 65074 12/22/2021 65075 12/27/2021 65076 12/27/2021 65077 12/28/2021 65078 12/29/2021 65079 TOTAL SERVICE HOURS BILLED: December Hours Rate Billed Code 2 1/4 1 1 1/2 3 2 1/4 1 1 3/4 3 1 1/2 3 2 1/4 1 4 1/4 2 3 3/4 1 1 3/4 2 2 3 1 1/2 3 2 1/4 1 3 1/4 1 3 1/2 3 1 1/2 3 3 1/2 1 4 1/2 1 43 1/4 Extended Labor 101.25 105.00 101.25 122.50 105.00 101.25 255.00 168.75 105.00 140.00 105.00 101.25 146.25 245.00 105.00 157.50 202.50 2,367.50 Parts 22.50 110.23 28.95 25.75 45.15 55.85 22.50 18.50 75.85 112.50 187.50 75.85 47.66 828.79 Total Invoice 123.75 215.23 130.20 122.50 130.75 146.40 310.85 191.25 105.00 140.00 123.50 177.10 258.75 432.50 180.85 157.50 250.16 3,196.29 20220107034703rsrservrpt Dec Rate Code Chart Description Regular Evening Weekend Code 1 2 3 Rate $ 45.00 $ 60.00 $ 70.00 ViewRite Payroll Week Ended: October 30, 2021 First Name Alexis Derrick Priya Emil Leslie Irene Ruthann Stefan Alex Erica Dana Ashley Susan Dana Maria Xue Annette Randy TOTAL Last Name Torres MacLean Singh Fedorov Sparling O'Rourke Goldstein Kominek Spivak Wilkins Vanderhoek Castillo Anez Ivanowski Alvarez Chen Fournier Brown Sun 8.0 8.0 8.0 4.0 5.5 8.0 4.0 4.5 8.0 8.0 6.5 72.5 Mon 5.0 8.0 6.0 6.0 5.0 7.0 7.0 8.0 3.0 4.0 7.0 66.0 Tue 7.0 6.5 4.0 8.0 7.5 8.0 8.0 8.0 8.0 5.5 6.0 4.5 9.0 90.0 Wed 8.0 8.0 8.0 4.5 8.0 8.5 5.0 8.0 7.5 8.0 6.5 5.5 85.5 Thu 8.0 8.0 8.0 8.0 8.0 6.5 4.0 8.0 7.5 8.0 8.5 7.5 7.5 4.0 4.0 105.5 Fri 9.0 8.5 8.5 9.5 7.5 8.5 8.0 6.0 5.5 4.0 7.0 6.8 7.5 8.0 7.5 6.0 117.8 Sat 9.5 9.0 4.5 8.0 8.0 7.5 6.0 4.5 8.0 6.5 5.0 8.5 7.0 7.0 4.0 103.0 Total Overtime Hours Hours 46.5 6.5 48.0 8.0 41.0 1.0 44.5 4.5 37.5 35.5 31.0 35.0 45.0 5.0 24.5 33.5 40.0 22.5 29.8 26.5 40.5 0.5 33.0 26.0 640.3 25.5 Pay Rate 11.75 11.35 11.35 11.75 12.15 13.50 11.75 11.75 11.75 11.35 11.35 12.15 12.15 11.75 11.35 11.35 13.25 11.35 Gross Pay 584.56 590.20 471.03 549.31 455.63 479.25 364.25 411.25 558.13 278.08 380.23 486.00 273.38 350.15 300.78 462.51 437.25 295.10 7,727.06 O'Donovan & Sullivan Law Associates BILLING SUMMARY OCTOBER 4 TO 8, 2021 File FL-325 EP-652 CL-412 IN-745 EL-632 RE-475 CL-501 CL-521 PL-348 RE-492 EL-632 PL-512 IN-745 FL-385 CL-412 CL-450 IN-801 EP-685 RE-501 Client 10104 10106 10125 10210 10225 10285 10341 10334 10420 10425 10225 10290 10210 10278 10125 10358 10346 10495 10384 Date Billing Code Attorney Code 10/4/2021 2 1 10/4/2021 3 1 10/4/2021 1 2 10/5/2021 6 3 10/5/2021 5 3 10/5/2021 4 4 10/6/2021 1 2 10/6/2021 1 2 10/6/2021 7 3 10/6/2021 4 4 10/7/2021 5 3 10/7/2021 7 3 10/7/2021 6 3 10/7/2021 2 1 10/8/2021 1 2 10/8/2021 1 2 10/8/2021 6 3 10/8/2021 3 3 10/8/2021 4 4 Legal Fees Disbursements Total Due 2,273.75 95.10 2,368.85 1,028.50 23.75 1,052.25 2,493.75 55.40 2,549.15 2,425.00 65.20 2,490.20 1,211.00 37.85 1,248.85 3,807.00 48.96 3,855.96 1,143.75 55.24 1,198.99 1,518.75 27.85 1,546.60 2,500.00 34.95 2,534.95 2,043.00 38.75 2,081.75 2,300.00 42.15 2,342.15 1,620.00 65.15 1,685.15 1,450.00 24.25 1,474.25 2,040.00 85.47 2,125.47 1,143.75 38.12 1,181.87 1,762.50 55.24 1,817.74 1,425.00 62.18 1,487.18 2,375.00 94.55 2,469.55 1,237.50 34.28 1,271.78 Billing Code Table Code Area of Practice 1 Corporate 2 Divorce & Separation 3 Wills & Estates 4 Real Estate 5 Employment Litigation 6 Insurance Personal Injury 7 Other Code 1 2 3 4 Attorney Code Table Attorney Marty O'Donovan Toni Sullivan Rosa Martinez Kyle Williams
Purchase answer to see full attachment