1. A Company has 3 profitable retail brands in India (Jeramai, Marzudo and Xandreial).

Post covid the company wants to study the following:

· Sale Trend of Sept19 – Feb20

o Region wise in graphical representation on MRP as well as Net Sales.

o Discount hit trending region wise.

o Average Transaction value (=Net Sale / Qty Sold) store wise and region wise monthly to be made. Final graphic representation should be region wise.

· Profitability Analysis

o Apart from expenses mentioned in the master sheet store wise, company has Cost of Good sold as 24.5%, Taxes of 8.22% of MRP and corporate overheads of 7.12% of MRP.

o Advise the company on which stores to continue, and which stores to shut down basis the profitability, sales trends and costs involved.

o Give a graphical representation on the company’s profitability month wise and region wise.

· Covid analysis

o Post Covid hitting the country, the company estimates a 15% rise in sales of brand Jeramai while a Monthly drop of 50%, 42%, 21%, 11% and 2% respectively in the months of Mar, Apr, May, Jun, Jul respectively from the existing 6 months avg sales.

o Advise the company on which stores it should retain and which stores it should shut down basis the store wise profitability analysis.

Use the excel sheets 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, upload on youtube. You may use a powerpoint presentation in the video but your face must be clearly visible.

3. Create a word document mentioning the formula/function you used to achieve the above, advise that you provide must be clearly indicated. Document must have a table of contents, your name and student id in header, page number and date in footer. At the end of the document, paste the link to the youtube video.

Please note Excel task must be submitted in link provided and document must be submitted on Turnitin link only.