Profit sharing percentage and avg

Case Study Description and Assignment Tasks

John Steed's Online Emporium (JSOE) is an online retailer that sells over 50 products to customers mainly based in Australia. Data has been obtained from JSOE's transaction system and placed into a Microsoft Access database file. The database contains: Product, Customer and Sales data.

Solution:

Picture above is the screenshot of excel window containing all the calculation required to answer the above question. In this we are assuming that Tara King will drop the product which has the minimum profit. Here I am considering average of profit percentage (profit percentage = (sell-cost)/cost)). So the list of products which Tara king will drop will be- Teavine, Chillinnium, Pingzilla, Flipher, Tubalum, Collarama. The same is highlighted in table and graph in the image.

From the Table 1, it is clear that products dropped by Tara King can be wrong choice, as those products are having comparatively good profit sharing. Here, profit sharing is the share of particular product’s profit in total profit. So, as per the profit sharing percentage Tara King can choose products from Table 2 for dropping, as they are having least profit sharing.

2. A few months ago, a column named CustValueRating was added to the customer table. The rating values are intended to reflect the 'value' of the customer to the business based on sales history. A score of 1 indicates that the customer is highly valued while a score of 4 indicates that the customer is in the group that should be least valued by the JSOE. Emma Peel, JSOE's leading sales person has doubted the validity of the values in the database.

2. If Emma’s doubting is correct, create a new CustValueRating value and show the top 20 (or thereabouts) customers. Describe the method used to calculate new values.

Solution: