New Perspectives Excel 365 | Module 1: SAM Project B Evergreen Technology
ENTER AND UPDATE COMPANY DATA
· Excel Projects Help
GETTING STARTED
1. Save the file NP_EX365_1B_FirstLastName_1.xlsx as NP_EX365_1B_FirstLastName_2.xlsx
a. Edit the file name by changing “1” to “2”.
b. If you do not see the .xlsx file extension, do not type it. The file extension will be added for you automatically.
2. With the file NP_EX365_1B_FirstLastName_2.xlsx open, ensure that your first and last name is displayed in cell B6 of the Documentation worksheet.
a. If cell B6 does not display your name, delete the file and download a new copy.
PROJECT STEPS
1. Kayla Friedrich is a sales manager at Evergreen Technology, a company that sells solar panels. She uses Excel to maintain data about the sales made by the company's sales agents. She asks you as her financial assistant to finalize the Sales and Expenses worksheets for the week. Begin on the Sales worksheet by cutting the contents of the range D1:D3 and pasting them into the range A1:A3.
2. Adjust the width of column A using AutoFit.
3. In cell A3, enter the text Week of May 25, 2029 to note the week.
4. Change the width of column B to 15.00 to narrow the column.
5. Enter the values shown in Table 1 into the corresponding cells in the range D15:D17.
Table 1: Data for the Range D15-D17
| D 15 | 30,300 16 | 21,500 17 | 35,700
6. Enter robin.merritt@cengage.com in cell E6. Select the range E6:E17, and then use the Flash Fill command to automatically enter email addresses into the remaining cells in the range.
7. Change the width of column E to 35.00 to display the full text of each email address in the cell.
8. Enter the word Total in cell B18 to identify the results of the calculations you are about to make.
9. Kayla needs you to determine the total amount of sales made by each sales agent. In cell C18, create a formula using the SUM function to total the sales amounts (the range C6:C17). Copy the formula you created in cell C18 to cell D18 to total the expenses.
10. Kayla also wants you to complete the summary statistics at the bottom of the worksheet. Enter the text Average Expenses in cell A22 to identify the data in cell B22.
11. In cell B20, create a formula using the COUNTA function to determine the number of sales agents employed by the company by counting the sales agent names (the range A6:A17).
12. Add an Outside Border to the range B20:B22 to set the cells apart from other cells in the worksheet.
13. Switch to the Expenses worksheet. Kayla wants to make this worksheet easier to read and print. Change the orientation to Landscape.
14. Change the Zoom level to 130% to better read the worksheet contents.
15. In cell A2, edit the text so that it reads Expenses: Week of May 25, 2029 to include the correct date.
16. Select the column headings (the range B5:F5) and the cell A10, and then increase the font size of the cells to 12 point to make the text more prominent.
17. Kayla also needs you to complete the calculations on the worksheet. In cell B8, enter a formula without using a function to determine the net expenses in the North region by subtracting the Commissions (cell B7) from the Expenses amount (cell B6). Copy the formula you created in cell B8 to the range C8:E8 to display the income for the other regions.
18. Next, calculate the total expenses, commissions, and net expenses for all four regions. Select the range F6:F8, and then apply AutoSum to calculate the totals.
19. Apply the Wrap Text formatting to cell A10 to fit the contents within the cell borders.
20. Adjust the height of row 10 using AutoFit to display all the text in cell A10.
21. In cell B11, enter 5/25/2029 as the forecast date of the last update.
22. Since you just calculated the income and totals, you no longer need the brown tip instructions in cell D11. Delete the contents of cell D11.
23. In preparation for tracking new sales data, insert a new worksheet in the workbook, rename the worksheet Agents, and if necessary, move the new worksheet after the Expenses 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 website to submit your completed project.
Final Figure 1: Sales Worksheet
Windows, Access, Excel, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.
Final Figure 2: Expenses Worksheet
Windows, Access, Excel, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.
Final Figure 3: Agents Worksheet
Windows, Access, Excel, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.