microsoft excell assign

Tutorial 1: SAM Project 1a

Spring Software

CREATE AN EXPENSE REPORT

Project Goal

M Project Name

Project Goal

PROJECT DESCRIPTION

Brendan Stevenson is an administrative assistant with Spring Software, a software development company in Cambridge, Massachusetts. He has been asked to track monthly expenses for each of the company’s salespeople, so he has begun work on a workbook summarizing monthly expenses for the sales team. His workbook contains two worksheets: Expenses Detail, which lists and categorizes all expenses, and Expenses by Salesperson, which summarizes the expense information. Brendan would like your help adding information to the workbook.

GETTING STARTED

· Download the following file from the SAM website:

· NP_Excel2013_T1_P1a_FirstLastName_1.xlsx

· Open the file you just downloaded and save it with the name:

· NP_Excel2013_T1_P1a_FirstLastName_2.xlsx

· Hint: If you do not see the .xlsx file extension in the Save file dialog box, do not type it. Excel will add the file extension for you automatically.

· With the file NP_Excel2013_T1_P1a_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. On the Expenses Detail worksheet, cut the contents of the range B1:B3 and paste them into the range A1:A3.

2. Add outside borders to the range A5:D5.

3. Change the width of column C to 16.00.

4. Select cell B6 and then edit the cell content to be 125.24.

5. Enter the values shown in Table 1 below into the corresponding cells in the range B20:B25.

Table 1: Range B20:B25

© 2014 Cengage Learning.

Cell	Value
B20	53.02
B21	545.64
B22	198.05
B23	68.24
B24	55.04
B25	779.08

6. Select cell A26 and then enter the word Total. Add cell borders to the range A26:B26 using the All Borders border format.

7. In cell B26, use the SUM function to create totals for the data in the range B6:B25.

8. In cell D6, type TCM, in cell D7, type KTT, and then use Flash Fill to automatically enter codes in the range D8:D25.

9. Zoom out to 90% on the Expenses Detail worksheet.

10. On the Expenses by Salesperson worksheet, change the orientation to landscape. (Note: Do not change the orientation of the Expenses Detail worksheet.)

11. In cell A2, edit the cell content to read Sales Team Expenses by Salesperson.

12. In the Expenses by Salesperson worksheet, change the height of row 3 to 15.00.

13. In cell A5, edit the cell content to read Salesperson.

14. Change the width of column D to 19.00.

15. Enter the values shown in Table 2 below into the corresponding cells in the range A6:A10.

Table 2: Range A6:A10

© 2014 Cengage Learning.

16. In cell D6, enter a formula to determine the Non-Travel expenses, where the Non-Travel Expenses equals the Total Expenses (cell B6) minus the Travel Expenses (cell C6).

17. Copy the formula you created in cell D6 to the range D7:D10.

18. In cell A12, delete the text As of April 30, 2016.

19. Apply the wrap text formatting to cell A13.

20. Zoom in to 110% on the Expenses by Salesperson worksheet. (Note: Leave the zoom at 90% for the Expenses Detail worksheet.)

21. Move the Expenses Detail worksheet to the left of the Expenses by Salesperson worksheet. (Tip: The Expenses Detail worksheet should be between the Documentation and the Expenses by Salesperson worksheets.)

22. Insert a new worksheet in the workbook, rename the worksheet Expenses by Category, and move the new worksheet to the end of the workbook.

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

Final Figure 1 Expenses Detail worksheet

Microsoft product screenshots used with permission from Microsoft Corporation.

Copyright © 2014 Cengage Learning. All Rights Reserved.

Final Figure 2 Expenses by Salesperson worksheet

Microsoft product screenshots used with permission from Microsoft Corporation.

Copyright © 2014 Cengage Learning©. All Rights Reserved.