My Assignment Help

Snapshot Analysis of Sales Data for September of Profitable Retail Brands Assessment 3 Answer

Assessment 3

1. A company has three profitable retail brands in India (Joramai, Marzudo and Xanderial). Post COVID the company wants to study the following:

Sale trend of Sept19- Feb20

  • Region wise graphical representation on MRP as well as Net Sales. Net Sale is MRP less discount and Tax. 
  • Discount hit trending region wise.
  • Average Transaction Value (=Net Sale/ Qtd Sold) store wise and region wise monthly to be made. Final graphical representation should be region wise. 
  • Summary of brand wise and channel- wise net and average sales.

Use the excel sheet as provided to work out the above. You must combine data from all excel sheets in one to display results. 

2. Record a 5 minute video explaining the trends identified and upload on youtube. You may use a powerpoint presentation in the video but you must be clearly visible. 

3. Create a word document mentioning the formula/ function you used the achieve the above. Write a personal reflection in 200 words on the task. At the end of the document, paste the link to the youtube video. 


Sales Data Report

The following report provides a snapshot analysis of sales data for September 2019 till February 2020. Sales data pertains to three brands sales in India. The data is categorized through stores, regions and format of ownership.

Data Consolidation

Firstly, the given data for various months were consolidated in one sheet of excel for ease of creating pivot tables and charts. This was done by copying and pasting data one after the other for all months. A column for month was also added to identify what month a data point refers to.

Once this was done, various pivot tables and charts were created.


MRP & Net Salessum of MRP sale and Sum of Net sale graph

Regionwise MRP Trend line graph

Regionwise Net Sales Trend

The above charts indicate that maximum MRP sale and Net Sales were in South region. The next top region was West followed closely East and NCR. The north region indicated minimum numbers.

The trend charts indicate similar pattern for all states where the numbers are at peak in January and the lowest in September/October. 

It was also seen that while West was higher than East in terms of MRP sale, net sales were higher for East region indicating very high discounts and taxes in West.


RegionDisc & Tax% of MRP
 EAST                                                           891.00 33.2%
 NCR                                                           874.97 35.9%
 NORTH                                                           438.25 38.2%
 SOUTH                                                       1,411.72 35.9%
 WEST                                                           969.77 35.8%
 Grand Total                                                       4,585.71 35.6%

The table shows that overall, almost 35.6% of MRP is given as discount and taxes. The percentage was highest in North at 38.2% while the lowest in East at 33.2%. This is noted in following charts also that provide average of discounts in regions:Average of discount in regions line graph
Average of discount in regions bar graph

Average Transaction Value

The chart provides monthly average transaction value for various regions and can be seen that it stable at same level for September to December and then dips in January and picks again in February.

Ownership FormatSum of Net Sale bar graph

average of transaction value bar graph

It can be seen that maximum sales are own stores and lesser sales are from franchisee. However, average transaction value is same in either case. Also, Jaremei brand has maximum sales, followed by Marzudo and then Xanderiel.

Customer Testimonials