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 money to my purchasers in order for them to buy me some specific electronic components.
If I send him “1” of money, it means the money for one electronic component; when he purchases for 1, it means he bought one component (= all in the same unit).
I record weeks after weeks the amount of money I have send, and the purchases he has made. I then track the cumulative funding and purchase figures, for the specific weeks and as a cumulative.
The Outstanding equals to the cumulative funding minus the cumulative purchases (= the available money for him).
If I compute this data, here is how it would look (very simplified data):
I have compute what I am explaining in a file I put at your disposal.
What I would like to do is a summary of the number of weeks my purchaser has money at hand that he hasn’t converted to coco, but with further details. Let me explain.
If I use a count if formula of the all-time cumulative funding > to the current week cumulative purchase, (Column “Countif” in the file) I’ll get the amount of weeks since my purchaser has money that hasn’t been converted.
However, what I would like is to split the outstanding amount into the count if result I have. Let’s say my count if equals to 4, I would like to know which part of my outstanding comes from 4 weeks ago, which part come from 2 weeks, which part comes from the previous week…
I have a range of weeks I am interested in. I computed in the file the data you could use, and the expected result written manually.
I am not sure such a computation is possible on excel, however I require your excel brilliancy to crack this case, I don’t think it’s an easy one..
Thank you very much.
8 Replies
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.- 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.
