Check that your exam has all pages!
2. If you make any assumptions, please express them clearly.
Answer the problems in the Excel Spreadsheet provided.
Name your sheet: “Last Name_First Name.xls”
3. This exam is explicitly intended for individual work only. You are forbidden to discuss your exam in any
form with the other persons.
4. This is a closed–book exam.
5. You must RETURN this exam sheet at the end of the exam;
Otherwise; your exam will NOT be graded.
6. Exam Submission:
• Submit your exam via Blackboard by 3:30pm. The TA will collect the physical exams.
7. TIME – You have a total of 150 minutes for this exam.
SAVE YOUR EXAM OFTEN!!
2 Problem 1 [25 points]: IITtelecomm, Inc. relies on a network of salespersons to sell its products across the
country. You have been asked for a workbook that summarizes key information about salespersons and their performance.
You may not change the structure of the table to conduct intermediate calculations.
a. [1 pt] Format the data in worksheet Problem 1 as an Excel table, so that it is easy to summarize, sort, edit, and query. Make sure the title is centered across the table.
b. [2 pts] Add a column to the table that shows the percentage of the quota fulfilled by each salesperson. For example, Bert Anderson has fulfilled 62% of his annual quota with YTD sales of $185,560. Label the column “% of Quota”. Format the column’s output using the percent style.
c. [5 pts] The entries in the “% of Quota” column give the user an immediate evaluation of where each salesperson stands in relation to his/her annual quota. Calculate a grade for each salesperson based on the following grade table. Place the results in a new column labeled “Grade.”
% of Quota Grade
>=90% A
>=80% (and <90%) B >=70% (and <80%) C >=60% (and <70%) D
<60% F d. [3 pts] Use conditional formatting to display an icon next to the % of Quota percentage for each Salesperson. In particular, use the 5 Arrows con Set to generate the following results. e. [4 pts] Calculate each salesperson’s bonus. To be eligible, a salesperson must have a customer rating of 6 or higher, and have a received at least a C grade (% of Quota > 70%). If eligible, a salesperson receives 2% of YTD Sales as a bonus. Your output should look as follows:
3 Use COUNTIF, SUMIF, and/or AVERAGEIF to answer the following questions:
f. [4 pts] What are the average and total Bonus Amounts paid by region ? Place results in a table starting in cell P3.
Average Bonus Total
Bonus
North
East
West
South
g. [4 pts]Provide a breakdown by gender of salespersons who have received a customer rating of 8 or higher, and a grade of A. Also provide the Bonus totals for each group. Place results in table starting in cell P10.
Last Completed Projects
| topic title | academic level | Writer | delivered |
|---|
