Homework answers / question archive / New Perspectives Excel 2019 | Module 1: SAM Critical Thinking Project 1c Tabula Insurance Agency ENTER AND UPDATE COMPANY DATA GETTING STARTED Open the file NP_EX19_CT1c_FirstLastName_1

New Perspectives Excel 2019 | Module 1: SAM Critical Thinking Project 1c Tabula Insurance Agency ENTER AND UPDATE COMPANY DATA GETTING STARTED Open the file NP_EX19_CT1c_FirstLastName_1

MS Excel

Share With

New Perspectives Excel 2019 | Module 1: SAM Critical Thinking Project 1c

Tabula Insurance Agency

ENTER AND UPDATE COMPANY DATA

GETTING STARTED

• Open the file NP_EX19_CT1c_FirstLastName_1.xlsx, available for download from the SAM website.
• Save the file as NP_EX19_CT1c_FirstLastName_2.xlsx by changing the “1” to a “2”.
  • If you do not see the .xlsx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
• With the file NP_EX19_CT1c_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.
  • If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

• PROJECT STEPS

1. As the office administrator, you use Excel to maintain personnel data and summarize cash flow. You are finalizing the Personnel and Cash Flow worksheets for the current week.
   The Personnel worksheet needs cleaning up. Start by moving the two cells of title information one column to the left. Resize the column to best fit the newly included information.
2. Change the width of the column containing salaries to match the width of the column containing commissions.
3. The salary and commission columns need to be completed. Enter the data shown in Table 1 in the appropriate cells.

4. The emails need to be filled in as well. Start by entering albert.luna@cengage.com in the corresponding cell. Fill the remaining emails using Flash Fill.
5. The column containing emails could use a bit more space at the sides. Change the width to 36.00.
6. The employee information table also needs show the total salary and total commission. Start by entering the word Total under the final employee name.
7. Use a function to calculate the total salaries in the appropriate cell. Use the same function to calculate the total commissions.
8. To finish up the worksheet, it should also show the total number of employees, as well as the average commission. Start by entering Number of Employees in the top left shaded cell below the employee information table, and Average Commission in the bottom left shaded cell.
9. Use a function to count the number of employees in the top right space to complete the worksheet.
10. The Cash Flow worksheet also needs to be completed. Begin by changing its orientation to Landscape.
11. Add Cash Flow: before the date to mirror the title information on the Personnel worksheet.
12. The font for the table headers and the note should be larger. Increase the font size of each to 12 points.
13. To better display the content, decrease the height of row 3 to 8.00.
14. Determine the profit generated from auto insurance policies by subtracting auto expenses from auto sales. Use a similar formula to show the profit for home and life insurance policies.
15. After determining the total profits generated by each store, you are now interested in calculating the total sales, expenses, and profits across all types of insurance sold. Use a function to calculate the totals for these categories across all three types of insurance.
16. Since you just edited the worksheet, cell A9 is no longer accurate. Remove the text.
17. Wrap the text of the remaining note to display multiple lines in a single cell.
18. Change the Zoom level of the Cash Flow worksheet to 130%.
19. After reviewing the Personnel and Cash Flow worksheets, you typically generate some additional data for accounting. To create a placeholder worksheet for this data, insert a new worksheet in the workbook, rename the worksheet Accounting, and if necessary, move the new worksheet after the Cash Flow worksheet.

Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project.