Homework answers / question archive / Shelly Cashman Excel 2019 | Modules 1-3: SAM Critical Thinking Capstone Project 1c Ensight Healthcare Consultants CREATE FORMULAS WITH FUNCTIONS   GETTING STARTED Open the file SC_EX19_CT_CS1-3c_FirstLastName_1

Shelly Cashman Excel 2019 | Modules 1-3: SAM Critical Thinking Capstone Project 1c Ensight Healthcare Consultants CREATE FORMULAS WITH FUNCTIONS   GETTING STARTED Open the file SC_EX19_CT_CS1-3c_FirstLastName_1

MS Excel

Share With

Shelly Cashman Excel 2019 | Modules 1-3: SAM Critical Thinking Capstone Project 1c

Ensight Healthcare Consultants

CREATE FORMULAS WITH FUNCTIONS

• GETTING STARTED

• Open the file SC_EX19_CT_CS1-3c_FirstLastName_1.xlsx, available for download from the SAM website.
• Save the file as SC_EX19_CT_CS1-3c_FirstLastName_2.xlsx by changing the “1” to a “2”.
  • If you do not see the .xlsx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
• With the file SC_EX19_CT_CS1-3c_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.
  • If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

• PROJECT STEPS

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. The data is incomplete and the formatting needs to be finished. Unfreeze the first column since it does not display information that applies to the rest of the worksheet.
2. Enter a function to the right of the cell marked "Date:" to display the current date and time, then use the Short Date number format to display only the date in the cell.
3. The worksheet title should use a consistent design throughout the workbook.
   Change the fill color and the font color of the cell containing the text "Professional Therapies System Upgrade" to match the cell below. Merge and center the contents through the end of the table data. Use AutoFit to resize the row to its best fit.
4. Format the billing rate data to suit the design of the worksheet and make the data easier to understand.
   Italicize the label "Billing Rate" to match the formatting in the cell labeled "Date:". Then apply the Currency number format to the rate to display it as a dollar amount.
5. The text in the first column needs to be displayed in a better way. Format the data in the first column as follows:
   Merge the cells from the text "Everett Hospital" down to the cell next to the last value in the Service ID column. Rotate the text up in the merged cell so that the text reads from bottom to top. Middle-align and center the text. Remove the border from the merged cell, and then resize the column width to 4.00.
6. Format the row of data under the cell containing "Project Status Report" to show that it contains column headings.
   Change "Description" to Service Description as the complete column heading. Apply the Accent 6 cell style to the row of headings, and then resize the Actual Hours column to its best fit.
7. Carla wants to include the actual dollar amount of the services performed in the Actual $ column.
   Starting with the actual dollar amount for General administrative services, enter a formula without using a function that multiplies the actual hours by the billing rate to determine the actual dollar amount charged for general administrative services. Be sure that the formula always references the billing rate. Copy the formula down to fill the remaining amounts, and then match the number formats to the Estimated $ column.
8. Carla needs to show how much of the estimate remains after the services are performed.
   Starting with General administrative services, enter a formula without using a function that subtracts the actual dollars billed from the estimated amount to determine the remaining amount of the estimate. Copy the formula down to fill the remaining amounts, and then match the number formats to the Estimated $ column.
9. Carla also wants to show the remaining amount as a percentage of the actual amount.
   Starting with General administrative services, enter a formula without using a function that divides the remaining dollar amount by the estimated dollar amount. Copy the formula down to fill the remaining percentages. Copy the formula (but not the formatting) to the total row as well.
10. Now, Carla needs to calculate the project status totals.
    In the appropriate cell, use a function to total the actual hours. Copy the formula across to fill the rest of the totals, and then apply the Accounting number format with no decimals.
11. Carla also wants to identify the services for which Ensight has billed more than the full estimate amount.
    Use Conditional Formatting to highlight remaining percentages with values less than 1% using the default option.
12. Carla imported data about the consultants working on the Everett Hospital project and stored the data on a separate worksheet, but she wants to include the data in the Project Status worksheet. Go to the Consultants worksheet and copy the entire table of consultants. Paste the data beginning in the cell under the billing rate in the Project Status worksheet, keeping the source formatting.
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.
    Starting with Kirk Deegan, use a function to determine the consultant's role, displaying "Lead" or "Associate" as appropriate. Copy the formula down to fill the remaining roles, pasting only the formula. Resize the Role column to its best fit.
14. Carla wants to include summary statistics about the project and the consultants.
    In the appropriate cell under the Project Status Report table, use a function to determine the average the number of years of experience of all consultants.
15. The 3-D clustered column chart is not the clearest format to use to show the actual hours. Reformat the chart to make it easier to interpret.
    Change the chart type to a Clustered Bar chart. Use Actual Project Hours as the chart title. Add a primary horizontal axis title to the chart, using Hours as the axis title text. Add data labels in the center of each bar.
16. Delete row 33 since Carla has reformatted the clustered column chart.
17. Go to the Schedule worksheet. Rename the Schedule worksheet tab Project Schedule to use a more descriptive name.
18. Each service starts on a different date because the services depend on each other.
    Starting with Phase 2, enter a formula without using a function that adds 4 days to the G-131 service date. Next, use a similar formula that subtracts 3 days from the G-131 service date. For the next date, use a similar formula that adds 2 days to the F-125 service date. Finally, use a similar formula that adds 2 days to the G-131 service date.
19. Copy the formulas in Phase 2 down to fill the rest of the schedule.
20. Next to the cell labeled "Start date", use a function to find the earliest of the project schedule dates.
21. In the appropriate cell, use a function to find the latest of the project schedule dates.

Your workbook should look like the Final Figures on the following pages. The value in cell J1 has been intentionally blurred as it will never be constant. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project.