Forum Discussion
Funds usage monitoring, weekly details
Dear community, I am coming to you with an excel problem that I am sure will challenge you. I am not sure what I expect is possible to compute though. I am a reseller. T buy what I sell, I send...
durendal I don't think I understand exactly what you want. In the attached I calculated how much of the money contributed in week X was still in the spending queue:
notice how my number are much different than yours in that column 1 goes to 0 by wk 2 and then column 2 goes to 0 on wk 3 because money is spent by then. so please explain what you really want because I apparently don't have it right.
mtarler,
Thanks a lot for trying. Indeed it is not the result I am expecting.
Let me try to be more clear, though I know it Can be really challenging.
The Key is to understand what the count if formula does.
It shows the amount of week since the outstanding money is superior to a funding in the past week.
Let me give you an example.
I found 1000 in week one, and my purchaser buys for 900.
The count if will show 1, as one week ago, we had a funding of 1000, and the total amount of sales (900) is not higher than my funding of 1000. What it means to me is that when I will prepare my New funding for the next week, I'll know he has not finished using the funds I send from a week ago.
If the week 2, I fund him one thousand again, but he buys nothing, total purchases will still be 900, and now the count if will show 2 weeks because he still hasn't overcome those one thousand from the previous week (so current week + previous week = 2 weeks).
If in week 3 I fund him 1000, and he purchases for 200, now his total purchases will be 1100. It means he overcome the funding of 1000 from the first week, but not the 1000 from the second week (which he will when his total purchases will be over 2000).
So the count if formula will show 2, because it has been two weeks (week 2 and three) that he has had funding he has not overcome.
I Hope you understand the Idea, excuse me if it's tough to understand, I am not native english.
What I now want is to break down this week count.
If I start with the simplest case, the week one of my little example. The count if formula equals to one. At that at the same Time,the money outstanding equals to 100 (1000 funding - 900 purchases, he still has 100 available).
What I want to achieve is the table to show that the 100 Comes from 1 week ago. Very simple in that case.
In week 2, the count if equals to 2. At that at the same Time, the money outstanding equals to 1100 (2000 cumulative funding - 900 purchases, he now 1100 available).
The table should show that amongst the 1100, 100 Comes from 2 weeks ago and 1000 Comes from 1 week ago.
Now, if I take week 3, the count if formula equals to 2 again. At the same Time, the money outstanding equals to 1900, as I funded 1000 more for this week 2 (cumulative funding 3000 - cumulative sales 1100).
What I now want to achieve is the table to show me that amont those 1900, there are 900 that Comes from 2 weeks ago, and 1000 that Comes from 1 week ago.
And so on and so forth.
I can't put figures for my example right now, I will add one tonight.
I Hope that makes it clearer, I really appreciate the help, thank you so much.
Don't hesitate if you have a question.
Thanks a lot for trying. Indeed it is not the result I am expecting.
Let me try to be more clear, though I know it Can be really challenging.
The Key is to understand what the count if formula does.
It shows the amount of week since the outstanding money is superior to a funding in the past week.
Let me give you an example.
I found 1000 in week one, and my purchaser buys for 900.
The count if will show 1, as one week ago, we had a funding of 1000, and the total amount of sales (900) is not higher than my funding of 1000. What it means to me is that when I will prepare my New funding for the next week, I'll know he has not finished using the funds I send from a week ago.
If the week 2, I fund him one thousand again, but he buys nothing, total purchases will still be 900, and now the count if will show 2 weeks because he still hasn't overcome those one thousand from the previous week (so current week + previous week = 2 weeks).
If in week 3 I fund him 1000, and he purchases for 200, now his total purchases will be 1100. It means he overcome the funding of 1000 from the first week, but not the 1000 from the second week (which he will when his total purchases will be over 2000).
So the count if formula will show 2, because it has been two weeks (week 2 and three) that he has had funding he has not overcome.
I Hope you understand the Idea, excuse me if it's tough to understand, I am not native english.
What I now want is to break down this week count.
If I start with the simplest case, the week one of my little example. The count if formula equals to one. At that at the same Time,the money outstanding equals to 100 (1000 funding - 900 purchases, he still has 100 available).
What I want to achieve is the table to show that the 100 Comes from 1 week ago. Very simple in that case.
In week 2, the count if equals to 2. At that at the same Time, the money outstanding equals to 1100 (2000 cumulative funding - 900 purchases, he now 1100 available).
The table should show that amongst the 1100, 100 Comes from 2 weeks ago and 1000 Comes from 1 week ago.
Now, if I take week 3, the count if formula equals to 2 again. At the same Time, the money outstanding equals to 1900, as I funded 1000 more for this week 2 (cumulative funding 3000 - cumulative sales 1100).
What I now want to achieve is the table to show me that amont those 1900, there are 900 that Comes from 2 weeks ago, and 1000 that Comes from 1 week ago.
And so on and so forth.
I can't put figures for my example right now, I will add one tonight.
I Hope that makes it clearer, I really appreciate the help, thank you so much.
Don't hesitate if you have a question.
- i still don't get it as you week 1 shows 1000 in and 900 out so 100 left and then another 1000 in and 200 out so ALL of the 1000 from wk 1 and more is spent but now you want to show 900 but that 900 is from wk 2. And is this cumulative table what you really want or are you hoping it will be a means to the end where you want a value N for which input week still has a outstanding balance because that could be a single formula.
- mtarler ,
"i still don't get it as you week 1 shows 1000 in and 900 out so 100 left and then another 1000 in and 200 out so ALL of the 1000 from wk 1 and more is spent but now you want to show 900 but that 900 is from wk 2"
--> Yes, the 900 is from week two, meaning that as in week two, those 900 are less than one week old.
How I explained the weeks isn't very clear, sorry;
I distinguish two kinds of weeks: in my expected result table, Vertically, it is the point in time we are at. Week three means the third week of the year;
Horizontally, it is the seniority of the money, in weeks. So in week n°2 (of the year), those 900 outstanding are one week old, since they are coming from a funding tht happend within this same week. That's why I put 900 in the Week<1 vertically in the table, for the week n° 2 horizontally .
In week n°5, we can see 1000 in the Week<1 column, which are the 1000 funded in week 5 that havn't been used (those funds are less than a week old); 1000 in the 1<Week<2 which are the 1000 funded in week 4 that havn't been used (those funds are now between 1 and 2 weeks old); and 1000 in the 2<Week<4 which are the 1000 funded in week 3 that havn't been used (those funds are now between 2 and 3 weeks old).
Hope it makes it clearer.
"And is this cumulative table what you really want or are you hoping it will be a means to the end where you want a value N for which input week still has a outstanding balance because that could be a single formula. "
--> I am not sure to understand everything; the table I want is the "expected result" cells in orange; it is suppose to split the seniority of the funds in different categories, to check the efficiency of avery purchaser quickly and to assess which one deserves new funds.
Hope it is clear, thank you again- ahhhhh, I get it now. I try my hand at it later when I have some time, but should be do-able now that I understand.