Solved

Gross margin trend analysis - Excel - visualizations

Posted on 2016-07-25
10
76 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 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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

 
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

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

860 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