Shelly      Cashman Excel 2019 | Modules 1-3: SAM Capstone Project 1a Ensight Healthcare Consultants CREATE FORMULAS WITH FUNCTIONS GETTING STARTED · Open the file SC_EX19_CS1-3a_ FirstLastName _1.xlsx , available for download from the SAM website. · Save the file as SC_EX19_CS1-3a_ FirstLastName _2.xlsx by changing the “1” to a “2”. o 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_CS1-3a_ FirstLastName _2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet. o 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. Unfreeze the first column since it does not display information that applies to the rest of the worksheet. 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. 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. 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. 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 . 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. 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. 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. 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…

Looking for solution of this Assignment?

WHY CHOOSE US?

We deliver quality original papers

Our experts write quality original papers using academic databases.  

Free revisions

We offer our clients multiple free revisions just to ensure you get what you want.

Discounted prices

All our prices are discounted which makes it affordable to you. Use code FIRST15 to get your discount

100% originality

We deliver papers that are written from scratch to deliver 100% originality. Our papers are free from plagiarism and NO similarity

On-time delivery

We will deliver your paper on time even on short notice or  short deadline, overnight essay or even an urgent essay