excel question 2,3
Bayhawks22
Week2ExcelAssignment31.xlsxInstructions
| INSTRUCTIONS |
| CEO Salaries |
| Use Vlookup to fill in Salary for CEOs. |
| Add If statement to remove any errors. |
| Calculate Total and Average for Salary. |
| Paystub |
| Use VLOOKUP to complete (however, if you can't figure it out this way, remember there is always more than one way in Excel) |
| Format a paystub using lookups and formulas for the following 3 employees: Elijah Ward, Kimberly Hall, and Jackson Stephens. |
| Use Blue Cell as the input cell and use formulas in Green Cells. |
| Tip: Complete for one employee first and then copy and paste for the others (remember to anchor the correct cells in the formulas). |
| Financial Calcs |
| Answer the two questions on the Financial Calcs tab. |
| Coffee Sales |
| Make two new tabs making copies of this data set and label Coffee Sales- Pivot Tables and Coffee Sales- Charts |
| Coffee Sales- Pivot Tables |
| Pivot Table to show Sales by State sort in ascending order of sales |
| Pivot Table to show Sales by Product sort in alpha order |
| Pivot Table to show quantity of Sales by Month (i.e. number of sales made by month) |
| Coffee Sales- Charts |
| Create Pie Chart to show Sales by Product |
| Create Bar Chart to show Sales by State |
| Create a Line Chart to show Sales by Month |
CEO Salaries
| Company | CEO | Salary | 2012 Salary | Name | |
| Credit Acceptance Corp | Brett A. Roberts | 33,247,178 | David M. Cote | ||
| Cheniere Energy Inc | Charif Souki | 45,302,040 | Gregory B. Maffei | ||
| Honeywell International Inc | David M. Cote | 51,744,999 | John H. Hammergren | ||
| Discovery Communications Inc | David M. Zaslav | 62,157,026 | Leslie Moonves | ||
| Tesla Motors Inc | Elon Musk | 68,970,486 | Mario J. Gabelli | ||
| Liberty Interactive Corp | Gregory B. Maffei | 36,615,404 | Marissa A. Mayer | ||
| Level 3 Communications Inc | James Q. Crowe | 35,212,678 | Mark G. Parker | ||
| Mckesson Corp | John H. Hammergren | 40,266,501 | R. W. Tillerson | ||
| Oracle Corp | Lawrence J. Ellison | 36,325,782 | Ralph Lauren | ||
| Pall Corp | Lawrence Kingsley | ||||
| CBS Corp | Leslie Moonves | ||||
| Gamco Investors Inc | Mario J. Gabelli | ||||
| Yahoo Inc | Marissa A. Mayer | ||||
| Nike Inc | Mark G. Parker | ||||
| Nuance Communications Inc | Paul A. Ricci | ||||
| Viacom Inc | Philippe P. Dauman | ||||
| Exxon Mobil Corp | R. W. Tillerson | ||||
| Ralph Lauren Corp | Ralph Lauren | ||||
| Jefferies Group Llc | Richard B. Handler | ||||
| HCA Holdings Inc | Richard M. Bracken | ||||
| Disney (Walt) Co | Robert A. Iger | ||||
| Activision Blizzard Inc | Robert A. Kotick | ||||
Paystub
| ID | Employee Name | Address | Frequency | Salary | Tax Rate | Insurance | 401k |
| 154 | Paige Jones | 427 John A. Creighton Boulevard Anytown, USA 12345 | 26 | 42,900.00 | 15% | 100.00 | 8% |
| 240 | Elijah Ward | 888 192nd Street Anytown, USA 12345 | 26 | 64,600.00 | 16% | 200.00 | 7% |
| 319 | Elizabeth Marshall | 530 Dodge Street Anytown, USA 12345 | 52 | 72,300.00 | 24% | 300.00 | 3% |
| 331 | Cooper Smith | 271 Dodge Street Anytown, USA 12345 | 12 | 99,700.00 | 20% | 300.00 | 5% |
| 428 | Isabella Harris | 715 136th Street Anytown, USA 12345 | 26 | 57,600.00 | 25% | 100.00 | 5% |
| 451 | Kaylee Perez | 772 North 30th Street Anytown, USA 12345 | 12 | 82,800.00 | 23% | 200.00 | 7% |
| 527 | Kimberly Hall | 652 Regency Parkway Drive Anytown, USA 12345 | 12 | 41,700.00 | 17% | 300.00 | 4% |
| 540 | Jesus Clark | 803 Fontenelle Boulevard Anytown, USA 12345 | 52 | 83,100.00 | 18% | 200.00 | 5% |
| 665 | Kylie Woods | 245 Fontenelle Boulevard Anytown, USA 12345 | 26 | 70,400.00 | 21% | 100.00 | 1% |
| 981 | Jackson Stephens | 827 Harrison Street Anytown, USA 12345 | 52 | 50,200.00 | 18% | 100.00 | 8% |
| Employee ID | Employee Name | Check # | Net Pay | ||||
| 1 | |||||||
| Employee Address | |||||||
| Pay | Deductions | ||||||
| Taxes | |||||||
| Insurance | |||||||
| Retirement (401 k) | |||||||
| Total |
Financial Calcs
| 1.) | The average student loan debt in Massachusetts is approximately $32,000. |
| Calculate the monthly payment amount, assuming a 5% interest rate and a 30-year repayment period. | |
| 2.) | Assume you save $100/month starting at age 25 for the next 25 years. If your interest rate is 2.5%, how much will you have at age 50. |
| Hint: Create four columns: Month #, Deposit, Beginning Balance, Ending Balance. | |
| Tip: Unlike your amortization schedule, the ending balance should be going up, not down. | |
Coffee Sales
| Month | Product | State | Monthly Sales $ |
| August | Best Blend of Arabia | California | $ 4.37 |
| June | Best Blend of Arabia | Oregon | $ 40.86 |
| April | Best Blend of Arabia | Oregon | $ 42.52 |
| January | Best Blend of Arabia | Oregon | $ 64.50 |
| March | Best Blend of Arabia | California | $ 146.13 |
| March | Best Blend of Arabia | Oregon | $ 227.08 |
| July | Best Blend of Arabia | Washington | $ 276.51 |
| March | Best Blend of Arabia | Washington | $ 320.73 |
| March | Best Blend of Arabia | Oregon | $ 324.50 |
| September | Best Blend of Arabia | California | $ 360.43 |
| March | Best Blend of Arabia | Washington | $ 426.45 |
| May | Best Blend of Arabia | Washington | $ 428.55 |
| April | Best Blend of Arabia | Washington | $ 450.03 |
| March | Best Blend of Arabia | California | $ 457.08 |
| March | Best Blend of Arabia | California | $ 462.08 |
| August | Best Blend of Arabia | Washington | $ 532.91 |
| February | Best Blend of Arabia | Washington | $ 541.34 |
| April | Best Blend of Arabia | California | $ 567.38 |
| March | Best Blend of Arabia | Washington | $ 602.04 |
| August | Best Blend of Arabia | Oregon | $ 674.29 |
| July | Best Blend of Arabia | California | $ 676.74 |
| March | Best Blend of Arabia | Washington | $ 680.71 |
| May | Best Blend of Arabia | Oregon | $ 685.84 |
| January | Best Blend of Arabia | California | $ 709.82 |
| September | Best Blend of Arabia | Washington | $ 743.13 |
| March | Best Blend of Arabia | Oregon | $ 744.97 |
| June | Best Blend of Arabia | California | $ 763.22 |
| June | Best Blend of Arabia | Washington | $ 810.14 |
| May | Best Blend of Arabia | California | $ 816.84 |
| March | Best Blend of Arabia | Washington | $ 832.28 |
| March | Best Blend of Arabia | California | $ 841.99 |
| March | Best Blend of Arabia | Oregon | $ 864.90 |
| September | Best Blend of Arabia | Oregon | $ 869.25 |
| March | Best Blend of Arabia | California | $ 883.44 |
| July | Best Blend of Arabia | Oregon | $ 920.61 |
| February | Best Blend of Arabia | California | $ 962.63 |
| February | Best Blend of Arabia | Oregon | $ 962.91 |
| March | Best Blend of Arabia | Oregon | $ 997.66 |
| March | Breakfast Blastoff | California | $ 20.40 |
| February | Breakfast Blastoff | Washington | $ 99.99 |
| September | Breakfast Blastoff | California | $ 120.73 |
| March | Breakfast Blastoff | California | $ 150.33 |
| March | Breakfast Blastoff | California | $ 171.27 |
| April | Breakfast Blastoff | Oregon | $ 182.15 |
| May | Breakfast Blastoff | California | $ 191.45 |
| April | Breakfast Blastoff | California | $ 200.14 |
| May | Breakfast Blastoff | Washington | $ 222.34 |
| March | Breakfast Blastoff | Oregon | $ 238.34 |
| March | Breakfast Blastoff | Oregon | $ 262.06 |
| March | Breakfast Blastoff | Washington | $ 272.55 |
| August | Breakfast Blastoff | Washington | $ 285.85 |
| July | Breakfast Blastoff | Oregon | $ 311.85 |
| January | Breakfast Blastoff | Oregon | $ 314.94 |
| June | Breakfast Blastoff | Oregon | $ 368.25 |
| July | Breakfast Blastoff | Washington | $ 403.95 |
| April | Breakfast Blastoff | Washington | $ 409.14 |
| March | Breakfast Blastoff | Oregon | $ 415.54 |
| May | Breakfast Blastoff | Oregon | $ 448.23 |
| September | Breakfast Blastoff | Washington | $ 454.87 |
| March | Breakfast Blastoff | Washington | $ 456.86 |
| June | Breakfast Blastoff | California | $ 517.77 |
| June | Breakfast Blastoff | Washington | $ 523.56 |
| July | Breakfast Blastoff | California | $ 556.08 |
| March | Breakfast Blastoff | Washington | $ 588.29 |
| March | Breakfast Blastoff | California | $ 598.73 |
| February | Breakfast Blastoff | Oregon | $ 603.64 |
| March | Breakfast Blastoff | Oregon | $ 640.32 |
| September | Breakfast Blastoff | Oregon | $ 681.53 |
| March | Breakfast Blastoff | Washington | $ 755.67 |
| March | Breakfast Blastoff | Washington | $ 827.73 |
| March | Breakfast Blastoff | Oregon | $ 869.88 |
| February | Breakfast Blastoff | Washington | $ 918.08 |
| August | Breakfast Blastoff | California | $ 936.14 |
| August | Breakfast Blastoff | Oregon | $ 956.18 |
| March | Breakfast Blastoff | California | $ 984.39 |
| May | Breakfast Blastoff Decaf | Washington | $ 22.88 |
| May | Breakfast Blastoff Decaf | Oregon | $ 51.22 |
| July | Breakfast Blastoff Decaf | California | $ 96.32 |
| February | Breakfast Blastoff Decaf | Oregon | $ 128.72 |
| June | Breakfast Blastoff Decaf | Oregon | $ 164.91 |
| March | Breakfast Blastoff Decaf | Oregon | $ 187.84 |
| August | Breakfast Blastoff Decaf | Oregon | $ 207.64 |
| August | Breakfast Blastoff Decaf | California | $ 286.28 |
| March | Breakfast Blastoff Decaf | Washington | $ 391.64 |
| April | Breakfast Blastoff Decaf | Oregon | $ 445.57 |
| July | Breakfast Blastoff Decaf | Oregon | $ 450.12 |
| January | Breakfast Blastoff Decaf | Oregon | $ 451.51 |
| September | Breakfast Blastoff Decaf | Washington | $ 457.13 |
| March | Breakfast Blastoff Decaf | Washington | $ 467.67 |
| February | Breakfast Blastoff Decaf | Washington | $ 472.54 |
| March | Breakfast Blastoff Decaf | Oregon | $ 496.37 |
| March | Breakfast Blastoff Decaf | Washington | $ 550.18 |
| March | Breakfast Blastoff Decaf | California | $ 580.97 |
| September | Breakfast Blastoff Decaf | California | $ 596.59 |
| June | Breakfast Blastoff Decaf | Washington | $ 622.81 |
| March | Breakfast Blastoff Decaf | California | $ 623.33 |
| April | Breakfast Blastoff Decaf | California | $ 631.14 |
| August | Breakfast Blastoff Decaf | Washington | $ 666.75 |
| September | Breakfast Blastoff Decaf | Oregon | $ 668.97 |
| June | Breakfast Blastoff Decaf | California | $ 709.91 |
| March | Breakfast Blastoff Decaf | California | $ 784.83 |
| April | Breakfast Blastoff Decaf | Washington | $ 805.96 |
| March | Breakfast Blastoff Decaf | Oregon | $ 810.15 |
| March | Breakfast Blastoff Decaf | California | $ 820.21 |
| March | Breakfast Blastoff Decaf | Oregon | $ 840.06 |
| February | Breakfast Blastoff Decaf | Washington | $ 844.93 |
| March | Breakfast Blastoff Decaf | Oregon | $ 879.53 |
| March | Breakfast Blastoff Decaf | Washington | $ 884.68 |
| July | Breakfast Blastoff Decaf | Washington | $ 892.36 |
| March | Breakfast Blastoff Decaf | Washington | $ 935.71 |
| March | Breakfast Blastoff Decaf | California | $ 967.70 |
| May | Breakfast Blastoff Decaf | California | $ 979.21 |
| September | Kona Koast | Oregon | $ 37.02 |
| May | Kona Koast | Oregon | $ 58.84 |
| March | Kona Koast | Oregon | $ 79.78 |
| March | Kona Koast | Washington | $ 149.10 |
| August | Kona Koast | Washington | $ 194.40 |
| March | Kona Koast | Washington | $ 197.75 |
| September | Kona Koast | California | $ 263.29 |
| May | Kona Koast | California | $ 299.52 |
| March | Kona Koast | Washington | $ 305.39 |
| July | Kona Koast | Washington | $ 320.19 |
| June | Kona Koast | Washington | $ 336.84 |
| March | Kona Koast | California | $ 419.91 |
| September | Kona Koast | Washington | $ 427.83 |
| May | Kona Koast | Washington | $ 473.70 |
| August | Kona Koast | California | $ 484.77 |
| March | Kona Koast | Washington | $ 506.53 |
| June | Kona Koast | Oregon | $ 512.23 |
| January | Kona Koast | California | $ 512.91 |
| March | Kona Koast | Oregon | $ 521.45 |
| February | Kona Koast | Washington | $ 584.59 |
| April | Kona Koast | Oregon | $ 611.74 |
| June | Kona Koast | California | $ 639.89 |
| March | Kona Koast | California | $ 677.52 |
| March | Kona Koast | Oregon | $ 701.48 |
| March | Kona Koast | Washington | $ 706.57 |
| March | Kona Koast | California | $ 713.56 |
| February | Kona Koast | California | $ 715.94 |
| April | Kona Koast | California | $ 739.04 |
| August | Kona Koast | Oregon | $ 774.59 |
| April | Kona Koast | Washington | $ 795.24 |
| March | Kona Koast | Oregon | $ 801.38 |
| February | Kona Koast | Oregon | $ 852.43 |
| March | Kona Koast | California | $ 883.04 |
| March | Kona Koast | Oregon | $ 946.83 |
| July | Kona Koast | Oregon | $ 958.53 |
| July | Kona Koast | California | $ 962.50 |
| March | Kona Koast | California | $ 991.36 |
| June | Turkish Delight Roast | California | $ 13.05 |
| March | Turkish Delight Roast | Washington | $ 14.85 |
| May | Turkish Delight Roast | Oregon | $ 15.06 |
| June | Turkish Delight Roast | Washington | $ 52.87 |
| September | Turkish Delight Roast | California | $ 70.04 |
| March | Turkish Delight Roast | Washington | $ 88.51 |
| March | Turkish Delight Roast | Washington | $ 90.38 |
| March | Turkish Delight Roast | California | $ 99.37 |
| August | Turkish Delight Roast | Oregon | $ 99.51 |
| March | Turkish Delight Roast | Oregon | $ 175.71 |
| June | Turkish Delight Roast | Oregon | $ 190.58 |
| January | Turkish Delight Roast | Oregon | $ 242.93 |
| July | Turkish Delight Roast | Washington | $ 310.00 |
| March | Turkish Delight Roast | Oregon | $ 344.60 |
| May | Turkish Delight Roast | California | $ 345.69 |
| January | Turkish Delight Roast | California | $ 352.34 |
| March | Turkish Delight Roast | California | $ 361.34 |
| March | Turkish Delight Roast | Oregon | $ 401.54 |
| September | Turkish Delight Roast | Oregon | $ 456.40 |
| February | Turkish Delight Roast | Oregon | $ 468.83 |
| March | Turkish Delight Roast | Oregon | $ 551.47 |
| March | Turkish Delight Roast | Washington | $ 569.86 |
| February | Turkish Delight Roast | California | $ 598.97 |
| February | Turkish Delight Roast | Washington | $ 621.65 |
| April | Turkish Delight Roast | California | $ 626.83 |
| September | Turkish Delight Roast | Washington | $ 644.24 |
| July | Turkish Delight Roast | California | $ 649.99 |
| March | Turkish Delight Roast | California | $ 681.20 |
| March | Turkish Delight Roast | Oregon | $ 696.01 |
| March | Turkish Delight Roast | California | $ 741.96 |
| May | Turkish Delight Roast | Washington | $ 786.42 |
| March | Turkish Delight Roast | Washington | $ 787.48 |
| August | Turkish Delight Roast | Washington | $ 812.53 |
| April | Turkish Delight Roast | Washington | $ 880.28 |
| April | Turkish Delight Roast | Oregon | $ 920.04 |
| March | Turkish Delight Roast | California | $ 942.25 |
| July | Turkish Delight Roast | Oregon | $ 951.45 |
| August | Turkish Delight Roast | California | $ 962.20 |
| March | Volcano Blend | Oregon | $ 20.80 |
| May | Volcano Blend | Oregon | $ 23.92 |
| March | Volcano Blend | Washington | $ 43.45 |
| August | Volcano Blend | California | $ 63.28 |
| March | Volcano Blend | California | $ 116.31 |
| July | Volcano Blend | Oregon | $ 174.80 |
| March | Volcano Blend | California | $ 200.43 |
| July | Volcano Blend | California | $ 205.54 |
| May | Volcano Blend | Washington | $ 211.78 |
| February | Volcano Blend | Washington | $ 256.71 |
| March | Volcano Blend | California | $ 300.49 |
| February | Volcano Blend | California | $ 352.97 |
| August | Volcano Blend | Washington | $ 466.71 |
| March | Volcano Blend | Washington | $ 491.42 |
| April | Volcano Blend | Oregon | $ 520.40 |
| March | Volcano Blend | Washington | $ 573.77 |
| May | Volcano Blend | California | $ 599.26 |
| March | Volcano Blend | Oregon | $ 695.81 |
| March | Volcano Blend | Oregon | $ 704.54 |
| February | Volcano Blend | Oregon | $ 733.15 |
| April | Volcano Blend | Washington | $ 764.58 |
| July | Volcano Blend | Washington | $ 785.18 |
| June | Volcano Blend | California | $ 807.30 |
| March | Volcano Blend | Washington | $ 815.67 |
| August | Volcano Blend | Oregon | $ 829.89 |
| March | Volcano Blend | California | $ 830.31 |
| March | Volcano Blend | Oregon | $ 849.79 |
| September | Volcano Blend | Oregon | $ 857.26 |
| September | Volcano Blend | California | $ 862.45 |
| September | Volcano Blend | Washington | $ 880.27 |
| January | Volcano Blend | California | $ 894.34 |
| April | Volcano Blend | California | $ 919.12 |
| March | Volcano Blend | Washington | $ 941.87 |
| June | Volcano Blend | Oregon | $ 948.27 |
| June | Volcano Blend | Washington | $ 966.43 |
| March | Volcano Blend | Oregon | $ 993.00 |
| March | Volcano Blend | California | $ 995.18 |
