Case 2 - Outdoor Store (35 marks)

Evelvn Cho is the owner of Cairn Outdoor Store, a small camping store she runs in Fort Smith. Arkansas. To help her manage her inventory and orders, she wants to develop an Excel worksheet for recording orders. The worksheet needs to calculate the cost of each order, including the cost of shipping and sales tax. Shipping costs vary based on whether the customer wants to use standard, three-day, two-day, or overnight shipping. Evelyn will also offer free shipping for orders that are more than $250. The shipping form worksheet will use lookup functions so that Evelyn can enter each product's ID code and have the name and price of the product automatically entered. into the form. Complete the following:

1. Open the Outdoor worksheet, enter the following sample order data: Customer Roger Dent; Order Number 6721; Order Date April 6, 2018 (NOTE: your computer will change the date format when you hit enter, just leave the changes) Street 414 Tapeak Lane: City Austin; State TX; ZIP 72914; Phone (619) 555-2081; and Delivery Type Standard
2. In cell B17, calculate the number of delivery days for the order using an exact match wookup function and the data in the range F5:H8 using the value of cell B16 as the lookup value.
3. In cell B18, ebtimate the date of weekday delivery by applying the WORKDAY function using the order date and number of days for delivery.
4. In cell D13, enter gps1587 as the initial item ordered by the customer. In cell G13, enter 2 as the number of items ordered.
5. In cell E13, enter an IF function that tests whether cell D13 is a blank cell. If true, the cell should remain blank. If false, apply an exact match VLOOKUP function and the Product List to return the name of the product ID entered in cell D13.
6. In cell F13, enter another IF function that tests whether cell D13 is a blank cell. If true, the cell should remain blank. If not, look up the price of the product ID entered in cell D13 using the appropriate function.
7. In cell H13, enter another IF function to test whether cell D13 is a blank cell. If so, the cell should remain blank; otherwise, calculate the Charge for the item based on its price and quantity purchased.

8. Copy the formula in the range EI3:F13 to the range E13:F20. Use AutoFill to copy the

   formula from cell H13 into the range I13:H20.

   1. In cell H22, calculate the sum of the values in the range H13:H20.
   2. In cell H23, calculate the sales tax using the Subtotal and the sales tax rate.
   3. In cell H24, calculate the shipping cost of the order by inserting an exact match

   *lookup function to return the shipping cost for the indicated Delivery Type.

   12. In cell H25, insert an IF function that tests whether the value of cell H22 is greater than

   250.If it is, return a value of cell H24; otherwise, return a value of 0.

   13. In cell H27, calculate the total cost of the order by summing the values in the range H22:H24 and subtracting the value of cell H25.

   Voice

   + Predictions: On

   1. Complete the customer order by adding the following items: Item sb3089 and Qty 2; Item t7815 and Qty 1; and Item bb7117 and Qty 5.
   2. Fit the worksheet to print on one page in landscape orientation. Enter your name in the lower left footer.
   3. Save your workbook.

7. In cell H13, enter another IF function to test whether cell D13 is a blank cell. If so, the cell should remain blank; otherwise, calculate the Charge for the item based on its price and quantity purchased. 8. Copy the formula in the range E13:F13 to the range E13:F20. Use AutoFill to copy the formula from cell H13 into the range H13:H20. 9. In cell H22, calculate the sum of the values in the range H13:H20. 10. In cell H23, calculate the sales tax using the Subtotal and the sales tax rate. 11. In cell H24, calculate the shipping cost of the order by inserting an exact match vlockup function to return the shipping cost for the indicated Delivery Type. 12. In cell H25, insert an IF function that tests whether the value of cell H22 is greater than 250. If it is, return a value of cell H24; otherwise, return a value of 0. 13. In cell H27, calculate the total cost of the order by summing the values in the range H22H24 and subtracting the value of cell H25. 3 14. Complete the customer order by adding the following items: Item sb3089 and Qty 2: Item t7815 and Qty 1; and Item bb7117 and Qty 5. 15. Fit the worksheet to print on one page in landscape orientation. Enter your name in the lower left footer. Evelyn Cho is the owner of Caim Outdoor Store, a small camping store she runs in Fort Smith, Arkansas, To help her manage her inventory and orders, she wants to develop an Excel worksheet for recording orders. The worksheet needs to calculate the cost of each order, including the cost of shipping and sales tax. Shipping costs vary based on whether the custoaser wants to use standard, three-day, two-day, or overnight shipping. Evelya will also offer free shipping for orders that are more than $250. The shipping form worksbet will use lookup functions so that Evelyn can enter each product's ID code and have the name and price of the product automatically sntered. into the form. Complete the following: 1. Open the Outdoor workshet, enter the following sample order data: Customer Roger Dent; Order Number 6721; Order Date April 6, 2018 (NOTE: your computer will change the date format when you hit enter, just leave the changes) Strect 414 Topeak Lane; City Austin; State TX; ZIP 72914; Phone (619) 555-2081; and Delivery Type Staadard. 2. In cell B17, calculate the number of delivery days for the order usiug an exact match ylookup function and the data in the range F5:H8 using the value of cell BI6 as the lookup value. 3. In cell B18, estimate the date of weekday delivery by applyiug the WORKDAY function using the order date and number of days for delivery 4. In cell D13, enter aps 1587 as the initial item ordered by the customer. In cell G13, enter 2 as the number of items ondered. 5. In cell E13, enter an IF function that tests whether cell D13 is a blank cell. If true, the cell should remain blank. If false, apply an exact match VLOOKUP function and the Product List to return the name of the product ID entered in cell D13. 6. In cell F13, enter another IF function that tests whether cell D13 is a blank cell. If true, the cell should remain blank. If not, look up the price of the product ID entered in cell D) 3 using the appropriate function. 7. In cell H13, enter another IF function to test whether cell D13 is a blank cell. If so, the cell should remain blank; otherwise, calculate the Charge for the item based on its price and quantity purchased. 8. Copy the formula in the range E13:F13 to the range E13:F20. Use AutoFill to copy the formula from cell H13 into the range H13:H20. 9. In cell H22, calculate the sum of the values in the range H13:H20. 10. In cell H23, calculate the sales tax using the Subtotal and the sales tax rate. 11. In cell H24, calculate the shipping cost of the order by inserting an exact match vlockup function to return the shipping cost for the indicated Delivery Type. 12. In cell H25, insert an IF function that tests whether the value of cell H22 is greater than 250. If it is, return a value of cell H24; otherwise, return a value of 0. 13. In cell H27, calculate the total cost of the order by summing the values in the range H22H24 and subtracting the value of cell H25. 3 14. Complete the customer order by adding the following items: Item sb3089 and Qty 2: Item t7815 and Qty 1; and Item bb7117 and Qty 5. 15. Fit the worksheet to print on one page in landscape orientation. Enter your name in the lower left footer. Evelyn Cho is the owner of Caim Outdoor Store, a small camping store she runs in Fort Smith, Arkansas, To help her manage her inventory and orders, she wants to develop an Excel worksheet for recording orders. The worksheet needs to calculate the cost of each order, including the cost of shipping and sales tax. Shipping costs vary based on whether the custoaser wants to use standard, three-day, two-day, or overnight shipping. Evelya will also offer free shipping for orders that are more than $250. The shipping form worksbet will use lookup functions so that Evelyn can enter each product's ID code and have the name and price of the product automatically sntered. into the form. Complete the following: 1. Open the Outdoor workshet, enter the following sample order data: Customer Roger Dent; Order Number 6721; Order Date April 6, 2018 (NOTE: your computer will change the date format when you hit enter, just leave the changes) Strect 414 Topeak Lane; City Austin; State TX; ZIP 72914; Phone (619) 555-2081; and Delivery Type Staadard. 2. In cell B17, calculate the number of delivery days for the order usiug an exact match ylookup function and the data in the range F5:H8 using the value of cell BI6 as the lookup value. 3. In cell B18, estimate the date of weekday delivery by applyiug the WORKDAY function using the order date and number of days for delivery 4. In cell D13, enter aps 1587 as the initial item ordered by the customer. In cell G13, enter 2 as the number of items ondered. 5. In cell E13, enter an IF function that tests whether cell D13 is a blank cell. If true, the cell should remain blank. If false, apply an exact match VLOOKUP function and the Product List to return the name of the product ID entered in cell D13. 6. In cell F13, enter another IF function that tests whether cell D13 is a blank cell. If true, the cell should remain blank. If not, look up the price of the product ID entered in cell D) 3 using the appropriate function