Solved

Gross margin trend analysis - Excel - visualizations

Posted on 2016-07-25
10
50 Views
Last Modified: 2016-07-28
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
0
Comment
Question by:mikes6058
  • 5
  • 3
  • 2
10 Comments
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41730186
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
 

Author Comment

by:mikes6058
ID: 41730205
I'm not actually sure. Trying to figure the reason for those too.
0
 

Author Comment

by:mikes6058
ID: 41730211
Can you pick out the reason for the increase in overall gross margins in May?

Mike
0
 
LVL 31

Accepted Solution

by:
Rob Henson earned 250 total points
ID: 41730215
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
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41730217
I will take another look tomorrow lunchtime. Got to catch an early train tomorrow morning.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:mikes6058
ID: 41730226
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
 
LVL 27

Assisted Solution

by:Glenn Ray
Glenn Ray earned 250 total points
ID: 41730243
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
 

Author Comment

by:mikes6058
ID: 41733632
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
 

Author Closing Comment

by:mikes6058
ID: 41733633
More related questions to follow....
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 41733908
Glad we could help.  Very creative list of companies!
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now