Objective: Sales Performance report of all products sold at Emily Computers to identify top-performing products, products to be discontinued, best-selling channels, and locations.

The report took the following approach;

• Data cleaning with Excel
• Data modeling and analysis for insights with PowerBi
• Data visualization to present findings effectively

Key Findings

Product

Revenue Analysis

• The top-performing product in terms of revenue was “Computers,” generating a total revenue of N21.6 million.
• Following closely, the “Cameras and Camcorders” category emerged as the second-highest revenue generator, bringing in N17.3 million.
• Other categories such as “TV and Video,” “Cell Phones,” “Music, Movies, and Audio Books,” and “Audio” followed with respective revenues of N9.2 million, N5.9 million, N1 million, and N1 million.

Profit Analysis

• “Computers” stood out as the most profitable product during the reviewed period, yielding a profit of N12.5 million.
• Subsequently, “Cameras and Camcorders” secured the second-highest profit with N10.4 million.
• Other categories including “TV and Video,” “Cell Phones,” “Music, Movies, and Audio Books,” and “Audio” followed with profits of N5 million, N3.3 million, N650K, and N592K respectively.

Profit Margin Analysis

• “Music, Movies, and Audio Books” boasted the highest profit margin at 61%, followed closely by “Cameras and Camcorders” at 60%.
• “Computers,” “Cell Phones,” “Audio,” and “TV and Video” followed suit with profit margins of 58%, 57%, 57%, and 54% respectively.

Product Revenue vs Quantity Analysis

• “Computers” generated N22 million in revenue with a quantity of 69K, while “Cameras and Camcorders” brought in N17 million with 44K units sold.
• Across all products, the revenue-to-quantity ratio remained notably impressive.

Product Cost-to-Revenue Analysis

• The cost-to-revenue ratio varied across categories, with “Audio” at 43%, “Cameras and Camcorders” at 40%, “Cell Phones” at 43%, “Computers” at 42%, “Music, Movies, and Audio Books” at 39%, and “TV and Video” at 46%.

Sales Channel

Sales Channel Profit and Profit Margin Analysis

• Looking at Sales Channels, the top performing channel was “Store” with a total profit of N18.5M and a profit margin of 58%.

Most Profitable Sales Channel in Each Zone

• The most profitable zones are North-East and South-East with a profit margin of 58%. This is attributed to the low cost-to-revenue ratio (41%) in these zones.

Most Profitable Sales Channel for Each Product:

• For “Music, Movies, and Audio Books,” the online channel emerges as the most profitable, while for “Cameras and Camcorders,” the mobile outlet channel proves to be the most lucrative. This distinction is attributed to the profit margins generated by each respective sales channel.

Sales Channel Cost-to-Revenue Analysis

• The cost-to-revenue ratio remained consistent across categories, with online, store, reseller, and mobile outlet sales channels each exhibiting a 42% ratio.

Brand Analysis

Various Products Manufactured in Each Brand

• Fabrikam: Manufactured products such as “Cameras and Camcorders” and “Computers.”
• Datum: Manufactured products including “Cameras and Camcorders.”
• Proseware: Primarily manufactured “Computers.”
• Contoso: Diversified its product range, manufacturing items like “Audio,” “Cameras and Camcorders,” “Cell Phones,” “Computers,” “Music, Movies, and Audio Books,” and “TV and Video.”
• The Phone Company: Specialized in producing “Cell Phones.”
• Wide World Importers: Manufactured “Audio” and “Computers.”
• Adventure Works: Produced “Computers” and “TV and Video.”
• Northwind Traders: Focused on “Audio.”
• Litware: Specialized in “TV and Video” products.
• Southridge Video: Produced a variety of items including “Computers,” “Music, Movies, and Audio Books,” and “TV and Video.”

Revenue Analysis of Each Brand

• Fabrikam: Emerged as the top revenue generator with N12 million, closely followed by Contoso and Adventure Works with revenues of N10 million and N8 million respectively.

Brand Profit and Profit Margin Analysis

• Fabrikam: Achieved the highest profit of N7 million, boasting a profit margin of 60%. Contoso followed closely with a profit of N6 million and a profit margin of 57%.

Brand Cost-to-Revenue Analysis

• Among the brands, Southridge Video exhibited the highest ratio of 47.49%, while Litware followed with a ratio of 46.71%. Northwind Traders had a ratio of 45.50%. Adventure Works, Wide World Importers, The Phone Company, Contoso, Proseware, A. Datum, and Fabrikam contributed with varying cost-to-revenue ratios.

Zone

Zone Profit and Profit Margin Analysis

• North West: Recorded the highest profit of N6 million with a profit margin of 57%. Following closely, North East achieved a profit of N5 million with a margin of 59%. North Central, South West, South South, and South East each attained profits of N5 million, with varying profit margins of 57%, 57%, 58%, and 59% respectively.

Revenue Generated in Each State

• Kebbi State: Emerged as the highest revenue generator with N1.8 million, trailed by Imo, Abia, Kaduna, Kano, and other states.

Most Profitable Zone for Each Products

• “Music, Movies, and Audio Books”: South West zone proves to be the most profitable. For “Cameras and Camcorders,” FCT and South East are the most lucrative. “Computers” show higher profitability in the South East, while “Cell Phones” are more profitable in FCT. “Audio” products yield more profit in the North East, and “TV and Video” products dominate in the North West.

Zone Cost-to-Revenue Analysis

• North Central exhibited the highest ratio of 43%, while South West, North West, South South, and FCT contributed with varying cost-to-revenue ratios. South East and North East had ratios of 41%.

Promotion Analysis

The Effect of Promotion on Product Performance

• Higher sales were consistently recorded during “Seasonal Discount” periods across all products, indicating the effectiveness of this promotion strategy.

Analysis of Revenue, Profit and Profit Margin of Promotion Types

• Among all types of promotions, “X-mas Holiday Promotion” emerged as the most profitable, boasting a profit margin of 61%.
• “Regular Sales” stood out with the highest revenue of N18.3 million and the highest profit of N10.6 million.

Most Suitable Promotion for Each Product

• For “Music, Movies, and Audio Books,” the most suitable promotion is the “Summer Promotion.” “Cameras and Camcorders” perform best with the “Sallah Promotion,” while “Computers” thrive during the “Summer Promotion.” “Cell Phones” show optimal results with the “Anniversary Promotion,” “Audio” products excel with the “Xmas Holiday Promotion,” and “TV and Video” products fare well with the “Easter Promotion.” These determinations were made based on profit margins.

Year-on-Year Promotion Analysis for Each Product (in terms of quantity)

• Across all products, “Regular Sales” consistently yielded the highest quantity in 2012, 2013, and 2014. In 2011, since “Regular Sales” was not introduced yet, the “New Year Promotion” had the highest quantities throughout the year.

Business Questions Addressed

• Why has revenue been dropping since 2012?

Revenue has been dropping since 2012 because of the following reasons;

• Factors such as declining product sales volume (quantity of products), and
• changes in pricing of the products which affected customers’ purchasing power, have contributed to the downward revenue trend post-2012.

• 2014 experienced a higher qty compared to 2013, yet revenue was lower, what could be responsible for the drop-in revenue?
• The shift towards lower-priced products like “Cell phones” resulted in higher quantities sold but lower overall revenue compared to higher-priced items. In 2014, the top performing product was “Cell Phones” with a total quantity of 42,522 and an avg. the unit price of N165.26. Products like “TV and Video”, “Computers”, “Cameras and Camcorders” with an avg. unit price of N501.33, N374.27 and N420.16 respectively experienced a low quantity.
• The most profitable year was 2011. Why?
• 2011’s profitability can be attributed to its low cost-to-revenue ratio of 41%, despite operating on a smaller scale compared to subsequent years. Although, in 2011, the company was operating on a low scale, the total quantity of products recorded during this period was 5,000.
• What is the reason behind the drastic increase in the quantity of cell phones in 2014?
• Various factors, including increased demand for cell phone accessories (a sub-product under “Cell phones”), and promotional activities carried out in that year.
• There were price reductions from N166.80 in 2013 to N165.26 in 2014, which could have increased purchasing power.
• And rising internet adoption, drove the surge in cell phone sales in 2014.

Recommendations

• Given the significant revenue and profitability generated by products such as “Computers” and “Cameras and Camcorders,” prioritize resources and marketing efforts towards these high-performing categories.
• With the “Store” channel proving to be the top-performing channel, consider investing further in optimizing store operations and customer experiences to maintain and potentially increase profitability.
• Brands like Fabrikam, Contoso, and Adventure Works have demonstrated strong revenue and profit margins. Explore opportunities to expand product offerings under these brands and capitalize on their success.
• Allocate resources strategically to regions like North East and South East, which have shown high profitability. Tailor marketing campaigns and promotions to better resonate with consumers in these regions.
• Given the positive impact of promotions, particularly “X-mas Holiday Promotion,” continue to leverage seasonal discounts to drive sales and maintain customer engagement throughout the year.
• Continuously track key performance indicators such as revenue, profit margins, and sales quantities across products, channels, and regions. Conduct regular analyses to identify emerging trends and opportunities for improvement.
• All products performed well. However, if there will be a need to discontinue any product, it could be “Audio”. This is because of its comparatively lower revenue, and profit performance, coupled with a less favorable profit margin ranking.
• Even though the pricing of cell phone accessories increased in 2014, there was a significant increase in demand. Further investigation is needed to determine the underlying factors driving this trend. I will suggest the provision of additional data sources or conduct surveys to gather insights on potential reasons for the observed changes.
• More data is required to understand the promotional dynamics applied in 2014, in order to have a better understanding of the cause of the drastic increase of the sale of “Cell Phones” in 2014.