alinka
Board Regular
- Joined
- Oct 30, 2002
- Messages
- 70
Hi, I have a complicated (for my brain) excel problem:
1) I need to share revenues between two employees and want to create automatic formulas. The revenue is shared for 2 years only. In the 1st year, the revenue is shared 50%-50%. In the 2nd year, the revenue is shared 25%-75%.
2) The exact revenue share END date for each year is specified in the file always, so there's no need to think or make formulas for the dates. In the attached image, that is column B and C.
Now keep in mind that we only care about 2003 revenues:
3) What has to be calculated is the dollar amount of revenue share for each person.
So for example, for the Person#1: (see attached picture)
a) Since the 1st yr Share End Date is Dec-02, since it's now May-03, in the 1st year -- there are no revenues shared in the 1st year, because the date has expired, i.e. the last date to share revenues was Dec-02.
b) Now for the 2nd year share, since the 2nd yr Share End Date is Dec-03, and it's now May-03, we need to take any and all revenues for 2003 (Sum of columns G-J), but only up until May-03, because after May-03, there's no more share, i.e. after May-03, we are no longer sharing anything.
a) and b) fills in columns D & E without taking the 50% and 25%.
3) Another example for Person#3:
a) Since the 1st yr share end date is Jan-04, we ARE sharing revenues in the 1st yr (unlike in the 1st example) Since it's now May03, we are taking a sum of all the months of 2003 for which we have revenues -- cause the end date is Jan-2004. Hence column D is a sum of columns G:J
b) Since the 2nd yr share end date is Jan-2005 and 1st year share date does not expire until Jan-2004, we do not YET have any revenues being shared in 2003. That is, we would start sharing 2nd year share after Jan-2004.
4) The last step is to fill in the Total CNR shared column (ColumnF), in which we need to figure out the total revenue shared in 1st year by taking 50% of column D and total revenue shared in 2nd year by taking 25% of column E and then summing both numbers up. This part is very easy and I know how to do it (I use a formula: =D2*0.5+E2*0.25
What I need help is how to figure out 3a and 3b or columns D & E using a formula rather than manually doing a formula for each month.
See attached picture and if you have any questions, you can email me. Please know that any help is MUCH APPRECIATED.
Muchos Gracias, Thank you!!!!!!
1) I need to share revenues between two employees and want to create automatic formulas. The revenue is shared for 2 years only. In the 1st year, the revenue is shared 50%-50%. In the 2nd year, the revenue is shared 25%-75%.
2) The exact revenue share END date for each year is specified in the file always, so there's no need to think or make formulas for the dates. In the attached image, that is column B and C.
Now keep in mind that we only care about 2003 revenues:
3) What has to be calculated is the dollar amount of revenue share for each person.
So for example, for the Person#1: (see attached picture)
a) Since the 1st yr Share End Date is Dec-02, since it's now May-03, in the 1st year -- there are no revenues shared in the 1st year, because the date has expired, i.e. the last date to share revenues was Dec-02.
b) Now for the 2nd year share, since the 2nd yr Share End Date is Dec-03, and it's now May-03, we need to take any and all revenues for 2003 (Sum of columns G-J), but only up until May-03, because after May-03, there's no more share, i.e. after May-03, we are no longer sharing anything.
a) and b) fills in columns D & E without taking the 50% and 25%.
3) Another example for Person#3:
a) Since the 1st yr share end date is Jan-04, we ARE sharing revenues in the 1st yr (unlike in the 1st example) Since it's now May03, we are taking a sum of all the months of 2003 for which we have revenues -- cause the end date is Jan-2004. Hence column D is a sum of columns G:J
b) Since the 2nd yr share end date is Jan-2005 and 1st year share date does not expire until Jan-2004, we do not YET have any revenues being shared in 2003. That is, we would start sharing 2nd year share after Jan-2004.
4) The last step is to fill in the Total CNR shared column (ColumnF), in which we need to figure out the total revenue shared in 1st year by taking 50% of column D and total revenue shared in 2nd year by taking 25% of column E and then summing both numbers up. This part is very easy and I know how to do it (I use a formula: =D2*0.5+E2*0.25
What I need help is how to figure out 3a and 3b or columns D & E using a formula rather than manually doing a formula for each month.
See attached picture and if you have any questions, you can email me. Please know that any help is MUCH APPRECIATED.
Muchos Gracias, Thank you!!!!!!
| Book4.xls | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | |||
| 1 | Employee | 50% End Date | 25% End Date | 1st year share | 2nd year share | Total CNR Shared | Jan | Feb | Mar | Apr | ||
| 2 | Person 1 | Dec-02 | Dec-03 | - | 75 | 19 | - | 250 | 112 | (287) | ||
| 3 | Person 2 | Dec-02 | Dec-03 | - | 117,083 | 29,271 | 28,459 | 26,660 | 33,566 | 28,398 | ||
| 4 | Person 3 | Jan-04 | Jan-05 | 3,727 | - | 1,863 | - | 1,417 | 1,103 | 1,207 | ||
| 5 | Person 4 | Sep-02 | Sep-03 | - | 113 | 28 | 1 | 2 | 1 | 108 | ||
| 6 | Person 5 | Feb-03 | Feb-04 | (38) | (32) | (27) | (25) | (13) | (15) | (17) | ||
Sheet1 | ||||||||||||
