Excel 2019 In Practice Guided Project Worksheet

User Generated

nyvfvqqvdv0007

Business Finance

Description

Unformatted Attachment Preview

11/2/23, 5:50 PM Excel 2019 In Practice - Ch 4 Guided Project 4-3 - SIMnet Print Info Student Name: Siddiqi, Ali Student ID: ali.siddiqi@tulsacc.edu Username: ali.siddiqi@tulsacc.edu Excel 2019 In Practice - Ch 4 Guided Project 4-3 COURSE NAME CSCI 1203 Glenda Seiter | F23 CSCI 1203 937 12181 Glenda Seiter Guided Project 4-3 Clemenson Imaging Analyzes Expense Reports From Field Representatives As Well As Patient And Image Data. To Complete The Worksheets, You Format Data As A Table And Build An Advanced Filter. You Import A Comma-Separated Text File (.Csv) And Use The Subtotal Command. Finally, You Display Data In A PivotTable. [Student Learning Outcomes 4.1, 4.2, 4.3, 4.4, 4.5, 4.6, 4.7, 4.8] File Needed: Clemenson-04.xlsx (Available from the Start File link.), ClemensonText-04.csv and ClemensonWord04.docx (Available from the Resources link.) Completed Project File Names: [your name]-Clemenson-04.xlsx and [your initials] Excel 4-3Word.docx Skills Covered in This Project Format data as an Excel table. Build an Advanced Filter. Apply conditional formatting to filtered results. Sort data by multiple columns. Import a comma-separated text file. Use the Subtotal command. Export data via the Clipboard. Create a PivotTable. This image appears when a project instruction has changed to accommodate an update to Microsoft 365 Apps. If the instruction does not match your version of Office, try using the alternate instruction instead. https://tulsacc.simnetonline.com/sp/assignments/projects/details/8248555 1/5 11/2/23, 5:50 PM Excel 2019 In Practice - Ch 4 Guided Project 4-3 - SIMnet IMPORTANT: If you are a Canadian user, be sure to verify that your browser and Microsoft Office use the same country settings. See here for a Help topic on how to change your settings. Steps to complete This Project Mark the steps as checked when you complete them. 1. Open the Clemenson-04.xlsx start file. Click the Enable Editing button. The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor. NOTE: If group titles are not visible on your Ribbon in Excel for Mac, click the Excel menu and select Preferences to open the Excel Preferences dialog box. Click the View button and check the Group Titles check box under In Ribbon, Show. Close the Excel Preferences dialog box. 2. Copy the Past&Projected sheet, place the copy before the Criteria sheet, and name the copied sheet Adv Filter. 3. Create and format an Excel table. a. Select cells A4:E60 on the Adv Filter sheet. b. Click the Table button [Insert tab, Tables group]. c. In the Create Table window, verify that My table has headers is selected and click OK. 4. Apply a table style. a. Click cell A5 in the table. b. Click the More arrow [Table tab, Table Styles group]. c. Select White, Table Style Medium 15. 5. Create an output range for an Advanced Filter. a. Select cells A4:E4 and copy and paste them to cell G4. b. Type Extract Range in cell G3 and set the font to Cambria 16 pt. c. Adjust column widths for columns G:K to show the labels. d. Adjust row 4 to a height of 33.75 (45 pixels). 6. Create an Advanced Filter. a. Click the Criteria sheet tab. b. Type >12/31/18 in cell A3 to find records after 2018. c. Type mri in cell B3. d. Type >12/31/19 in cell A4, to find records after 2019. e. f. Type ct scan in cell B4. This criteria will find records dated 2019 or later for MRIs and records for CT scans dated 2020 or later (Figure 4-94). Click cell A5 on the Adv Filter sheet. g. Click the Advanced button [Data tab, Sort & Filter group]. h. Select the Copy to another location radio button. i. Verify that the List range is cells $A$4:$E$60. If the range is incorrect, click and drag to select the range including the header row. j. Click the Criteria range box and select cells A2:B4 on the Criteria sheet. k. Click the Copy to box and select cells G4:K4 on the Adv Filter sheet. l. Click OK in the Advanced Filter dialog box. Figure 4-94 Criteria for Advanced Filter 7. Sort data in the output range. a. Select cell G5 and click the Sort button [Data tab, Sort & Filter group]. b. Click the Sort by arrow and choose Image for the first level. c. Verify that the Sort On selection is Values and the Order is A to Z. d. Click Add Level (the + button) and choose Month as the Then by selection. e. Verify or select Values and Oldest to Newest for the second level. f. Click OK. 8. Apply conditional formatting and borders. a. Select the cells with values in column I. b. Click the Conditional Formatting button [Home tab, Styles group]. c. Choose Highlight Cells Rules and Greater Than. d. Type 750 and choose Green Fill with Dark Green Text. https://tulsacc.simnetonline.com/sp/assignments/projects/details/8248555 2/5 11/2/23, 5:50 PM Excel 2019 In Practice - Ch 4 Guided Project 4-3 - SIMnet e. f. g. Click OK. Select the extract range starting at cell G5 and apply All Borders (Figure 4-95). (Your results may be different from Figure 4-95 depending on the current date.) Press FN+command+Left arrow or FN+Ctrl+Left arrow. 9. Import a comma-separated values text file. a. Click the Expense Info sheet tab. b. Select cell A4. c. Click the From Text button [Data tab, Get External Data group]. d. Find and select the ClemensonText-04.csv file downloaded from the Resources link in the Import Data window. Figure 4-95 Advanced Filter results e. Click Get Data. f. Verify that Delimited is selected in the Text Import Wizard. g. Click Next. h. Uncheck Tab if necessary, and check Comma as the Delimiter. i. Click the Finish button. j. Verify that Existing sheet is selected in the Import Data window. k. Verify that cell A4 displays as the destination in the Import Data window l. Click OK to import the text data (Figure 4-96). m. Insert a row above row 3 in the worksheet. n. Select the labels in row 4 and center align them. o. Format the dates in column C with the Short Date format. p. Select columns A:D and size each column to 12.14 (90 pixels) wide. Deselect the columns. 10. Use the Subtotal command. a. Click cell A5. b. Click the Sort A to Z button [Data tab, Sort & Filter group] to sort by last name. c. Click the Subtotal button [Data tab, Outline group]. d. Verify or choose Last Name for the At each change in box. e. Click the Use function arrow and choose Average. f. Verify or choose the Amount box in the Add subtotal to area. g. Click OK. h. Format the values in column D as Currency with zero decimal places. Figure 4-96 CSV file imported into the worksheet 11. Collapse outline groups. a. Click the collapse symbol (−) for Allen in row 8. b. Click the collapse symbol (−) for McAllister (Figure 4-97). 12. Create a PivotTable. a. Click the Past&Projected sheet tab. b. Select cells A4:E60. c. Click the PivotTable button [Insert tab, Tables group]. Verify that New worksheet is selected and click OK. d. Drag the Image field to Rows in the PivotTable Builder. e. Drag the # of Employees, the Total Hours, and the Patients fields to Values in the PivotTable Builder (Figure 498). Figure 4-97 Subtotals added and outline collapsed for two groups https://tulsacc.simnetonline.com/sp/assignments/projects/details/8248555 3/5 11/2/23, 5:50 PM Excel 2019 In Practice - Ch 4 Guided Project 4-3 - SIMnet Figure 4-98 PivotTable Builder f. Rename the sheet PivotTable. g. Close the PivotTable Builder. 13. Format a PivotTable. a. Click cell C4. b. Click the Field Settings button [PivotTable Analyze tab, Active Field group]. c. Click Number... d. Choose Number as the Category. e. Select the Use 1000 Separator (,) box and set zero decimal places. f. Click OK to close each dialog box. g. Apply the same number format for the “Sum of Patients” field. h. Expand the Styles gallery [PivotTable Design tab, PivotTable Styles group]. i. Select Dark Gray, Pivot Style Dark 9 [PivotTable Design tab, PivotTable Styles group]. j. Select the Banded Rows box [PivotTable Design tab, PivotTable Styles Options group]. k. Select the Banded Columns box [PivotTable Design tab, PivotTable Styles Options group] (Figure 4-99). 14. Create and format a PivotChart. a. Click the PivotChart button [PivotTable Analyze tab, Tools group] to insert a PivotChart. b. Right-click the chart object and point to Change Chart Type, then Column. c. Scroll down to 2-D Bar section in the list, and click Stacked Bar. d. Figure 4-99 PivotTable with new settings Position the chart object so that its top-left corner is at cell A12. e. Drag the bottom-right selection handle to reach cell J30. f. Expand the Styles gallery [PivotChart Design tab, Chart Styles group] and choose Style 8. g. Click the Change Colors button [PivotChart Design tab, Chart Styles group]. h. Select Monochromatic Palette 1 in the Monochromatic group. i. Deselect the # of Employees box in the PivotChart Fields pane (Figure 4-100). Figure 4-100 PivotChart object https://tulsacc.simnetonline.com/sp/assignments/projects/details/8248555 4/5 11/2/23, 5:50 PM Excel 2019 In Practice - Ch 4 Guided Project 4-3 - SIMnet j. Click cell A1. 15. Export data using the Clipboard. a. Click the Adv Filter sheet tab. b. Starting at cell G4, select the cells in the Extract Range and click the Copy button [Home tab, Clipboard group]. c. Open the ClemensonWord-04 Word document downloaded from the Resources link. d. Press fn+Cmd+right arrow or fn+Ctrl+right arrow to position the insertion point. e. Click the arrow with the Paste button [Home tab, Clipboard group] and choose Paste Special. f. Select Microsoft Excel Worksheet Object in the Paste Special dialog box. g. h. i. Select the Paste link radio button in the Paste Special dialog box and click OK. (The Excel data in your document may be different from the figure.) Click the Center button [Home tab, Paragraph group]. Save the Word document as [your initials] Excel 4-3Word in your usual folder (Figure 4-101). You will not be uploading this file to SIMnet. Figure 4-101 Word document with pasted Excel object j. Close Word. 16. Save and close the Clemenson-04 Excel workbook. 17. Upload and save the Clemenson-04 Excel workbook. 18. Submit project for grading. https://tulsacc.simnetonline.com/sp/assignments/projects/details/8248555 5/5 Clemenson Imaging Patient Count and Hours Tracking Past and Projected Values, 2017 –2021 Month Image Jan 2017 MRI Feb 2017 Ultrasonography Mar 2017 MRI Apr 2017 CT Scan May 2017 MRI Jun 2017 Ultrasonography Jul 2017 Angiography Aug 2017 CT Scan Sep 2017 MRI Oct 2017 Ultrasonography Nov 2017 Angiography Dec 2017 CT Scan Jan 2018 MRI Feb 2018 Ultrasonography Mar 2018 Angiography Apr 2018 CT Scan May 2018 MRI Jun 2018 Ultrasonography Jul 2018 Angiography Aug 2018 CT Scan Sep 2018 MRI Oct 2018 Ultrasonography Nov 2018 Angiography Dec 2018 CT Scan Jan 2019 MRI Feb 2019 Ultrasonography Mar 2019 Angiography Apr 2019 CT Scan May 2019 MRI Jun 2019 Ultrasonography Jul 2019 Angiography Aug 2019 CT Scan Sep 2019 MRI Oct 2019 Ultrasonography Nov 2019 Angiography Dec 2019 CT Scan Patients 500 750 1000 500 750 1000 1000 750 500 1000 750 500 1000 1000 500 750 750 750 500 1000 750 750 1000 750 500 1000 1000 1000 750 750 500 500 750 1000 1000 500 Total Hours 1800 2400 3000 1800 2400 3000 3000 1800 2400 2400 2400 2400 1800 3000 3000 1800 2400 3000 3000 1800 1800 2400 2400 2400 2400 1800 1800 1800 3000 3000 3000 2400 1800 2400 2400 2400 # of Employees 4 5 3 4 3 4 2 2 2 5 4 3 2 3 4 2 2 3 3 4 5 5 3 4 3 4 3 5 5 2 3 3 4 4 5 2 Jan 2020 MRI Feb 2020 Ultrasonography Mar 2020 Angiography Apr 2020 CT Scan May 2020 MRI Jun 2020 Ultrasonography Jul 2020 Angiography Aug 2020 CT Scan Sep 2020 MRI Oct 2020 Ultrasonography Nov 2020 Angiography Dec 2020 CT Scan Jan 2021 MRI Feb 2021 Ultrasonography Mar 2021 Angiography Apr 2021 CT Scan May 2021 MRI Jun 2021 Ultrasonography Jul 2021 Angiography Aug 2021 CT Scan 750 750 1000 500 750 750 1000 750 750 750 1000 500 1000 1000 1000 750 750 1000 1000 500 3000 3000 3000 2400 2400 2400 3000 2400 2400 3000 3000 1800 1800 1800 2400 2400 3000 3000 3000 2400 3 4 3 3 2 3 4 3 4 4 3 4 3 2 3 2 4 2 3 3 Criteria Range Month Image Patients Total Hours # of Employees Clemenson Imaging Expense Report Information Last Name First Name Date Amount ClemensonText-04 Allen, Mary Jo, 1/1/20 19,450 Gabrys, Eli zabeth, 1/1 /2019,555 Bashir, Ate f, 1/1/2019 ,635 Gomez, Paol a, 1/1/2019 ,845 McAllister, Lex, 1/1/2 19,925 Allen, Mary Jo, 2/1/20 19,750 Gabrys, Eli zabeth, 2/1 /2019,635 Bashir, Ate f, 2/1/2019 ,575 Gomez, Paol a, 2/1/2019 ,435 McAllister, Lex, 2/1/2 19,650 Allen, Mary Jo, 3/1/20 19,875 Gabrys, Eli zabeth, 3/1 /2019,765 Bashir, Ate f, 3/1/2019 ,655 Gomez, Paol a, 3/1/2019 ,535 McAllister, Lex, 3/1/2 Gabrys, Eli zabeth, 4/1 /2019,765 Bashir, Ate f, 4/1/2019 19,485 ,655 Gomez, Paol a, 4/1/2019 ,535 McAllister, Lex, 4/1/2 Gabrys, Eli zabeth, 5/1 /2019,765 Bashir, Ate f, 5/1/2019 19,485 ,655 Gomez, Paol a, 5/1/2019 ,535 McAllister, Lex, 5/1/2 Gabrys, Eli zabeth, 6/1 /2019,765 Bashir, Ate f, 6/1/2019 19,485 ,655 Gomez, Paol a, 6/1/2019 ,535 McAllister, Lex, 6/1/2 19,485 1 ClemensonText-04 Allen, Mary Jo, 1/1/20 19.450 Gabrys, Eli zabeth, 1/1 /2019,555 Bashir, Ate f, 1/1/2019 ,635 Gomez, Paol a, 1/1/2019 ,845 McAllister, Lex, 1/1/2 19.925 Allen, Mary Jo, 2/1/20 19.750 Gabrys, Eli zabeth, 2/1 /2019,635 Bashir, Ate f, 2/1/2019 ,575 Gomez, Paol a, 2/1/2019 ,435 McAllister, Lex, 2/1/2 19.650 Allen, Mary Jo, 3/1/20 19.875 Gabrys, Eli zabeth, 3/1 /2019,765 Bashir, Ate f, 3/1/2019 ,655 Gomez, Paol a, 3/1/2019 ,535 McAllister, Lex, 3/1/2 19.485 Gabrys, Eli zabeth, 4/1 /2019,765 Bashir, Ate f, 4/1/2019 ,655 Gomez, Paol a, 4/1/2019 ,535 McAllister, Lex, 4/1/2 19.485 Gabrys, Eli zabeth, 5/1 /2019,765 Bashir, Ate f, 5/1/2019 ,655 Gomez, Paol a, 5/1/2019 ,535 McAllister, Lex, 5/1/2 19.485 Gabrys, Eli zabeth, 6/1 /2019,765 Bashir, Ate f, 6/1/2019 ,655 Gomez, Paol a, 6/1/2019 ,535 McAllister, Lex, 6/1/2 19.485 1 Mobile App Development Business Plan Thesis statement: The objective of this business plan is to provide a comprehensive overview of a mobile app development company, including a mission statement, company objectives and goals, industry description, major company values and core competencies, legal form of ownership, products and services. I. Executive Summary II. General Company Description III. Mission Statement IV. Company Objectives and Goals V. Trade Philosophy VI. Industry Description VII. Most Important Company Qualities and Core Competencies VIII. Legal Form of Ownership IX. Products and Services X. Features and Benefits XI. Customers XII. Competition XIII. Niche XIV. Strategy XV. Promotion XVI. Promotional Budget XVII. Pricing XVIII. Proposed Location XIX. Distribution Channels XX. Sales Forecast XXI. Operational Plan XXII. Physical Requirements XXIII. Organizational Chart XXIV. Professional and Advisory Support XXV. Conclusion and Recommendation Clemenson Imaging Patient Count and Hours Tracking Past and Projected Values, 2017 –2021 Month Image Jan 2017 MRI Feb 2017 Ultrasonography Mar 2017 MRI Apr 2017 CT Scan May 2017 MRI Jun 2017 Ultrasonography Jul 2017 Angiography Aug 2017 CT Scan Sep 2017 MRI Oct 2017 Ultrasonography Nov 2017 Angiography Dec 2017 CT Scan Jan 2018 MRI Feb 2018 Ultrasonography Mar 2018 Angiography Apr 2018 CT Scan May 2018 MRI Jun 2018 Ultrasonography Jul 2018 Angiography Aug 2018 CT Scan Sep 2018 MRI Oct 2018 Ultrasonography Nov 2018 Angiography Dec 2018 CT Scan Jan 2019 MRI Feb 2019 Ultrasonography Mar 2019 Angiography Apr 2019 CT Scan May 2019 MRI Jun 2019 Ultrasonography Jul 2019 Angiography Aug 2019 CT Scan Sep 2019 MRI Oct 2019 Ultrasonography Nov 2019 Angiography Dec 2019 CT Scan Patients 500 750 1000 500 750 1000 1000 750 500 1000 750 500 1000 1000 500 750 750 750 500 1000 750 750 1000 750 500 1000 1000 1000 750 750 500 500 750 1000 1000 500 Total Hours 1800 2400 3000 1800 2400 3000 3000 1800 2400 2400 2400 2400 1800 3000 3000 1800 2400 3000 3000 1800 1800 2400 2400 2400 2400 1800 1800 1800 3000 3000 3000 2400 1800 2400 2400 2400 # of Employees 4 5 3 4 3 4 2 2 2 5 4 3 2 3 4 2 2 3 3 4 5 5 3 4 3 4 3 5 5 2 3 3 4 4 5 2 Jan 2020 MRI Feb 2020 Ultrasonography Mar 2020 Angiography Apr 2020 CT Scan May 2020 MRI Jun 2020 Ultrasonography Jul 2020 Angiography Aug 2020 CT Scan Sep 2020 MRI Oct 2020 Ultrasonography Nov 2020 Angiography Dec 2020 CT Scan Jan 2021 MRI Feb 2021 Ultrasonography Mar 2021 Angiography Apr 2021 CT Scan May 2021 MRI Jun 2021 Ultrasonography Jul 2021 Angiography Aug 2021 CT Scan 750 750 1000 500 750 750 1000 750 750 750 1000 500 1000 1000 1000 750 750 1000 1000 500 3000 3000 3000 2400 2400 2400 3000 2400 2400 3000 3000 1800 1800 1800 2400 2400 3000 3000 3000 2400 3 4 3 3 2 3 4 3 4 4 3 4 3 2 3 2 4 2 3 3 Criteria Range Month Image Patients Total Hours # of Employees Clemenson Imaging Expense Report Information Last Name First Name Date Amount
Purchase answer to see full attachment
Explanation & Answer:
1 Worksheet
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

Attached.
Attached.

Row Labels
Sum of Total Hours Sum of Patients
Angiography
35.400
11.250
CT Scan
30.000
9.250
MRI
35.400
11.250
Ultrasonography
36.600
12.250
Grand Total
137.400
44.000

Sum of Total HoursSum of Patients

Ultrasonography

MRI

Values
Image
CT Scan

Angiography

0

10.000

20.000

30.000

40.000

50.000

60.000

Values
Sum of Total Hours
Sum of Patients

Clemenson Imaging
Patient Count and Hours Tracking
Past and Projected Values, 2017 –2021
Month
Image
Jan 2017 MRI
Feb 2017 Ultrasonography
Mar 2017 MRI
Apr 2017 CT Scan
May 2017 MRI
Jun 2017 Ultrasonography
Jul 2017 Angiography
Aug 2017 CT Scan
Sep 2017 MRI
Oct 2017 Ultrasonography
Nov 2017 Angiography
Dec 2017 CT Scan
Jan 2018 MRI
Feb 2018 Ultrasonography
Mar 2018 Angiography
Apr 2018 CT Scan
May 2018 MRI
Jun 2018 Ultrasonography
Jul 2018 Angiography
Aug 2018 CT Scan
Sep 2018 MRI
Oct 2018 Ultrasonography
Nov 2018 Angiography
Dec 2018 CT Scan
Jan 2019 MRI
Feb 2019 Ultrasonography
Mar 2019 Angiography
Apr 2019 CT Scan
May 2019 MRI
Jun 2019 Ultrasonography
Jul 2019 Angiography
Aug 2019 ...


Anonymous
Great! Studypool always delivers quality work.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Similar Content

Related Tags