Answer Happy

AstroCoffee Company AstroCoffee: Cynthia McHenry owns a coffeesupply company named AstroCoffee. She needs some help writing theformulas for the order form she uses to invoice customers. You willneed to write the formulas for all of the calculations on the form.Some of the more complex parts are determining if the customer willget a discount (based on the customer status) as well as theshipping charge (orders over $200 get free shipping). You will useIF functions for both of those calculations. When saving files forsubmission, include your last name in the file name.
Open the SC3 Data workbook from the Data Files and save theworkbook as SC3 AstroCoffee.
Enter the following order information: Order #: 45676 OrderDate: use a function that displays the current date
Enter the following Billing Information: Edwina Copeland 4270Heron Way Portland, OR 97225 [email protected]
For the Shipping Information, create formulas using cellreferences to display the corresponding information from theBilling Information section. For example, the Customer cell willdisplay the name of the customer in cell C11, the address from C12and so on. In the range B19:E22, enter the following item orders:Information for table Item # Description Qty Unit Price K56 DarkMocha K-Cups (12 pack) 1 10.99 G03 Decaf Dark Roast – Ground (1lb.) 3 12.99 B07 Dark Roast – Whole Bean (1 lb.) 2 13.99 K52 ChaiLatte K-Cups (12 pack) 3 12.99
In cell F19, enter an IF function that tests whether the orderquantity in cell D19 is greater than 0 (zero). lf it is, return thevalue of the Qty (in D19) multiplied by the Unit Price (in E19);otherwise, return no text by entering “”.
Copy/fill this formula into the other cells in the rangeF19:F25. Hint: be sure to copy the formula to all of the Item Totalcells, even if it is a blank row. You want the worksheet to beprepared for orders with more items in the future.
In cell F26, calculate the sum of all of the Item Totalcells.
In cell F27, use an IF function to calculate the discount amountfor this order based on the customer’s status (which is found inF16). If the customer’s status is Preferred, the discount amountwill be the Order Subtotal times the discount percentage found incell B29; otherwise, the discount amount will be 0 (zero). Hint:You will need to use a formula for the Value if True argument.
Calculate the Discounted Total for this order in cell F28. Hint:Use a simple subtraction formula.
In cell F29, use an IF function to display the correct ShippingCharge, based on the amount of the Discounted Total. If theDiscounted Total is greater than or equal to the Free ShippingMinimum found in cell B28, the Shipping Charge is 0 (zero);otherwise, the Shipping Charge is 5% of the Discounted Total. Hint:You will need to use a formula for the Value if False to calculatewhat 5% of the Discounted Total will be.
Calculate the Invoice Total in cell F31. Hint: This will be thetotal of the Discounted Total and the Shipping Charge.
Review the worksheet in Print Preview. Make any changes neededto make the worksheet print on one page.
Save the SC3 AstroCoffee workbook.
Submit the SC3 Astro Coffee workbook as directed by yourinstructor.
7 10 11 12 13 14 15 16 17 11 13 20 21 ܐܐ 29 24 25 26 27 20 29 31 31 32 ===== 33 34 95 36 37 AstroCoffee Company 2050 Hape Street, Saile 578 Parlland, OR 97281 Order: Billing Information Calamer: Adderan Phone: Email: Item # Description Special Offers 42 Shipping Minimes 1x Plus Ne [email protected] www.almaffer.com Order Dale: Shipping Information Canlam! Adderas: Slalan: Preferred Qty Unit Price Item Total Order Sablalal Dicennal Dissanaled Talal Shipping Charge Iesnier Talal H