GreenBoy
New member
- Joined
- Apr 13, 2015
- Messages
- 26
- Reaction score
- 0
- Points
- 0
- Excel Version(s)
- Office 365
Hi
This may be terribly easy or not possible - my experience of PQ is so limited I cant say for sure.
I have a report with all the purchase Orders we have been issued by our Client, and dependant on the activity and issue date of the PO, gives us a different period before we are due to recieve the monies in.
For example - for a Survey, PO issued today, we expect the report to be issued, and accepted by the Client in 10 working days - at which time we can invoice. A set of drawings cannot start until after the survey is accepted, so the PO will come out some time around that of the authorisation to Invoice, the drawing can take up to 20 working days and on submission the client then has 5 days to accept, and barring issues, we can bill on that date.... and thus it goes on.
My finance director wants a list of all instructions received, with the value of it to show in the appropriate column on a spreadsheet, so if we are in week 22, then the Survey should be claimable for invoice in week 24 (week 22 plus 10 working days), and then following that the Drawings should be claimable in 25 days beyond that, or 5 weeks making it week 29...
So the data should look something like this:
Start week ----- Activity ----- PO No ------Value ---- Money Due
----------------------------------------------------------- Wk 22 ----- Wk 23 ---- Wk24 ----- Wk25 ----- Wk26 ---- Wk27 ---- Wk28 ----- Wk29 ----- Wk
22 ----------------Survey ------1234 -------£250 ---------------------------------£250
24 ----------------Drawings ----1235 -------£800 ----------------------------------------------------------------------------------------------£800
My Data is currently in columns and I calculate the week due, and I am assuming to get to this arrangement I need to pivot or unpivot the data, but I am not sure how to go about it, and all attempts have come to nothing.
Is this possible - yes/ no
And if so any suggestions , or links to appropriate tutorials appreciate.
Thanks
Dave
This may be terribly easy or not possible - my experience of PQ is so limited I cant say for sure.
I have a report with all the purchase Orders we have been issued by our Client, and dependant on the activity and issue date of the PO, gives us a different period before we are due to recieve the monies in.
For example - for a Survey, PO issued today, we expect the report to be issued, and accepted by the Client in 10 working days - at which time we can invoice. A set of drawings cannot start until after the survey is accepted, so the PO will come out some time around that of the authorisation to Invoice, the drawing can take up to 20 working days and on submission the client then has 5 days to accept, and barring issues, we can bill on that date.... and thus it goes on.
My finance director wants a list of all instructions received, with the value of it to show in the appropriate column on a spreadsheet, so if we are in week 22, then the Survey should be claimable for invoice in week 24 (week 22 plus 10 working days), and then following that the Drawings should be claimable in 25 days beyond that, or 5 weeks making it week 29...
So the data should look something like this:
Start week ----- Activity ----- PO No ------Value ---- Money Due
----------------------------------------------------------- Wk 22 ----- Wk 23 ---- Wk24 ----- Wk25 ----- Wk26 ---- Wk27 ---- Wk28 ----- Wk29 ----- Wk
22 ----------------Survey ------1234 -------£250 ---------------------------------£250
24 ----------------Drawings ----1235 -------£800 ----------------------------------------------------------------------------------------------£800
My Data is currently in columns and I calculate the week due, and I am assuming to get to this arrangement I need to pivot or unpivot the data, but I am not sure how to go about it, and all attempts have come to nothing.
Is this possible - yes/ no
And if so any suggestions , or links to appropriate tutorials appreciate.
Thanks
Dave