Using the dataset below, summarise   the sum of HC FTE in a pivot table on a new sheet (named A1) by Region and   Officer Code Group. Limit the pivot table to show only HC FTE for the   “Employee” Person Type Long Description.

 

In the same pivot table and without   making any amendments to the dataset below, aggregate the AVP and ASO Officer   Code Groups into a new category called “AVP/ASO” and the STAFF,   ENO, NEX and OTH Officer Code Groups into a new category called   “STAFF/ANL”.

 

In the same pivot table in addition   to showing the sum of HC FTE, show the percentage HC FTE distribution of each   Officer Code Group by Region.

 

Use SUMIFS and SUM functions to   populate columns E and F in the table below using the “Ref” sheet   as source data. Please do not make any changes to the “Ref” data   sheet. Please bucket all “Officer Code Groups” other than MDR, DIR   and VP into the “Other” category.

 

In column G in the table below,   express the change from 31-Dec-12 to 31-Dec-13 as a percentage. Display the   results to 1 decimal place.

 

Under the column below labelled   “Q3a” (column D), write a vlookup formula using the “Ref”   sheet as reference to show which IDs are common in both datasets.

 

For those IDs which appear in both   datasets, write one formula which can be used in columns E through I (the   same formula must work in all five columns) to pull the data from the   associated columns. No additional columns can be added to the “Ref”   sheet nor the table below.

 

Sort the table below by ID and   under the column labelled “Q4a” (column G), write a formula which   returns TRUE where the ID on the row is the same as the ID on the row above.

 

Under the column below labelled   “Q4b” (column H), write a formula which satisfies the following   conditions for all rows:

 

– Rows with an MDR Officer Code   Group and a Private Banking, Asset Management or Investment Banking Per Owned   Name (L2) should reflect as “Front Office MDR”.
  – Rows with a Shared Services Per Owned Name (L2) where the Product Name (L3)   value is COCP Corp Centre S0700, SCEO CEO Functions Division or SSBC Centers   Of Excellence Pro should reflect as “Shared Services Other”.
  – Other rows with a Shared Services Per Owned Name (L2) should return the   Product Name (L3) value.
  – All other rows should reflect the Per Owned Name (L2) value.

 

Represent each of the datasets   below using an appropriate chart embedded in this sheet.

 

Officer Code Group

% Females

 

MDR

30%

 

DIR

35%

 

VP

40%

 

AVP

45%

 

STAFF

50%

       

 

Americas

Asia Pacific

Europe

Switzerland

 

MDR

100

80

120

300

 

DIR

500

600

550

400

 

VP

1000

800

2000

2500

 

AVP

3000

3000

3500

2500

 

STAFF

5000

7500

10000

4500

       

 

# Leavers

Attrition

 

31-Jan-13

100

13.2%

 

28-Feb-13

120

13.5%

 

31-Mar-13

150

13.6%

 

30-Apr-13

180

13.7%

 

31-May-13

220

14.0%

 

30-Jun-13

300

14.5%

 

31-Jul-13

250

14.3%

 

31-Aug-13

180

14.1%

 

30-Sep-13

160

13.9%

 

31-Oct-13

140

13.5%

 

30-Nov-13

80

13.1%

 

31-Dec-13

75

12.8%

       

 

Vol Leavers

Invol Leavers

Vol Attrition

Invol Attrition

 

31-Jan-13

80

20

9.1%

4.1%

 

28-Feb-13

100

20

9.2%

4.3%

 

31-Mar-13

120

30

9.4%

4.2%

 

30-Apr-13

100

80

9.6%

4.1%

 

31-May-13

180

40

10.1%

3.9%

 

30-Jun-13

160

140

10.2%

4.3%

 

31-Jul-13

200

50

11.0%

3.3%

 

31-Aug-13

150

30

10.0%

4.1%

 

30-Sep-13

100

60

9.9%

4.0%

 

31-Oct-13

40

100

9.8%

3.7%

 

30-Nov-13

40

40

9.2%

3.9%

 

31-Dec-13

30

45

9.1%

3.7%

Under the column below labelled Q6a (column D), write a formula which outputs the value between the “,” and “-” references in the Data column. Text to columns is not allowed. 

  

Under the column below labelled Q7a   (column G), write a formula which concatenates the Title and Country columns   and sort the table by this column.

 

Under the column below labelled Q7b   (column H), write a formula which outputs the median salary for each value in   the Q7a column. One formula must work for all rows in the table. You can add   additional columns to the table for reference as needed.