# WEEK 5: INDEPENDENT PROJECT: EXCEL 2019 IN PRACTICE – CH 6 INDEPENDENT PROJECT 6-6

Clemenson Imaging LLC monitors increased revenue from the use of CT scan equipment. You analyze the number of patients and procedures by technician and location. [Student Learning Outcomes 6.3, 6.4, 6.5, 6.6, 6.7] File Needed: ClemensonImaging-06.xlsx (Available from the Start file link.) Completed Project File Name: [your name]-ClemensonImaging-06.xlsx Skills Covered in This Project Calculate the net present value of a purchase. Use TRANSPOSE to rearrange labels into a column. Concatenate cells to display full names. Use SUMIFS to summarize data. Calculate procedure times. Format times with fractions. Open the ClemensonImaging-06 workbook and click the Enable Editing button. The file will be renamed automatically to include your name. Determine the net present value of a new equipment purchase. Click the Financials sheet tab and select cell H5 . Use NPV with a Rate argument of 4.25% . Select cells D7:D13 for the Value1 argument and click OK . This is the same as entering each value argument separately. Edit the formula to add both costs (cells D4 and D5 ) at the end of the formula. The net present value is \$268,921.79. Use TRANSPOSE and CONCAT to display technician names. Click the Technicians sheet tab. The names are in rows. Select cells A4:A10 , seven rows in one column. Select TRANSPOSE from the Lookup & Reference category and select cells A1:G1 for the Array argument. Press Ctrl + Shift + Enter to complete the array formula. Repeat the TRANSPOSE task for the first names in cells B4:B10 . Select cell D4 and create a CONCAT formula to display the name in first name, last name order (Figure 6-111). Figure 6-111 CONCAT formula to display names Copy the formula in cell D4 to cells D5:D10 . Click the Summary sheet tab, select cell A5 , and create a 3D reference to cell D4 on the Technicians sheet. Copy the formula and preserve the borders. Figure 6-112 SUMIFS with absolute and relative references Figure 6-113 Change time format to display fractions Use SUMIFS to total number of patients by procedure and technician. Click the Summary sheet tab and select cell C5 . Use the SUMIFS function with an absolute reference to cells \$D\$5:\$D\$41 on the Procedures sheet as the Sum_range argument. The Criteria_range1 argument is an absolute reference to the image type column on the Procedures sheet, cells \$E\$5:\$E\$41 . The Criteria1 argument is a relative reference to cell B5 on the Summary sheet. The Criteria_range2 argument is an absolute reference to the technician names column on the Procedures sheet. Select cell A5 for the Criteria2 argument (Figure 6-112). Copy the formula in cell C5 to cells C6:C11 and preserve the borders. Use SUMIFS to total number of patients by category and location in cells C14:C15 . Look for and correct format inconsistencies. Calculate procedure times. Click the Times sheet tab and select cell F6 . Build a formula to subtract the start time from the end time and multiply those results by 24. The result is shown in hours. Copy the formula to row 41 . Select cells F6:F41 and open the Format Cells dialog box. On the Number tab, choose Fraction with a Type of Up to two digits (Figure 6-113). Save and close the workbook (Figure 6-114). Upload and save your project file. Submit project for grading.

## 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