Hi All,
Until a few weeks ago when we finally upgrade to Office 2010 (from 2003), I considered myself to be of the intermediate level in Excel. After finding forums like this one I am now feeling that I am more like the remedial level
Anyway, I have a very basic model, two tables. One is just a 'date' table that currently just has dates from Jan 1, 2012-Jan 1, 2015 (although honestly I don't have any data past mid-2013). The second table is 'transactional' data, containing mainly information about purchases and sales, specfically when the product will either be shipped or received as well as the quantity, price, deal value(purchase or sales volume * price), and due dates among other things.
To project my inventory I can use the "running total" in my pivot, but I'm struggling on projecting my payables and receivables to help manage my cash flow.
What I would like to do is create a formula that I can add into my pivot showing outstanding payables and receivables on a given day. Criteria is that in order to generate an outstanding payable/receivable, the product must have been shipped (if I'm selling) or received (when I'm buying) today or some point in the past.
I've tried without success to use a calculated column in my 'date' table, thinking that I could create a formula saying "sum all deal values where the shipping/receiving date (in the 'transactional' table) is less than or equal to the date shown (in the 'date' table) AND the due date (in the 'transactional' table) is greater than the date shown."
For example, for Mar 5th I would like to see outstanding payments or receipts that are due Mar 6 or later, provided that product has moved Mar 5th or sooner.
How would I do this ? Been going crazy for over a week trying to figure this out.
I'm convinced that this is either a very complex calculation that is way beyond my capbility, or so simple that I'm over thinking it and can't see something that is right in front of my nose.
Thanks in advance,
Chris
Until a few weeks ago when we finally upgrade to Office 2010 (from 2003), I considered myself to be of the intermediate level in Excel. After finding forums like this one I am now feeling that I am more like the remedial level
Anyway, I have a very basic model, two tables. One is just a 'date' table that currently just has dates from Jan 1, 2012-Jan 1, 2015 (although honestly I don't have any data past mid-2013). The second table is 'transactional' data, containing mainly information about purchases and sales, specfically when the product will either be shipped or received as well as the quantity, price, deal value(purchase or sales volume * price), and due dates among other things.
To project my inventory I can use the "running total" in my pivot, but I'm struggling on projecting my payables and receivables to help manage my cash flow.
What I would like to do is create a formula that I can add into my pivot showing outstanding payables and receivables on a given day. Criteria is that in order to generate an outstanding payable/receivable, the product must have been shipped (if I'm selling) or received (when I'm buying) today or some point in the past.
I've tried without success to use a calculated column in my 'date' table, thinking that I could create a formula saying "sum all deal values where the shipping/receiving date (in the 'transactional' table) is less than or equal to the date shown (in the 'date' table) AND the due date (in the 'transactional' table) is greater than the date shown."
For example, for Mar 5th I would like to see outstanding payments or receipts that are due Mar 6 or later, provided that product has moved Mar 5th or sooner.
How would I do this ? Been going crazy for over a week trying to figure this out.
I'm convinced that this is either a very complex calculation that is way beyond my capbility, or so simple that I'm over thinking it and can't see something that is right in front of my nose.
Thanks in advance,
Chris
Last edited:
