SHORT 10 MIN TASK :

See attached Excel - do the following:

First you will determine the total sales. To do this, you need to enter a formula in cells G8-G19 of

the worksheet labeled Scenario_2 to compute the total dollar value of sales for each manager.

Once you do this, cell B5 should automatically compute for you the Third Ranked ($-value of)

Weekly Sales (i.e., across all managers).

To find the top-3 managers (based on total sales), you will enter an IF() based formula in cells

H8 H19 to calculate whether the total ($ value) of weekly sales is among the top-3. The

calculation will compare the weekly sales by a manager with the Third Ranked ($-value of)

weekly sales (across all managers) seen in cell B5 of Scenario_2. As an example, let us assume

the third highest weekly sales across cells G8 G19 is $3,000 (computed in cell B5 of

Scenario_2). Therefore the cut-off for showing a Yes in cells H8-H19 is the value $3,000.

Consider a manager, Adam Chase. Lets assume he has a total weekly $-value of sales of

$2,500. You will show a - for Adam Chase in column H. If on the other hand, the total sales for

Adam Chase were $3,000 (or greater), your formula would show a result of Yes for Adam

Chase in column H.

For this part of the question, use the worksheet named Scenario_2.

Enter formulas in cell G8-G19 to compute the Total ($ value) of weekly sales for each

manager.

Fill in IF() formulas in cells H8 - H19 (column heading: Is Sales Figure among Top 3?) to

calculate if the weekly sales ($-value) for a manager is among the top 3. If a managers

sales equals or exceeds the value in B5, you will show a Yes in cells H8-H19. If it is not

among the top 3 sales, show a - (i.e., a dash) in cells H8-H19.

Note: owing to the random generation of data, if you do not use a formula (and try to

manually enter values), you will end up with incorrect results.

Please write (or copy and paste) the formula from cell G19 (Total ($ value of) Weekly

Sales) . Ensureyour formula is correctly written, and that it is a formula and not a value.

(25-50 words) Explain the parameters of the Excel IF() function used in cells H8- H19.

(50-70 words) Use the data generated for Task 2(a). Write down the names of the

top 3 managers from Scenario 1 (i.e., had the three best results in the column: Total Orders on

"High-Sales" days).

Next, use the data generated for Task 2(c). Write down the names of the top-3 managers in

Scenario 2 (based on data in column H).

Enter your 8-digit StudentID below (cell B4) Average Number of Orders automatically calculated after student ID is entered Enter your 8-digit studentID Average Number of Orders calculated in shaded box on the right Err:508 Managers Monday Fred Aykroyd Amy Belushi Adam Chase Maya Shannon Jason Thompson Kristen Walls Nancy Spade Seth O'Brien Julia Franken Phil Myers Tina Morgan 15 17 46 49 31 35 37 23 20 32 12 10 Average Number of Orders Tuesday 33 39 29 24 26 40 19 44 27 14 20 25 Wednesday 43 35 45 39 40 40 42 47 50 39 12 10 Thursday 28 38 45 26 48 11 48 31 44 23 39 38 Total Orders on "High-Sales" days Friday 16 20 48 15 10 45 37 28 41 38 21 45 Automatically computed (after you enter formulas in column G) Managers Fred Aykroyd Amy Belushi Adam Chase Maya Shannon Jason Thompson Kristen Walls Nancy Spade Seth O'Brien Julia Franken Phil Myers Tina Morgan Err:508 Monday $324 $680 $560 $833 $561 $592 $544 $253 $444 $602 $672 $152 Third Ranked ($-value of) Weekly Sales Tuesday $550 $396 $540 $144 $270 $270 $420 $656 $864 $418 $425 $184 Wednesday $546 $261 $504 $120 $288 $340 $507 $640 $1,152 $196 $357 $546 Thursday $121 $312 $374 $352 $384 $600 $481 $306 $850 $476 $713 $220 Friday $609 $264 $646 $828 $950 $294 $645 $490 $825 $390 $407 $833 Total ($ value of) Weekly Sales Is Sales Figure among Top 3