We visited a customer that had TitaniumGP for a while and we wanted to see how they were doing. They are a company that handles the administrative tasks of over 140 companies from several investors. They ranged from real state, restaurants, hotels and couple of aircrafts.
This is not their spreadsheet but might make some sense.
They explained that all expenses must be collected before the month end or the Financial Statements won’t be accurate, they needed a mechanism to identify when a bill was not included so it won’t be duplicated the next month.
Some of their JEs where almost 1000 lines each.
A very clever user, that had already good experience using TitaniumGP, so he created a template that would allow them to enter some bills and this spreadsheet would create the template to upload several JE’s and distribute among several departments.
The first worksheet, includes the mail information including the header and notes information for the JE:
Automatically, the second template would be based on formulas and use the first tab information to generate the template:
In the template, he had implemented conditional formatting to indicate when an account was not a valid GP G/L account by formatting it in red:
He would copy the COA into a 3rd worksheet and with the following formula was able to mark them to visually identify errors before pasting into GP:
=IF(E11=””,FALSE,ISERROR(MATCH(E11,’Chart of Accounts’!$D$5:$D$222,0)))
This worksheet also includes other segment information to assist in creating the distribution reference in the JE:
The following scripts were generated by their IT department to upload the tables that would be the foundation of the validation:
140 of these spreadsheets were distributed among several accountants to facilitate in the generation of the recurring JE
And they would become the evidence for the journal entries. The Excel file, would be attached to an elastic window in the first journal entry for future reference