Margarita Romero is the clinic manager for the Wellpoint Medical Clinic in Columbia, Maryland

INSTRUCTIONS TO CANDIDATES

ANSWER ALL QUESTIONS

  PROJECT STEPS

1. Margarita Romero is the clinic manager for the Wellpoint Medical Clinic in Columbia, Maryland. The clinic has recently completed its first year of operation, and Margarita is using an Excel workbook to prepare for the annual report, which will include data about revenue and the clinic's health studies. She asks for your help to calculate totals, incorporate data from Access, and insert charts to complete the workbook.

Open your Excel Project file workbook, go to the Department worksheet, and then modify it as follows: 

a. Rename the worksheet to use the more descriptive Department Analysis as the worksheet name. 

b. Unfreeze the top rows since Margarita does not need to display them while scrolling.

2. Format and insert data in the first two rows as follows to provide a date and a more striking worksheet title:

a. In cell B1, apply the Title cell style. 

b. Add a Bottom Border to cell B1 using the thick line style (2nd column, 5th entry in the Style list) and the Lime, Accent 2 color. 

c. Enter the date 7/29/21 in cell C2.

d. Using the Number Format list, apply the Short Date number format to cell C2 to display the date with a four-digit year.

3. Margarita wants to format cell A4 to match cells A12, A19, and A27. Format the cell as follows:

a. Merge and center the range A4:A9.

b. Rotate the text up so it reads from bottom to top. 

c. Change the fill color of the cell to Lime, Accent 2, Lighter 60%.

d. Middle-align the text.

e. Autofit column A to reduce the width of the worksheet contents.

4. Format the Quarterly Revenue and Projected Revenue data as follows to make it easier to read:

a. In the ranges C5:F5 and C9:F9, apply the Accounting number format.

b. Copy the formatting from the range B5:H9 to the range B20:H24 so that the format of the Projected Revenue data matches the format of the Quarterly Revenue data.

5. In the range H5:I9, Margarita wants to calculate the annual revenue per department and show the percentage each department contributed to total revenue. Insert formulas to provide these calculations as follows:

a. In cell H5, enter a formula using the SUM function to calculate the total revenue for the General Medicine Department (range C5:F5). 

b. Copy the formula without formatting from cell H5 to the range H6:H9.

c. In cell I5, enter a formula without using a function that divides the General Medicine total in cell H5 by the annual total in cell H9 to display the department's percentage of total revenue. Use an absolute reference for cell H9, and then copy the formula without formatting from cell I5 to the range I6:I9.

d. To display more precise percentages, increase the number of decimal places in the range I5 to I9 to show one decimal place.

6. Margarita also wants to show the average, maximum, and minimum revenue amounts per department and for all departments. Calculate these statistics in the range J5:L9 by entering formulas as follows:

a. In cell J5, enter a formula using the AVERAGE function to average the quarterly revenue from the General Medicine Department (range C5:F5). Copy the formula without formatting from cell J5 to the range J6:J9.

b. In cell K5, enter a formula using the MAX function to find the maximum quarterly revenue from the General Medicine Department (range C5:F5). Copy the formula without formatting from cell K5 to the range K6:K9.

c. In cell L5, enter a formula using the MIN function to find the minimum quarterly revenue from the General Medicine Department (range C5:F5). Copy the formula without formatting from cell L5 to the range L6:L9.

7. Providers and staff collectively are eligible for a 20 percent bonus if the total quarterly revenue this year is more than $230,000. Otherwise, the bonus is 15 percent of the quarterly revenue. Next year, the departments will have to earn more than $250,000 per quarter to be eligible for the 20 percent bonus. Apply conditional formatting as follows to highlight the revenue totals eligible for the higher bonus:

a. In the range C9:F9, apply a conditional formatting Highlight Cells Rule that formats cells greater than 230,000 with Green Fill with Dark Green Text.

b. In the range C24:F24, apply the same conditional formatting to cells with totals greater than 250,000.

8. Next, Margarita asks you to complete the Bonuses Earned data in the range C12:H15. The amount eligible for a bonus depends on the quarterly revenue. The providers and staff reimburse the clinic $1250 per quarter for nonmedical services. The final bonus is 35 percent of the remaining amount.

a. Using the text in cell C12, fill the range D12:F12 with the names of the other three quarters.

b. In cell C13, enter a formula using an IF function that tests whether cell C9 is greater than 230,000. If it is, multiply cell C9 by 0.20 to calculate the 20 percent eligible amount. If cell C9 is not greater than 230,000, multiply cell C9 by 0.15 to calculate the 15 percent eligible amount.

c. Copy the formula in cell C13 to the range D13:F13 to calculate the other quarterly bonus amounts.

d. In cell C15, enter a formula without using a function that subtracts the Share amount (cell C14) from the Amount Eligible (cell C13) and then multiplies the result by the Bonus Percentage (cell C16). Use an absolute reference to cell C16.

e. Copy the formula in cell C15 to the range D15:F15 to calculate the bonuses for the other quarters.

 

Attachments:

Related Questions

. Introgramming & Unix Fall 2018, CRN 44882, Oakland University Homework Assignment 6 - Using Arrays and Functions in C

DescriptionIn this final assignment, the students will demonstrate their ability to apply two ma

. The standard path finding involves finding the (shortest) path from an origin to a destination, typically on a map. This is an

Path finding involves finding a path from A to B. Typically we want the path to have certain properties,such as being the shortest or to avoid going t

. Develop a program to emulate a purchase transaction at a retail store. This program will have two classes, a LineItem class and a Transaction class. The LineItem class will represent an individual

Develop a program to emulate a purchase transaction at a retail store. Thisprogram will have two classes, a LineItem class and a Transaction class. Th

. SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

1 Project 1 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of

. Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

1 Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of