1 2 Previous Next 19 Replies Latest reply on Feb 18, 2013 9:14 PM by BRIAN SULLIVAN

    Creating a Rollforward

    BRIAN SULLIVAN

      I am having difficulties creating a rollforward in a text table for our purchase orders.  What this table should include is the months in the rows (12 months rolling), # of po's @ the beginning of the month, # of po's created during the month, # of po's closed during the month then the # of po's at the end of the month.  my source has a report date column, which is what I plan to use for the months in the rows.  The reason for that is that each month we run a query of open po's for our department, so this column helps take a snapshot of each month.

       

      Given that information, how would I accomplish this?

       

      Thanks in advance.

        • 1. Re: Creating a Rollforward
          Shawn Wallwork

          Brian, everything you describe sounds do-able. Could you provide a sample of dummy data, so we can show you how to do it.

           

          --Shawn

          • 2. Re: Creating a Rollforward
            BRIAN SULLIVAN

            Here is the sample data you requested.

            • 3. Re: Creating a Rollforward
              Shawn Wallwork

              Brian, how do you tell when a PO has been closed? Is it the Last Activ field?

               

              --Shawn

              • 4. Re: Creating a Rollforward
                BRIAN SULLIVAN

                That's a good question.  There is no field that tells us the date a PO is closed, so I was hoping to back into that number somehow.  The thought process behind that is I know what I started with, what I added and what I ended up with at the end of the month.

                • 5. Re: Creating a Rollforward
                  Shawn Wallwork

                  So in that case I can get you a net change for month-to-month, but I can't tell you how many were opened and how many were closed. (See attached.)

                   

                  Now if we can make assumptions about that Last Activ field, we might be able to get at some of those figures. For instance if PO Date = Last Activ then can we assume the PO is still open? And if the only activity is to close a PO, then we could figure both your open and close numbers.

                   

                  One other note, the beginning of a month would be the same figure as the end of the previous month, right? So I'm not sure you are really looking for (need?) the whole beginning of the month end of the month thing, do you?

                   

                  Anyway, take a look at the file and we can build from there.

                   

                  --Shawn

                  • 6. Re: Creating a Rollforward
                    BRIAN SULLIVAN

                    All of the POs listed in the sample I gave you are open POs.  The Last Activ field is the date of the last charge on a given PO.  Lastly I do need to see the beginning balance and a breakout of the net change between new and closed POs as well as the ending balance.

                     

                    Unfortunately, what you gave me does not accomplish my goal.

                     

                    I appreaciate all of the work you've done thusfar.

                    • 7. Re: Creating a Rollforward
                      BRIAN SULLIVAN

                      Would it be possible to see if a PO exists from one month to the next if we did some kind of concatenation of Report Date and PO No?

                       

                      For example if I had PO # 1 in November of 2012, can I test to see if it existed in Oct 2012?  Maybe this will give us the # of POs closed?

                      • 8. Re: Creating a Rollforward
                        Shawn Wallwork

                        Certainly we can find a way to test that sort of thing, but I'm a bit confused. I the dummy data you provided there is only one PO No in a single month, so that sort of test would return FALSE for all rows. [Just to be clear, your data only contains PO No once, there are no duplicates.]

                         

                        Can you provide some data that represents what you're referring to. Thanks,

                         

                        --Shawn

                        • 9. Re: Creating a Rollforward
                          Shawn Wallwork

                          BRIAN SULLIVAN wrote:

                           

                          All of the POs listed in the sample I gave you are open POs. 

                           

                          So how are we going to count closed POs?

                           

                          --Shawn

                          • 10. Re: Creating a Rollforward
                            BRIAN SULLIVAN

                            I was hoping to count closed POs by comparing the PO No in each month.  So if I had PO No # 1 in October 2012 I would see if that same PO No existed in November 2012.  If it doesn't exist in November 2012, then it is assumed closed.  I may have explained this backwards in my previous post.  My apologies.

                            • 11. Re: Creating a Rollforward
                              Shawn Wallwork

                              Brian, I understand what you're saying here, but I just sorted your Excel file on PO No. and then checked to see if there were any duplicates. There aren't any, not one. So to check for a PO No in two different months makes no sense, there just aren't going to be any.

                               

                              Each PO No. has:

                               

                              Report Date

                              PO Date

                              Last Activity Date

                              Due Date

                               

                              Working with these four fields is there anyway to generate a closing date? If not, I'm not sure you can get what you're looking for. The other possibility is that when you mocked up you data you didn't accurately represent it regarding the PO No field. Are there duplicate entries for that field in your real data?

                               

                              --Shawn

                              • 12. Re: Creating a Rollforward
                                BRIAN SULLIVAN

                                You're right, in the data I originally attached, the scenario I described does not exist.

                                 

                                I have attached an updated file that is more reflective of the data we capture on a monthly basis.  In this updated file I have detailed out the POs that "Closed" each month.

                                • 13. Re: Creating a Rollforward
                                  Shawn Wallwork

                                  Brian, thanks for the new workbook. Your data is still not making any sense:

                                   

                                  Brian Data Layout.png

                                   

                                  This is how your data is laid out. I put PO No across the top and Report Date Months on the Y-axis. This shows where your data is. So let's talk.

                                   

                                  • Most of the POs have records for all months, so this means all these are NOT closed, right?
                                  • The area under the red circle doesn't make sense. These records opened, then closed, then opened again?

                                   

                                  What do you want to do with the ones under the red circles?

                                   

                                  --Shawn

                                  • 14. Re: Creating a Rollforward
                                    BRIAN SULLIVAN

                                    I see what you're saying.  For those 2 po's, delete them in the subsequent months.  That was an oversight on my part as I tried to get this together quickly for you.

                                     

                                    Answers to your questions are:

                                    • correct, not closed
                                    • see above
                                    1 2 Previous Next