Executive Solution Overview
[>Accounting homework help](https://www.sweetstudy.com/fields/business-finance-accounting) Please see the following **files (2)** – ## Week4HandsOn02-Instructions.doc – Week4-HandsOn01Instructions.doc **## Week4HandsOn02-Instructions.doc** Week 4 Hands On 02 Instructions **Exercise 4:** **Course Obj…
Instant Solution Download
$6.99Immediate access to the full model answer, formatted citations, and rubric breakdown.
Need a Fresh Custom Paper?
Have this question answered from scratch according to your specific instructions and deadline.
Order on TutorsGallery →Assignment Prompt & Instructions
[>Accounting homework help](https://www.sweetstudy.com/fields/business-finance-accounting)
Please see the following
**files (2)**
– ## Week4HandsOn02-Instructions.doc
– Week4-HandsOn01Instructions.doc
**## Week4HandsOn02-Instructions.doc**
Week 4 Hands On 02
Instructions
**Exercise 4:**
**Course Objectives:**
· CO8: Import and manipulate data from various sources, use templates and text functions, paste and transpose data, and utilize quick analysis tools for streamlined data handling and presentation.
**Estimated time to Complete:** 20 minutes
**Description:**
Student will learn about:
· Creating and using templates.
**Required Resources:**
· Access to Microsoft Excel.
**Deliverable:**
After completing the assignment, upload the spreadsheet to the Canvas Assignment.
**Setup:**
Access to Microsoft Excel.
**Lab Steps:**
**Creating and Using a Template**
1. Create a new spreadsheet with the following data:
| | | | | | | | | | |
| — | — | — | — | — | — | — | — | — | — |
| **Meyor Insurance**<br>**Sales Analysis** |
| **Branch** | **Category** | **2020 In-Office Premiums in $** | **2021 In-Office Premiums in $** | **2020 Online Premiums in $** | **2021 Online Premiums in $** | **Percentage Increase or Decrease** | | **Sum of Categories** | **2021 Totals** |
| | a | 1 | 2 | 3 | 4 | 50.00% | | a | 6 |
**Save the Template**
1. Click the File tab and click Save As to display the Save As screen.
2. Click the Browse button to display the Save As dialog box.
3. Type the desired file name in the File name box.
4. Click the “Save as type” arrow and then click Excel Template in the list to specify that this workbook should be saved as a template.
5. Navigate to the desired save location.
6. Click Save to save the template.
**Opening a Template-Based File and Save It as a Workbook**
1. With Excel active, click File Explorer button on the taskbar to start the File Explorer app.
2. Navigate to the location of the file to be opened.
3. Double-click the template file to open a new file based on the template.
4. Click the File tab and click Save As to display the Save As screen.
5. Click the Browse button to display the Save As dialog box.
6. Type the file name in the File name box and then navigate to your storage location.
Click Save to save the file
**Uploading completed worksheet**
1. Save the workbook to your local drive.
2. Upload the saved spreadsheet to Canvas.
**Part 5**
**Course Objectives:**
· CO8: Import and manipulate data from various sources, use templates and text functions, paste and transpose data, and utilize quick analysis tools for streamlined data handling and presentation.
**Estimated time to Complete:** 20 minutes
**Description:**
Student will learn about:
· Creating and using templates.
**Required Resources:**
· Access to Microsoft Excel.
**Deliverable:**
After completing the assignment, upload the spreadsheet to the Canvas module.
**Setup:**
Access to Microsoft Excel.
**Lab Steps:**
**Creating and Using a Template**
1. Create a tab delimited text file with the following data:
Branch Category 2020 In-Office Premiums in $ 2021 In-Office Premiums in $ 2020 Online Premiums in $ 2021 Online Premiums in $
82410 Auto 131654 136748 71165 72618
82410 Health 411298 473779 281852 287604
82410 Home 152500 160477 35680 36408
82410 Life 144100 147335 67556 68935
82410 Other 26137 27907 11271 11501
**Importing Data from a Text File**
1. Click the first cell on the worksheet to contain the imported data.
2. Click Data on the ribbon to display the Data tab.
3. Click the “From Text/CSV” button to display the Import Data dialog box.
4. If necessary, navigate to the location of the Data files to display the files.
5. Double-click the name of the file to display the preview window.
6. Click the Load arrow, then click Load To to display the Import Data dialog box.
7. Click the Exiting worksheet option button to place the data in the current worksheet rather than on a new sheet.
8. Click OK to import the data.
9. Capture a screen shot of the spreadsheet and upload it to Canvas.
**Using the Trim Function**
1. Select the desired cell, type =trim(cell#) and then click Enter button to trim the spaces from the data in the (cell) and display it in the desired cell.
**Pasting Values Only**
1. With the desired range selected press CTRL+C to copy the data.
2. Right-click one of the cells to display the shortcut menu.
3. In the Paste Options area, click the Values icon to paste only the values.
4. Click the save button on the Quick Access toolbar to save the file with the new data.
5. Capture a screen shot of the spreadsheet and upload it to Canvas.
**Deleting a Column Using Power Query**
1. In the Navigator dialog box, click the Transform Data button to display the Power Query Editor window.
2. Click the desired column heading to delete.
3. Click the Remove Columns button to remove the dollar value columns from the import.
4. Click the “Close & Load” arrow to display the “Close & Load” menu.
5. Click the “Close & Load To” command to close the Power Query Editor Window and to display the Import Data dialog box.
6. Click OK to import the data.
7. Capture a screen shot of the spreadsheet and upload it to Canvas.
**To Transpose Columns and Rows**
1. With the range to be copied selected, press CTRL+C to copy the selection to the Office Clipboard.
2. Click a cell to prepare for pasting data into that location.
3. Click the Paste arrow to display the Paste gallery.
4. Click the Transpose button in the Paste gallery to transpose and paste the copied cells.
5. Capture a screen shot of the spreadsheet and upload it to Canvas.
**Uploading completed worksheet**
1. Save the workbook to your local drive.
2. Upload the saved spreadsheet to Canvas.
**Part 6**
**Course Objectives:**
· CO8: Import and manipulate data from various sources, use templates and text functions, paste and transpose data, and utilize quick analysis tools for streamlined data handling and presentation.
**Estimated time to Complete:** 20 minutes
**Description:**
Students will learn about:
· Using the Quick Analysis gallery
· Finding and replacing data
**Required Resources:**
· Access to Microsoft Excel.
**Deliverable:**
After completing the assignment, upload the spreadsheet to the Canvas module.
**Setup:**
Access to Microsoft Excel.
**Lab Steps:**
**Formatting Using the Quick Analysis Gallery**
1. Select the 2020 In-Office Premiums range, then click the Quick Analysis button to display the Quick Analysis gallery.
2. If necessary, click the Formatting tab to display the Quick Analysis gallery formatting options.
3. Select the color formatting option.
4. Click a desired cell and type a legend for the formatting.
**Totaling Data**
1. Click the cell below the last entry for 2020 In-Office Premiums.
2. Type =sum(cell#:cell#) and then click the ENTER button.
3. Drag the fill handle to the right to replicate the totals for remaining columns
**Uploading completed worksheet**
1. Save the workbook to your local drive.
**Week4-HandsOn01Instructions.doc**
This file is too large to display. [View in new window](https://www.sweetstudy.com/files/week4-handson01instructions-doc)
**Week4-HandsOn01Instructions.doc**
This file is too large to display. [View in new window](https://www.sweetstudy.com/files/week4-handson01instructions-doc)
**## Week4HandsOn02-Instructions.doc**
Week 4 Hands On 02
Instructions
**Exercise 4:**
**Course Objectives:**
· CO8: Import and manipulate data from various sources, use templates and text functions, paste and transpose data, and utilize quick analysis tools for streamlined data handling and presentation.
**Estimated time to Complete:** 20 minutes
**Description:**
Student will learn about:
· Creating and using templates.
**Required Resources:**
· Access to Microsoft Excel.
**Deliverable:**
After completing the assignment, upload the spreadsheet to the Canvas Assignment.
**Setup:**
Access to Microsoft Excel.
**Lab Steps:**
**Creating and Using a Template**
1. Create a new spreadsheet with the following data:
| | | | | | | | | | |
| — | — | — | — | — | — | — | — | — | — |
| **Meyor Insurance**<br>**Sales Analysis** |
| **Branch** | **Category** | **2020 In-Office Premiums in $** | **2021 In-Office Premiums in $** | **2020 Online Premiums in $** | **2021 Online Premiums in $** | **Percentage Increase or Decrease** | | **Sum of Categories** | **2021 Totals** |
| | a | 1 | 2 | 3 | 4 | 50.00% | | a | 6 |
**Save the Template**
1. Click the File tab and click Save As to display the Save As screen.
2. Click the Browse button to display the Save As dialog box.
3. Type the desired file name in the File name box.
4. Click the “Save as type” arrow and then click Excel Template in the list to specify that this workbook should be saved as a template.
5. Navigate to the desired save location.
6. Click Save to save the template.
**Opening a Template-Based File and Save It as a Workbook**
1. With Excel active, click File Explorer button on the taskbar to start the File Explorer app.
2. Navigate to the location of the file to be opened.
3. Double-click the template file to open a new file based on the template.
4. Click the File tab and click Save As to display the Save As screen.
5. Click the Browse button to display the Save As dialog box.
6. Type the file name in the File name box and then navigate to your storage location.
Click Save to save the file
**Uploading completed worksheet**
1. Save the workbook to your local drive.
2. Upload the saved spreadsheet to Canvas.
**Part 5**
**Course Objectives:**
· CO8: Import and manipulate data from various sources, use templates and text functions, paste and transpose data, and utilize quick analysis tools for streamlined data handling and presentation.
**Estimated time to Complete:** 20 minutes
**Description:**
Student will learn about:
· Creating and using templates.
**Required Resources:**
· Access to Microsoft Excel.
**Deliverable:**
After completing the assignment, upload the spreadsheet to the Canvas module.
**Setup:**
Access to Microsoft Excel.
**Lab Steps:**
**Creating and Using a Template**
1. Create a tab delimited text file with the following data:
Branch Category 2020 In-Office Premiums in $ 2021 In-Office Premiums in $ 2020 Online Premiums in $ 2021 Online Premiums in $
82410 Auto 131654 136748 71165 72618
82410 Health 411298 473779 281852 287604
82410 Home 152500 160477 35680 36408
82410 Life 144100 147335 67556 68935
82410 Other 26137 27907 11271 11501
**Importing Data from a Text File**
1. Click the first cell on the worksheet to contain the imported data.
2. Click Data on the ribbon to display the Data tab.
3. Click the “From Text/CSV” button to display the Import Data dialog box.
4. If necessary, navigate to the location of the Data files to display the files.
5. Double-click the name of the file to display the preview window.
6. Click the Load arrow, then click Load To to display the Import Data dialog box.
7. Click the Exiting worksheet option button to place the data in the current worksheet rather than on a new sheet.
8. Click OK to import the data.
9. Capture a screen shot of the spreadsheet and upload it to Canvas.
**Using the Trim Function**
1. Select the desired cell, type =trim(cell#) and then click Enter button to trim the spaces from the data in the (cell) and display it in the desired cell.
**Pasting Values Only**
1. With the desired range selected press CTRL+C to copy the data.
2. Right-click one of the cells to display the shortcut menu.
3. In the Paste Options area, click the Values icon to paste only the values.
4. Click the save button on the Quick Access toolbar to save the file with the new data.
5. Capture a screen shot of the spreadsheet and upload it to Canvas.
**Deleting a Column Using Power Query**
1. In the Navigator dialog box, click the Transform Data button to display the Power Query Editor window.
2. Click the desired column heading to delete.
3. Click the Remove Columns button to remove the dollar value columns from the import.
4. Click the “Close & Load” arrow to display the “Close & Load” menu.
5. Click the “Close & Load To” command to close the Power Query Editor Window and to display the Import Data dialog box.
6. Click OK to import the data.
7. Capture a screen shot of the spreadsheet and upload it to Canvas.
**To Transpose Columns and Rows**
1. With the range to be copied selected, press CTRL+C to copy the selection to the Office Clipboard.
2. Click a cell to prepare for pasting data into that location.
3. Click the Paste arrow to display the Paste gallery.
4. Click the Transpose button in the Paste gallery to transpose and paste the copied cells.
5. Capture a screen shot of the spreadsheet and upload it to Canvas.
**Uploading completed worksheet**
1. Save the workbook to your local drive.
2. Upload the saved spreadsheet to Canvas.
**Part 6**
**Course Objectives:**
· CO8: Import and manipulate data from various sources, use templates and text functions, paste and transpose data, and utilize quick analysis tools for streamlined data handling and presentation.
**Estimated time to Complete:** 20 minutes
**Description:**
Students will learn about:
· Using the Quick Analysis gallery
· Finding and replacing data
**Required Resources:**
· Access to Microsoft Excel.
**Deliverable:**
After completing the assignment, upload the spreadsheet to the Canvas module.
**Setup:**
Access to Microsoft Excel.
**Lab Steps:**
**Formatting Using the Quick Analysis Gallery**
1. Select the 2020 In-Office Premiums range, then click the Quick Analysis button to display the Quick Analysis gallery.
2. If necessary, click the Formatting tab to display the Quick Analysis gallery formatting options.
3. Select the color formatting option.
4. Click a desired cell and type a legend for the formatting.
**Totaling Data**
1. Click the cell below the last entry for 2020 In-Office Premiums.
2. Type =sum(cell#:cell#) and then click the ENTER button.
3. Drag the fill handle to the right to replicate the totals for remaining columns
**Uploading completed worksheet**
1. Save the workbook to your local drive.
**Week4-HandsOn01Instructions.doc**
This file is too large to display. [View in new window](https://www.sweetstudy.com/files/week4-handson01instructions-doc)
**## Week4HandsOn02-Instructions.doc**
Week 4 Hands On 02
Instructions
**Exercise 4:**
**Course Objectives:**
· CO8: Import and manipulate data from various sources, use templates and text functions, paste and transpose data, and utilize quick analysis tools for streamlined data handling and presentation.
**Estimated time to Complete:** 20 minutes
**Description:**
Student will learn about:
· Creating and using templates.
**Required Resources:**
· Access to Microsoft Excel.
**Deliverable:**
After completing the assignment, upload the spreadsheet to the Canvas Assignment.
**Setup:**
Access to Microsoft Excel.
**Lab Steps:**
**Creating and Using a Template**
1. Create a new spreadsheet with the following data:
| | | | | | | | | | |
| — | — | — | — | — | — | — | — | — | — |
| **Meyor Insurance**<br>**Sales Analysis** |
| **Branch** | **Category** | **2020 In-Office Premiums in $** | **2021 In-Office Premiums in $** | **2020 Online Premiums in $** | **2021 Online Premiums in $** | **Percentage Increase or Decrease** | | **Sum of Categories** | **2021 Totals** |
| | a | 1 | 2 | 3 | 4 | 50.00% | | a | 6 |
**Save the Template**
1. Click the File tab and click Save As to display the Save As screen.
2. Click the Browse button to display the Save As dialog box.
3. Type the desired file name in the File name box.
4. Click the “Save as type” arrow and then click Excel Template in the list to specify that this workbook should be saved as a template.
5. Navigate to the desired save location.
6. Click Save to save the template.
**Opening a Template-Based File and Save It as a Workbook**
1. With Excel active, click File Explorer button on the taskbar to start the File Explorer app.
2. Navigate to the location of the file to be opened.
3. Double-click the template file to open a new file based on the template.
4. Click the File tab and click Save As to display the Save As screen.
5. Click the Browse button to display the Save As dialog box.
6. Type the file name in the File name box and then navigate to your storage location.
Click Save to save the file
**Uploading completed worksheet**
1. Save the workbook to your local drive.
2. Upload the saved spreadsheet to Canvas.
**Part 5**
**Course Objectives:**
· CO8: Import and manipulate data from various sources, use templates and text functions, paste and transpose data, and utilize quick analysis tools for streamlined data handling and presentation.
**Estimated time to Complete:** 20 minutes
**Description:**
Student will learn about:
· Creating and using templates.
**Required Resources:**
· Access to Microsoft Excel.
**Deliverable:**
After completing the assignment, upload the spreadsheet to the Canvas module.
**Setup:**
Access to Microsoft Excel.
**Lab Steps:**
**Creating and Using a Template**
1. Create a tab delimited text file with the following data:
Branch Category 2020 In-Office Premiums in $ 2021 In-Office Premiums in $ 2020 Online Premiums in $ 2021 Online Premiums in $
82410 Auto 131654 136748 71165 72618
82410 Health 411298 473779 281852 287604
82410 Home 152500 160477 35680 36408
82410 Life 144100 147335 67556 68935
82410 Other 26137 27907 11271 11501
**Importing Data from a Text File**
1. Click the first cell on the worksheet to contain the imported data.
2. Click Data on the ribbon to display the Data tab.
3. Click the “From Text/CSV” button to display the Import Data dialog box.
4. If necessary, navigate to the location of the Data files to display the files.
5. Double-click the name of the file to display the preview window.
6. Click the Load arrow, then click Load To to display the Import Data dialog box.
7. Click the Exiting worksheet option button to place the data in the current worksheet rather than on a new sheet.
8. Click OK to import the data.
9. Capture a screen shot of the spreadsheet and upload it to Canvas.
**Using the Trim Function**
1. Select the desired cell, type =trim(cell#) and then click Enter button to trim the spaces from the data in the (cell) and display it in the desired cell.
**Pasting Values Only**
1. With the desired range selected press CTRL+C to copy the data.
2. Right-click one of the cells to display the shortcut menu.
3. In the Paste Options area, click the Values icon to paste only the values.
4. Click the save button on the Quick Access toolbar to save the file with the new data.
5. Capture a screen shot of the spreadsheet and upload it to Canvas.
**Deleting a Column Using Power Query**
1. In the Navigator dialog box, click the Transform Data button to display the Power Query Editor window.
2. Click the desired column heading to delete.
3. Click the Remove Columns button to remove the dollar value columns from the import.
4. Click the “Close & Load” arrow to display the “Close & Load” menu.
5. Click the “Close & Load To” command to close the Power Query Editor Window and to display the Import Data dialog box.
6. Click OK to import the data.
7. Capture a screen shot of the spreadsheet and upload it to Canvas.
**To Transpose Columns and Rows**
1. With the range to be copied selected, press CTRL+C to copy the selection to the Office Clipboard.
2. Click a cell to prepare for pasting data into that location.
3. Click the Paste arrow to display the Paste gallery.
4. Click the Transpose button in the Paste gallery to transpose and paste the copied cells.
5. Capture a screen shot of the spreadsheet and upload it to Canvas.
**Uploading completed worksheet**
1. Save the workbook to your local drive.
2. Upload the saved spreadsheet to Canvas.
**Part 6**
**Course Objectives:**
· CO8: Import and manipulate data from various sources, use templates and text functions, paste and transpose data, and utilize quick analysis tools for streamlined data handling and presentation.
**Estimated time to Complete:** 20 minutes
**Description:**
Students will learn about:
· Using the Quick Analysis gallery
· Finding and replacing data
**Required Resources:**
· Access to Microsoft Excel.
**Deliverable:**
After completing the assignment, upload the spreadsheet to the Canvas module.
**Setup:**
Access to Microsoft Excel.
**Lab Steps:**
**Formatting Using the Quick Analysis Gallery**
1. Select the 2020 In-Office Premiums range, then click the Quick Analysis button to display the Quick Analysis gallery.
2. If necessary, click the Formatting tab to display the Quick Analysis gallery formatting options.
3. Select the color formatting option.
4. Click a desired cell and type a legend for the formatting.
**Totaling Data**
1. Click the cell below the last entry for 2020 In-Office Premiums.
2. Type =sum(cell#:cell#) and then click the ENTER button.
3. Drag the fill handle to the right to replicate the totals for remaining columns
**Uploading completed worksheet**
1. Save the workbook to your local drive.
**Week4-HandsOn01Instructions.doc**
This file is too large to display. [View in new window](https://www.sweetstudy.com/files/week4-handson01instructions-doc)
Leave a Reply