Solved

Gross margin trend analysis - Excel - visualizations

Posted on 2016-07-25
10
84 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

733 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