# Computer Science

## Graded Summary Report

Shelly Cashman Excel 2019 | Modules 1-3: SAM Capstone Project 1a | ||

Nikita Thapaliya | ||

SUBMISSION #1 | SCORE IS: 13 OUT OF 20 | GE ver. 7.63 | |

1. | Carla Arranga is a senior account manager at Ensight Healthcare Consultants, a consulting firm that works with hospitals, clinics, and other healthcare providers around the world. Carla has created a workbook summarizing the status of the consulting project for Everett Hospital. She asks for your help in completing the workbook. Go to the Project Status worksheet. Unfreeze the first column since it does not display information that applies to the rest of the worksheet. | 0/0 |

Unfreeze columns. | ||

2. | In cell J1, enter a formula using the NOW function to display today’s date. Apply the Short Date number format to display only the date in the cell. | 1/1 |

Create a formula using a function. | ||

3. | Format the worksheet title as follows to use a consistent design throughout the workbook: a. Fill cell B2 with the Dark Red, Accent 6, Lighter 40% shading color. b. Change the font color to White, Background 1. c. Merge and center the contents of cell B2 across the range B2:H2. d. Use AutoFit to resize row 2 to its best fit. | 1/1 |

Change the fill color of a cell. | ||

Change the font color. | ||

Merge and center a range. | ||

Change the row height using AutoFit. | ||

4. | Format the billing rate data as follows to suit the design of the worksheet and make the data easier to understand: a. Italicize the contents of cell I2 to match the formatting in cell I1. b. Apply the Currency number format to cell J2 to clarify that it contains a dollar amount. | 1/1 |

Change the font style. | ||

Change the number format. | ||

5. | Format the data in cell A4 as follows to display all of the text: a. Merge the cells in the range A4:A13. b. Rotate the text up in the merged cell so that the text reads from bottom to top. c. Middle-align and center the text. d. Remove the border from the merged cell. e. Resize column A to a width of 4.00. | 1/1 |

Change the orientation of merged cell content. | ||

Align cell content horizontally. | ||

Align cell content vertically. | ||

Remove the border from the merged cell. | ||

Change the column width. | ||

Merge a range. | ||

6. | Format the data in row 4 as follows to show that it contains column headings: a. Change “Description” to use Service Description as the complete column heading. b. Apply the Accent 6 cell style to the range B4:H4. c. Use AutoFit to resize column D to its best fit. | 1/1 |

Update a value in a cell. | ||

Apply a cell style. | ||

Change the column width using AutoFit. | ||

7. | Carla wants to include the actual dollar amount of the services performed in column E. Enter this information as follows: a. In cell E5, enter a formula without using a function that multiplies the actual hours (cell D5) by the billing rate (cell J2) to determine the actual dollar amount charged for general administrative services. Include an absolute reference to cell J2 in the formula. b. Use the Fill Handle to fill the range E6:E13 with the formula in cell E5 to include the charges for the other services. c. Format the range E6:E13 using the Comma number format and no decimal places to match the formatting in column F. | 1/1 |

Create a formula without using a function. | ||

Copy a formula into a range. | ||

Apply a cell style. | ||

8. | Carla needs to show how much of the estimate remains after the services performed. Provide this information as follows: a. In cell G5, enter a formula without using a function that subtracts the actual dollars billed (cell E5) from the estimated amount (cell F5) to determine the remaining amount of the estimate for general administrative services. b. Use the Fill Handle to fill the range G6:G13 with the formula in cell G5 to include the remaining amount for the other services. c. Format the range G6:G13 using the Comma number format and no decimal places to match the formatting in column F. | 0/1 |

Create a formula without using a function. | ||

Copy a formula into a range. | ||

Apply a cell style. | ||

In the Project Status worksheet, the range G6:G13 should be formatted using the Comma cell style with zero decimal places. | ||

9. | Carla also wants to show the remaining amount as a percentage of the actual amount. Enter this information as follows: a. In cell H5, enter a formula that divides the remaining dollar amount (cell G5) by the estimated dollar amount (cell F5). b. Copy the formula in cell H5 to the range H6:H14, pasting only the formula and number formatting to display the remaining amount as a percentage of the actual amount for the other services and the total. | 0/1 |

Create a formula using arithmetic operators. | ||

In the Project Status worksheet, the function in cell H5 should divide the value in cell G5 by the value in cell F5. | ||

Copy a formula into a range. | ||

In the Project Status worksheet, cell H5 contains an incorrect formula. | ||

10. | Calculate the project status totals as follows: a. In cell D14, enter a formula using the SUM function to total the actual hours (range D5:D13). b. Use the Fill Handle to fill the range E14:G14 with the formula in cell D14. c. Apply the Accounting number format with no decimal places to the range E14:G14. | 1/1 |

Create a formula using a function. | ||

Copy a formula into a range. | ||

Change the number format. | ||

11. | Carla also wants to identify the services for which Ensight has billed more than the full estimate amount. In the range H5:H13, use Conditional Formatting Highlight Cells Rules to format values less than 1% (0.01) in Light Red Fill with Dark Red Text. | 1/1 |

Format a conditional formatting highlight cells rule. | ||

Apply a conditional formatting rule. | ||

12. | Carla imported data about the consultants working on the Everett Hospital project and stored the data on a separate worksheet, but wants to include the data in the Project Status worksheet. Copy and paste the data as follows: a. Go to the Consultants worksheet and copy the data in the range B2:G12. b. Return to the Project Status worksheet. Paste the data in cell J3, keeping the source formatting when you paste it. | 0/1 |

Copy and paste formatting. | ||

In the Project Status worksheet, the formatting from the range B2:G12 in the Consultants worksheet should be copied to the range J3:O13. | ||

Copy and paste values in a range. | ||

13. | Carla needs to list the role for each consultant. Those with four or more years of experience take the Lead role. Otherwise, they take the Associate role. List this information as follows: a. In cell N5 on the Project Status worksheet, enter a formula that uses the IF function to test whether the number of years of experience (cell M5) is greater than or equal to 4. b. If the consultant has four or more years of experience, display “Lead” in cell N5. c. If the consultant has less than four years of experience, display “Associate” in cell N5. d. Copy the formula in cell N5 to the range N6:N13, pasting the formula only. e. Use AutoFit to resize column N to its best fit. | 1/1 |

Create a formula using a function. | ||

Change the column width using AutoFit. | ||

Copy a formula into a range. | ||

14. | Carla wants to include summary statistics about the project and the consultants. Include this information as follows: In cell D16, enter a formula that uses the AVERAGE function to average the number of years of experience (range M5:M13). | 0/1 |

Create a formula using a function. | ||

In the Project Status worksheet, the formula in cell D17 should contain the AVERAGE function. | ||

15. | Make the 3-D Clustered Column chart in the range B17:H31 easier to interpret as follows: a. Change the chart type to a Clustered Bar chart. b. Use Actual Project Hours as the chart title. c. Add a primary horizontal axis title to the chart, using Hours as the axis title text. d. Add data labels in the center of each bar. | 1/1 |

Change the chart type. | ||

Change a chart title. | ||

Add an axis title to a chart. | ||

Change the position of the data labels. | ||

16. | Delete row 33 since Carla has reformatted the clustered column chart. | 1/1 |

Delete a row. | ||

17. | Go to the Schedule worksheet. Rename the Schedule worksheet tab to Project Schedule to use a more descriptive name. | 0/1 |

Change the name of a worksheet. | ||

The Schedule worksheet should be renamed “Project Schedule”. | ||

18. | Each service starts on a different date because the services depend on each other. Enter the starting dates for the remaining services as follows: a. In cell D6, enter a formula without using a function that adds 4 days to the value in cell C6. b. In cell E6, enter a formula without using a function that subtracts 3 days from the value in cell C6. c. In cell F6, enter a formula without using a function that adds 2 days to the value in cell E6. d. In cell G6, enter a formula without using a function that adds 2 days to the value in cell C6. | 0/1 |

Create a formula without using a function. | ||

Create a formula without using a function. | ||

Create a formula without using a function. | ||

In the Project Schedule worksheet, cell F6 contains an incorrect formula. | ||

Create a formula without using a function. | ||

19. | Copy the formulas in Phase 2 to the rest of the schedule as follows: a. Copy the formula in cell D6 to the range D7:D9. b. Copy the formula in cell E6 to the range E7:E9. c. Copy the formula in cell F6 to the range F7:F9. d. Copy the formula in cell G6 to the range G7:G9. | 0/1 |

Copy a formula into a range. | ||

In the Project Schedule worksheet, one or more cells in the range D7:D9 contains an incorrect formula. | ||

Copy a formula into a range. | ||

In the Project Schedule worksheet, one or more cells in the range E7:E9 contains an incorrect formula. | ||

Copy a formula into a range. | ||

In the Project Schedule worksheet, cell F6 contains an incorrect formula. | ||

Copy a formula into a range. | ||

In the Project Schedule worksheet, one or more cells in the range G7:G9 contains an incorrect formula. | ||

20. | In cell C11, enter a formula that uses the MIN function to find the earliest date in the project schedule (range C6:G9). | 1/1 |

Create a formula using a function. | ||

21. | In cell C12, enter a formula that uses the MAX function to find the latest date in the project schedule (range C6:G9). | 1/1 |

Create a formula using a function. |

## Documentation

Shelly Cashman Excel 2019 | Modules 1- 3: SAM Capstone Project 1a | |

Ensight Healthcare Consultants | |

CREATE FORMULAS WITH FUNCTIONS | |

Author: | Nikita Thapaliya |

Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the SAM website. |

## Project Status

Grading Engine: Grading Error: Step 8: In the Project Status worksheet, the range G6:G13 should be formatted using the Comma cell style with zero decimal places. Step 9: In the Project Status worksheet, the function in cell H5 should divide the value in cell G5 by the value in cell F5. Step 9: In the Project Status worksheet, cell H5 contains an incorrect formula. Step 12: In the Project Status worksheet, the formatting from the range B2:G12 in the Consultants worksheet should be copied to the range J3:O13. Step 14: In the Project Status worksheet, the formula in cell D17 should contain the AVERAGE function. | Date: | 4/7/20 | ||||||||||||

Professional Therapies System Upgrade | Billing rate: | $85.00 | ||||||||||||

Project Status Report | Consultants | |||||||||||||

Everett Hospital | Service ID | Service Description | Actual Hours | Actual $ | Estimated $ | Remaining $ | Remaining % | Name | Services | Manager | Years | Role | Eval Score | Level |

G-131 | General administrative | 25 | $ 2,125 | $ 2,500 | $ 375 | 15% | Kirk Deegan | F-125 | Cisneros | 4.5 | Lead | 88 | 2 | |

G-240 | HIPAA compliance | 100 | 8,500 | 8,000 | (500) | -6% | Rhonda Meilani | P-314 | Ottinger | 6.0 | Lead | 91 | 1 | |

P-255 | Therapy practice evaluation | 40 | 3,400 | 6,800 | 3,400 | 50% | Sherry Quam | P-255 | Ottinger | 3.0 | Associate | 80 | 2 | |

P-314 | Strategic planning | 35 | 2,975 | 5,500 | 2,525 | 46% | Joe Shepard | G-131 | Hetzel | 2.7 | Associate | 79 | 3 | |

P-400 | New services development | 84 | 7,140 | 7,400 | 260 | 4% | Maya Gupta | P-245 | Ottinger | 3.4 | Associate | 92 | 1 | |

P-245 | Therapy analysis | 40 | 3,400 | 7,401 | 4,001 | 54% | Jacob Fay | F-130 | Cisneros | 3.8 | Associate | 83 | 2 | |

F-125 | New revenue generation | 125 | 10,625 | 10,000 | (625) | -6% | Rosalie Dorne | H-305 | Mika | 5.1 | Lead | 75 | 3 | |

F-130 | Expense reduction | 95 | 8,075 | 10,000 | 1,925 | 19% | Andre Patanka | G-240 | Hetzel | 4.8 | Lead | 90 | 1 | |

H-305 | Medical staff development | 40 | 3,400 | 10,200 | 6,800 | 67% | Cam Wells | P-400 | Ottinger | 5.2 | Lead | 85 | 2 | |

Total | 584 | $ 49,640 | $ 67,801 | $ 18,161 | 242% | |||||||||

Services performed | 9 | |||||||||||||

Average years of experience | 4.3 |

Actual Project Hours

Actual Hours

General administrative HIPAA compliance Therapy practice evaluation Strategic planning New services development Therapy analysis New revenue generation Expense reduction Medical staff development 25 100 40 35 84 40 125 95 40

Hours

## Project Schedule

Grading Engine: Grading Error: Step 18: In the Project Schedule worksheet, cell F6 contains an incorrect formula. | |||||||||||

Professional Therapies System Upgrade | |||||||||||

Project Schedule for Remaining Services | |||||||||||

G-131 | P-400 | F-125 | F-130 | H-305 | |||||||

Workdays | 10 | 12 | 5 | 14 | 15 | ||||||

Phase 2 | 5/17/21 | 5/21/21 | 5/14/21 | 5/19/21 | 5/19/21 | ||||||

Phase 3 | 6/1/21 | 6/9/21 Grading Engine: Grading Error: Step 19: In the Project Schedule worksheet, one or more cells in the range D7:D9 contains an incorrect formula. |
5/21/21 Grading Engine: Grading Error: Step 19: In the Project Schedule worksheet, one or more cells in the range E7:E9 contains an incorrect formula. |
6/9/21 Grading Engine: Grading Error: Step 19: In the Project Schedule worksheet, cell F6 contains an incorrect formula. |
6/10/21 Grading Engine: Grading Error: Step 19: In the Project Schedule worksheet, one or more cells in the range G7:G9 contains an incorrect formula. |
||||||

Phase 4 | 6/15/21 | 6/25/21 Grading Engine: Grading Error: Step 19: In the Project Schedule worksheet, one or more cells in the range D7:D9 contains an incorrect formula. |
5/28/21 Grading Engine: Grading Error: Step 19: In the Project Schedule worksheet, one or more cells in the range E7:E9 contains an incorrect formula. |
6/29/21 Grading Engine: Grading Error: Step 19: In the Project Schedule worksheet, cell F6 contains an incorrect formula. |
7/1/21 Grading Engine: Grading Error: Step 19: In the Project Schedule worksheet, one or more cells in the range G7:G9 contains an incorrect formula. |
||||||

Phase 5 | 6/29/21 | 7/13/21 Grading Engine: Grading Error: Step 19: In the Project Schedule worksheet, one or more cells in the range D7:D9 contains an incorrect formula. |
Grading Engine: Grading Error: Step 19: In the Project Schedule worksheet, one or more cells in the range E7:E9 contains an incorrect formula. | Grading Engine: Grading Error: Step 19: In the Project Schedule worksheet, one or more cells in the range E7:E9 contains an incorrect formula. | 6/7/21 Grading Engine: Grading Error: Step 19: In the Project Schedule worksheet, one or more cells in the range E7:E9 contains an incorrect formula. |
Grading Engine: Grading Error: Step 19: In the Project Schedule worksheet, cell F6 contains an incorrect formula. | Grading Engine: Grading Error: Step 19: In the Project Schedule worksheet, cell F6 contains an incorrect formula. | 7/19/21 Grading Engine: Grading Error: Step 19: In the Project Schedule worksheet, cell F6 contains an incorrect formula. |
Grading Engine: Grading Error: Step 19: In the Project Schedule worksheet, one or more cells in the range G7:G9 contains an incorrect formula. | Grading Engine: Grading Error: Step 19: In the Project Schedule worksheet, one or more cells in the range G7:G9 contains an incorrect formula. | 7/22/21 Grading Engine: Grading Error: Step 19: In the Project Schedule worksheet, one or more cells in the range G7:G9 contains an incorrect formula. |

Start date | 5/14/21 | ||||||||||

Completion date | 7/22/21 | ||||||||||

Holidays: | 5/31/21 | ||||||||||

7/4/21 |