ANALYSIS
I carried out an analysis of 10 Sales staff who sold 22 chocolate products in 6 countries. 45,660 units were sold, generating a total amount of $1.24 million with a total cost of about $440K and a profit of about $801K.
Firstly, I used Vlookup to get the Cost per unit, then multiplied it by the number of units sold.
Next, I analyzed sales by country with formulas and pivot tables (included a slicer for sales staff). The country that generated the most amount is India with $252,469 which is 20.35% of the total value while Australia generated the least amount representing 13.6%. The USA sold 22.25% units of chocolates making it the highest-selling country.
On further analysis, the top product by profit % is Eclairs with 88.6%, it has a low cost of $7,249. 85% Dark bar is the second top product with 85.3%.
Across the 6 countries, Gigi Bohling is the top salesperson in 3 countries namely; Australia, Canada and India. Australia has the least sales of $25,221, the UK has the most sales with $45,752 by Barr Faughny. On the opposite end, Barr Faughny is part of the least salesperson across the 6 countries, the USA being the overall lowest with $2,142. Brien Boise is the least sales person in Canada and India.
Drilling further with a slicer shows products with the poorest sales in different countries. Organic Choco syrup had a profit of -$2,369.9 in Australia, Baker’s Choco chips and Caramel stuffed bars in Canada both had profits of -$1,044.4 and -$3,062. In India, Milk bars had a profit of -$251.8 while Almond Choco had a profit of -$5,610.2. The product with the poorest sale in the UK is White Choc with -$5,389.2 as profit and the profit of Baker’s Choco chips and Spicy special slims are -$792.4 and -$1,485 in the USA respectively.
DASHBOARD
RECOMMENDATIONS
Some products may need to be discontinued in some countries because sales are very poor; Investigation should be done to know the reason(s) for the poor sales of some of the products in the different countries.
Eclairs have a high-profit percentage in all countries this is because the amount of cost is low; cost reduction strategies should be applied to some products and marketing strategies should be put in place to improve the sales of most products.
Commentaires