excel

bostonguy90
InventoryTest.xlsx
Home>Computer Science homework help>excel

Instructions

Instructions
The Permission Information tab contains a simplified set of data, which resembles what you might come across in day to day activities. This data needs to be used to create month end figures, however certain pieces of information are incomplete or missing. Using the formulas, functions and features of Excel, please complete the following tasks, leaving all your workings in place to be reviewed
Task
Apply a 20% tax rate to Amount, to populate the Tax field
Combine FirstName and Lastname to create Fullname, in the format "Firstname Lastname"
Use the HRID Data tab, to populate the HRID column
The Month End Metrics tab needs populating with the below summaries. There is a Workings tab, which you can use as a space to calculate figures, before populating into the Final Metrics tab. 1. Total Permissions 2. Total Spend 3. Total Spend to nearest $ 4. Terminals Spend 5. Services Spend 6. Distinct User Count 7. User with Maximum Spend 8. User with Minimum Spend 9. Average User Spend 10. Service with the Longest Description The format and presentation of the Final Metrics tab is at your discretion.
Finally, add to the Final Metrics tab, a Pivot Table of the Permission Information to show the net spend, with Fullname in Row Labels and Category in Column Labels. Format this to be in USD currency.

Permission Information

PersonnelID Lastname Firstname Fullname HRID Provider Carrier Category Description Amount Tax
1 Jones Martin MDSL MDSL Terminal MDM Pro Terminal 325 0
2 Skinner James MDSL MDSL Terminal MDM Pro Terminal 325 0
3 Cruise Lisa MDSL MDSL Terminal MDM Pro Terminal 325 0
4 Daniel Richard MDSL MDSL Terminal MDM Pro Terminal 325 0
5 Platt Tim MDSL MDSL Terminal MDM Pro Terminal 325 0
6 McDonald Harry MDSL MDSL Terminal MDM Pro Terminal 325 0
7 Kirk Rachel MDSL MDSL Terminal MDM Pro Terminal 325 0
8 Olsen Amy MDSL MDSL Terminal MDM Pro Terminal 325 0
9 Wayne Clark MDSL MDSL Terminal MDM Pro Terminal 325 0
10 Simmons Robert MDSL MDSL Terminal MDM Pro Terminal 325 0
11 Brosnan Tina MDSL MDSL Terminal MDM Pro Terminal 325 0
12 Weaks Harriet MDSL MDSL Terminal MDM Pro Terminal 325 0
1 Jones Martin MDSL MDSL Infrastructure Terminal Charge 100 0
2 Skinner James MDSL MDSL Infrastructure Terminal Charge 100 0
3 Cruise Lisa MDSL MDSL Infrastructure Terminal Charge 100 0
4 Daniel Richard MDSL MDSL Infrastructure Terminal Charge 100 0
5 Platt Tim MDSL MDSL Infrastructure Terminal Charge 100 0
6 McDonald Harry MDSL MDSL Infrastructure Terminal Charge 100 0
7 Kirk Rachel MDSL MDSL Infrastructure Terminal Charge 100 0
8 Olsen Amy MDSL MDSL Infrastructure Terminal Charge 100 0
9 Wayne Clark MDSL MDSL Infrastructure Terminal Charge 100 0
10 Simmons Robert MDSL MDSL Infrastructure Terminal Charge 100 0
11 Brosnan Tina MDSL MDSL Infrastructure Terminal Charge 100 0
12 Weaks Harriet MDSL MDSL Infrastructure Terminal Charge 100 0
1 Jones Martin Intercontinental Exchange MDSL Service ICE Futures Europe 43.88 0
2 Skinner James Intercontinental Exchange MDSL Service ICE Futures Europe 43.88 0
3 Cruise Lisa Intercontinental Exchange MDSL Service ICE Futures Europe 43.88 0
4 Daniel Richard Intercontinental Exchange MDSL Service ICE Futures Europe 43.88 0
5 Platt Tim Intercontinental Exchange MDSL Service ICE Futures Europe 43.88 0
6 McDonald Harry Intercontinental Exchange MDSL Service ICE Futures Europe 43.88 0
7 Kirk Rachel Intercontinental Exchange MDSL Service ICE Futures US 43.88 0
8 Olsen Amy Intercontinental Exchange MDSL Service ICE Futures Europe 43.88 0
9 Wayne Clark Intercontinental Exchange MDSL Service ICE Futures Europe 43.88 0
10 Simmons Robert Intercontinental Exchange MDSL Service ICE Futures Europe 43.88 0
11 Brosnan Tina Intercontinental Exchange MDSL Service ICE Futures Europe 43.88 0
12 Weaks Harriet Intercontinental Exchange MDSL Service ICE Futures Europe 43.88 0
2 Skinner James New York Mercantile Exchange MDSL Service NYMEX 40.22 0
3 Cruise Lisa New York Mercantile Exchange MDSL Service NYMEX 40.22 0
5 Platt Tim New York Mercantile Exchange MDSL Service NYMEX 40.22 0
6 McDonald Harry New York Mercantile Exchange MDSL Service NYMEX 40.22 0
7 Kirk Rachel New York Mercantile Exchange MDSL Service NYMEX 40.22 0
8 Olsen Amy New York Mercantile Exchange MDSL Service NYMEX 40.22 0
9 Wayne Clark New York Mercantile Exchange MDSL Service NYMEX 40.22 0
10 Simmons Robert New York Mercantile Exchange MDSL Service NYMEX 40.22 0
11 Brosnan Tina New York Mercantile Exchange MDSL Service NYMEX 40.22 0
12 Weaks Harriet New York Mercantile Exchange MDSL Service NYMEX 40.22 0
1 Jones Martin New York Mercantile Exchange MDSL Service NYMEX 40.22 0
2 Skinner James New York Mercantile Exchange MDSL Service NYMEX Site Fee 16.68 0
3 Cruise Lisa New York Mercantile Exchange MDSL Service NYMEX Site Fee 16.68 0
5 Platt Tim New York Mercantile Exchange MDSL Service NYMEX Site Fee 16.68 0
6 McDonald Harry New York Mercantile Exchange MDSL Service NYMEX Site Fee 16.68 0
7 Kirk Rachel New York Mercantile Exchange MDSL Service NYMEX Site Fee 16.68 0
8 Olsen Amy New York Mercantile Exchange MDSL Service NYMEX Site Fee 16.68 0
9 Wayne Clark New York Mercantile Exchange MDSL Service NYMEX Site Fee 16.68 0
10 Simmons Robert New York Mercantile Exchange MDSL Service NYMEX Site Fee 16.68 0
11 Brosnan Tina New York Mercantile Exchange MDSL Service NYMEX Site Fee 16.68 0
12 Weaks Harriet New York Mercantile Exchange MDSL Service NYMEX Site Fee 16.68 0
1 Jones Martin New York Mercantile Exchange MDSL Service NYMEX Site Fee 16.68 0
3 Cruise Lisa Tokyo Financial Exchange MDSL Service Tokyo International Financial Futures Exchange 8.58 0
9 Wayne Clark London Metal Exchange MDSL Service London Metals Exchange 35.95 0
6 McDonald Harry London Metal Exchange MDSL Service London Metals Exchange 35.95 0
8 Olsen Amy London Metal Exchange MDSL Service London Metals Exchange 35.95 0
2 Skinner James Chicago Board of Trade MDSL Service CBOT 40.22 0
4 Daniel Richard Chicago Board of Trade MDSL Service CBOT 40.22 0
6 McDonald Harry Chicago Board of Trade MDSL Service CBOT 40.22 0
2 Skinner James Chicago Board of Trade MDSL Service CBOT SF 61.17 0
4 Daniel Richard Chicago Board of Trade MDSL Service CBOT SF 61.17 0
6 McDonald Harry Chicago Board of Trade MDSL Service CBOT SF 61.17 0
8 Olsen Amy NYSE Euronext MDSL Service Euronext Commodities 32.83 0
9 Wayne Clark NYSE Euronext MDSL Service Euronext Commodities 32.83 0
7 Kirk Rachel Singapore Stock Exchange MDSL Service Singapore Stock Exchange 62.24 0

HRID Data

PersonnelID HRID
1 Jonin6
2 Skies8
3 Crusa7
4 Danrd7
5 Plaim6
6 McDry9
7 Kirel5
8 Olsmy6
9 Wayrk6
10 Simrt8
11 Brona8
12 Weaet6

Month End Metrics - Workings

Month End Metrics - Final