A lot of choices, huh but do you really want it?
How about perusing 500 reports versus 20 reports? Here the decision is probably easier – you would prefer 20 reports.
So why are you asking for 500 WBS charge codes?
For those of you not in on this lingo, WBS is short for work breakdown structure. They are charge codes against which you put the expenses. Those funny little numbers your expense report asks for are most likely a combination of general ledger accounting code and a charge code. Every expense will hit a GL code and a charge code. In project world, WBS code is used to break down and track each component of the work required to be done on the project. You might have a code for needs requirement analysis in Philapdelphia and a WBS code for software development in Anchorage, all within the same project. You can get very granular or keep it to the essentials.
Sometimes, project managers will want to open up every variety of codes, just in case a situation comes up and they need it. But then they have trouble managing it or more commonly, only a few codes are actually used.
I once did an analysis of an outsourcing program and found it had approximately 2000 lines of charge codes, of which 500 were considered account level. (The account level is where you actually charge your expenses. The other codes were levels to build up to the account level.). The system held 2000 codes, 500 of which were available for expenses.
Wait, it gets worse.
Further analysis showed that maybe around 50 codes were actually used and that the big dollars resided in maybe 15 codes. Quite a few of the other codes had almost next to nothing in them. Folks just can’t handle 500 codes in their brain.
So rather than trying to cover for every contingency, try to identify the essentials and then when a situation comes up where you will incur significant dollars, open up a new code to represent those expenses.
[Addendum – February 13]
How I did that analysis
The data was pulled from SAP. Now I don’t remember the exact sequence to get the report of WBS layout in SAP (and each company’s SAP will be different – Getronics’ was different from SAIC’s which in turn was different from Wipro’s) but you should be able to find a report in SAP that will show you at least the WBS structure. The report I used allowed me to pull in different fields such as the account level field and whether the code was open or closed.
SAP gives you various ways to download the report; find the one that allows you to download as raw data. From this raw data, you will be able to use Excel formulas to do the analysis.
To get the total number of WBS codes in a project, just use COUNTA function to pick up the text. If you use COUNT, it may not work. Then to count how many WBS codes are for expenses, again use COUNTA on the account level column. If you could not get a report that provided the account level information, you will have to do some fancy work in Excel to pull out the account level, but I believe it is doable.
Here’s a possible solution to pulling out the account level if you don’t have a SAP report for it:
First use LEN on the WBS structure to find the longest string – that is most likely your account level if you set up the structure according to conventional rules.
Then use IF statement: =IF(LEN(C1)=22,”x”,””). Here if the WBS structure in cell C1 has a length of 22 (the longest length), then mark down “x”; otherwise, leave the cell empty. The “x” will be used to denote the account level.
Finally, to count the x’s, you need to change the counting from COUNTA(range) to COUNTA(range) – COUNTBLANK(range) because COUNTA will count the blanks (“”).