Case 4
Data File needed for this Case Problem: Cairn.xlsx Cairn Camping Supplies Diane Cho is the owner of Cairn Camping Supplies, a small camping store she runs out of her home 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. Diane will also offer free shipping for orders that are more than $250. The shipping form worksheet will use lookup functions so that Diane can enter each product's ID code and have the name and price of the product automatically entered into the form. To keep the worksheet clean without distracting error values when no input values have been entered, you'll use IF functions to test whether the user has entered a required value first before applying a formula using that value. Complete the following: 1. Open the Cairn workbook located in the Excel 3 > Case4 folder, and then save the workbook as Cairn Camping in the location specified by your instructor. 2. In the Documentation worksheet, enter your name in cell B3 and the date in cell B4. 3. In the Order Form worksheet, enter the following sample order data: Customer Dixie Kaufmann; Order Number 381; Order Date 4/5/2018; Street 414 Topeak Lane; City Fort Smith; State AK; ZIP 72914; Phone (479) 555-2081; and Delivery Type 3 Day. Explore 4. In cell B17, calculate the number of delivery days for the order. Insert an IF function that first tests whether the value in cell B16 is equal to an empty text string ("'I'). If it is, return an empty text string; otherwise, apply a lookup function to retrieve the lookup value from the table in the range F5:H8 using the value of cell B16 as the lookup value. Explore 5. In cell B18, estimate the date of weekday delivery by inserting an IF function that tests whether cell B16 is equal to an empty text string. If it is, return an empty text string, otherwise apply the WORKDAY function using the values in cell B6 as the starting date and cell B17 as the number of days. 6. In cell D13, enter p4981 as the initial item ordered by the customer. In cell G13, enter 2 as the number of items ordered. 7. In cell E13, enter an IF function that tests whether the value in cell D13 is equal to an empty text string. If true, return an empty text string. If false, apply the VLOOKUP function to return the name of the product ID entered into cell D13. 8. In cell F13, enter another IF function that tests whether the value in cell D13 is equal to an empty text string. If true, return an empty text string. If false, return the price of the product ID 9. In cell H13, enter another IF function to test whether the value in cell D13 is equal to an empty text string. If true, return an empty text string; otherwise, calculate the value of the price of the item multiplied by the number of items ordered. 10. 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. 11. In cell H22, calculate the sum of the values in the range H13:H2O. 12. In cell H23, calculate the sales tax equal to the total cost of the items ordered multiplied by the sales tax rate in cell G10. 13. In cell H24, calculate the shipping cost of the order by inserting an IF function that tests whether the value of cell B16 is an empty text string. If it is, return the value 0 ; otherwise, use a lookup function to return the shipping cost for the indicated shipping method. 14. In cell H25, insert an IF function that tests whether the value of cell H22 is greater than 250 (the minimum order needed to qualify for free shipping). If it is, return a value of cell H24; otherwise, return a value of 0 . 15. 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. 16. Complete the customer order by adding the following items: Item t7829 and Qty 1; Item led7331 and Qty 3; and Item sb8502 and Qty 5. 17. Confirm that your worksheet correctly calculates the total cost, and then save your workbook. 18. Save the workbook as Cairn Order Form in the location specified by your instructor. 19. Create a blank order form sheet by deleting the input values in the ranges B4:B6,B9:B13,B16, D13:D16, G13:G16. Do not delete any formulas in the worksheet. Confirm that the worksheet does not show any error values when the input data is removed. 20. Save and close the workbook. Data File needed for this Case Problem: Cairn.xlsx Cairn Camping Supplies Diane Cho is the owner of Cairn Camping Supplies, a small camping store she runs out of her home 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. Diane will also offer free shipping for orders that are more than $250. The shipping form worksheet will use lookup functions so that Diane can enter each product's ID code and have the name and price of the product automatically entered into the form. To keep the worksheet clean without distracting error values when no input values have been entered, you'll use IF functions to test whether the user has entered a required value first before applying a formula using that value. Complete the following: 1. Open the Cairn workbook located in the Excel 3 > Case4 folder, and then save the workbook as Cairn Camping in the location specified by your instructor. 2. In the Documentation worksheet, enter your name in cell B3 and the date in cell B4. 3. In the Order Form worksheet, enter the following sample order data: Customer Dixie Kaufmann; Order Number 381; Order Date 4/5/2018; Street 414 Topeak Lane; City Fort Smith; State AK; ZIP 72914; Phone (479) 555-2081; and Delivery Type 3 Day. Explore 4. In cell B17, calculate the number of delivery days for the order. Insert an IF function that first tests whether the value in cell B16 is equal to an empty text string ("'I'). If it is, return an empty text string; otherwise, apply a lookup function to retrieve the lookup value from the table in the range F5:H8 using the value of cell B16 as the lookup value. Explore 5. In cell B18, estimate the date of weekday delivery by inserting an IF function that tests whether cell B16 is equal to an empty text string. If it is, return an empty text string, otherwise apply the WORKDAY function using the values in cell B6 as the starting date and cell B17 as the number of days. 6. In cell D13, enter p4981 as the initial item ordered by the customer. In cell G13, enter 2 as the number of items ordered. 7. In cell E13, enter an IF function that tests whether the value in cell D13 is equal to an empty text string. If true, return an empty text string. If false, apply the VLOOKUP function to return the name of the product ID entered into cell D13. 8. In cell F13, enter another IF function that tests whether the value in cell D13 is equal to an empty text string. If true, return an empty text string. If false, return the price of the product ID 9. In cell H13, enter another IF function to test whether the value in cell D13 is equal to an empty text string. If true, return an empty text string; otherwise, calculate the value of the price of the item multiplied by the number of items ordered. 10. 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. 11. In cell H22, calculate the sum of the values in the range H13:H2O. 12. In cell H23, calculate the sales tax equal to the total cost of the items ordered multiplied by the sales tax rate in cell G10. 13. In cell H24, calculate the shipping cost of the order by inserting an IF function that tests whether the value of cell B16 is an empty text string. If it is, return the value 0 ; otherwise, use a lookup function to return the shipping cost for the indicated shipping method. 14. In cell H25, insert an IF function that tests whether the value of cell H22 is greater than 250 (the minimum order needed to qualify for free shipping). If it is, return a value of cell H24; otherwise, return a value of 0 . 15. 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. 16. Complete the customer order by adding the following items: Item t7829 and Qty 1; Item led7331 and Qty 3; and Item sb8502 and Qty 5. 17. Confirm that your worksheet correctly calculates the total cost, and then save your workbook. 18. Save the workbook as Cairn Order Form in the location specified by your instructor. 19. Create a blank order form sheet by deleting the input values in the ranges B4:B6,B9:B13,B16, D13:D16, G13:G16. Do not delete any formulas in the worksheet. Confirm that the worksheet does not show any error values when the input data is removed. 20. Save and close the workbook
