Excel project

Finish excel project until satiafactory of instructions.

Clemenson Imaging
Second Quarter Mobile Image Procedures
Technician
Donna McFarland
MaryAnne Vonbank
Jonathan Douglas
Samantha Woods
Annie Olander
Patti Lynfield
James Boyd
Donna McFarland
MaryAnne Vonbank
Annie Olander
Patti Lynfield
James Boyd
Donna McFarland
MaryAnne Vonbank
MaryAnne Vonbank
Jonathan Douglas
Samantha Woods
Annie Olander
Patti Lynfield
James Boyd
Jonathan Douglas
Samantha Woods
Annie Olander
Patti Lynfield
MaryAnne Vonbank
Donna McFarland
James Boyd
Jonathan Douglas
Annie Olander
Patti Lynfield
MaryAnne Vonbank
Donna McFarland
James Boyd
Jonathan Douglas
MaryAnne Vonbank
Patti Lynfield
Location
Green Bay
Manitowoc
Appleton
Green Bay
Green Bay
Manitowoc
Manitowoc
Appleton
Appleton
Green Bay
Appleton
Manitowoc
Manitowoc
Appleton
Appleton
Green Bay
Appleton
Green Bay
Manitowoc
Manitowoc
Appleton
Appleton
Green Bay
Manitowoc
Appleton
Appleton
Green Bay
Appleton
Green Bay
Manitowoc
Appleton
Green Bay
Manitowoc
Appleton
Green Bay
Manitowoc
Date
Patients
Image Type
4/4/2020
4 Dexascan
4/8/2020
5 CT Scan
4/10/2020
1 MRI
4/20/2020
6 Ultrasonography
4/22/2020
5 Angiography
4/27/2020
3 Dexascan
4/30/2020
4 MRI
5/2/2020
1 MRI
5/5/2020
3 Angiography
5/8/2020
2 Angiography
5/10/2020
6 Ultrasonography
5/12/2020
2 Ultrasonography
5/14/2020
3 MRI
5/16/2020
12 Dexascan
5/18/2020
5 Dexascan
5/20/2020
3 CT Scan
5/22/2020
1 CT Scan
5/24/2020
4 MRI
5/26/2020
4 MRI
5/28/2020
8 CT Scan
5/30/2020
2 CT Scan
6/1/2020
3 MRI
6/3/2020
1 MRI
6/5/2020
6 Ultrasonography
6/7/2020
1 MRI
6/9/2020
7 Dexascan
6/11/2020
7 Dexascan
6/13/2020
8 CT Scan
6/15/2020
3 CT Scan
6/17/2020
2 Angiography
6/19/2020
1 Angiography
6/21/2020
8 Ultrasonography
6/23/2020
4 Ultrasonography
6/25/2020
3 Angiography
6/27/2020
1 MRI
6/29/2020
3 MRI
Category
Scheduled
Walk-in
Walk-in
Walk-in
Scheduled
Scheduled
Scheduled
Scheduled
Walk-in
Scheduled
Walk-in
Scheduled
Scheduled
Scheduled
Walk-in
Walk-in
Scheduled
Scheduled
Walk-in
Walk-in
Walk-in
Scheduled
Scheduled
Scheduled
Scheduled
Walk-in
Scheduled
Walk-in
Scheduled
Scheduled
Scheduled
Walk-in
Walk-in
Scheduled
Scheduled
Scheduled
Clemenson Imaging
Second Quarter Summary
Technician
Procedure
# of Patients
MRI
CT Scan
Angiography
Angiography
MRI
MRI
CT Scan
Patient Category
Location
Scheduled
Appleton
Walk-In
Green Bay
# of Patients
Boyd
James
Douglas
Jonathan
Lynfield
Patti
McFarland Olander
Donna
Annie
Vonbank Woods
MaryAnne Samantha
Clemenson Imaging, LLC
Purchase and Training Cost Analysis
Cost of CT Scan Equipment
Cost of Staff Training
-$1,500,000
-$35,000
Additional Revenue Year 1
Additional Revenue Year 2
Additional Revenue Year 3
Additional Revenue Year 4
Additional Revenue Year 5
Additional Revenue Year 6
Additional Revenue Year 7
$150,000
$150,000
$250,000
$300,000
$350,000
$500,000
$500,000
Net Present Value
Clemenson Imaging
Time Duration for Procedures in Hours
Patient ID
Location
CL024
Green Bay
CL027
Manitowoc
CL030
Appleton
CL033
Green Bay
CL036
Green Bay
CL039
Manitowoc
CL042
Manitowoc
CL045
Appleton
CL048
Appleton
CL051
Green Bay
CL054
Appleton
CL057
Manitowoc
CL060
Manitowoc
CL063
Appleton
CL066
Appleton
CL069
Green Bay
CL072
Appleton
CL075
Green Bay
CL078
Manitowoc
CL081
Manitowoc
CL084
Appleton
CL087
Appleton
CL090
Green Bay
CL093
Manitowoc
CL096
Appleton
CL099
Appleton
CL102
Green Bay
CL105
Appleton
CL108
Green Bay
CL111
Manitowoc
CL114
Appleton
CL117
Green Bay
CL120
Manitowoc
CL123
Appleton
CL126
Green Bay
CL129
Manitowoc
Image Type
Dexascan
CT Scan
MRI
Ultrasonography
Angiography
Dexascan
MRI
MRI
Angiography
Angiography
Ultrasonography
Ultrasonography
MRI
Dexascan
Dexascan
CT Scan
CT Scan
MRI
MRI
CT Scan
CT Scan
MRI
MRI
Ultrasonography
MRI
Dexascan
Dexascan
CT Scan
CT Scan
Angiography
Angiography
Ultrasonography
Ultrasonography
Angiography
MRI
MRI
Start
9:15 AM
1:00 PM
10:15 AM
9:00 AM
2:30 PM
10:30
2:45 PM
8:00 AM
1:30 PM
3:00 PM
1:00 PM
11:30 AM
10:15 AM
10:30
11:00 AM
2:15 PM
3:30 PM
8:00 AM
9:00 AM
4:00 PM
8:00 AM
12:00 PM
2:15 PM
1:00 PM
10:15 AM
11:00 AM
8:00 AM
2:00 PM
3:00 PM
4:00 PM
3:45 PM
11:00 AM
12:00 PM
4:00 PM
10:15 AM
9:00 AM
End
Duration
9:30 AM
1:45 PM
11:30 AM
10:00 AM
4:15 PM
11:00 AM
4:15 PM
10:30 AM
3:45 PM
4:45 PM
1:45 PM
1:45 PM
11:30 AM
11:00 AM
11:20 AM
3:30 PM
5:00 PM
11:00 AM
11:30 AM
5:30 PM
9:45 AM
2:30 PM
4:45 PM
1:45 PM
11:30 AM
11:20 AM
8:20 AM
3:30 PM
3:45 PM
4:45 PM
5:00 PM
11:20 AM
1:30 PM
4:45 PM
11:30 AM
11:15 AM
5/31/24, 1:04 PM
Excel 6 – Project 2 – SIMnet
Print Info
Student Name: Brown, Kamen
Student ID: kbrow540
Username: kbrow540
Excel 6 – Project 2
COURSE NAME CISM 2530 – SUMMER A 2024- Dyer | Summer A 2024
Class ends at 06/15/2024 11:59 PM
Start Date:05/06/202412:01 AMUS/Eastern
Due Date:06/02/202411:59 PMUS/Eastern
End Date:06/04/202411:59 PMUS/Eastern
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.
Steps to complete This Project
Mark the steps as checked when you complete them.
1.
Open the ClemensonImaging-06 workbook and click the Enable Editing button. The file will be renamed automatically
to include your name.
2. Determine the net present value of a new equipment purchase.
a.
Click the Financials sheet tab and select cell H5.
b.
Use NPV with a Rate argument of 4.25%.
c.
Select cells D7:D13 for the Value1 argument and click OK. This is the same as entering each value argument
separately.
d.
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.
3. Use TRANSPOSE and CONCAT to display technician names.
a.
Click the Technicians sheet tab. The names are in rows.
b.
Select cells A4:A10, seven rows in one column.
c.
Select TRANSPOSE from the Lookup & Reference category and select cells A1:G1 for the Array argument.
d.
Press Ctrl+Shift+Enter to complete the array formula.
e.
Repeat the TRANSPOSE task for the first names in cells B4:B10.
f.
Select cell D4 and create a CONCAT formula to display the name in first name, last name order (Figure 6-111).
https://georgiasouthern.desire2learn.com/d2l/le/content/766537/viewContent/12260824/View
1/3
5/31/24, 1:04 PM
Excel 6 – Project 2 – SIMnet
Figure 6-111 CONCAT formula to display names
g.
Copy the formula in cell D4 to cells D5:D10.
h.
Click the Summary sheet tab, select cell A5, and create a 3D reference to cell D4 on the Technicians sheet.
i.
Copy the formula and preserve the borders.
4. Use SUMIFS to total number of patients by procedure and technician.
a.
Click the Summary sheet tab and select cell C5.
b.
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.
c.
Select cell A5 for the Criteria2 argument (Figure 6-112).
d.
Copy the formula in cell C5 to cells C6:C11 and
preserve the borders.
5.
6.
Use SUMIFS to total number of patients by category and
location in cells C14:C15.
Look for and correct format inconsistencies.
7. Calculate procedure times.
a.
b.
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
Figure 6-112 SUMIFS with absolute and relative references
in hours.
c.
Copy the formula to row 41.
d.
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).
8.
Save and close the workbook (Figure 6-114).
Figure 6-113 Change time format to display fractions
https://georgiasouthern.desire2learn.com/d2l/le/content/766537/viewContent/12260824/View
2/3
5/31/24, 1:04 PM
Excel 6 – Project 2 – SIMnet
Figure 6-114 Completed worksheets for Excel 6-6
9.
Upload and save your project file.
10.
Submit project for grading.
https://georgiasouthern.desire2learn.com/d2l/le/content/766537/viewContent/12260824/View
3/3