Mr Keane has worked for over 30 years in Australia and the United Kingdom in a variety of positions including Business Systems Consultant, Financial Controller, Manager Data Processing, Troubleshooter, Chief Accountant, Manager Systems Applications and Support, Finance Manager, Junior and Senior Tax and Audit Clerk and holds a Business Studies degree majoring in Accounting and is a CPA.

The File Control department set up new accounts received from Salesmen in the field. The Salesman would telephone giving basic details to the File Control clerk who would record the information on paper forms. An account number given by the clerk to the salesman that would enable deliveries to be made. The signed and completed new account forms being sent in by post later on.

A problem arose due to the late arrival of the signed completed new account forms - no invoices were produced for the incomplete new accounts. Further delays occurred in matching the documents with the partly completed forms in the File control department. Credit checks were also done after the event.

When the problem was given to Mr Keane to rectify, the number of uninvoiced deliveries was approaching 1,000. This translated to millions of $'s in uninvoiced sales that adversely affected Debtors, Cash Flow, Sales and Sales Commission, Stock records and Production planning - virtually all operations throughout the company.

A line was drawn under the current practice and a new system put in place to stop further occurrences of the problem.  Two temporary clerks from an agency were engaged to marry up the new account forms and chase salesman for the outstanding forms.

The new system was imlimentated that involved purchasing headsets and microphones for the File Control clerks with the clerks operating like a booking agent. When a Salesman phoned for a new account, the File Control clerks keyed in the basic customer information immediately and the salesman given the new account number. Salesman were able to sign up new customers and promise delivery within 3 days which increased business.

A risk assessment policy was implimentated where only new accounts with initial greater than a specified amount of product were referred to Credit Control immediately. Credit Control would do credit checks and suspend the delivery on any accounts that they considered a credit risk. 

A policy of excluding new accounts to Salesmen with outstanding new customer account forms of more than a week was implimented to ensure timely completion and forwarding of signed and completed new forms to file control.

The new systems and procedures put in place stopped the problem immediately, enabled Saleman to win more customers as a result of delivery guarantees within 3 days without exposing the company to undue credit risk and the other areas of incorrect Sales, Debtors, Cash Flow, commissions, production planning and stock control simply dissappeared.

Within a month the number of uninvoiced delivers from the old system had been reduced to just over 50.

The Company had 2 offices as a result of the Company acquring the soft drink arm of Beechams- 1 in Chelmsford (Britvic arm) north east of London and the other in Birmingham ( Beechams arm) - 3 hours drive away.

The company decided to  retain the Management and Sales functions in Chelmsford and to retain the IT, Sales Ledger and Purchase Ledger functions in Birmingham.

I was called in to relocate the Britvic Sales ledger function from Chelmsford a month before the actual move.

The contraints were that I could not use any IT resources as they were committed elsewhere, the mergers of the 2 Sales Ledgers was not an option at that point in time, the Customers were not to be told as there were concerns on a number of levels, the banking could not be delayed because of the adverse affect this would have on cash flow and the budget allowed for the project was $0.

A courier was already delivering important documents and files between the 2 offices each evening. This was changed so that he went each day at 9.30 am from Chelmsford to Birmingham and returned back to Chelmsford every day.

Arrangments were made with the Post Office to delivery all mail at 6.30 am. The Post Room at Chelmsford was put onto shift with the early shift starting at 6.30 am.

All cheques and remittance advices were put into a separate bag. Mail opening had to be completed by 9.30 am. The courier would then take this bag along with any other mail to Birmingham.

At Birmingham the Sales Ledger staff would process the Beechams cheques which had to be completed by 12.30 pm. In the afternoon they would process the Britvic cheques. Arrangments were made with the Bank at Birmingham for a late banking after 4 pm.

The Sales Ledger function was successfully transferred without a hitch. In fact the operation was made more efficient resulting in less staff required for the combined Birmham operation which netted staff cost savings of more than $250,000

Company policy was to invoice from signed copies of delivery notes including the crediting for returned empties.

A problem arose getting the delivery notes back for the big trailer deliveries. These would be delivered in the early hours of the morning. A security guard would be notified of the delivery and let the driver in on arrival. The diver would unhitch the trailer and leave it at the customers premises. The empties were picked up a few days later.

As one trailer would often have over $100,000 in product, it did not take long for the uninvoiced delivers to mount up and be several million dollars with an annualised interest cost of over $200,000 alone. There was also an adverse effect on cash flow, sales, production planning and interest costs.

There were frequent mistakeson deliveries and resultant sales invoicing as orders were entered directly on delivery notes but often had to be changed because of out of stock situation - due, in a large part to all the unprocessed deliveries/sales. The number of incorrrect sales invoicing caused significant problems and frustrations between the company and its largest customers which could not be allowed to continue.

Past measures of throwing labour resources at the problem chasing the delivery notes had short term benefits, at the expense of high labour costs, but there was an inevitable reversion as soon as the resources were removed.

The company was persuaded that a total re-think was necessary as there could never be any control over the staff of third party customers.

The real problem was identified as an inappropriate invoicing policy ... late return of signed delivery was really just a sympton of this problem. The business had grown and was, in part, fundamentally different ..... there were now Retail and Wholesale deliveries which required separate invoicing policies.

Retail deliveries continued on the current system. A new wholesale invoicing policy was implemented with the agreement of the wholesale customers. Wholsale deliveries were broken down into 2 separate transactions ... a delivery of product to the customer and a separate return of empties at a later date (normally a few days at the most).

The second change was to record the order onto an order form first. The product was then taken or "picked" from stock using the order form. If there were any cross outs or substitute products supplied as a result of stock outs the amendments and cross outs were made on the order form. The delivery note was only made out when the product was loaded onto the trailer. It was stressed to staff that what went on the trailer had to match exactly what was on the delivery note without exception - this was under the company's control. The customer was then invoiced from the depot/factory copy of the delivery note as soon as it left the factory or depot. The customer did not know or care what he was invoiced from - the customers only concern was that he was invoiced for what was delivered. The signed copy of the delivery note was married up with the depot copy when it came back.

The Bank was upgrading its computer hardware and software system under the control of the IT Manager. He had used a "PC Automater" to convert the data from the old system to the new system. It became apparant that a number of mistakes had been made in the conversion rules.

The errors that arose during testing raised serious concerns with the whole implimentation in danger of having to be abandoned. 

Three days after commencing at the bank I was seconded to the implementation and subsequently put in charge of the conversion and parrallel run which ended up going 5 weeks.

Prints of the static data ( things like Resident vs Non Res, Country code, Industry code, Institution code etc) which banks use for both Bank and Regulatory reporting, had to be printed from both systems, compared, checked and corrected when appropraite prior to commencement of the parrallel run.

At the end of each week of the parrallel run balances and interest accruals where compared and where different, the underlying reason identified with appropriate corrective action taken.

The installation was done in the last week of October. Conversion to the new system was on the 1st of December. A quarterly current account interest payment made on 15 December. The company's year end was on the 31st of  December.

As a result of the dates above I had to rebuild the systems over 3 separate databases and 5 different time frames to get everything correct.

The conversion and implementation was a success. 

All customer queries on retail interest payments made on 15 December were checked.

The banks entries for current account debit and credit interest figures were found to be 100% correct.

Banks pay and receive interest on a variety of financial instruments. Banks want to be certain that the interest is correct.

Because loans and deposits are for varying amounts, varying currencies, varying interest rates based on different account types it is impossible to check gross interest paid and received from one month to the next like non bank organisations check income and expenses accounts.

The Bank asked me to investigate its Interest Reasonability process to verify its accuracy and appropriateness.

I went through what was done with the person responsible. It took an hour to explain everything and 2 weeks a month to actually do the job. When I asked how many errors are identified each month I was advised that no errors had ever been found in all the years that the checks had been done.

My immediate response was that the checks don't work then because I did not believe there were never any errors. I decided that I had to design something completely new.

Interest is simply the mathematical equation

   Interest  = (Principal  x  Rate  x  No Days ) / Days in Year

It is impossible to check the interest between accounts and in summary where the amounts, currency, term, start date, account type are all completely different.

The only variable in the above equation that will be similiar is the interest rate (within a range within each currency).  So I used the actual Interest I wanted to check to calculate the Interest rate. If the calculated interest rate, using the actual interest to be checked was within the expected range for the currency and account type, the actual interest would be determined to be correct. If not the account would be examined to determined why.

To do the above I wrote SQL programs to calculate the Interest rate ..... if on AUD term deposits the calculated rate gave an interest rate of between 5% - 6%  I would consider those to be within an acceptable range but it the calcualted rate was say between 1 and 3 percent or say above 6% I would need to investigate - exception reporting.

The Monthly Interest rate equation was

         Interest Rate  =   Actual Interest / Average Balance

eg     Principal  = $5,000, Interest Rate = 5.25%, 
         No of Days = 30, days in Year = 365 

                I      = ((5,000 * 5.25%) * 30 ) /365

                       =  $21.58

         Av Bal     = (5000/365)*30

                       =   410.96

        Calc rate  = 21.58/410.96

                       = 5.25

The Result

A monthly report was produced sorted by Currency, Account Type and the Calculated rate. Any rates outside expected results were investigated. Initially a number of errors (mainly operational) were found which were corrected.

It now took only about 30 minutes a month to check all the Interest with confidence.

The new system took around 30 minutes a month and detected exceptions for investigation. I also had to get the software supplier to amended and rebuild the Average Balances before the project could begin as I found the mathamatical calculation rules to be incorrect.

There was no physical control of assets and the depreciation calcualtions where all done manuall on a 14 column analysis. Because of the time it took it was only done once a year.

Arranged for all the assets to be entered into a spreadsheet database for control purpose with assets codes to track similiar items (desks, printers, cupboards etc).

The spreadsheet calculated the depreciation for the month along with profit on sale of assets and various summary reporting.

At the time Mr Keane started as Finance Manager the Accounting staff and systems were is disarray with only 1 long term person still there although he had already resigned and about to walk out the door in a matter of weeks.

The PC based accouting systems that were used to produce the financial and management accounting records were unreliable, kept crashing, and were in effect a black box (there was no way of determining what made up the figures and there was no documentation on how to do any tasks.

The main banking package was to be replaced in 6 months with an upgraded system. When this was done the existing financial and management systems systems (using around 300 visual basic programs and Excel) would not work anymore - either the Sydney head office had to rewrite the 300 VB programs or a complete new Financial and Management system would be required - designed, specified and implemented.

The first priority was to stabalise the current situation. Mr Keane sat with the person about to leave and did every job documentating as he went. Replacement staff were found and trained after this.

Mr Keane advised that the old VB programs were very poor and it would be a total waste to even contemplate converting them. Mr Keane advised that he could design, specify and impliment a new system while still doing his main duties. This was accepted. Mr Keane built a data wharehose system using downloads from legacy systems together with Microsoft Access and Excel. This involved:

a) Downloading the key data in the main banking system (acct bals, acct names & addresses, postings & other information

b) Building supplementary tables in Access

c) Running Access queries to join the data and export to Excel were the information would populate pre             formated spreadsheet reports

Under the old system 1 Balance Sheet and P&L was produced in around 5 days.

Under the new system Divisional Balance Sheets and around 50 P& L's ( both summary and detailed level) were produced in half a day.

The reports included, YTD, Month on Month with Budgets, Actuals and %age Variances with full back up and drill downs. The reports were published on the network for profitabability managers to view as and when they required. The new systems also enabled the following to be done.

• Build a new budget system with the first budget produced in 3 days at profit/cost centre level/ profit groups/division and London branch level.
• Take new and additional tasks from other departments that were being closed down and moved back to Sydney
• Accuracy of reporting was better (in some cases right for the first time)
• A number of new projects were undertaken for a variety of clients (Head Office, Bank of England etc)
• Overtime and weekend working was no longer necessary and was able to be eliminated.

Large journal entries were often required to be entered because of the allocation of costs to the various projects and/or costs centres. These journal entries could be +/- 500 lines which took a long time to prepare, review, enter and check for accuracy.

A Excel spreadsheet was created that looked the same as the journal input form on the mainframe software package. Journal entries were  prepared using the spreadsheet.

The data was was mapped to screen positions on a separate worksheet or tab. The contents of this tab were copied from the spreadsheet and then pasted to the software program using the standard Windows copy and paste functions.

Journals that would take hours could be be posted in less than a minute and they could be used again next month by simply copying from the previous month and amended the narrative or other information as required.

The time taken was reduced from hundreds of hours a month to only a few hours with the resultant labour cost savings.