Innovation/Emerging, Learning

Benefits of SharePoint over Excel for Program Management

By Dave Bagatelle July 18, 2012

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) users still 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.

Read More

Share this Story
Join the Conversation

Our Team becomes stronger with every person who adds to the conversation. So please join the conversation. Comment on our posts and share!

Leave a Reply

Your email address will not be published. Required fields are marked *