A B C D E F G Exercise: A company sells 6 electronics products in its 9 store locations. The prices are listed to the right. Depending on the total price of the products purchased, the customer gets a discount based on the discount schedule shown. For example, if the total price is at least $1,000 but 4 less than $3,000, the discount is 2%. Then, depending on the store location, the salesperson gets a commission on the total price after the UT discount. To create an invoice for a particular customer (and calculate the commission for internal purposes), proceed as follows. 6 1. Add a data validation for cell B20, so that one of the 9 store locations can be chosen from a list. Make sure the validation is linked to the store location list in column I. Then choose any store location for cell B20. 8 9 2. Enter nonnegative integers in row 24 to indicate the number of items of each product purchased by the customer. 10 11 3. Use formulas to calculate the prices in row 25, and then use appropriate formulas to calculate the values in cells B27, B28, and B32. Hint: For 12 row 25, make sure you refer to the product indexes in row 20 in lookup formulas. 13 14 15 16K M N O P Unit prices of products at a company's 9 store locations Store Location 32" TV Laptop 19" monitor DVD recorder Camcorder Home theater Bloomington $487.99 $706.99 $381.99 $252.99 $990.99 $533.99 Indianapolis $498.99 $692.99 $400.99 $241.99 $979.99 $524.99 Columbus $463.99 $704.99 $372.99 $253.99 $1,035.99 $520.99 Fort Wayne $507.99 $739.99 $417.99 $246.99 $1,024.99 $535.99 Evansville $474.99 $664.99 $395.99 $247.99 $1,040.99 $540.99 Muncie $499.99 $655.99 $364.99 $254.99 $1,016.99 $528.99 Terre Haute $499.99 $713.99 $397.99 $232.99 $1,020.99 $524.99 West Lafayette $504.99 $727.99 $387.99 $256.99 $973.99 $535.99 Richmond $471.99 $722.99 $410.99 $242.99 $989.99 $517.99 Discount rate, based on total price of order Commission rate to sales people (depends on store location) Total price Discount Bloomington 4% $0.00 0% Indianapolis 3% $2,000.00 2% Columbus 3% $5,000.00 4% Fort Wayne 3% $10,000.00 7% Evansville 3% Muncie 3% Terre Haute 4% West Lafayette 3% Richmond 4%A B C D E F G 14 15 16 17 18 19 Invoice 20 Store Columbus 21 22 Product 32" TV Laptop 19" monitor DVD recorder Camcorder Home theater 23 Product index 1 2 3 4 5 6 24 Number purchased 1 H 25 Total Price 26 27 Total price before discount $0.00 28 Discount 29 Total price after discount $0.00 30 31 For internal purposes 32 Commission to salesperson ($)K B G N A C F H M O P D E Exercise: A company sells 6 electronics products in its 9 store locations. The prices are listed to the right. Depending on the total price of the Unit prices of products at a company's 9 store locations Store Location Laptop 19" monitor DVD recorder Home theater WNI 32" TV Camcorder products purchased, the customer gets a discount based on the discount schedule shown. For example, if the total price is at least $1,000 but $990.99 $533.99 less than $3,000, the discount is 2%. Then, depending on the store location, the salesperson gets a commission on the total price after the Bloomington $487.99 $706.99 $381.99 $252.99 $979.99 $524.99 discount. To create an invoice for a particular customer (and calculate the commission for internal purposes), proceed as follows. Indianapolis $498.99 $692.99 $400.99 $241.99 Columbus $463.99 $704.99 $372.99 $253.99 $1,035.99 $520.99 1. Add a data validation for cell B20, so that one of the 9 store locations can be chosen from a list. Make sure the validation is linked to the store Fort Wayne $507.99 $739.99 $417.99 $246.99 $1,024.99 $535.99 location list in column I. Then choose any store location for cell B20 Evansville $474.99 $664.99 $395.99 $247.99 $1,040.99 $540.99 Muncie $499.99 $655.99 $364.99 $254.99 $1,016.99 $528.99 2. Enter nonnegative integers in row 24 to indicate the number of items of each product purchased by the customer. Terre Haute $499.99 $713.99 $397.99 $232.99 $1,020.99 $524.99 10 West Lafayette $504.99 $727.99 $387.99 $256.99 $973.99 $535.99 11 3. Use formulas to calculate the prices in row 25, and then use appropriate formulas to calculate the values in cells B27, B28, and B32. Hint: For $471.99 $722.99 $410.99 $242.99 $989.99 $517.99 12 row 25, make sure you refer to the product indexes in row 20 in lookup formulas. Richmond 13 Discount rate, based on total price of order Commission rate to sales people (depends on store location) 14 Total price Discount Bloomington 4% 15 $0.00 0% Indianapolis 3% 16 $2,000.00 2% Columbus 3% 17 $5,000.00 4% Fort Wayne 3% 18 $10,000.00 7% Evansville 3% 19 Invoice Muncie 3% 20 Store Columbus Terre Haute 4% 21 3% 22 Product 32" TV Laptop 19" monitor DVD recorder Camcorder Home theater West Lafayette 4 5 6 Richmond 4% 23 Product index 1 2 3 24 Number purchased 1 1 1 1 1 25 Total Price 26 27 Total price before discount $0.00 28 Discount 29 Total price after discount $0.00 30 31 For internal purposes 32 Commission to salesperson ($)