Want to make a Sales Man wise Dashboard with Graphs

[rush2rajen]

Hi Excel Gurus,

I want to make a Sales Dashboard showing the performance of individual Sales Man Monthwise with Graphs showing the Growth and Degrowth Trend.

I would like you to help me with a Dashboard in which there should be a Dropdown or a pointer in which if my Boss clicks he can see the individual trend Salesman wise monthwise and if he clicks on all can see the entire Sales Man Trend with clear data label mentioning Target and Achievement.

Thanks
Raj

 

[Smallman]

Hi Raj

This is not a dashboard per se but should give you a starting point to built on. I have reorganised your data to provide some calculations.

Take care

Smallman

 

[rush2rajen]

Thanks a lot Smallman.

Can you please help in one more thing.

For e.g : Debu data is there. Like in April he had achieved 101% of his Target ( mentioned in Calcs Sheet )
I want to show for all the salesman If they had achieved their target that they had achieved 100% or 101% or 110% inside the Label of Column Graph with Green Colour and if they had not achieved with Red Colour.
If they had achieved then by how much Rupees and if they had not achieved then by how much Rupees.

Or any other way where in for achievement or non achievement some marker is there ?

And also some line graph on Achievement that his performance in May had degrown if compared to April, and again June had degrown if compared to May and so on...

I hope I had been able to clarify the same.


Thanks
Raj

 

[Smallman]

Hi Raj

Sounds like you are looking for some conditional formatting. Here is some basic formatting for you to work with and add to.

Take care

Smallman

 

[rush2rajen]

Thanks Smallman. I had adapted the same and updated in my books.

One more help Can a Trendline Like Line Graph be shown on Achievement ?


Thanks
Raj

 

[Smallman]

Hi Raj

Sorry for the late reply. What constitutes achievement. Can you show me in a file?

I am out to the world for the next 8-10 hours as the sand man is hitting me pretty hard. I will have a look in the morning if no one else comes to your aid in the mean time.

Take care

Smallman

 

[rush2rajen]

Good Morning Smallman,

Please find the attached file. Here you can see for e.g. : BC had from in May from Apr but degrown in June from May.
Excel file attached.

Thanks
Raj

 

[Smallman]

Hi Raj

I am well thanks.

I honestly don't think you should show any more information in the chart. You already have lots going on in there. I changed the colours in the chart, unless you are wedded to them from a corporate perspective I would steer clear of bright orange and britsh racing green as a combination.

I put a table below the chart showing movement which I think adds a little value but not much as the negative movement between May and June is clearly visible on the chart.

Take care

Smallman

 

[rush2rajen]

Thank you very much Smallman.

Just a more help reqd. I want to make a Dashboard or you can say an interactive chart showing monthwise Joinee & Resignee with a dropdown on Cost Centre and Monthwise. Sample file attached for your reference.


Thanks
Raj.

 

[Smallman]

Hi Raj

The file attached shows how I would summarise it to the point of Calculation. You just have to apply the same methodology as post 2 from this thread to apply the Chart and perhaps a combo box which can be found in that post.

Take care

Smallman