Interactive Accounting Spreadsheets

Projects | 2014 | Links: Github Repo

For use by LSYOU office and the LSU Dept. of Education Bursar’s Office

Accounting Spreadsheet

This is the template I made for the LSYOU office accounting. When I took over for the full-time accountant, I found that the previous system frequently led to inconsistent data, was a drain on the data entry team's time, and required the head accountant in the Bursar's Office to do unnecessarily tedious tasks by hand. My goals for the project were to reduce the workload on the data entry team and the Bursar’s Office while allowing the directors who were unfamiliar with data analysis and excel to easily see any information they might need.

Coversheets

The coversheets, the first and last pages, pull from the backpages to aggregate charges by month and by expense type. From there, section totals and remaining budgets can be easily calculated and displayed in a readable format. The front coversheet, with yellow highlights, is for use by the Bursar's office. It is only used to ensure that all recorded charges balance out with the ledger at the end of the month. The back coversheet, with blue highlights, is for use by the directors to give them up to the minute readouts on charges and budget allocations.

Backpages

There is a seperate data page for each type of expenditure to simplify the task of searching for a specific charge. As can be see from the example I left in the template on the Personnel sheet, if there is an incorrect name or a typo in the name (or expenditure code for non-payroll items), the entire row is highlighted in red to indicate the error. The litany of dates and types of charges are required by LSU. After charges are filed in from our office, the Bursar's Office went through and assigned a group of them to a 'BF' report. Then, the data entry team would have to locate each charge in the BF report and include the reference number. To ease this process, I had lines which needed further work highlight in green.

Example

The example is on the first data page: ‘Personnel 1000’.

Line by line it is displaying:

1. An incorrect name flagged as an error
2. A proper input line that needs further work on our end
3. A proper input line that needs no further work but has yet to be reconciled with the ledger
4. A proper input line that is fully complete by us and by the Bursar’s Office
5. A typo in the name flagged as an error
6. A misordered name flagged as an error

In addition to displaying the colors, the results of this example can be seen on the front and back coversheets.

Front: Only charges which have been reconciled with the ledger by the Bursar's Office are included on the front ledger. They are summed according to the 'Effective Date' which is the date they are included in the collective LSU ledger. This only includes line 4 of the example, totalling $5,000 in January.

Back: All proper charges are included in the back ledger summed according to the date the charge was incurred. The back page is for in-house use so the directors can keep track of all financial data including remaining budgets. This includes lines 2, 3, and 4 of the example, totalling $15,000 in December.

Use

There were five of these spreadsheets, one for each grant awarded to the program in order to keep track of the charges put towards that grant. Originally, the LSYOU office had one set of spreadsheets and the Bursar's Office had another set. After comparing my work with the previous systems, both our office's and the Bursar's office spreadsheets were replaced by a single set of my spreadsheets which were under my care.