Excel for salesteam

I am trying to make excel work for our salesteam. The thing I try to put togheter is a workbook where each salesperson have a place to write down each sale. They put in the amount of each sale and excel will show how much is earned that month.
For instance when each new month starts, each teammember is given a budget. All sales within the budget is rewarded with a commision on 10 %. All sales that are made one top of the monthly budget is rewarded 30 % of the amount of each sale. Say a sale of product X gives a total of USD 1.000 added to the monthly sale, this will give the person whom made this sale either 100 USD or 300 USD, if they are over budget or not.
In addition they have a minimum vage of 100 USD each and every working day Mon-Fri each week. On top of that comes the sales reward, either 10 or 30 % of each sales made.

I belive that the salesteam will focus better if they can use a excel book like this to keep track of the salary and paycheck.

I have tried many things but I am new to excel so I can't seem to make this work. I even tried to modify an excisting template that I downloaded from microsoft. I found several other templates on the net but they all seemed to be based on the one over at MS.

Originally Posted by -slope

I am trying to make excel work for our salesteam. The thing I try to put togheter is a workbook where each salesperson have a place to write down each sale. They put in the amount of each sale and excel will show how much is earned that month.
For instance when each new month starts, each teammember is given a budget. All sales within the budget is rewarded with a commision on 10 %. All sales that are made one top of the monthly budget is rewarded 30 % of the amount of each sale. Say a sale of product X gives a total of USD 1.000 added to the monthly sale, this will give the person whom made this sale either 100 USD or 300 USD, if they are over budget or not.
In addition they have a minimum vage of 100 USD each and every working day Mon-Fri each week. On top of that comes the sales reward, either 10 or 30 % of each sales made.

I belive that the salesteam will focus better if they can use a excel book like this to keep track of the salary and paycheck.

A somewhat sweeping statement, it could also cause some consternation amoung the team if not correctly handled.

You would need to ensure correct security for each person and for the summary sheet, I doubt that each member of the team would appreciate their salary details being open-view to all other team members, and others.
Are any of your team members Excel experienced? You could find it difficult to keep such details secure.

I have tried many things but I am new to excel so I can't seem to make this work. I even tried to modify an excisting template that I downloaded from microsoft. I found several other templates on the net but they all seemed to be based on the one over at MS.

Whilst there are many templates at MS it is doubtful that you will ever find one to suit your specific purpose.

For ease-of-understanding, set up additional columns for the additional features that you require, and include the formula to calculate each column, use a separate column to count days-in-this-month, another for that * 100, another for sales * 10%, and yet another for =If(Sales>Budget,(Sales-Budget)*.3,0) where the appropriate cells are used for Sales and Budget etc.

note, when you mention "templates ... based on the one over at MS" I would doubt that anyone here has seen or used that template, or would know to which MS template you refer.

For specific problems in your template, post a copy of your .xls file (as a .zip) together with what you are trying to achieve, and the help will be much easier to give.

Good luck in your project.
---

Thx for the reply.

To better explain my needs:
The excel workbook is purely for an extra motivation for each teammember. We will not generate the paycheck based on the workbook. Because each member on the end of each day make a handwritten report/statemant of each and every sale, with product ID, and the correct amount of each sale. Then they make a summary. So I do not think we will need to focus much on a security issues here.

The plan is that they each have an excel file where they put in each and every sale, and based on the total amount of sales the month the workbook will show how much they will get in salary each month. This is meant as a interim solution while we wait for our new crm system. The importent thing is that each member starts to focus and thinking of the paycheck they will recieve the next month. This is because I belive if each member is foused on the salary they will put in more energy into each customer, and make more money for themself and in the end the firm.

Here are the template I tried to modefy from MS website:

Sales Commission Calculator From Microsoft

As easy as this would work for us. But I struggle with me weak mathematic knowledge ;-)

Here is how I picture this workbook to be in final stage:
Two cells with commission rate as oposed to the one the tamplate holds. One cell for 10 and one for 30 %.
Excel needs to handle the summary part so it knows what commission to put:

All sales with a grand total (All sales that month calculated togheter) < 10.000 $ commission = 10%. (The 10.000 $ is the budget each month, when one passes 10.000 the commission rises to 30 %)
All sales > 10.000 $ commission = 30%.
Each day at work = 100 $

Lets say that the team just put one sale "on top of" the other each day.
One the bottom line it will then calculate the amount of sales, and give correct commission.
It will add money for each day at work, and show the correct amount of earnings BEFORE taxes.

That way they will see for each and every sale they make how it affects their paycheck, and I hope this will bring out the best in every one so we can continue the growth.

I will be happy with any help and ideas here ;-)

I was finally able to solve this case.
What I did was that I used the following formula:

=IF(F15<100000,F15*F9,((F15-10000)*F10)+10000*F9)

Where F15 = the total of all sales
F9 = 10 %
F10 = 30 %

I have gotten what I needed, so now I will need to work out how to link all workbooks togheter to one "master" workbook where the manager can keep an eye on the sales.

Originally Posted by -slope

I was finally able to solve this case.
What I did was that I used the following formula:

=IF(F15<100000,F15*F9,((F15-10000)*F10)+10000*F9)

Where F15 = the total of all sales
F9 = 10 %
F10 = 30 %

I have gotten what I needed, so now I will need to work out how to link all workbooks togheter to one "master" workbook where the manager can keep an eye on the sales.

good to see, for the link probably variations of VLookup (see http://www.contextures.com/xlFunctions02.html and http://www.mvps.org/dmcritchie/excel/vlookup.htm etc) for the ability to extract data from books that are not open might help.

hth
---

Thanks a lot Bryan. I will hammer this into shape on sunday.
Thx for the links.

My biggest problem was actually how to write the formulas so that excel new what I wanted it to do. Finally I got it to work.

Now I will make it so that the manager can track each and every sale the team makes, and the managers workbook will also show a grand total for the team as well as for each individual.

I will read up on the links you gave me and hopefully on monday this is all sorted.

I run into one small problem.

It all works fine now, just that I will try to run a contest this week, between alle peoeple in the salesteam. They are now all linked up to a master excel workbook. I have inserted the correct path and that part is all good now.

The problem is that I have each excel workbook make add each day in the month together. But as we are on the third workday this month, and the contest starts today I need to only get part of the month added into my contestsheet.

='c:\salesteam\officenumber1\[name_of_salesperson.xls]Sheet1'!$B$28

B28 has this formula in each workbooked linked: =SUM(B5:B27)
Where B5 is the first workday of month, and B27 the last. This as it stands give me the wrong value into my contestbook, becauses it also includes day 1 + day 2. They belongs to last week and for this actuall contest they should not be a part.

What I need to do is to make the master contestbook pick up only cells B7 as that is the first workday this week, and it could also make a =SUM(B7:B27)in the end.

I hopeI was able to make you understend my needs. I belive here is where I need to make the changes:
='c:\salesteam\officenumber1\[name_of_salesperson.xls]Sheet1'!$B$28

How do I make this path just add =Sum(B7:B27) rather then "Sheet1'!$B$28 " ?

If I was unclear or vage about my problem pls ask me. I would really like to have resolved this tonite. That way we have a correctly workbook tomorrow.

If Ican do anything to better clearify this pls let me know.

How do I make this path just add =Sum(B7:B27) rather then "Sheet1'!$B$28 " ?

Try =Sheet1'!$B$7:$B$27

Thx for the help. Will try this out right away.

Thx

Thx for the help. Worked like a charm.

Glad to hear it worked for you - thanks for the feedback