For Nmims answersheets contact
[email protected]
+91 95030-94040

Essentials of IT

1. The annual summary for the sales representatives of ABC. Co. is presented in the table below, which indicates the sales rep's name, the target sales assigned to him, the sales achieved by him, and the percentage of sales. Explain any four different conditional formatting styles (4 marks) and apply the following conditional formatting rules to the table presented below:

a. Show in green the percentage for any sales rep achieving more than 100% of their target (1 mark)
b. Show arrows icons for the achieved column (1 mark)
c. Highlight the sales rep with the name Carla in pink with a red font (2 marks)
d. Use the yellow colour to show a sales rep who has a target of less than Rs. 75000 sales (2 marks)
You can choose to show the final table in the colour formatted manner.
Sales Reps - Annual Summary
Name Target
(in
Rs.) Achieved Percentage
Jenifer Murthy 85000 94,500 111.18%
Ivan Martha 120000 1,18,000 98.33%
Carla Parth 100000 1,07,000 107.00%
Randy Cunningham 65000 29,000 44.62%
Arthur Shore 50000 67,000 134.00%
Hugh Rego 150000 1,98,000 132.00%
Wendy Hussain 95000 1,24,750 131.32%
Carla Suzan 95000 94750 99.74%
Christmas Dias 75000 75,000 100.00%

Note: Q 1 is of 10 marks. The mark bifurcation is mentioned with each sub-question.
2. Use the table mentioned below to convert this data into a pivot table and find the overall average speed of all rides that satisfy the following criteria:

• The Type is Steel (3 marks)
• The Design is Sit Down (3 marks)
• The Amusement Park has the word adventure somewhere in the title ( 2 marks)
• Use the filter option to display the different designs of rides. Mention the count for each of these ( 2 marks)
Roller
Coaster Amusement Park Type Design Status Opened Speed ( mph
)
Air Alton Towers Steel Flying Operating 2002 46.6
Boomerang Pleasure Island Family Theme
Park Steel Sit Down Operating 1993 47
Cobra Paultons Park Steel Sit Down Operating 2006 31.1
Colossus Thorpe Park Steel Sit Down Operating 2002 45
Corkscrew Alton Towers Steel Sit Down Operating 1980 40
Corkscrew Flamingo Land Theme Park &
Zoo Steel Sit Down Operating 1983 40
Crazy Mouse South Pier Steel Sit Down Operating 1998 29.1
Crazy Mouse Brighton Pier Steel Sit Down Operating 2000 29.1
Enigma Pleasurewood Hills Steel Sit Down Operating 1995 34
Express M&Ds Scotland's Theme Park Steel Sit Down Operating 2006 28
Fantasy
Mouse Fantasy Island Steel Sit Down Operating 2000 29.1
G Force Drayton Manor Park Steel Sit Down Operating 2005 43.5
Grand
National Pleasure Beach, Blackpool Wood Sit Down Operating 1935 40
Infusion Pleasure Beach, Blackpool Steel Inverted Operating 2007 49.7
Irn-Bru
Revolution Pleasure Beach, Blackpool Steel Sit Down Operating 1979 45
Jubilee
Odyssey Fantasy Island Steel Inverted Operating 2002 63
Jungle
Coaster Legoland Windsor Steel Sit Down Operating 2004 35
Knightmare Camelot Theme Park Steel Sit Down Operating 2007 43.5
Kumali Flamingo Land Theme Park &
Zoo Steel Inverted Operating 2006 54.9
Magic Mouse Brean Leisure Park Steel Sit Down Operating 2007 29.1
Megafobia Oakwood Theme Park Wood Sit Down Operating 1996 48
Millennium
Roller
Coaster Fantasy Island Steel Sit Down Operating 1999 55.9
Nemesis Alton Towers Steel Inverted Operating 1994 50
Nemesis
Inferno Thorpe Park Steel Inverted Operating 2003 47.8
New Roller
Coaster New MetroLand Steel Sit Down Operating 1988 26.8
Oblivion Alton Towers Steel Sit Down Operating 1998 68
Pepsi Max
Big One Pleasure Beach, Blackpool Steel Sit Down Operating 1994 74
Rage Adventure Island Steel Sit Down Operating 2007 43.5
Rat Loudoun Castle Steel Sit Down Operating 2005 28
Rattlesnake Chessington World of
Adventures Steel Sit Down Operating 1998 28
Rhino
Coaster West Midlands Safari Park Steel Sit Down Operating 1992 28.5
Rita - Queen
of Speed Alton Towers Steel Sit Down Operating 2005 61.1
Roller
Coaster Great Yarmouth Pleasure
Beach Wood Sit Down Operating 1932 45
Roller
Coas