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.