Imnoss can help you in many different ways. Below are just some examples of how we have delighted others and saved them time and money.
Monthly performance report
A law firm used printable performance reports for discussion in monthly team meetings. The reports were being prepared manually, and as a result were limited in scope and were often delayed.
Business Solution
We created an automated process which prepared the reports without any human intervention. This saved two days per month of a senior accountant’s time with the press of a button.
As part of the automation process, we were able to identify and add in additional metrics enabling the teams to achieve greater insight into that month’s numbers and improving the quality of discussion in the team meetings.
Technical Solution
We prepared custom SQL procedures and views to retrieve the relevant data from the company’s time recordal and billing databases. The output of these queries was loaded into an Excel workbook using PowerQuery, enabling them to be easily refreshed.
A printable “dashboard” was created in the Excel workbook which combined formulas, pivot tables and charts to aggregate and present the data for each team.
Finally, a VBA macro was added which refreshed the data and then formatted the resulting sheets so that elements like tables and charts were not split across pages when printed.
Project Monitoring Sheets
The client provided services as a series of projects. Each project was provided with an Excel spreadsheet to provide a focus for collaboration between finance, project managers, and sales.
Maintaining the data in the sheets was time-consuming and often fell behind. Monitoring for completion was also time-consuming; but allowing completion to lapse lead to further problems down the line. Finally, aggregating the data from all the sheets was a manual process and took multiple days each month.
Business Solution
We helped the client update the project spreadsheets so that they linked the company’s databases and could automatically populate as much of the spreadsheet as possible. This reduced the time wasted on completing the sheets (estimated at approximately 1 person-day per project) and ensured the data was accurate and up to date.
In addition, we helped the client create a system to aggregate the data from all the sheets. This aggregated data was used to automatically generate reminders to people to update their sections, saving time on manually checking and reminding. It also provided summary data for reporting on the status of projects. This saved approximately four person-days per month.
Technical Solution
We used PowerQuery to create connections from the project sheets to the various databases used by the company. These connections were set to automatically refresh every time the project sheets were opened so the data was always up to date.
In addition, a central Excel Workbook was created which used PowerQuery to retrieve data from each of the project sheets. This data was aggregated into a number of tables – some powering the reminder system and others used to report on the aggregated data.
System Integration
This client whished to integrate the data in their systems (project management, HR and finance). There was a lot of duplication of data fields, and in many cases the data did not match up.
One option which was being considered was changing the one or more of the systems to those which were sold as being integrated or enabling integration. However there was also resistance as none of the affected teams wanted the overheads of having to re-train on a new system.
Business Solution
With the client we identified with the business which data fields in which database were considered to be the primary data and which were to be secondary data.
We then configured each system with suitable reports and bulk imports so that data could be quickly extracted from and written to the databases.
Finally, we created a system which would take the exported data from the systems, compare the data and produce suitable bulk upload files which would update the secondary data as required.
While the nature of the systems meant that an employee had to run the exports and imports, the overall process of syncronisation took less than 20 minutes to perform.
We had created the system in close cooperation with one of the employees of the company. A few months after creating this system, we were pleased to receive an email from this employee noting how easy it had been for them to add a new data field to the process – we had not been and did not need to be consulted for such updates.
Technical Solution
In each system, we set up the export and import functions to enable exporting to and loading from CSV files.
The CSVs were saved to specific file locations, from which they were read, manipulated and compared using PowerQuery in Excel.
The results of this comparison were automatically exported as CSVs using a VBA macro.
We discussed using PowerAutomate to perform the exports and imports. However, this could not be made to work consistently with one of the systems, so on cost grounds we agreed to keep the import/export a manual process.
Data Migration
Migrating from one database system to another is a difficult and time-consuming process. The difficulty was compounded substantial differences in the design of the two databases and by a desire to run both the old and new systems in parallel and check consistency.
Business Solution
We supported the business by creating a system to map the data from the old system into the format used by the new system. The mapping process was automatic – the only manual part was downloading a series reports from the old system and saving them, a process which took less than 30 minutes.
This mapped data was loaded into the new system to start the parallel trial. During the trial the data in the old system was retrieved and mapped and then compared against data in the new system, so discrepancies identified, and corrections made.
Once set up the monitoring of the parallel run took a few hours, instead of the days which were envisaged manually spot checking the two data sets.
Technical Solution
Neither the old system nor the new system had adequate APIs so we had to create reports which extracted all the required data. These could be quickly run and the results stored in a target folder.
A series of PowerQuery queries were set up to read the old data and map it to the table structure of the new system.
One particular complication arose from the need to merge two mismatched, date based, tables into one. Where one table had values for a particular date and the other did not, the previous data had to be “filled down” to fill the gap.
Further queries read the new system data, and compared the two tables identifying where data was inconsistent.
Overall, downloading the data and refreshing the queries took about 20 minutes.
Budget Tracking Dashboard
This client had about 50 people working across 30 projects in five project groups all for one client. That client required weekly updates on the time and costs incurred. Preparing these reports was made substantially harder because some projects crossed over the project groups and each group had an expected budget.
Business Solution
We created a dashboard which would automatically update using the data stored in the time recordal and project management databases. This dashboard was set up to read business rules from a user-maintained table and apply these rules to apportion spending across the project groups. Each new report would take seconds to update, rather than the one day per week it was taking to do manually.
Technical Solution
This solution required some custom SQL views to be created in the databases. After that PowerQuery was used to retrieve and manipulate the data.
The dashboard was created in Excel as this enabled the business rules table to be easily edited.
Data Integrity Check
This client had been recently hurt by some data errors in their new cloud based database system.
While the user interface was undoubtedly elegant, it offered no easy way to discover these and similar errors, and checking through multiple records manually was laborious.
Business Solution
We discussed the clients specific business requirements and created a series of business rules. These were applied to data exported from the database to generate a series of dashboards. These dashboards could be quickly reviewed and reported on erronious, missing or inconsistent data. They would also identify areas where problems might occur in the future so these could be pro-actively addressed in good time.
Technical Solution
We were able to create an export from the database in CSV format which could be easily run and saved to a target location. A series of PowerQuery queries were created to implement the business rules and generate data sets which were suitable to be displayed in PowerBI.
Reference Assignment
This client’s system was setup to assign a unique reference to each invoice. However, the client needed an additional group reference to be automatically applied based on certain business rules, such as the client, date, and Purchase Order/Business Unit Number.
The existing solution was an Excel spreadsheet to track the assignment of group references. In theory, when a new reference was needed, a row would be added to the sheet with that reference. Likewise, when an invoice was raised, the sheet would be manually checked and the group reference retrieved to be manually added to the invoice.
The problem was that this was not only time consuming, but prone to error as the spreadsheet made it difficult for multiple users trying to access and edit the same spreadsheet. Errors in the group reference could be time consuming to correct, in some cases requiring many invoices to have to be credited and re-created a process that could take a day.
Business Solution
We modified the billing system itself to apply the business rules and automatically assign group references as they were required and write them into the invoices. We additionally created a reporting system which identified where group references had been assigned, confirm that the invoices had the correct references applied*, and aggregate the invoice amounts for reporting elsewhere.
* an invoice created and then stored as a draft might need to be assigned a new group invoice later.
Technical Solution
In a parallel database on the same server as the billings database we were able to create a stored procedure which would apply the business rules and generate or retrieve the correct group reference. We further identified existing fields in the billings database which could be re-purposed to enable the business rules to be edited by users of the billings system without needing modifying the stored procedure.
We were further able to add a SQL table trigger into the system which would add or update the group reference to the record when the invoice was saved.
To help the client tracking the utilisation of these group references, we created an Excel workbook which had a series of PowerQuery based connections to the database. These tracked which group references had been assigned, and enable aggregation of the amounts invoiced based on the group reference.
Mailmerge to PDF and Email
Our client wrote a personalised letter to every employee before the new year. However, with the pandemic, the company now needed to email a personalised PDF letter to each employee.
Business Solution
We created two systems. The first automatically created the letters in PDF format, and save them into the desired location in the clients SharePoint site. The second created the emails, attached the PDF documents and sent the email. As a bonus we were able to have the emails sent out “on behalf of” the Managing Director.
Technical Solution
Word mail merge gives the ability to create letters and emails for mass mailings. However, it does not natively have the ability to convert the mail merge into separate, properly named, PDF documents. We created a macro in VBA which would enhance the mail merge process to enable saving of the PDFs with desired names.
Word mail merge to email is similarly limited, and does not allow for a user to add attachments, use the send on behalf of feature in Outlook, and even have a custom subject. We created another VBA macro which would enhance this mail merge to email system to enable all of these features.
We have made both macros available on our website, and have created tutorial videos on YouTube describing how do use these yourselves.
Holiday Conflict Check
This company wanted a system for identifying where annual leave requests would result in a lack of staffing in key groups.
The company used a matrix structure with employees engaged in overlapping client teams which existed independently of the company’s reporting hierarchy. The company wanted the ability to ensure minimum coverage in key client teams as well as more traditional hierarchical groups.
Off the shelf leave tracking systems were not able to handle this complexity and typically only allowed for employees to be in a single group dependent on the reporting hierarchy.
Business Solution
We created a system using multiple custom forms to enable easy maintenance of client teams and the recording of leave. When a new leave request came in, it was automatically checked to ensure that minimum staffing levels would be maintained for all relevant client teams.
If a conflict was identified the system had an override facility enabling a senior manager to authorise leave, despite it effecting the minimum staffing.
The system was additionally capable of automatically sending emails to confirm changes to leave and help in the maintenance of the client teams.
Technical Solution
We created an MS Access based back-end database to store the relevant data. We then configured Excel spreadsheet as the front end.
We used VBA to generate multiple forms which provided the user interface. The business rules were implemented by a combination of SQL queries on the database and VBA functions in the spreadsheet.
Excel was used because it was familiar to the company and additionally system was configured to be able to generate a series reports directly into the spreadsheet itself.
VBA was additionally used to link to Outlook to enable automatic emails to be sent. A confirmation email would contain a leave chart generated in HTML. Additional emails would be created and sent to team leaders to enable the maintenance of the members of the client teams.
