You are provided with the database that tracks customer orders for various furniture products manufactured by Lone Pine Furniture (LPF) company. LPF database provides information on their customers, product line, product description, product finish, order quantity and price.

1. Calculate the total order amount for each customer in column Total Order Amount. Then calculate the sales tax in $$ for each order based on the sales tax percentage in column Sales Tax. Lastly, calculate the Final Order with tax which is sum of total order amount and the sales tax amount for each customer record.

2. A product category is simply the second word from the product finish field. For example, product category for product finish Natural Maple, Smooth would be the second word Maple. Using the correct Excel function, extract the Product Category for each record.

[SEARCH, MID]

3. How many customers placed orders with order quantities between 25 and 48 (both inclusive) after 4/15/2012? [DCOUNT]

4. What is the total order quantity for customers who are buying either tables (i.e. any kind of tables) or computer desk? [DSUM]

5. What was the minimum order quantity for orders placed by companies in New Jersey (NJ) or California (CA), and with order dates between 2/01/2011 and 4/30/2012, both dates inclusive? [DMIN]
6. What is the total order quantity for Scandinavian desks with smooth finish? [DSUM]

7. Find the percent rank of the selling price for client who became customer on 10/17/2010. [PERCENTRANK,DGET]

8. Company wants to reward its most profitable customers by giving them a discount and wants to calculate the Discounted Selling Price for the qualifying order. Marketing manager, Monica, uses the following rule to make this decision for each record in DB:

If the customer is Contemporary Casuals, they get a discount of 15% on selling price regardless of order quantity.

For all other customers, if the order quantity is at least 10 and they buy dining tables; then the selling price is discounted by 10%,

In all other cases, there is no discount on the selling price.

Write an IF statement in Column Discounted Selling Price that will calculate the new discounted selling price according to the above rule. [IF, AND]

Save frequently.

9. How many customers living in FL (i.e. FL state) either order product line name cherry tree or country look? [DCOUNTA]

10. How many customers (see customers name) are there with last letter of their name as letter "s" and either living in FL or CA (see state)? [DCOUNTA, WILDCARD]

11. In Shipment Date, calculate the shipment date as 2 months and 3 days from the date of order for each record. [DATE,YEAR,MONTH,DAY]

12. In the LOOKUP sheet, create a drop down menu in cell D10 by selecting order number from database provided in LPF worksheet. Using this as the lookup value, you should be able to automatically lookup additional information. [VLOOKUP]

13. Create a pivot table to summarize data by customer name, the table will show the average quantity sold to this customer and the sum of order amount (before taxes) in dollars and no decimal points.

Bonus Question:

On the 4^th of July, The Company has decided to make an offer for customers with names starting with the letter A or C or worth more than $5000 (before taxes). In the column Discount Applicable, if an order satisfies the above conditions, you should display the text Applicable otherwise, display the text NA.