In: Computer Science
Exercise 3:
Create a worksheet that gives a price quote. You will select an item name and enter the quantity of that item purchased. Use Data Validation to create a drop down list for your item names. Your worksheet should look up the item name in the Price Table to find the Unit Price. Then it should calculate the Total Before Discount. Look up this amount in the Discount Table to find the discount percent. Then your worksheet should calculate the Discount Amount, the Total After Discount, add in Sales Tax (use an assumption for tax percentage), and give the final Total Due for the order. Place the Price Table, Discount Table and Sales Tax Percentage in the assumptions area.
Use the layout shown below starting your title in A4:
Price Quote (title)
Item: (input)
Quantity: (input)
Unit Price:
Total Before Discount:
Discount %:
Discount Amount:
Total After Discount:
Sales Tax:
Total Due:
(The price and discount information is shown below…you may need to reconfigure the layout to meet your needs. All information shown below needs to be included in your Lookup Tables, even if it is not used in the problem.)
Price Information:
Unit Price Item # Item Name
9.99 H5528 Hat
5.99 L8511 Lanyard
14.99 T3592 T-shirt
19.99 D7435 Drawstring Bag
Discount Information:
Total Price
Before Discount ($) Discount
$0 – $99.99 0%
$100 – $399.99 5%
$400 – $999.99 10%
$1000 and over 15%
Dear Seeker,
Please find the model completed,
Formulas used
References
You can also download the prepared model from below link of G Drive
https://docs.google.com/spreadsheets/d/1MaUdKzsYiciiRPRb9aC741GmlFzyoj6v1OTSczdepjw/edit?usp=sharing
Regards,