Gross margin trend analysis - Excel - visualizations

Hi

Attached is a workbook containing sales transactions for a retail store for the first 5 months of the year. On sheet Revenue_cost_profit you will see that in May revenues dropped significantly along with costs, yet profits remained consistent with previous months due to a rise in gross margin.

I'd like some help investigating the hike in the gross margins.

e.g. which customers, products, pricing type etc. is responsible for the increase.

More over I'm interested to see which visualizations people come up with to demonstrate their findings.

Also, if anyone discovers and other obvious trends or issues with the data I'm also interested.

Look forward to your feedback.

Mike
trans.xlsx
mikes6058Asked:
Who is Participating?
 
Rob HensonFinance AnalystCommented:
There are some examples of Qty with no value: (Pasting below from data doesn't look too good!!)

custcode      ProdSect      ProdSet      prodgroup      partcode      qty      salestotal      costtotal      margin
12984      16      66      278      254939      1      0      0.01      -0.01
23694      10      44      59      253791      2      0      14.6      -14.6

Cust 15279
Part 255177
Sold 2 in Feb but returned 3 in March giving loss of 9.95

Using a Pivot table with Cust Code and Prod Code as Rows, Sales Qty and Sales Value as columns and Date as Column but grouped by month.
0
 
Rob HensonFinance AnalystCommented:
Is there a reason for the big negatives in April and May?

Filter data on Cust 21705 and Sales Total < 0 gives £666.7k credit
0
 
mikes6058Author Commented:
I'm not actually sure. Trying to figure the reason for those too.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
mikes6058Author Commented:
Can you pick out the reason for the increase in overall gross margins in May?

Mike
0
 
Rob HensonFinance AnalystCommented:
I will take another look tomorrow lunchtime. Got to catch an early train tomorrow morning.
0
 
mikes6058Author Commented:
It would appear as if returns are an issue. I'm still struggling to fathom the increase in GP May. Thanks for your help Rob. I think I'm going to have to spend some time delving deeper into this data to understand the month on month trends.

Mike
0
 
Glenn RayExcel VBA DeveloperCommented:
Is this, by any chance, an educational assignment?  I ask because
1) the customer names listed are fictional (i.e., drawn from fictional sources)
2) it is not EE's policy to aid in academic assignments.

I will offer up this suggestion regarding visualization:  ensure that similar metrics are displayed accordingly.

Your PivotChart shows all values stacked and this is an issue because
1) Revenue, COGS, and Profit are all measured in currency (£)
2) Gross Margin is a ratio and measured in percent (%).
3) Since COGS and Profit are the two components to overall revenue, they should not be stacked on top of it.

Instead, a compound chart showing the currency components as bars and the gross margin as a line might better help demonstrate what is happening:
alternate chart:
Regarding your questions about the causes behind the May results, your "May Sales" PivotTable is a good start.  I'd use that approach (sorting by margin dollars, descending) to see the highest impacting customers, then replace custcode with partcode or prodgroup as appropriate.   You could also add a factor to measure how much impact each margin component is of the total margin to gauge the impact and then limit your analysis to the topmost percent.  For example, the top 12 customers account for 75% of all the margin.

Good luck,
-Glenn
1
 
mikes6058Author Commented:
Hi Guys,

Thank-you for your contributions,

Glenn, this is not an educational assignment. I have added the fictional company names myself as this data relates to a real company and I wanted the real company names to remain anonymous.

Thank-you for you tips on the visualisation. I have quite a lot of questions regarding charting and visualisations.   I am going to raise several more specific questions rather than one ambiguous one.

Watch this space. I'm going to split the points on this one.

Thanks
Mike
0
 
mikes6058Author Commented:
More related questions to follow....
0
 
Glenn RayExcel VBA DeveloperCommented:
Glad we could help.  Very creative list of companies!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.