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
Frederic74Asked:
Who is Participating?
 
Patrick MatthewsConnect With a Mentor Commented:
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
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.