Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 562
  • Last Modified:

excel spreadsheet, graph

Hi

I have a monthly sales and cancellation summary spreadsheet, please see attached work sheet august(2).. I would like to summarise the number and EUR of Saels per caregory and total and also compare it to cancellations visually and in a table. Additionally I also would need to have a summary per week, I guess in a different table and graph.

Nothing I have tried has got me close to a solution. Any advise would be very much appreciated.

Many thanks

Frederic
Finance-Break-down-Analysis-pppp.xlsx
0
Frederic74
Asked:
Frederic74
  • 4
  • 2
  • 2
1 Solution
 
Patrick MatthewsCommented:
Frederic74,This would probably be easiest if you reorganize your data:1) Instead of having separate worksheets for each month, have a single worksheet.  Excel 207 and later give you one million rows, so use them :)2) Instead of having separate regions for sales and cancellations, put them all in the same range, with a column indicating sale or cancellationOnce you do this, getting the summaries/graphs for your data is a simple matter of creating a PivotTable/PivotChart.Patrick
0
 
Frederic74Author Commented:
Thank you

I have  followed your advise and have put all in one table.

I guess a pivot table would mean I do not need my summarising formulas on the top my spreadsheet?

I'm new to pivot tables do you know a good example introducing it to me ideally similar to my spreadsheet?
0
 
calacucciaCommented:
Attaced example shows how you could visually show number & sales + cancellation.
As the numbers are much lower, they need to be plotted on a secondary axis.

Regarding your weekly report, I think Patrick has a very valid suggestion overall.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
calacucciaCommented:
Forgot to attach the workbook
Xl0000011.xls
0
 
calacucciaCommented:
Pivot Table won't help you for the chart though :-)

BTW: your charts are much too small to get a nice visualisation.
0
 
Patrick MatthewsCommented:
>>Pivot Table won't help you for the chart though :-)I wouldn't say that: if the PT powers a PivotChart, then it should faciltate the charting: one could see new sales vs cancellations, and depending on how the source data is reconstructed, you could use either a calculated field or a calculated item to see the variance of sales over cancellations...
0
 
calacucciaCommented:
Could be Patrick, but he will need secondary axis for his amount as it has a complete different dimension.
0
 
Frederic74Author Commented:
I have added the cancellattions to the sales, please see attached.  Does this make it easier?
Finance-Break-down-Analysis-pppp.xlsx
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now