K
Krish
Hi All,
Please help in this complicated problem with reference to the annexure
given below:
Problem Statement:
1 I have three Tables
Sales Data
Debtors data
Month Data
2 I have to calculate debtor days by each manager and by each
workgroup
3 I have more than 2000 datapoints, (around 98 manager names and 16
workgroups)
4 In the calc sheet you would see that I have calculated the debtor
days for Manager A for DEC (but any point I could be asked for earlier
months also like ex Nov or Jul)
5 The procedure is to deduct the latest months sales or the month for
which it is to be calculated from the debtors
The process is continued by deducting on month prior (if we are
looking at dec then dec debtors - dec sales = x1 then x1-nov sales=x2
then x3= x2-oct sales and so on)
This is done till a negative amt is arrived and then the
proportionate days is taken when it becomes negative.
a How can I automate this process
b What should be the structure of this data
c How can I update when next months data arrives
d Since mananger and workgroup are different columns how do I work
around
Report required:
Final Report Required
Debtor Days
By Manager
A 133
B xxx
C xxx
D xxx
E xxx
F xxx
By Workgroup
ME01 yyy
ME02 yyy
Annexure Data
Table 1
Manager Workgroup Sales Month
A ME01 450 DEC
A ME01 450 DEC
B ME02 500 DEC
C ME01 625 DEC
D ME02 353 DEC
E ME01 151 DEC
F ME02 362 DEC
A ME01 252 Nov
B ME01 124 Nov
C ME02 589 Nov
D ME01 100 Nov
E ME02 250 Nov
F ME02 236 Nov
A ME02 362 Oct
B ME01 102 Oct
C ME02 104 Oct
D ME01 105 Oct
......> for 10 months
Table 2
Debtors A B C D E F
Nov 1500 1400 300 1000 1100 50
DEC 2000 1500 250 1300 1200 150
Table 3
DEC 31
Nov 30
OCT 31
SEP 30
AUG 31
JUL 31
Solution
Debtor Days - DEC
A
Debtors 2000
Less Dec 900 31
1100
Less Nov 252 30
848
Less Oct 362 31
486
Less Sep 405 30
81
Less Aug 81 11.07142857 take proportionate days for aug for bal
0
Total Debtor Days 133.0714286
Solution
Debtor Days - Nov
A
Debtors 1500
Less Nov 252 30
1248
Less Oct 362 31
886
Less Sep 405 30
481
Less Aug 226.8 31
254.2
Less jul 254.2 24.18723143 take proportionate days
0
Total Debtor Days 146.1872314
Please help in this complicated problem with reference to the annexure
given below:
Problem Statement:
1 I have three Tables
Sales Data
Debtors data
Month Data
2 I have to calculate debtor days by each manager and by each
workgroup
3 I have more than 2000 datapoints, (around 98 manager names and 16
workgroups)
4 In the calc sheet you would see that I have calculated the debtor
days for Manager A for DEC (but any point I could be asked for earlier
months also like ex Nov or Jul)
5 The procedure is to deduct the latest months sales or the month for
which it is to be calculated from the debtors
The process is continued by deducting on month prior (if we are
looking at dec then dec debtors - dec sales = x1 then x1-nov sales=x2
then x3= x2-oct sales and so on)
This is done till a negative amt is arrived and then the
proportionate days is taken when it becomes negative.
a How can I automate this process
b What should be the structure of this data
c How can I update when next months data arrives
d Since mananger and workgroup are different columns how do I work
around
Report required:
Final Report Required
Debtor Days
By Manager
A 133
B xxx
C xxx
D xxx
E xxx
F xxx
By Workgroup
ME01 yyy
ME02 yyy
Annexure Data
Table 1
Manager Workgroup Sales Month
A ME01 450 DEC
A ME01 450 DEC
B ME02 500 DEC
C ME01 625 DEC
D ME02 353 DEC
E ME01 151 DEC
F ME02 362 DEC
A ME01 252 Nov
B ME01 124 Nov
C ME02 589 Nov
D ME01 100 Nov
E ME02 250 Nov
F ME02 236 Nov
A ME02 362 Oct
B ME01 102 Oct
C ME02 104 Oct
D ME01 105 Oct
......> for 10 months
Table 2
Debtors A B C D E F
Nov 1500 1400 300 1000 1100 50
DEC 2000 1500 250 1300 1200 150
Table 3
DEC 31
Nov 30
OCT 31
SEP 30
AUG 31
JUL 31
Solution
Debtor Days - DEC
A
Debtors 2000
Less Dec 900 31
1100
Less Nov 252 30
848
Less Oct 362 31
486
Less Sep 405 30
81
Less Aug 81 11.07142857 take proportionate days for aug for bal
0
Total Debtor Days 133.0714286
Solution
Debtor Days - Nov
A
Debtors 1500
Less Nov 252 30
1248
Less Oct 362 31
886
Less Sep 405 30
481
Less Aug 226.8 31
254.2
Less jul 254.2 24.18723143 take proportionate days
0
Total Debtor Days 146.1872314