About: As part of FP20 Analytics Challenge, the data set if from The Mediterranean Camera Company where the CFO is expecting a report with no more than 2 pages with a horizontal navigation menu.
This is an (EDA) Exploratory Data Analysis and product specifications have been masked. Page 1 should be an Executive overview of the Company Sales performance YTD report. Page 2 should be dedicated to a Products Data Analysis and Time Intelligence report. Any trends/seasonality for any of the Products and or Countries of sales. Does any product sell more over weekends?
The dataset contain over 500 rows of sales throughout the year 2021.
Approach: As mentioned in the above paragraph, the dashboard had to have 2 pages, so the first thing was to decide what to show in each one. In overview page is included monthly variations, discounts per month by buyer type, net margin, units sold and profit per country. While in the second page it is all related to product (units, monthly tendencies, profits per weekday and discounts).
The first thing was to create DAX measures that shows the total values for Cost, Profit, Units Sold, Net Sales and Gross Sales. Then, monthly variations measures were created: Cost MoM%, Net Sales MoM%, Profit MoM% and Unit Solds MoM%. For the Product page, a measure was created to display the percentage of discount agains the gross sales.Some graphs are dynamic in the sense that they have special filters next to them, such as Monthly Variations of Key Indicators, Units and Profits per Week, Monthly Discounts, Profits throughout the year and Product type Indicators per Month. These were created using Fileds Parameters.
As for the structure, the dahsboard follows the instructions above using a mediterranean palette that goes along the theme of the data.Analysis: Regarding Country: France is the highest revenue-generating country with the most sold units and the highest profit and net margin. The United Kingdom has the highest net sales and cost-over-gross sales ratio, while Greece has the lowest cost-over-gross sales ratio.
For Product type: Camera 3 is the most sold product with the highest profit but has the lowest net margin among all products. Weekdays, especially Thursdays, witness significantly higher unit sales than weekends, and Tuesdays generate more profits. December shows high product sales and the highest profit growth.
With Pareto Analysis, we can see that with Cameras 1, 3, 5,and 6 we have a bit more than 70%of total Net Sales. Camera 6 has the highest average price.As for discounts: Institutions receive the most discounts, especially in June, followed by MiniMarkets, with high discount amounts in October and July. Germany has the lowest percentage of discounts over gross sales, while the United Kingdom has the highest discounts.
Camera 2 has the highest discount percentage (7.5%), while Camera 3 receives the highest discount amount (US$2.1MM).
Conclusions: Given France's high profitability and net margin, it would be prudent to focus on expanding operations in the French market and investing in marketing strategies to maintain their dominant position. For the United Kingdom, efforts should be made to optimize cost management and reduce the cost-over-gross sales ratio to improve overall profitability. Greece, with the lowest cost-over-gross sales ratio, presents an opportunity to explore cost-effective distribution channels and expand the customer base.While Camera 3 is popular and generates high profits, its low net margin indicates that there might be room for cost optimization. It would be beneficial to analyze the production and distribution costs for Camera 3 and explore ways to improve efficiency.
With higher unit sales on weekdays, marketing efforts and promotions could be targeted towards weekdays to further increase sales and profits. Additionally, since December shows high product sales and profit growth, consider implementing special promotions or discounts during this period to capitalize on the demand.June appears to be a crucial month for sales to institutions, so tailor marketing campaigns and discounts to attract institutional buyers during this period.
To optimize profitability, analyze the impact of high discounts provided to MiniMarkets in October and July and consider adjusting the discount strategy. For Germany, where discounts are relatively low compared to other countries, evaluate the market's response to discounts and consider a balanced approach to maintain competitiveness without compromising profit margins. For Camera 2, explore alternative pricing or promotional strategies to reduce the high discount percentage. Similarly, analyze the impact of providing significant discounts on Camera 3's profitability and consider adjusting the discount structure to maintain a healthier net margin.