COAP 117 – LO4 ASSIGNMENT (DISTANCE)
Open the file Greasy Bean Coffee.xlsx
- You want to examine four different options for a promotional discount. On this file are four separate, but identical worksheets for you to calculate each of these options: (1a, 1b, 1c, 1d). Create an IF function in the Discount column to calculate the discount on each of these sheets given the criteria below. [3 marks each] [12]
- 5% Discount on the Total Sale amount where the Total Sale was over $100.
- 10% Discount on the Total Sale amount where the Item Class category was Equipment.
- 50% Discount on the Item Price for any Quantity over 4. Ex. if an Item Price was $5 and if 7 items were purchased, 3 would receive a Discount of $7.50 (3 x 5.00 x 50%).
- 8% Discount on the Total Sale amount where the Item Class was Tea, a 5% Discount on the Total Sale amount where the Item Class was Coffee. Otherwise, no discount.
- On the worksheet Parts 2-4, create a single Pivot Table that provides the Total Sales ($), Number of Sales, % of Sales, Average Sale and Average Price ($) for each Item Class. Format as shown below. [17]
| Item Class | Total Sales | # of Sales | % of Sales | Average Sale | Average Price |
|---|---|---|---|---|---|
| Coffee | |||||
| Equipment | |||||
| Gift | |||||
| Tea | |||||
| Totals |
- Create a table similar to the Pivot Table in Part 2 above. Your goal is to create the same answers you achieved in Question 2, but this time using the appropriate Excel function (COUNTIF, SUMIF, AVERAGEIF or formula). Place this below the Pivot table. [15] ** Note: your answers in the Totals row for the Average Sale and Average Price will be slightly different than the Pivot Table, as it uses a true weighted average. You can omit the Totals row calculation for these two if you like.
- Use a function to find the following. Place this information below Part 3. Label the information so it is clear. You can also use a label as your criteria.
- The average sale amount ($) when the Quantity was over 4. [3]
- The number of sales where the Total Sale amount was over $100 [3]
- Submit your completed file in the Assignment 5 – LO4 Assignment Dropbox.