TGO Budgeting Solution – Why Excel for the End User Interface
Posted by Alan on February 1, 2011
As we at TGO Consulting were working on designing the high-level architecture plan for the TGO Budgeting Solution we had to decide upon the interface the end-user would use to interact with the system. In the past I have worked with solutions that use Excel for the interface, Office Web Components presented through Internet Explorer and custom data grids that look like Excel but aren’t quite. When it came time to decide we ultimately went with full-fledged Excel, even though there are some technical and business process challenges associated with using it.
So what were the factors that tipped the scales in favour of Excel? Some of them were that:
- Practically everyone has worked with it and done budgeting with it
- Most organizations have at least one “super user” who can turn it on its ear
- Users can leverage well known formulas like SUM, AVERAGE and IF as well as lesser known formulas like SLN, DB, CEILING and ROUND
- It has excellent Copy/Paste, Drag, Sort and Filter functionality
- Users can add Conditional Formatting and Charts/Graphs to provide visual clues during the budgeting process
When it is all said and done, from the point of view of the individual user, Excel is probably the perfect interface for the capture of budgeting information. So why doesn’t every solution use Excel? It all comes down to the fact that Excel is really geared towards single user interaction and not group interaction and the budgeting process rarely occurs in a vacuum. As you add more and more users to your budgeting process the problems of using Excel grow exponentially. Some of these problems include the:
- Creation of dozens (or hundreds) of individual spreadsheets to send to individual users is difficult to accomplish and very time consuming
- Lack of insight into where the process stands in terms of completeness and the countless back and forth emails as people submit, approve and/or reject their budgets
- Tedious and sometimes difficult task of merging these dozens or hundreds of spreadsheets together to form the completed budget
- Inability to add new members (e.g., Departments, Natural Accounts, etc.) once the process has started without having to recall and restart from scratch
- Loss of any comments once the budgets are submitted and consolidated when doing future budget to actual comparisons
So the problems with Excel that I listed above are what our solution will seek to address. We will be doing this through the creation of a custom add-in accessible through the Ribbon Bar written in .NET using Visual Studio 2010. This add-in will allow the end-user to use all the Excel functionality they want while addressing the issues that Excel causes in a multi-user budgeting environment. In addition, we will be building an Excel add-in that is geared towards designing budget templates as well as doing some cool stuff in SharePoint to deal with the needed administration functions. All of those items along with other exciting topics such as dynamically constructing MDX queries on the fly and implementing SSAS dimension member security in such a way that finance people can understand it will be covered in future posts. Plus as an added bonus we will soon have some draft screen shots to post as well.