Project 1
yeny.r
np_excel2013_t5_p1a_1.xlsxDocumentation
| New Perspectives Excel 2013 | |
| Tutorial 5: SAM Project 1a | |
| Spring Software | |
| WORKING WITH TABLES, PIVOTTABLES, AND PIVOTCHARTS | |
| Author: | John Due |
| Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the SAM website. | |
Cambridge
| Spring Software | ||||||
| Salaried Employees-Cambridge | ||||||
| LastName | FirstName | Office | Status | 2014 | 2015 | 2016 |
| Brown | Brianne | Cambridge | PT | $ 30,000 | $ 32,000 | $ 35,000 |
| Miller | James | Cambridge | FT | $ 75,000 | $ 80,000 | $ 86,000 |
| Lang | Noah | Cambridge | FT | $ 75,000 | $ 77,000 | $ 80,000 |
| Walsh | Michael | Cambridge | FT | $ 80,000 | $ 83,000 | $ 86,000 |
| Fahey | Riley | Cambridge | FT | $ 67,000 | $ 70,000 | $ 74,000 |
| Gonzalez | Andres | Cambridge | FT | $ 88,000 | $ 90,000 | $ 93,000 |
| Jackson | Randall | Cambridge | FT | $ 53,000 | $ 60,000 | $ 63,000 |
| Campbell | Kate | Cambridge | FT | $ 80,000 | $ 82,000 | $ 85,000 |
| Chang | Vanessa | Cambridge | PT | $ 24,000 | $ 15,000 | $ 25,000 |
| Dickenson | Amanda | Cambridge | PT | $ 15,000 | $ 18,000 | $ 18,000 |
| Foster | Brian | Cambridge | PT | $ 35,000 | $ 32,000 | $ 35,000 |
| Kosteczki | Malgosia | Cambridge | PT | $ 20,000 | $ 25,000 | $ 28,000 |
| Shao | Yugi | Cambridge | PT | $ 25,000 | $ 26,500 | $ 27,500 |
| Stanosky | Ivan | Cambridge | PT | $ 20,000 | $ 24,000 | $ 28,000 |
Waltham
| Spring Software | ||||||
| Salaried Employees-Waltham | ||||||
| LastName | FirstName | Office | Status | 2014 | 2015 | 2016 |
| Anderson | Niles | Waltham | FT | $ 55,000 | $ 57,000 | $ 60,000 |
| Lien | Aimee | Waltham | PT | $ 18,500 | $ 20,000 | $ 21,500 |
| McDonald | Susan | Waltham | PT | $ 32,000 | $ 32,500 | $ 33,000 |
| Ng | Julia | Waltham | FT | $ 42,000 | $ 47,000 | $ 53,000 |
| Petrova | Irina | Waltham | FT | $ 55,000 | $ 57,000 | $ 60,000 |
| Silva | Henrique | Waltham | FT | $ 45,000 | $ 47,000 | $ 50,000 |
| White | Mark | Waltham | FT | $ 68,000 | $ 71,000 | $ 75,000 |
Nashua
| Spring Software | ||||||
| Salaried Employees-Nashua | ||||||
| LastName | FirstName | Office | Status | 2014 | 2015 | 2016 |
| Lee | Samantha | Nashua | FT | $ 80,000 | $ 83,000 | $ 87,000 |
| Kerenkov | Natasha | Nashua | PT | $ 32,600 | $ 34,300 | $ 36,000 |
| Nelson | Ryan | Nashua | PT | $ 35,000 | $ 36,500 | $ 39,000 |
| Midha | Neha | Nashua | FT | $ 49,000 | $ 54,000 | $ 70,000 |
| Huang | Jennifer | Nashua | FT | $ 80,000 | $ 83,000 | $ 86,000 |
Framingham
| Spring Software | ||||||
| Salaried Employees-Framingham | ||||||
| LastName | FirstName | Office | Status | 2014 | 2015 | 2016 |
| Gupta | Richa | Framingham | PT | $ 33,000 | $ 34,500 | $ 36,000 |
| Kong | Jason | Framingham | FT | $ 66,000 | $ 69,000 | $ 73,000 |
| Mateev | Rohan | Framingham | FT | $ 66,000 | $ 70,000 | $ 75,000 |
| Nichols | Mary | Framingham | PT | $ 25,000 | $ 27,000 | $ 28,000 |
| Nichols | Mary | Framingham | PT | $ 25,000 | $ 27,000 | $ 28,000 |
All Employees
| Spring Software | ||||||
| Consolidated Payroll | ||||||
| LastName | FirstName | Office | Status | 2014 | 2015 | 2016 |
| Foster | Brian | Cambridge | PT | $ 40,000 | $ 42,000 | $ 45,000 |
| Ng | Julia | Waltham | FT | $ 42,000 | $ 47,000 | $ 53,000 |
| Nichols | Mary | Framingham | PT | $ 25,000 | $ 27,000 | $ 28,000 |
| Lee | Samantha | Nashua | FT | $ 80,000 | $ 83,000 | $ 87,000 |
| Mateev | Rohan | Framingham | FT | $ 66,000 | $ 70,000 | $ 75,000 |
| McDonald | Susan | Waltham | PT | $ 32,000 | $ 32,500 | $ 33,000 |
| Shao | Yugi | Cambridge | PT | $ 25,000 | $ 26,500 | $ 27,500 |
| Silva | Henrique | Waltham | FT | $ 45,000 | $ 47,000 | $ 50,000 |
| Gupta | Richa | Framingham | PT | $ 33,000 | $ 34,500 | $ 36,000 |
| Miller | James | Cambridge | FT | $ 75,000 | $ 80,000 | $ 86,000 |
| Kong | Jason | Framingham | FT | $ 66,000 | $ 69,000 | $ 73,000 |
| Lang | Noah | Cambridge | FT | $ 75,000 | $ 77,000 | $ 80,000 |
| Walsh | Michael | Cambridge | FT | $ 80,000 | $ 83,000 | $ 86,000 |
| Kerenkov | Natasha | Nashua | PT | $ 32,600 | $ 34,300 | $ 36,000 |
| Anderson | Niles | Waltham | FT | $ 55,000 | $ 57,000 | $ 60,000 |
| Fahey | Riley | Cambridge | FT | $ 67,000 | $ 70,000 | $ 74,000 |
| Gonzalez | Andres | Cambridge | FT | $ 88,000 | $ 90,000 | $ 93,000 |
| Nelson | Ryan | Nashua | PT | $ 35,000 | $ 36,500 | $ 39,000 |
| Midha | Neha | Nashua | FT | $ 49,000 | $ 54,000 | $ 70,000 |
| Jackson | Randall | Cambridge | FT | $ 53,000 | $ 60,000 | $ 63,000 |
| Petrova | Irina | Waltham | FT | $ 55,000 | $ 57,000 | $ 60,000 |
| White | Mark | Waltham | FT | $ 68,000 | $ 71,000 | $ 75,000 |
| Huang | Jennifer | Nashua | FT | $ 80,000 | $ 83,000 | $ 86,000 |
| Campbell | Kathryn | Cambridge | FT | $ 80,000 | $ 82,000 | $ 85,000 |
| Lien | Aimee | Waltham | PT | $ 18,500 | $ 20,000 | $ 21,500 |
| Brown | Brianne | Cambridge | PT | $ 30,000 | $ 32,000 | $ 30,000 |
| Chang | Vanessa | Cambridge | PT | $ 24,000 | $ 15,000 | $ 25,000 |
| Stanosky | Ivan | Cambridge | PT | $ 20,000 | $ 24,000 | $ 28,000 |
| Dickenson | Amanda | Cambridge | PT | $ 15,000 | $ 18,000 | $ 18,000 |
| Kosteczki | Malgosia | Cambridge | PT | $ 20,000 | $ 25,000 | $ 28,000 |
| Chohan | Danielle | Nashua | PT | $ 32,000 | $ 33,000 | $ 36,000 |
Employees PivotTable
| Row Labels | Sum of 2014 | 2015 Salaries | 2016 Salaries |
| Cambridge | 690000 | $ 724,500 | $ 768,500 |
| Brown | 30000 | $ 32,000 | $ 30,000 |
| Campbell | 80000 | $ 82,000 | $ 85,000 |
| Chang | 22000 | $ 15,000 | $ 25,000 |
| Dickenson | 15000 | $ 18,000 | $ 18,000 |
| Fahey | 67000 | $ 70,000 | $ 74,000 |
| Foster | 40000 | $ 42,000 | $ 45,000 |
| Gonzalez | 88000 | $ 90,000 | $ 93,000 |
| Jackson | 53000 | $ 60,000 | $ 63,000 |
| Kosteczki | 20000 | $ 25,000 | $ 28,000 |
| Lang | 75000 | $ 77,000 | $ 80,000 |
| Miller | 75000 | $ 80,000 | $ 86,000 |
| Shao | 25000 | $ 26,500 | $ 27,500 |
| Stanosky | 20000 | $ 24,000 | $ 28,000 |
| Walsh | 80000 | $ 83,000 | $ 86,000 |
| Framingham | 190000 | $ 200,500 | $ 212,000 |
| Gupta | 33000 | $ 34,500 | $ 36,000 |
| Kong | 66000 | $ 69,000 | $ 73,000 |
| Mateev | 66000 | $ 70,000 | $ 75,000 |
| Nichols | 25000 | $ 27,000 | $ 28,000 |
| Nashua | 308600 | $ 323,800 | $ 354,000 |
| Chohan | 32000 | $ 33,000 | $ 36,000 |
| Huang | 80000 | $ 83,000 | $ 86,000 |
| Kerenkov | 32600 | $ 34,300 | $ 36,000 |
| Lee | 80000 | $ 83,000 | $ 87,000 |
| Midha | 49000 | $ 54,000 | $ 70,000 |
| Nelson | 35000 | $ 36,500 | $ 39,000 |
| Waltham | 315500 | $ 331,500 | $ 352,500 |
| Anderson | 55000 | $ 57,000 | $ 60,000 |
| Lien | 18500 | $ 20,000 | $ 21,500 |
| McDonald | 32000 | $ 32,500 | $ 33,000 |
| Ng | 42000 | $ 47,000 | $ 53,000 |
| Petrova | 55000 | $ 57,000 | $ 60,000 |
| Silva | 45000 | $ 47,000 | $ 50,000 |
| White | 68000 | $ 71,000 | $ 75,000 |
| Grand Total | 1504100 | $ 1,580,300 | $ 1,687,000 |
Office PivotTable
| Row Labels | 2014 Salaries | 2015 Salaries | 2016 Salaries |
| Cambridge | $ 690,000 | $ 724,500 | $ 768,500 |
| FT | $ 518,000 | $ 542,000 | $ 567,000 |
| PT | $ 172,000 | $ 182,500 | $ 201,500 |
| Framingham | $ 190,000 | $ 200,500 | $ 212,000 |
| FT | $ 132,000 | $ 139,000 | $ 148,000 |
| PT | $ 58,000 | $ 61,500 | $ 64,000 |
| Nashua | $ 308,600 | $ 323,800 | $ 354,000 |
| FT | $ 209,000 | $ 220,000 | $ 243,000 |
| PT | $ 99,600 | $ 103,800 | $ 111,000 |
| Waltham | $ 315,500 | $ 331,500 | $ 352,500 |
| FT | $ 265,000 | $ 279,000 | $ 298,000 |
| PT | $ 50,500 | $ 52,500 | $ 54,500 |
| Grand Total | $ 1,504,100 | $ 1,580,300 | $ 1,687,000 |
