Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

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:

  1. Download the COMMISSIONS workbook available on Canvas. Save it as COMMISSIONS-YOUR NAME. (Use your own first initial and last name.)
  2. Expand the data on the worksheet to include:
    1. Calculation of total sales revenue for each invoice.
    2. Calculation of the total invoice amount including sales tax at 8%.
    3. Calculation of commissions and incentive bonuses due on each invoice and total commission. You must use VLOOKUP functions in these calculations.
  3. Include column totals where appropriate.
  4. Create a separate worksheet of sales statistics that could be provided to sales management. The following information should be included in the worksheet:
    1. Number of invoices for the month.
    2. Average invoice amount for the month. (Invoice amount is the total including tax.)
    3. The three highest invoice amounts for the month.
    4. The lowest invoice amount for the month.
  5. 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

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Public Finance

Authors: Richard W. Tresch

4th Edition

0128228644, 978-0128228647

More Books

Students also viewed these Finance questions

Question

Conduct a needs assessment. page 269

Answered: 1 week ago