top of page
Numbers

Case Studies

Please find below examples of work performed by our team to help you understand the kinds of services we offer.

 

In each case certain information, including the client's business name, are withheld to protect the client's privacy.

01

Debt collection ledger

The problem

Our client is 'The Agency', an Australian government body. The Agency has an ongoing arrangement with a debt collector 'Baycorp' who collect overdue payments from past customers of The Agency on their behalf. Every month Baycorp sends a report to The Agency listing overdue payments collected over the past month. The Agency enters this information into an internal database to verify once an account has been paid and for record-keeping purposes. This requires the manual entry of approximately 800-1000 records per month. The Finance branch of The Agency, comprising four individuals, undertake this work, which takes two to three days to complete, greatly affecting their ability to complete other work. The Agency require a solution which (a) makes the above process more efficient and (b) still enables the Finance Team to enter the data manually. This is due to a workplace policy which requires The Agency to maintain full manual control of the task, and prohibits fully automating tasks (i.e. in unattended mode) due to the sensitive nature of the data.

​

The solution

​Crystal Logic built an Excel workbook for The Agency which reduces the time taken to complete the task from three days to 20 minutes. Only one person is required to complete the task. To perform the task, The Agency must (a) copy the data from the Baycorp monthly report and paste it into the workbook and (b) export a full copy of their customer data (including payment history) from their internal database and paste it into the workbook. The workbook marries the customer data with the newly collected payments and produces a ledger which the Finance Team can import into their internal database en masse, thereby circumventing the need to enter the new payments individually. Furthermore, the task is performed 100% on-site by their own staff, thereby maintaining their strict privacy policy.

02

Backdated allowance payout

The problem

The client 'AdTech' is a regional dealer of mining machinery and equipment. Their blue collar workforce are covered under an Enterprise Agreement (EA) award. Since 2017 the EA has seen multiple revisions. One of the conditions of employment prescribed by the EA is a description of how Public Holiday Allowance should be calculated and paid to team members. This allowance is paid to employees expected to work at least one public holiday during the year. An eagle-eyed employee notices that the description is loosely worded and allows for multiple interpretations. The employee calculates that AdTech has been paying the Public Holiday Allowance according to a less generous interpretation and airs his grievances with the Human Resources department. AdTech agrees to re-calculate the Public Holiday Allowance paid to approximately 1200 former and current employees since 2017 according to the more generous interpretation of the EA and pay the difference to the employee. Due to the sensitive nature of the project AdTech would like a third party to perform the calculations.

​

The solution

Crystal Logic obtained a number of detailed reports from the AdTech payroll team containing the information necessary to re-calculate the Public Holiday Allowance delta amount owed to each eligible former and current team member (i.e. received the allowance at some point during their employ) based on the more generous interpretation of the EA. The information required to perform this calculation included work roster, actual hours worked, base hourly rate and number of public holidays worked each year per team member since 2017. Adding to the complexity of this analysis is the fact that the team member's work roster and base hourly rate varied during their employ, making it necessary to 'map' the team member's unique circumstances at each point in time over a seven year period, as this would affect the amount of Public Holiday Allowance owed to them. Crystal Logic provided AdTech with a detailed summary of Public Holiday Allowance owed to each eligible team member based on the more generous interpretation of the EA.

03

A faster way to work

The problem

Our client is an Italian organisation 'Filius' focusing on waste collection, treatment, disposal, and materials recovery. Filius uses a Microsoft Excel file to keep a record of clients serviced. The workbook is organised into sheets - one for every month of the year. Once Filius has serviced a client they add an entry into the ledger with the details of the job, including the client's business name, address, weight of product delivered and associated transportation tariff. Most of Filius' clients have multiple addresses throughout Italy with different tariffs depending on the distance and route travelled to deliver the product. The clerk must manually enter the client's details into Excel including the address and tariff and uses a printed reference guide to do so. They require a more efficient way to perform this work as business is going well and their client base is growing, along with the number of jobs per day.

​

The solution

Crystal Logic enhanced Filius' Excel workbook with some additional features which makes it much simpler to add new jobs into the ledger. Firstly, rather than manually entering the client's business name each time the clerk now selects the appropriate business from a dropdown menu. Next, instead of having to manually enter the relevant delivery address the clerk now picks the appropriate address from a second dropdown menu which changes according to the business selected initially. Hence, the second dropdown will only show the addresses for the business selected in the first dropdown menu. Finally, the transportation tariff for the selected address is automatically populated as it is unique to the address selected, removing the need for the clerk to retrieve this information from their reference sheet. We also added another feature to the workbook which provides a sum of the product delivered to each of Filius' clients for a given month. Filius have full control of the workbook and have ongoing access to our world-class support team should they require any further assistance.

bottom of page