Cisco World
PR TRACKER

A Tool for Budget and Purchase Order Tracking and Analyses

PR TRACKER DIAGRAM

Concepts

There are a few concepts used in PR TRACKER that are worth understanding in depth.  These include:

  • Why tracking is needed;
  • Tracking by group;
  • The Centralized FETCH;
  • Matching up the budgets and expenditures;
  • Matching up with Oracle records;
  • Period and date issues;
  • Data tables and files;
  • ACCESS vs. EXCEL.

Why tracking is needed

Tracking purchase requests and purchase orders against budgets is one of the best ways to get timely information about how you are doing against your budget commitments.  The idea is that purchase requests represent pending expenditures, and purchase orders represent approved and committed expenditures.  So if those amounts can be captured and summarized against the relevant budget line item(s), then you can use that to see how much you have already spent (or committed), and what is remaining from your budget(s).

Tracking by group

This tracking must be done separately for each management group that has it's own budget.  PR TRACKER lets you define a group by management levels within the Cisco Hierarchy.  This definition is really only used when pulling and matching records from the Oracle FETCH -- to filter those records before doing the matching of PR numbers, in order to speed up that process.  The group involved is automatically implicit in which purchase requests you enter into the tracking table, and which budgets you enter into that table.  The pulling data from Oracle based on PR numbers keeps it within the group.

The Centralized FETCH

The process of using FETCH to pull records from Oracle is relatively slow, so PR TRACKER does it once and the result is shared by all of the tracking groups. Also as a part of that process, PR TRACKER adds the Cisco Hierarchy information into each record. Then when each group does it's UPDATE process, the Oracle data is available in that shared Central Database. Click here for full details on the centralized FETCH process.

Matching up the budgets and expenditures

A key part of the PR TRACKER is how it matches the PR's/PO's being tracked against the budget line items.  Generally there can be any number of PO's that correspond with a particular budget line.  The way these are tied together is by the Budget Category(s) you specify each time you enter a new PR or a new Budget Line item.  If you do not categorize them properly, the system will have no way to determine which budget line should reflect the PO or PR amount.  Note also that each PR/PO will incorporate the ID of the Budget Line it gets matched into, so that later you can always pull up a list of the PR's, XC's, and PO's that make up the expended amounts.

Matching up with Oracle records

After you enter new PR's into the tracking table, the UPDATE process either matches them with Oracle Open PO and AP records or leaves them as Pending (PR's).  Those matched are changed from Pending to either Open or Closed (plus a lot of additional information is added to the tracking record).   The matching is based solely on the PR number and the Line number.  The system also looks at the Oracle Open PO's (for this group) that are not matched to any PR in the tracking table, and brings them in as "Unapproved" PO's.  However, this process also can be limited by a list of Account Numbers you enter in the SETUP area, which are used to filter out any large number of small-dollar amount PO's that would otherwise be added to the tracking table.  You also specify a minimum amount that is used in conjunction with the Account Numbers and overrides them, so any large-amount PO's that show up using those Account Numbers do still come into the tracking table.

Period and date issues

In addition to all of the categorization, matching, and summarization, there is another set of issues involving dates and accounting periods.  The budgets are done by fiscal year and quarter, and each PR/PO has a year and quarter.  But it will frequently happen that some of PR's being entered will not correspond to the current budget year/quarter, or else some will come in from Oracle Open PO's that do not correspond to the current budget year/quarter.  So the MERGE process must reach beyond the current year/quarter, and be able to place XC/PR/PO amounts into the correct period bucket of the correct budget line item.  Additionally, when tracking budgets, it is most useful to be able to look pack and/or look ahead a year to compare and see trends.

To accomplish this PR TRACKER actually tracks twelve separate period buckets for each budget line item -- for each of the following types of amounts: budget, revised budget, cross-charges, approved PO's, and pending PO's.  While you can specify the Current Year and Quarter which PRTRACKER will use for new XC's and PR's you enter, the MERGE doesn't use that; it uses the Current Budget Year.   The Current Budget Year is always the center four quarters of the twelve buckets of the budget table.  This is only changed when you run the End-of-Year process -- which changes the Current Budget Year and also rolls all of the amounts involved by four period buckets.  For example, if the Current Budget Year is 2009, and you run the EOY process, then all of the amounts that are in the 5th Quarter buckets will be put into the 1st Quarter buckets; the 6th Quarter buckets will go into the 2nd Quarter buckets, etc.  So 2009 is moved from the Current Budget Year to Last Year and 2010 becomes the new Current Budget Year.

Data tables and files

PR TRACKER consists of a program file and three data files.  Each PC running PR TRACKER must run the program file on their local Drive C in a folder named PR_TRACKER.  The data files include two that all users of PR TRACKER connect to (and share simultaneously), and one that is unique for each group being tracked.  These data files all reside on a network server; they are named PT_CFG_ALL.MDB, PT_CDB_ALL.MDB, and PT_DATA.MDB.  The last is the group's unique data file and must reside on the server in a separate folder (because each group connects to a file of the same name).

In the SETUP area of PR TRACKER you can enter the location of each of these three data files and "REATTACH" to the specified files.  On that form you can also pop up a list of all of the tables used by the system; that list shows which of the three data files each of the tables resides in.

ACCESS vs. EXCEL

Once the PR TRACKER has completed all of the processes and verifications to build the end-result budget table.  This ACCESS budget table can then be used as a source for analyses using both ACCESS and EXCEL.  ACCESS makes it easy to do queries and cross-tabs on it's tables, and to do more complex, fancier report and sub-report formatting.  EXCEL is better at Pivot-tables and can do graphs very easily.  Many more people are fluent in EXCEL; for that reason alone the exporting from PR TRACKER into EXCEL will allow more people to get the analysis they are looking for.