Reconciliation Preparation

Executive Summary

This program was created to assist Brigham Young University’s dining services accounting department in their monthly inventory reconciliations. There are two main software programs that dining services to account for their inventory:  Eatec and Peoplesoft. When food is delivered to the various locations on BYU campus, the locations receive invoices on the deliveries. All of the invoices get sent to the accounting office where they are put in the Eatec system. With the information given in the invoices, Eatec keeps track of all the inventory in each department. For example, when sales are made and physical counts of food are done in the cafeterias, these amounts get put into Eatec, where Eatec will then deplete the inventory in the cafeteria. Peoplesoft, on the other hand, is the software that handles all the payments and money balances for dining services. When invoices get put into Eatec, Eatec initiates payment requests that are finalized on Peoplesoft. Peoplesoft should essentially mirror the transactions Eatec makes so the inventory balance on Eatec and the financial balance on Peoplesoft should ideally always be the same.

The reason for reconciling the Eatec balance to the Peoplesoft balance each month is because the two balances are never the same. Eatec transactions occur that do not get followed through on the Peoplesoft side. For example, sometimes Eatec may put an invoice in for the Cannon Center department but Peoplesoft processes a payment coming from the Creamery on Ninth. Sometimes, an invoice will be put into Eatec and due to the supplier, will not automatically go into Peoplesoft. In this case employees from other departments will have to manually put the transaction into Peoplesoft for payment. Oftentimes these transactions are put in incorrectly or not at all, causing imbalances.

Every month a long workbook of data is prepared that has every single transaction that is not matched up on both the Eatec and Peoplesoft side. The employees are asked to look at all the unreconciled transactions by department and identify the cause of the problem. When I first started working here, preparing the data by separating the transactions by department was nearly as time consuming as the reconciliation process. This macro will take the monthly reports and create a workbook for a specific employee and their designated departments.

• http://files.gove.net/shares/files/15f/takaboyo/TakaboriFinalProjectWriteUp.pdf
• http://files.gove.net/shares/files/15f/takaboyo/Reconciliation_Project.zip