Request for Help in Creating a Solution

[Azhar Nadeem]

Hi,
We are a small firm involved in sales for a 3rd party. We have a list of employees who do the marketing and paid on the number of leads generated. All the sales are not paid in the current month and we get some (+-) balance each month. Also, our employees' list is growing as a lot of people leave every month. I am managing this with a simple spreadsheet but looking to build a more professional looking template which should ease the task.

Please somebody help me build this in a step by step manner. I can work with formulas but not with VBA.

Regards

Azhar Nadeem

 

[GraH - Guido]

Hi,
My 5 cents on the topic. Segregate, normalize and standardize your data first. It is mandatory to organize data in a smart way, no matter which of the solutions get implemented. Install some master data management on the fly if possible. Make a table for:

1. unique employee id, names, in/out date, status as dimension ("lookup") table
2. leads generated (win/loss), date as fact ("transaction") table
3. calendar table as dimension table, containing all dates and derived dimensions like weekday, month, quarter, year,...
4. unique locations as dimension table
5. unique accounts (customers?) as dimension table
6. ... whatever missing

Second step would be to consider alternatives and evaluate pros/cons of each. Use native Excel with heaps of formulae, go for a data model with PowerPivot and DAX (What I would do, personally) or even consider building an access DB with a front and back office.

PS: I have no experience in guiding a (small) project via a forum and I won't feel comfortable doing it like that. Perhaps, if possible for your firm, bring in a local consultant to guide you through the exercise. I emphasis on guidance and not doing it entirely for you. It is a way to ensure you master the implemented solution. Else one can become too dependent of what the consultant did. I've witnessed a couple of times that it is hardly sustainable like so.
It also depends on the skills you have in-house. Some girls and guys over here have stunning abilities to help others out, even for small projects. And if you have some fair understanding of data concepts and formulae it might just turn out to be the best thing to do. There might be a financial aspect to it too.
So good luck with it.