Case Study on a Scenario

[Nikhil Suhas]

Hi Excel Gurus,
I have done necessary homework on how to solve this case study but unable to go further at all.
Would like to upload the file here so that anyone could help me on this case study given at my institute.
Any help is appreciated

 

[Xiq]

Hi Nikhil Suhas,

What exactly are you looking for? Try playing with the Pivot Table I inserted (see attached file).

 

[Somendra Misra]

Hi Nikhil....

I am not any Excel Guru...But out of curiosity I had gone through your file and could not found the purpose or objective of analysis, like what exactly do you want for e.g. trend of sales region wise or sales rep wise or anything else.

And secondly how these values are derived? In your file they are plane values just typed in. Why I am asking this because I observed -ve in Washing Machine Unit column? Now what does -ve Unit means?

If you can clarify over this than I can also try to do something on your file.

Somendra.

 

[Balli]

Hi Nikhil,

See this if it is helpful or make any sense to your purpose. Attached.

 

[Nikhil Suhas]

Hi Nikhil....

I am not any Excel Guru...But out of curiosity I had gone through your file and could not found the purpose or objective of analysis, like what exactly do you want for e.g. trend of sales region wise or sales rep wise or anything else.

And secondly how these values are derived? In your file they are plane values just typed in. Why I am asking this because I observed -ve in Washing Machine Unit column? Now what does -ve Unit means?

If you can clarify over this than I can also try to do something on your file.

Somendra.

-ve Unit means negative margin on selling the product...In other words, understand that there is a product which we sell it in Discounts, we get Margin more than of the rebate price so we end up getting Negative Margins. Simple!

 

[Nikhil Suhas]

Hi Nikhil Suhas,

What exactly are you looking for? Try playing with the Pivot Table I inserted (see attached file).

"Can you help him analyse the performance of sales reps and understand their performance" is the question in the sheet.
So What i understood is the objective is to analyze the performance of sales reps in regards to the Regions where he can sell more Washing machine units.

 

[Somendra Misra]

Hi Nikhil,

Just see the attached file. If this is helpful. Secondary, on what criteria you will judge the performance, means on units, margins etc.

Somendra.

 

[Nikhil Suhas]

Hi Nikhil,

Just see the attached file. If this is helpful. Secondary, on what criteria you will judge the performance, means on units, margins etc.

Somendra.

Thanks I'll check and get back if I have any questions. Well, the performance always would be measured on Margins..Higher the Margin the good the rep is performing...So having said that, good number of units sold can give much margins as well....

 

[Nikhil Suhas]

Below is something I have analyzed to see the difference between regions having Margins on Washing machine units Vs Other products..So we could probably derive on how to increase the Margins on WM Units considering the reps who does better in their own regions.


from the table below

Region Washing machine Margin($) Total Mar ($)
Region1 $ 244,915 $ 928,044
Region2 $ 708,184 $ 5,892,487
Region3 $ 270,408 $ 3,329,307
Grand Total $ 1,223,508 $ 10,149,838

So after this analysis, I'm stuck on how to go further to improve the Margins keeping the good performing Reps in mind and show it in a pictorial manner.
Hope it helps : /