Background
Prior to using BPC to build our budgets and forecasts, we used SEM-BPS essentially as a repository for our budget and plan data that was really being determined using Excel models. When it came time in the process to lock down the data, we had no real feasible way of doing this.
We developed a custom BW transaction where we could allow only certain people into BPS to make changes to the data being held in the repository, but there was nothing preventing people from changing their Excel models (other than the fact that they would no longer agree to the data in the system). Furthermore, we were unable to limit the entities (for us, Profit Centers) that a user could change – if they were able to get into the system, they were able to change figures for any entity that they had access to. Upon implementing BPC to replace BPS, the requirement to be able to allow only certain users in the system at certain points in the process was still relevant, but I’ll get back to that in a little bit.
Our monthly forecast process calls for users to make changes to our Working Projection category (which is also known as a version) beginning on/about the 15th of the month. In general, users have until about the 25th of the month to make changes, at which point in time the Working Projection category is locked down and the business units are required to sign-off on their updated forecast. With many people involved in the process—almost all of whom had duties outside the forecast process—it seemed as though most of the changes would come through on the last few days before the category was locked down. This often frustrated our business unit leads who were responsible for reviewing the forecast reports for accuracy – with data changing up to and including the day of sign-off, their job was next to impossible.
Making It Happen
Using BPC’s Work Status functionality, not only were we able to meet the business requirement of being able to lock down the category, we were able to better the forecast process to allow for an intermediate step of locking out everyone but the business unit leads. In addition, we’re also now able to allow access to only certain entities so that we can maintain tighter control during times in our process when data integrity is at the utmost importance, yet a few select changes need to be made.
I’m not going to go into great detail about how to set up Work Status in BPC (there’s a good SCN Article for that), but rather just some details of how we’ve modified the system delivered setup to reap the benefits.
Out of the box, BPC comes with three Work States – Unlocked, Submitted and Approved. The thought behind these delivered Work States are that in the Unlocked state any user with access can make changes to the data (and when I say data, I mean financial records, comments stored, journal entries, Data Manager packages and documents); while in the Submitted status, you can limit the users that have access to make changes, but in the Approved status, no user can make changes.
We didn’t particularly care for the titles of those Work States (they didn’t really make sense to our users), plus our business requirements called for an additional Work State, so we needed to make some modifications. Making these changes is easy in BPC – simply go to the Admin Console and click on Work Status on the menu screen on the left hand side:
On the right hand side appears the list of tasks that you can perform for the Work Status feature:
We simply added a new Work State, edited the descriptions and reordered them as necessary and our resulting Work States were set:
For us, we needed four Work States in order to allow everyone access including our property managers (Unlocked), only allow access to our accounting staff (Analyst), only allow access to our business unit leads (SBU Admin) and to lock everyone out to keep them from making changes (Locked).
Now that we’d modified the available Work States to our liking, it was time to set the Work Status Settings for our BPC Applications. The Work Status Settings tell the system that you actually want to be able to use the Work Status feature for your given BPC applications – setting up the Work State is not enough.
When configuring Work Status Settings for a given Application, you must think through the process(es) that are using that Application and at what level it makes sense to maintain the ability to lock users out. For our processes, the combination of Category, Entity and Time was plenty enough detail to lock out users, so we set it up this way, using our Finance Application as an example:
Why was setting it up this way enough for us? Setting it up this way means that we can open and close any combination of Category/Entity/Time period so this means that we could lock down (or open up, depending on points in the process) the Working Projection for a single profit center (or multiple profit centers) for a certain time period. For us, it wasn’t important to be able to lock down at the GL Account level (or any other of our Dimensions) as this would be way too much maintenance and not worth it.
Changes in Process
With BPC and the Work Status settings in place now, our monthly forecast process now goes something like this: on/about the 15th of the month the Work Status of the Working Projection category for all of our entities and all time periods is set to Unlocked so that any user with access can make changes.
On/about the 20th of the month, our business unit leads begin to change the Work Status for their business unit (entities) to SBU Admin, effectively keeping everyone else in their business unit from making changes while they begin to review their reports. In this SBU Admin Work State, the business unit leads can still make necessary changes themselves, but they have much tighter control over the data. On/about the 25th of the month, we change the Work State to Locked so that no user can make changes and our corporate reporting staff can begin to run their consolidated forecast reports.
Utilizing Work Status has allowed us to improve data quality (by allowing our business unit leads to review their reports earlier in the process with the confidence that no changes will be made that they haven’t made themselves).
Because the business leads previously couldn’t get static reports until BPS was locked for all users, we’ve also become more timely by cutting down on time spent after the actual deadline making changes.
So, you probably noticed that we went from Unlocked right to SBU Admin as part of our forecast process and skipped over the Analyst Work State. That’s because we really only use the Analyst Work State for our annual budgeting process. Our annual budgeting process requires that our property managers (those on site at our malls, office buildings, apartment complexes, etc.) start off the process by budgeting revenues and certain expenses associated with operating the properties. The property managers have access only in the Unlocked Work State. Once they’re done with their piece of the budget, we change the Work State to Analyst, which allows all of our accounting staff to formulate the rest of the budget for the property. Ultimately, we use the SBU Admin and Locked state in a similar way for our budget process.
Setting Work Status
The actual task of setting Work Status is traditionally done through the BPC Web interface (you can see more details in the article I referenced above), but we’ve found that interface to not effectively support our process. For certain entities we’re forecasting out 5 years each month and for others it’s 2 years, so maintaining Work Status becomes very cumbersome when changing only one intersection of data at a time.
For example, for our Residential business unit to move from the Unlocked Work State to SBU Admin, they have to use the web interface 5 separate times:
Category/Entity/Time
Working Projection/Residential SBU/2011.Total
Working Projection/Residential SBU/2012.Total
Working Projection/Residential SBU/2013.Total
Working Projection/Residential SBU/2014.Total
Working Projection/Residential SBU/2015.Total
Each of our business units must follow the same steps. Only five instances are necessary when moving the entire company (All Entities) to Locked status since everyone moves to that status at one time, but if a change needs to be made to the data for a single entity, we must go through several iterations to ensure that the change can be made: first, change all five years of the All Entities dimension member (and only that member, none of its children) to SBU Admin status. Next, set Residential SBU (and only that member, none of its children) to SBU Admin status. Lastly, set the specified entity to SBU Admin Status. As you can see, that’s 15 executions of the web interface screen just to make a change to one entity.
Luckily for us, we’ve got some savvy Microsoft SQL developers on our team that have built a handy web interface for us to maintain Work Status more easily. This is possible because Work Status is really driven by entries into certain BPC tables in the backend database.
For those who are interested in an easier way to maintain Work Status, check out a blog by James Lim on maintaining Work Status through the use of Data Manager Packages here. Our custom built web interface was developed before James’ blog was available, but our developers tell me that our interface is essentially doing the same things in the backend as what’s going on in the packages James describes how to build.
Reporting on Work Status
There are a couple different ways that Work Status can be reported. I personally prefer the EVLCK function that’s available in BPC’s Excel client. The EVLCK function allows a user to display the Work State for a given intersection of Work State Dimensions. For example, in a single cell in Excel, we would type =EVLCK(“Finance”,0,”Residential”,”2010.Total,”Working Projection”) and Excel displays the current Work State for the intersection of Residential/2010.Total/Working Projection. The first two qualifiers refer to the Application (“Finance”) and the Parameter (0=Status, 1=User who changed the Work State and 2=Time when the Work State was changed).
We’ve incorporated an EVLCK formula in the header section of all of our BPC Input Templates so that users know whether or not they’ll be allowed to make changes to the data before they even interact with the template. That helps to prevent them from the frustration of making changes that they expect to save successfully, but getting rejected when they actually click the ‘Send’ button.
Using the EVLCK function, you can build a Work Status report/dashboard to display the Work State for multiple intersections at once – a snippet of ours looks like this:
The other alternative for reporting on Work Status is through the BPC Web interface, which allows you to view activity of the Work Status tables to gain insight into which users changed the Work State, what they changed it to, and when the change was made. This, to me, is a little less useful and the web interface is a little restrictive, but you may find a need for it in your implementation.
Conclusion
Work Status is a very useful and powerful tool when it comes to maintaining integrity of your forecast and budget data. Yes, it can be a bit of a pain to maintain, but with some creativeness you can work around those issues and unlock just one of the many ways that BPC can help you to improve your budgeting and planning processes.
