09 Jul Excel Spreadsheet (Accounting)

Assignment 8

Tiger Company sells used cars. Tiger has 3 differentlocations and 12 total salespeople. Data about each salesperson is as follows:

All of the required formulas must use at least one functionfrom the material in chapter 11 that was covered in class on Monday, October 5.You may use the numbers listed in the required items in the functions.

Required:

        Enter the above data, as shown, starting withSalesperson in cell F1.

        For items 1-15, use the number below as a labelin column B (cells B1:B15) and put the formula that calculates the answer incolumn C (cells C1:C15).

1.     What is the total years of experience of thesalespeople working at the Montgomery location?

2.     How many cars were sold by salespeople with lessthan 8 years of experience?

3.     Calculate the total price of all cars sold bysalespeople who sold 15 or more cars, and round the total price to the nearestdollar.

4.     Calculate the total price of all cars sold byTiger Company and round the total price to the nearest $1,000.

5.     Determine the remainder (modulus) from dividingthe total price of all cars sold by Tiger Company by the total number of carssold by Tiger Company.

6.     How many of the salespeople work at theMontgomery location?

7.     How many of the salespeople have more than 5years of experience and sold at least 15 cars?

8.     What is the average number of cars sold at the Elmorelocation?

9.     What is the median of the total price of all carssold?

10.  Whatwas the 4^th largest number of cars sold?

11.  Whatis the 3^rd smallest years of experience?

12.  Calculatethe variance of the number of cars sold assuming that the above list is theentire population.

13.  Calculatethe standard deviation of the total price of all cars sold assuming that theabove list is a sample.

14.  Calculatethe average number of cars sold by employees at the Opelika location and haveless than 10 years of experience.

15.  Whatis the mode of the years of experience?

        Use the Data Analysis ToolPak to create thesummary statistics for the number of cars sold and the total price of all carssold on a new worksheet named Summary. The worksheet Summary must be the lastworksheet in the workbook.

Remember to follow all items in the Excel Requirementsdocument.