Excel

2.      In the Sales by Div sheet, summarize the data so that only
the following are displayed:

 

        The divisions (in column A)

 

        The subtotals (in rows 13, 21, 29, 37, 45, and 53)

 

        The grand totals in row 55 and column R

 

        The Qtr subtotals in columns E, I, M, and Q

 When finished, your worksheet should look as follows:



3.      Summarize the data in the Manager Sales sheet, placing the
summarized data on a new sheet named Total by Month. 

Display all values with commas and 0 decimal places.

Your finished worksheet should look like the following:



 4.      Summarize the same data on another sheet named Total By
Mgr, with the data organized by category and manager. 

 Make sure all managers are represented and that the Total values are
formatted to display commas and 0 decimal places.

Your worksheet should look like the following:



5.      Summarize the same data in another sheet named Total by
Category so that the data are organized by category and year. 

Make sure all of the book categories are included in the summary.

Your finished worksheet should look like the following:



6.      Summarize the same data in another sheet named Totals &
Averages, so the total and average sales are displayed for all book
categories.

Organize the data by manager.

Display all values as currency with two decimal places. 

Make sure your labels in column C are displayed as they are in the
graphic below, and make sure all book categories are included.

Your finished worksheet should look as follows:



7.      In column C of the Div Awards sheet, create a formula to
calculate a bonus of 5% of the Grand Total if the Grand Total amount
exceeds 20,000.  If the manager Grand Total in column B does not exceed
20,000, the cell in column C should read No Bonus.

 Format the values in column C to display with dollar signs and two
decimal places.

Ma敫猠牵â�¥æ¡´â�¥æ½¢ç•®â�³ç�©æŒ æ±¡ç•£æ…¬æ•´â�¤æ½¦â�²æ±¡â�¬æ…­æ…®æ•§ç
�²à´®â¸¸ê‚ ê‚ â‚ æ…ƒæ�¬æ±µç‘¡â�¥æ¡´â�¥ç�¡ç‰¡â�¤æ•¬æ•¶ç�¬æ˜ ç‰¯æ” æ�¡â�¨æ
…­æ…®æ•§â�²æ¹©æŒ æ±¯æµµâ�®â±„æ„ â�³æ½¦æ±¬ç�¯ã©³

        If their grand total is below 20,000, they get no
Award.

        If their grand total falls between 20,000 and 40,000,
their award is Copper.

        If their grand total falls between 40,000 and 60,000,
their award is Bronze.

        If their grand total falls between 60,000 and 100,000,
their award is Silver.

        If their grand total is greater than 100,000, their
award is Gold.

Do not do these calculations manually, individually, or with nested If
statements.  Instead, use a formula that makes reference to the Award
table in columns I – K.

9.      Once you have determined the Award category in column D,
you can calculate the Award Amount in column E.  These award amounts
are indicated in the Add’l Bonus column of the Award table in columns
I – K.

The award amounts are as follows:

        If the award is Gold, 30% of the grand total amount is
calculated as the additional bonus.

 

        If the award is Silver, 15% of the grand total amount
is calculated as the additional bonus.

 

        If the award is Bronze, 10% of the grand total amount
is calculated as the additional bonus.

 

        If the award is Copper, 5% of the grand total amount
is calculated as the additional bonus.

 

        If there is No Award, there is no additional bonus.

You may calculate this any way you want, as long as you use a formula.

Make sure the Award Amount is calculated for all managers, and that the
values are displayed as currency with two decimal places.