GIG Portfolio Report

1. GIG Portfolio Report

2. Overview • Global Investment Group (GIG), a student club of Thunderbird School of Global Management, collected data from several web sources weekly to monitor the performance of investment portfolios. The collected data was used to update various intermediate reports, graphs and charts. • ORGWD was tasked with automating the manual process of collecting the data, organizing it and creating the final portfolio performance report. Overview

3. Prior to Using ORGWD • Too many steps:Data was collected manually as well using Excel web queries and included tens of steps for each of the stocks in the portfolio. • Too many web sites:For each stock the data was collected from multiple web sources, then posted in various Excel worksheets. Each worksheet calculated statistical and financial variables that were later used in the portfolio performance report. • Too many worksheets:There were up to 25 worksheets in 2 different Excel workbooks. • Too many updates:Every time a new stock was added to the portfolio, all Excel workbooks and worksheets had to be updated, in many cases manually. • Too tedious and error-prone:Some the computations required hundreds of permutations making the manual process extremely challenging. Because of that the student group took shortcuts sacrificing the accuracy of calculations. Prior to Using ORGWD

4. Data Collection Process • Data collection and processing took many steps. Here is an example of some of them: • Login to Finance.Yahoo.com and navigate to the stock portfolio • Use Download feature to download the portfolio information into Excel. • Copy downloaded data into the master excel workbook. Prior to Using ORGWD

5. Data Collection Process Cont. • Collect additional data for each stock. • Copy the data into Excel worksheet Prior to Using ORGWD

6. Data Collection Process Cont. • Get Stock correlation coefficient for each stock combination and copy it into the Excel worksheet. • Input • Result Prior to Using ORGWD

7. Data Collection Process Cont. • Prepare various statistical and financial data in Excel worksheets to be used in final consolidated report. Prior to Using ORGWD

8. Create Consolidated Portfolio Performance Report • Produce the report using collected intermediate data and Excel charting tools Prior to Using ORGWD

9. Automating with ORGWD • The Goal: ORGWD’s objective was to automate the entire process starting from data collection process and ending with the final performance report. • Business Analysis: ORGWD analyzed the manual process and identified the requirements for process automation. • Technical Implementation: ORGWD used its tools to construct data collection modules, intermediate computation modules and the final Excel report (see Appendix A for implementation diagram). • Automated Efficiency: ORGWD consolidated 2 workbooks and 25 worksheets into 1 workbook and 8 worksheets which are populated automatically. • Seamless Transition: Final dashboard performance report was implemented with no changes to the original business rules. • From days of effort to a single click: • The final performance report is now generated on demand by clicking the link provided on ORGWD’s web site. Automating with ORGWD

10. Automated/Manual Process Comparison Automating with ORGWD

11. Appendix A: Implementation Diagram

12. Appendix B: ORGWD Implementation Tool Screenshots Report Configuration – assembling intermediate report from collections Appendix B - ORGWD Implementation Tool Screenshots

13. Report Configuration – constructing the master report from intermediate reports Appendix B - ORGWD Implementation Tool Screenshots

14. Collection Configuration – constructing collection from extraction rules Appendix B - ORGWD Implementation Tool Screenshots