Question
Excel functions and tools used: VLOOLUP MAX, MIN, AVERAGE LARGE, SMALL, COUNT PRODUCT Your company (Precision Instruments, Inc.) has developed a new commission structure for
Excel functions and tools used:
VLOOLUP
MAX, MIN, AVERAGE
LARGE, SMALL, COUNT
PRODUCT
Your company (Precision Instruments, Inc.) has developed a new commission structure for salespersons. Commission rates will now be based on the amount of sales revenue generated on invoices. In addition, salespersons earn a $50 incentive bonus for all sales in the month to a new customer and a $25 incentive bonus for all sales in the month to a returning customer. (Returning customers are defined as customers that had not placed an order in the preceding six months.)
The new commission structure:
Total sales revenue | Commission Rate |
$1-1,000 | 1% |
$1,001-2,000 | 2% |
$2,001-10,000 | 4% |
$10,001+ | 6% |
Sales management has also asked you to provide some sales statistics for them.
Required:
- Download the COMMISSIONS workbook available on Canvas. Save it as COMMISSIONS-YOUR NAME. (Use your own first initial and last name.)
- Expand the data on the worksheet to include:
- Calculation of total sales revenue for each invoice.
- Calculation of the total invoice amount including sales tax at 8%.
- Calculation of commissions and incentive bonuses due on each invoice and total commission. You must use VLOOKUP functions in these calculations.
- Include column totals where appropriate.
- Create a separate worksheet of sales statistics that could be provided to sales management. The following information should be included in the worksheet:
- Number of invoices for the month.
- Average invoice amount for the month. (Invoice amount is the total including tax.)
- The three highest invoice amounts for the month.
- The lowest invoice amount for the month.
- Your worksheets should be formatted in a professional way, to include a report header on each worksheet.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started