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.
No comments:
Post a Comment