excel

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

excel

Instructions

Permission Information

HRID Data

Month End Metrics - Workings

Month End Metrics - Final