Benefits of SharePoint over Excel for Program Management

The Lazy Man is back, as promised, with an article on how to be lazy at work by leveraging SharePoint as a program management tool.

The method in which a project is managed has a significant impact on the effort required to track and report on the work.

On a recent project that required input from hundreds of employees throughout the client’s organization (both business and IT), we looked at various ways for collecting and storing information. The project consisted of creating a list of artifacts that needed to be modified and a place to store those artifacts centrally once produced. Our options:

SHARED EXCEL

A similar program had been managed several years ago using Excel. The program management team distributed a template to the business and IT owners for input. The program managers collected and consolidate the data into a master spreadsheet which was then available on a shared drive in read-only format.

Edits and updates went through a single owner who was responsible for maintaining the master file. The physical artifacts were stored in a shared folder with all files using a consistent naming convention and a UniqueID.

Benefits:

Control of data entry

Availability of ‘master’ file and artifacts to users

Excel was widely used and familiar to all users

SHAREPOINT LIST

For this effort, we proposed using a SharePoint List. A List in SharePoint includes rows and columns very similar to Excel, but has many additional benefits. It is constantly accessible to entitled users who can add and modify data and create custom views. Data entry is tracked and versioned so that users can see specific details of all changes and re-load prior versions.

SharePoint also allows documents to be attached directly to their respective rows. This easily enabled sortable metadata to be assigned. Users had the ability to upload documents of various types (ex. doc, .xls, pdf, etc…) and then filter their documents. For example, show all documents where ‘Lazy Man’ is the IT Owner.

Benefits:

Users can enter and modify data directly to the list

Data is always up-to-date and multiple users can work on the list at the same time.

Changes are tracked in ‘Version History’

Custom views can be created (including custom columns) for each user

Data can be exported to excel for additional reporting needs

Artifacts and meta-data on artifacts are stored together

CONCLUSION

A happy ending for all with SharePoint.

Users commented on how much more efficient the system was to enter and retrieve data. Information was consistent, centralized, yet customizable.

This resolved several problems that users mentioned had occurred on the prior project:

Delays to Updates – Being reliant on a single person to update the master file created delays (often, and for multiple days) and users had a hard time trusting that the version they were using contained all up-to-date data.

Template Issues – As the template changed (ex. additional columns were added) usersstill working on the older version provided data in a format that needed to be modified prior to importation into the master file. There were also instances where users removed data validation fields and where users did not provide ‘required’ data.

Inconsistent Data – Artifacts and the meta-data about those artifacts were stored in different locations. To know the owner of a certain artifact one would need to review the Excel list. To resolve this, meta-data was added to the artifacts (as SharePoint was used to store the files), but that data quickly did not match what was stored in Excel. Keeping these two lists in sync required significant work and was often ignored for weeks at a time, causing issues with reporting.

Significant time and effort was saved this time by implementing a solution that better suited the needs of the project than the previous iteration of Excel.

About Dave Bagatelle

David Bagatelle, CFA is an Advisory Consultant for the Asset and Wealth Management division of EMC Consulting as part of Global Services.

David offers a unique blend of financial services experience combined with technical knowledge and project management experience and has provided both strategic advice (roadmaps, vendor selection) and tactical implementation services to an array of large financial services firms. He specializes in financial planning, client reporting, performance measurement, account aggregation, attribution, analytics and advises on operational strategy for the front middle and back offices of institutional clients.

Prior to joining EMC, David worked at multiple money managers and advised clients on managing their personal finances. David holds a BBA from the John M. Olin School of Business at Washington University in St. Louis and is a CFA charterholder.