finish 3 excel form

Business Finance

Andover Newton Theological School

Question Description

1. according to requirement to complete the excel form step by step

2. all of the form in the attached

3. in the final pick one of the homework assignments and add a chart to it. Be sure that the chart fits the kind of data being reported. Be sure your chart is complete with a title, a legend, and axis labels. Please remember that you can create a summary table based on the main table in order to create a more meaningful chart.

4. Submit the following files:

(1) 2-BillableHrsOct4to8.xlsx
(2) 2-WPMCCardioCosts.xlsx
(3) U1-RSRHelpDesk.xlsx
(4) Add a chart to one of the worksheet

Unformatted Attachment Preview

Visual Benchmark 1 - Use Lookup, Statistical, and Math Functions in a Billing Summary 1. Open BillHrs0ct4to8.xlsx. 2. Save the workbook with the name 2-BillableHrsOct4to8. 3. Review the worksheet shown in Figure 2.1. Use the following information to create the required formulas. Create range names to use in all the formulas so readers can easily interpret the formula: a. In column F, create a formula to look up the attorney's hourly rate from the table at the bottom right of the worksheet. The formula should return results for exact matches only. b. In column G, calculate the legal fees billed by multiplying the billable hours times the hourly rate. c. In the range J6:J9, calculate the total legal fees billed by attorney (use SUMIF) d. In the range J13:J16, calculate the average hours billed by attorney (use AVERAGEIF). 4. Save and then close 2-BillableHrsOct4to8. Visual Benchmark 2 – Use lookup and logical functions to calculate cardiology costs 1. Open WPMCCardioCosts.xlsx. 2. Save the workbook with the name 2-WPMCCardioCosts. 3. Range names for this worksheet have already been created. Spend a few moments reviewing the range names and the cells each name references to become familiar with the worksheet. 4. Review the worksheet shown in Figure 2.2 and revise it to match the one shown by creating formulas using the following information: a. In column G, create a formula to look up the surgery fee in the table at the bottom of the worksheet. The formula should return results for exact matches only. b. In column H, create a conditional formula that inserts the cost of the aortic or mitral valve if the cardiac surgery required a replacement valve; otherwise, place a 0 in the cell. Hint: The surgery codes for surgeries that include replacement valves are ART and MRT (for aortic and mitral, respectively). Do not add a named range to represent the data in the Surgery Code column as Excel will use the entire range when calculating the result instead of an individual cell. c. In column I, calculate the postoperative hospital cost by multiplying the number of days the patient was in hospital by the postoperative cost per day. d. In column J, calculate the total cost as the sum of the surgery fee, valve cost, and postoperative hospital cost. e. Calculate the total cost for each column in row 22. 5. Format the numbers as shown in Figure 2.2. 6. Save, print, and then close 2-WPMCCardioCosts. Performance Assessment 1 – Conditional Format and Filter a Help Desk Worksheet 1. Open RSRHelpDesk.xlsx. 2. Save the workbook with the name U1-RSRHelpDesk. 3. Use the icon set from the Quick Analysis button to apply conditional formatting to the values in the Priority column. 4. Create a custom format for the values in the Time Spent column. The format should display a leading zero (that is, a 0 before the decimal point for a value less than 1), two digits after the decimal point, and the text hrs after each entry separated by one space from the number (for example, 2.20 hrs, 0.25 hrs, etc.). 5. Create two conditional formatting rules for the values in the Time Spent column as follows: a. For all the entries in which the time spent is less than one hour, apply bold formatting and the Olive Green, Accent 3, Lighter 80% fill color (seventh column, second row). b. For all the entries in which the time spent is more than two hours, apply the Yellow fill color (fourth column, last row). 6. Filter the worksheet by the Yellow fill color applied in the Time Spent column. 7. Clear the filter and filter arrow(s) 8. Save and then close U1-RSRHelpDesk. 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 RSR Computer Services Operator Summary Help Desk Status Report Operators Last Name First Name 1 Santini Hector 2 Rubin Jane 3 Listowel Terri 4 Benton Jamie Ticket No. Priority Type of Call Operator ID Date File No. 3/1/2021 14401 1 Hardware 1 14401-1-1 3/2/2021 14402 1 Password 2 14402-1-2 3/3/2021 14403 1 OS 2 14403-1-2 3/3/2021 14404 2 Email 1 14404-2-1 3/5/2021 14405 1 Password 3 14405-1-3 3/5/2021 14406 2 Hardware 4 14406-2-4 3/5/2021 14407 2 Email 4 14407-2-4 3/8/2021 14408 3 Internet 4 14408-3-4 3/9/2021 14409 3 Internet 2 14409-3-2 3/10/2021 14410 1 Hardware 2 14410-1-2 3/12/2021 14411 3 Email 1 14411-3-1 3/12/2021 14412 3 Email 1 14412-3-1 3/15/2021 14413 2 Internet 3 14413-2-3 3/15/2021 14414 2 Internet 3 14414-2-3 3/16/2021 14415 3 Password 2 14415-3-2 3/17/2021 14416 2 Email 2 14416-2-2 3/18/2021 14417 1 Email 1 14417-1-1 3/22/2021 14418 1 Hardware 1 14418-1-1 3/23/2021 14419 3 OS 4 14419-3-4 3/26/2021 14420 2 Hardware 3 14420-2-3 3/26/2021 14421 1 OS 1 14421-1-1 3/29/2021 14422 3 Internet 2 14422-3-2 3/30/2021 14423 1 Hardware 4 14423-1-4 ID 1 2 3 4 Number of Active calls: Number of Closed calls: Last Name First Name Time Spent 2.25 0.25 0.5 1.25 0.25 1.75 1 0.5 0.5 3.25 1 0.5 0.75 1.25 0.5 0.75 1.25 2.25 1.75 1.25 2.25 0.75 1 Status Closed Closed Closed Closed Closed Active Closed Closed Closed Closed Closed Closed Closed Closed Closed Active Active Active Active Closed Closed Active Active Calls Total Time Avg Time Wellington Park Medical Center Division of Cardiology Adult Cardiac Surgery Costs Month: Patient Number 60334124 60334567 60398754 60347821 60328192 60321349 60398545 60342548 60331569 60247859 60158642 60458962 68521245 63552158 68451278 October Patient Last Name Wagner Gonzalez Vezina Dowling Ashman Kaiser Van Bomm Youngblood Lorimar Peterson O'Connor Jenkins Norfolk Adams-Wiley Estevez Surgeon: Novak Patient First Name Sara Hector Paula Jager Carl Lana Emile Frank Hannah Mark Terry Esther Leslie Susan Stefan Surgery Code MRP ARP ABP MRT ARP ART ABP ABP MRT ART ABP MRP ABP MRT ARP Postoperative hospital cost per day: $ 1,500.00 Aortic or mitral valve cost: $ 1,275.00 Surgery Code ABP ARP ART MRP MRT Surgery Fee 8,820 8,876 10,190 9,325 10,240 Days in Hospital 7 10 5 11 4 12 7 6 8 9 7 9 8 6 6 Surgery Procedure Artery Bypass Aortic Valve Repair Aortic Valve Replacement Mitral Valve Repair Mitral Valve Replacement Total Cost: Surgery Fee Valve Cost Postoperative Hospital Cost Total Cost ...
Student has agreed that all tutoring, explanations, and answers provided by the tutor will be used to help in the learning process and in accordance with Studypool's honor code & terms of service.

This question has not been answered.

Create a free account to get help with this and any other question!

Similar Questions
Related Tags