3. Here is the question all about excel!

In 2016-17, the firm employed 68 salespeople. The attached spreadsheet provides information on their 2016-17 sales targets, new client accounts, and total sales. Answer the following questions. Show your calculations on the spread sheet and note your answer below.

1. How many new clients did Vantage Investments gain in 2016-17? What is the total sales volume ($)? On average, how much new investment did each new client bring in?

1)650

2)

3)9.701492537

2. Which salesperson performed the best, in terms of (a) number of new clients; (b) total sales; and (c) average investment per new client? Who performed the worst on each of these criteria? Note their names and their performance (# or $).

	New clients	Total sales	Average investment / client
Best performer	Lessig, Marcos (17)	Ismail (232449)
Worst performer	Houtz (1)	Warren (18025)

3. Which salesperson performed the best, on percentage outperformance or underperformance relative to his or her 2016-17 sales target? Who performed the worst? Note their names and percentage outperformance or underperformance.

	Name	Percentage of Target
Best performer	Florio, Ismail, Kennedy
Worst performer	Warren, Vitelloni, Mancini, Houtz,

4. How many salespersons beat their 2016-17 sales targets? What percentage of the total sales force is this group of outperformers?

1)

2)

5. What is the average number of new clients brought in by a sales person? What is the average value of the new investments brought in by a salesperson? What is the standard deviation of the new investments brought in by a salesperson?

1)9.701492537

2)122024.9104

3)

6. Salespersons are compensated based on number of new clients and the total investments brought in by them. Each sales person receives $400 for each new client, and 2% of the total new investment they brought in. What is the total amount spent by Vantage Investments on sales commissions? What is the average compensation per salesperson?

2016 SALES TARGET 215000- Kowalskie Leibowitze 152 170000 2016 NEW CLIENTS: 2016 NEW SALES- 212723- 1716161 1532014 1249040 2137561 665192 183450 Lessige Linne Liue SALESPERSON Almeida Anderson Balle Ballestero- Barrior Billimoria Budge- Cohen- Curtine Drewe Lower 140000 80000 185000- 35000- 110000 500002 50000- 200000 185000 185000 110000 80000 1402802 82431 187124- 33188 1091022" 5123123 507284 191832 1834012 18261221 1124062 7805121 155000- 1250000 215000+ 65000- 185000- 950002 185000- 1850002 2000000 2300002 155000- 350002 65000 Madison Mancini Marcos Marine 9637041 183008- 1867834 Fiennest 154906- 3393121 Florio Fondue Francise Geddese Goddene 18253121 10821821 1690732 199473-1 2315192 156292 36918 660142 785734 479332 140367 515234 2090241 4790421 Goetz+ Hallse Harrise Houtz Ignatius Inada- Martine Martineze Matthews Mercere Monroe- Napoli- Newmane Parke Pettigrewe Pfaue Poindextere Romanoski Simpson Smalle Smith, Se Smith, Te Vitelloni Wallise Warrene Wellesley Williams Willitze Wyman- Zhange 800002 500002 140000- 500002 200002 1700000 80000 230000- 155000- 200000- 170000- 35000- 1400000 1700000 800002 155000- 2300002 50000- Ismaile 155000- 35000 185000 110000- 1700002 500002 1250000 1700000 1400002 500000 200000- 2150000 155000- 140000 200002 800002 200002 350002 95000+ 65000- 35000- 2150002 Issae 127326 1692140 139292 52106- 1976740 2151402 157162 1388482 183252 792672. 180250 32700- 9624521 65226 34492 216042 17081621 7921721 2324492 1562752 2021452 16857521 36789 1414732 169756 792832 1537112.1 Jacksone Jamese Jarvise Johnsone Kakare aye Kempe Kennedy Kinaston- (Ctrl) 231776 481761