Solved

Gross margin trend analysis - Excel - visualizations

Posted on 2016-07-25
10
105 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
10 Comments
 
LVL 33

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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 33

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 33

Expert Comment

by:Rob Henson
ID: 41730217
I will take another look tomorrow lunchtime. Got to catch an early train tomorrow morning.
0
 

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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

691 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