By Andrew Chan
Actuaries have been using spreadsheets for a long time; spreadsheets are getting more powerful with every upgrade, but our demand is even higher. Excel 2013, another major upgrade, was released March 2013; what is on your Excel wish list? Recently, I discussed with a group of financial analysts what they want to see in Excel 2013. They are power Excel users and want Excel to do more for them. It is not surprising that they put together a long list, so I am going to summarize only the most popular items.
- Data analysis
- Data visualization
- Data storage/integrity
- Data extraction
- Security
- Collaboration
- Programming
- Control
Excel 2013 is currently available for purchase. As usual, it comes with a lot of enhancements. However, Excel 2013 does not address all our needs, and our offices may not upgrade to Excel 2013 for another few years. So, meanwhile, what can we do to address our needs? How can we effectively improve our efficiency and work smarter? Well, your organization may already have a solution, viz SharePoint.
I recently asked my colleagues if they have ever used SharePoint. Apparently most of them have used it, but mainly for document storage. SharePoint is an excellent content management system so it is a great idea to use it as an electronic library. However, it is more than just an expensive electronic library; it has many great features for data engineers to transform data into insights. Let me show you some useful SharePoint services.
Version Control
Figure 1. Version Control (from microsoft.com)
Version control allows us to build a version history and keep all previous versions with version information, e.g., when an Excel workbook was created, changed and even commented to describe the changes. It is handy to have access to all previous versions; we can view or compare previous versions or even restore to any previous version when we hit a dead end and like to drop all changes. SharePoint automatically maintains all versions, and there is always only one file to handle.
Excel Services
Figure 2. Excel Services (from microsoft.com)
Excel Services extends Excel from your desktop computer into server infrastructure. It manages security and does all calculations on the servers. Once we upload the workbook to SharePoint, anyone with the required permission can access the whole workbook or pieces from the browser. We still have to use Excel to create a workbook, but SharePoint allows us to distribute and share the workbook in a more controlled and secure environment.
PerformancePoint Services
Figure 3. PerformancePoint Services (from microsoft.com)
PerformancePoint Services allows us to create dynamic, powerful and reusable dashboards; we can create sophisticated key performance indicators (KPIs) and scorecards that have drill-down and drill-up capabilities. One of the handy analytical tools that I like to use is Decomposition Tree. With it we can easily visualize the root causes of a business problem.
Figure 4 Decomposition Tree (from microsoft.com)
Workflows
Figure 5. Workflows (from microsoft.com)
SharePoint provides a set of pre-configured workflows that automate a set of common business processes in a production environment, e.g., collecting signatures, feedback or approvals. We can also build our own customized workflows tailor-made for our specific business processes, e.g., workflows that automatically collect business planning workbooks from each business unit, approve them and consolidate them into a single workbook. All these business processes can be constructed with minimal coding in SharePoint.
Conclusion
SharePoint has many powerful features. It allows us to work together on a secure, automated collaboration platform, and it grants us critical business insights via interactive dashboards and scorecards. There are many other SharePoint features that I haven't covered in this article, e.g., Access Services, Visio Services, PowerPivot for SharePoint, Power View, etc.
I often say Excel is a handy Swiss army knife, because it can handle many analytical tasks. SharePoint moves Excel to another level, to an enterprise level that often requires tighter control, better collaboration and higher efficiency. Talk to your IT department; it may already be installed and all you have to do is just use it!
Andrew Chan, ASA, is a financial model engineer with ALG Consulting. He can be contacted at chan_a@algconsultings.com.