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 (add columns/rows) 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 ($299,290) 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
>=90% Grade
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 (colored) Icon 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:
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 (North, East, West, and South)? Place results in a table (see format below) starting in cell P3.
Average Bonus Total
Bonus
North
East
West
South
g. [4 pts]Provide a breakdown by gender (Male, Female) 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.
Grade = A, Customer
Rating >= 8 Count Bonus
Female
Male
SAVE YOUR EXAM!
Problem 2 [20 points]: Benjamin Johnson owes $450,786.70 in his home mortgage. He has decided to refinance his existing mortgage into a Chase Bank Mortgage. Chase charges 3 point in closing costs. That is, Benjamin’s new mortgage with Chase Bank will equal to his current mortgage, plus 3% of that amount.
Because this new mortgage has a much lower interest rate than most of Benjamin’s current mortgage, he’s considering refinancing.
The APR associated with the Chase Bank mortgage is 3.5%, compounded monthly.
a. [2 pts] Assume Benjamin goes through with the refinancing. How much will Benjamin pay monthly to to Chase card if he wants to pay off the mortgage in 30 years?
b. [4 pts] Suppose Benjamin decides that he can afford to make payments of $2,450 per month (not the answer to part a). How many months will this reduce from his repayment timeline?
c. [4 pts] How much more less interest will Benjamin pay over the lifetime of the loan if he chooses to pay $2,450/month rather than the answer from part a?
d. [5 pts] Create a two-variable data table that shows how much less interest Benjamin will have to pay by choosing to make monthly payments of $2,450 rather than making payments to pay off the set number of years (as he did in part a, where the number of years was 30). In other words, vary the loan term to pay off the Chase card to be between 15 and 40 years, in increments of 5 years, and allow the closing points fee to vary between 2.5% and 6%, in increments of 0.5%.
Appropriately format the column headers.
e. [5 pts] Create an amortization schedule for the transfer, assuming Benjamin will make the payments as found in part a, with a loan term of 30 years.
SAVE YOUR EXAM!
Problem 3 [25 points]: IITtelecomm, Inc. is a reseller of Internet Protocol (IP)-based networking technologies. The company sells a wide range of networking equipment, including routing, switching, and other technologies from various original equipment manufacturers (OEMs). Worksheet Problem 3 contains the sales price and quantity sold (by month) for all parts offered by IITtelecomm in 2021.
a. For any part number selected in cell B5 (selected using the drop-down menu in that cell), report the average dollar value sold per month for that part in cell C5. NOTE: No intermediate (additional) cells may be used (created) to perform the calculation.
b. For any month selected in cell B8 (again, note use of drop-down menu), report the average number of units sold for that month in cell C8. No intermediate (additional) cells may be used (created) to perform the calculation.
c. In cell C11, report the dollar value sold for the selected part (in B5), for the given month (B8). No intermediate (additional) cells may be used (created) to perform the calculation.
d. Create labels in cells C4, C7, and A11 as follows:
C4 should display the label: “Average $ Value Sold of” followed by the selected Part Number in quotes. For example:
C7 should display the label: “Average QTY sold in” + selected Month
A11 should display the label: "Dollar Value Sold of Part:” + selected Part Number (in quotes) + “in” + selected Month.
CS 340 Milestone One Guidelines and Rubric Overview: For this assignment, you will implement the fundamental operations of create, read, update,
Retail Transaction Programming Project Project Requirements: Develop a program to emulate a purchase transaction at a retail store. This
7COM1028 Secure Systems Programming Referral Coursework: Secure
Create a GUI program that:Accepts the following from a user:Item NameItem QuantityItem PriceAllows the user to create a file to store the sales receip
CS 340 Final Project Guidelines and Rubric Overview The final project will encompass developing a web service using a software stack and impleme
