Excel worksheet

PROJECT 11: Calculating a Discount

Key Concept: The VLOOKUP() Function
Secondary Concept: Absolute Addresses, Formatting

The Problem: In this project you are to complete and format the following table. You will need to use the VLOOKUP() function and a Lookup Table to determine the values in Column E.

A B C D E F G
1
2 Customer Units Cost per Unit Amount Discount Percentage Discount Amount Amount after Discount
3 Bronson 1125 25
4 Stern 900 20
5 Qin 1125 98
6 Adams 2008 46
7 Yoon 675 73
8 Gilbert 1200 30
9 Sullivan 300 18
10 Total:
11 Average:
12 Maximum:
13
14 Lookup Table
15 Amount Discount Percentage
16 0 0%
17 10000 2%
18 25000 4%
19 40000 6%
20 80000 8%
21 100000 10%

Relationships:

1.) Amount = Units x Cost per Unit
2.) Discount Percentage: vlookup function
3.) Discount Amount = Amount x Discount Percentage
4.) Amount after Discount = Amount – Discount Amount
5.) Use the SUM, AVERAGE, and MAX functions to calculate the Total, Average, and Maximum amounts.
6.) Make sure to format your spreadsheet so that all dollar amounts have a leading $ symbol.
(Continued on Reverse Side —>)

Here is how your spreadsheet should look when it is completed:

Customer Units Cost per Unit Amount Discount Percentage Discount Amount Amount After Discount
Bronson 1125 $25 $28,125 0.04 $1,125 $27,000
Stern 900 $20 $18,000 0.02 $360 $17,640
Qin 1125 $98 $110,250 0.1 $11,025 $99,225
Adams 2008 $46 $92,368 0.08 $7,389 $84,979
Yoon 675 $73 $49,275 0.06 $2,957 $46,319
Gilbert 1200 $30 $36,000 0.04 $1,440 $34,560
Sullivan 300 $18 $5,400 0 $0 $5,400
Total: $24,296 $315,122
Average: $3,471 $45,017
Maximum: $11,025 $99,225

Lookup Table
Amount Discount Percentage
0 0%
10000 2%
25000 4%
40000 6%
80000 8%
100000 10%

We are always aiming to provide top quality academic writing services that will surely enable you achieve your desired academic grades. Our support is round the clock!

Type of paper Academic level Subject area
Number of pages Paper urgency Cost per page:
 Total: