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

Wednesday, April 6, 2016

Reconciliation Assistant VBA

At the close of each fiscal month, the Inventory Reporting team at Walt Disney Parks and Resorts® Accounting and Controllership must reconcile account ledgers to their balances on the statement of profit and loss. This process is often automatic, but some manual reconciliations are required due to limitations in the enterprise SAP® system or because management has determined that manual processing is necessary for information accuracy or validation.

The in-transit reconciliation is one of the most tedious, requiring analysts to comb through thousands of transactions identifying transaction categories and organizing transaction-level information from one ‘other text’ cell from the SAP® output into appropriate columns on a spreadsheet. The most skilled users of Microsoft Excel® tend to take a few full work days to complete this reconciliation manually. With two separate accounts for the Disneyland® and Walt Disney World® resorts, this reconciliation is one of the most costly to the company. 

In each of the in-transit inventory accounts on the general ledger, there are a myriad of transaction types waiting to be cleared to their proper landing accounts. An FS10N report from SAP® of the in-transit account number shows all of the transactions from the fiscal month, but they are not sorted by transaction type and the purchase order, receipt, vendor, transaction code, park and category information is all combined somewhat inconsistently in one general text field on the SAP® output. This makes for a great deal of formatting, categorizing, filtering and clever manual maneuvering of the text-to-columns function built into Microsoft Excel®. This process is very repetitive and experienced analysts use a set of rules they have defined to sort through the information as quickly as possible. When completed, the FS10N transactions have all general text characters sorted into the appropriate columns and each transaction is categorized as either a freight, merchandise, money transfer or assist charge.

At the end of a few tedious work days, two separate analysts will have completed the reconciliation manually. The transactions will be separated by category as outlined above and this month’s transactions will be mapped with last month’s uncleared balances to determine which items are clearing transactions from previous month movements and which items are truly inventory items in transit. This requires output to a new database file in Microsoft Access® where transactions from each month are queried with SQL to match purchase orders and identify uncleared balances. The combined transaction ledgers are then exported from Microsoft Access® and returned to Excel® where the final report is compiled and the remaining balances are compared with the general ledger account balance. Any discrepancy requires the analyst to go back through the entire process to identify which transaction has been erroneously categorized, which happens frequently due to the manual nature of the lengthy process.

This first application is a tool designed to accomplish all of the formatting, categorizing and data mining automatically. The user then re-categorizes transactions that could not be automatically categorized by the rules given. The second application then combines all transactions by purchase order and identifies which transactions clear previous-month balances and which are outstanding at the end of the fiscal period. 

No comments:

Post a Comment

Blog Archive