Assignment_NP_Excel2013_T1_P1a
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:
o NP_Excel2013_T1_P1a_FirstLastName _1.xlsx ?
Open the file you just downloaded and save it with the name:
o NP_Excel2013_T1_P1a_FirstLastName _2.xlsx
o 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 12
5.24.
5.
Enter the values shown in Table 1 below into the corresponding cells in the range B20:B25.
New Perspectives Excel 2013 Tutorial 1: SAM Project 1a
Spring Software
CREATE AN EXPENSE REPORT
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.
? 2014 C e n g a g e L e a r n i n g .
Cell Value A6 Ken A7 Mona A8 Raj A9 Tori A10
Phillipe
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.
? 2014 C e n g a g e L e a r n i n g .
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.