MidasCoffee Company

Download Data File: SC3 data 

MidasCoffee: Ruth Kobran owns a coffee supply company named MidasCoffee. She needs some help writing the formulas for the order form she uses to invoice customers. You will need to write the formulas for all of the calculations on the form. Some of the more complex parts are determining if the customer will get a discount (based on the customer status) as well as the shipping charge (orders over $199 get free shipping). You will use IF functions for both of those calculations.

1. Open the SC3 Data workbook and save the workbook as SC3 MidasCoffee.
2. Enter the following order information:
   Order #: 56894
   Order Date: use a function that displays the current date
3. Enter the following Billing Information:
   Samantha Raitt
   4270 SW Cooper Ln, Portland, OR 97225
   503-674-1632
   samantha.raitt@zmail.com
4. For the Shipping Information, create formulas using cell references to display the corresponding information from the Billing Information section. For example, the Customer cell will display the name of the customer in cell C11.
5. In the range B19:E22, enter the following item orders:
   

   Item #	Description	Qty	Unit Price
   K56	Dark Mocha K-Cups (12 pack)	1	11.99
   G03	Decaf Dark Roast – Ground (1 lb.)	3	12.99
   B07	Organic Dark Roast – Whole Bean (1 lb.)	2	14.99
   K52	Chai Latte K-Cups (12 pack)	3	10.99

6. In cell F19, enter an IF function that tests whether the order quantity in cell D19 is greater than 0 (zero). lf it is, return the value of the Qty (in D19) multiplied by the Unit Price (in E19); otherwise, return no text by entering “”. Hint: You will need to use a formula for the Value if True argument.
7. Copy/fill this formula into the other cells in the range F19:F25. Hint: be sure to copy the formula to all of the Item Total cells, even if it is a blank row. You want the worksheet to be prepared for orders with more items in the future.
8. In cell F26, calculate the sum of all of the Item Total cells.
9. In cell F27, use an IF function to calculate the discount amount for this order based on the customer’s status (which is found in F16). If the customer’s status is Preferred, the discount amount will be the Order Subtotal times the discount percentage found in cell B29; otherwise the discount amount will be 0 (zero). Hint: You will need to use a formula for the Value if True argument. 
10. Test your IF function to make sure that it still works if the customer is NOT preferred by deleting the word Preferred in F16.  Make sure you do not end up with an error message! If you get an error message, check the IF function and make the changes needed.
11. Calculate the Discounted Total for this order in cell F28. Hint: Use a simple subtraction formula.
12. In cell F29, use an IF function to display the correct Shipping Charge, based on the amount of the Discounted Total. If the Discounted Total is greater than or equal to the Free Shipping Minimum 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 calculate what 5% of the Discounted Total will be.
13. Calculate the Invoice Total in cell F31. Hint: This will be the total of the Discounted Total and the Shipping Charge.
14. Take a critical look at your worksheet to ensure that all of the number and cell formatting is professional.
15. Review the worksheet in Print Preview. Make any changes needed to make the worksheet print on one page.
16. Check the spelling on all of the worksheets and make any necessary changes. Save the SC3 MidasCoffee workbook.
17. Submit the SC3 Midas Coffee workbook as directed by your instructor.

After reading the advanced section on using Power Tools and creating Macros, complete the next section:

18. Reopen the SC3 Midas Coffee assessment.  Apply a Power Tool of your choice to the assessment. Include your rationale for using this tool for your assessment. Save it as Assessment 3B with your name and turn it in as directed by your instructor.

19. Reopen the SC3 Midas Coffee assessment. Record a macro that changes the item numbers to the color red.

Attribution

“3.6 Chapter Scored” by Noreen Brown, Art Schneider and Mary Schatz and Jennifer Evans, Portland Community College is licensed under CC BY 4.0