Question

Suppose that a company offers quantity discounts. If up to 1000 units are purchased, the unit price is $10; if more than 1000 and up to 5000 units are purchased, the unit price is $9; and if more than 5000 units are purchased, the unit price is $7.50. Develop a spreadsheet using the VLOOKUP function to find the unit price associated with any or-der quantity and compute the total cost of the order.

S&S No. 106 INVOICE Customer G. Kite Invoice Date: 1/23/19 40 Quatro Road Mesa, AZ 85050 Shi Freight charges: Interstate Carriers Purchase Order order # 2463 Prepaid Item 10 30 80 tion uantity sh Unit Price Extension Widgets Red Widgets Blue Widgets-White 750-VLOOKUP0 3500 5200 nter the problem information on the Pricing sheet and obtain the unit price using the Vlookup command formulas to multiply quantity price to obtain the extension and then total the invoice. You should have ata in all of the yellow cells Total Sale Sold by: S. Knight Suppose that a company offers quantity discounts If up to 1000 units are purchased, the unit price is $10; if more than 1000 and up to 5000 units are purchased, the unit price is S9; and if more than 5000 units are purchased, the unit price is $7.50 Develop a spreadsheet using the VLOOKUP function to find the unit price associated with any or der quantity and compute the total cost of the order

Answer 1

working notes					Answer
Order quantity	Price			Items	Description	Quantity Shipped	Unit Price	Extension
0	10.00			10	widgets-Red	750	10.00	7500
1000	9.00			30	widgets-Blue	3500	9.00	31500
5000	7.50			80	widgets-White	5200	7.50	39000
							TOTAL SALE	78000
Formula used
=VLOOKUP(E3,$A$3:$B$5,2,TRUE)			for unit price
=VLOOKUP(E4,$A$3:$B$5,2,TRUE)			for unit price
=VLOOKUP(F5,$A$3:$B$5,2,TRUE)			for unit price
=G6*H6	for extension
=G7*H7	for extension
=G8*H8	for extension
=SUM(I6:I8)	for total sale