These are projects posted by the students of Dr. Gove Allen at Brigham Young University. These students have taken one semester-long course on VBA and generally have had no prior programming experience

Thursday, December 6, 2012

Generating and Summarizing Large Financial Statements

Executive Summary

Business Overview
During my internship with Deloitte, I had the opportunity to work on a financial services firm.  Much of our work involved working with financial statements for “fund of funds”, which are investment funds with portfolios wholly comprised of other investment funds.  The financial statements include financial reporting numbers for each underlying fund in the conglomerate fund of funds, such as assets, income, etc.  This particular company issues dozens of financial statements (for different investment funds) on an annual basis; fortunately, the format and layout of the financial statements is largely consistent; thus, working on a new fund does not involve significant adjustment to the new financial statement format.

Existing Inefficiencies
Much of the audit work I performed involved comparing numbers from other workpapers to the numbers shown on the financial statements.  The primary problem that I faced was that for each printed page in the financial statements, a corresponding sheet existed in the workbook.

This layout was effective for some of the smaller funds; however, I was assigned to work on a fund that has several hundred underlying funds.  With only 3 to 6 funds appearing on each sheet of the financials, I had nearly 100 sheets of data for the statement of assets and liabilities (SAL), another 100 sheets of data for the statement of operating activities (SOA), and 50 sheets on the statement of changes in net assets (SCNA).  In order to do certain calculations and comparisons, I had to go through each tab.  Remaining on one sheet to access all the data would have been significantly more efficient, if only to use a basic formula to verify that the financials matched the amount on my workpaper.  While I tried to solve problems such as this during my internship, I lacked the VBA skills necessary to do so.

Solution Overview
Due to privacy agreements with the client, I was unable to obtain a copy of the spreadsheet they use for their financial statements; thus, the first part of the system I built serves as a random financial statement generator.  While this could be easily altered to pull actual data in from a data source, it currently creates random numbers within a reasonable range for generic accounts for each of 300 funds invested in, with 3 funds appearing on each sheet of the SAL and SOA, and 6 funds per sheet for the SCNA.  The naming and layout of each sheet is closely consistent with the actual financial statements.

Once a set of financial statements had been generated, the original goal of my system could be accomplished.  In order to increase the efficiency of audit work, I created a macro that cycles through each tab of the SAL, SOA, and/or SCNA, finds the current year data, and copies it onto a summary tab for each statement.  Three separate summary tabs have to be used (one each for the SAL, SOA, and SCNA) to prevent confusion and errors in the audit work; some funds do not appear on one or more of the statements (in which case they are not on our audit workpapers either).  This has been adequately addressed by the system that I built.



    Professional trading signals sent to your cell phone every day.

    Start following our trades NOW & gain up to 270% per day.

  2. eToro is the #1 forex broker for novice and advanced traders.


Blog Archive