Please click on one of the arrows below to read a recent case study.

Technology: Microsoft Power BI and Microsoft Excel
Client: Large distiller and distributor of some of the most famous Irish and internationally spirit brands
Year: 2020

Our client was seeking to develop a new global reporting suite of dashboards for the procurement side of the business, detailing high level spend reports across the business with a focus on a number of newly created KPI’s (Key Performance Indicators) across areas such as Cost Optimisation, Risk and Innovation.

In this instance the Microsoft Power BI business intelligence platform was chosen for the project.

The Power BI dashboards were developed for a top senior level executive audience. Data was reported summarised up to the highest level but then with interactive abilities for the user to delve into the detail using slicers to filter the data and to step further down the data hierarchies in place.

The reporting suite created, took in data relating to budgets, capex spends and P&L data and produced a very visually pleasing, easily readable, very clearly presented factual figures. With the ability to filter by procurement categories and sub categories.

Olas worked closely with the client to firstly help finalise the metrics/formulas of the newly created KPIs and to help gather all the required data to perform those calculations. The next task was then to design MS Excel templates that would be used to capture all the necessary data. In the end, we ended up with three source data MS Excel based files, that would be maintained and updated by the procurement team for continual data input going forward.

Once the data was captured and we had a blueprint on paper of what we needed to achieve from a reporting perspective, Olas then proceeded to take those inputs and to transform the data into a powerful suite of carefully thought out Power BI dashboard reports. The end result provided very clear views of detailed analytical data covering procurement KPI’s and spend right across the entire global operation.

We took advantage of the powerful business intelligence capabilities of the Power BI platform to seamlessly merge and transform data from separate inputs together and to produce interactive reports and visualisations. The reports were created using Power BI Desktop with an end goal of making the reports more readily available for the wider business audience using the Power BI Service platform so as to be able to host the reports in a web browser.

In this instance, by storing the three MS Excel source files from an OneDrive location, the automatic refreshing of the reports could also be achieved. Meaning that users would be able to access these reports themselves in a web browser, loaded and refreshed with the very latest data available to them at that moment in time.

Technology: Microsoft Excel
Client: A large multinational personal healthcare products and consumer goods manufacturer and distributor
Year: 2020

This project was headed up by our clients Global Commercial Capability team located in the UK but overlooking their 1,200 plus operations in multiple sites across Asia and Africa. The goal was to create a global standardised tool that could be used by the local markets to complete assessments upon their operations. The results of which could then be consolidated and reported regional and indeed globally.

As the tool was to be rolled out primarily across Asia and African locations the tool needed to be able to be available offline, easy to use, intuitive, multi lingual and not be to technologically complicated or resource heavy. For this task MS Excel was identified as the ideal tool for this project.

The project consisted of two main elements, a MS Excel Distributor Assessment file to be completed by each distributor and a MS Excel Consolidator Reporting file, used to bulk import and report upon the returned completed assessments. The project was further complicated by the need for the need to customise the assessment templates text regionally and also to include the ability to translate both tools text into different languages.

The assessment itself consisted of 45 Standards divided out across 6 Platforms, with standards either categorised as General, Commercial, Finance, HR or Supply Chain. In completing a standard, the distributor would be presented with a leading statement, from which they would need to select from a choice of four statements the statement that best represented their operation. A facility was also included to allow for the addition of evidence to support the chosen statement. Upon completion of a full assessment, the assessment would become locked down and the distributor would be given a score (Gold, Silver, Bronze). With the assessment completed, now open to them in the tool would be a printable Scorecard reporting how they performed in each Platform and Standard. Also an Action Plan, generated detailing what actions they need to take for each Standard in order to move up to the next level.

This completed assessment would now also be shared up to regional and global teams for consolidated reporting. Using a separate MS Excel based Consolidator tool, the completed assessments could be bulk imported and merged in the Consolidator tool, with regional/global pivot chart reporting outputted.

The end result was a highly portable and adaptable application that will become a key tool in assessing and managing the many global distributors for years to come.

Technology: Microsoft Excel and Sage Trial Balance, Sage Extracts, Credit Card Statements
Client: A large hotel chain with a wide variety of properties and amenities
Year: 2020

Our client was putting together a management pack of tools and processes that they were rolling out across all their hotels in their Irish operation. As part of this suite of tools was the envisaged Account Control Spreadsheet Tool.

Their central accounts team had put together the core of a very powerful custom made accounting tool, constructed within a MS Excel spreadsheet file. This file was pulling data from a huge number of different resources, such as account extracts from Sage, Bank Statements, Trial Balances and Credit Card statements. They were then using this Control Spreadsheet Tool to help both report on and to balance their accounts on both a daily and monthly bases. However, they faced a huge problem with the file! Although it was operational from a functional perspective it was not usable from a user’s point of view!

The construction of the file was so complex that there were left with thousands of external links to other files. These links were both pulling in data directly into cells but also used as part of formula and look-ups. The result of all this was a MS Excel file that was unmanageable from a user’s perspective as it would take an age just to load. Every time Excel then needed to perform a calculation (which is very often) it would hang. This waiting time could also end up being multiplied further in instances where other MS Excel files may have been open at the same time.

In delivering a solution Olas not only addressed these key performance issues but also brought some great extra benefits to the project. Transforming it from a spreadsheet based file to that of a fully-fledged MS Excel based application.

The following is a summary of tasks Olas sought to complete as part of this project:

• Remove the need for links to external resources
• Reduce and improve formula where possible
• Improve overall performance and load times
• Automatically creating new journals, to be posted back up to Sage
• Add Worksheet and Workbook Protection
• Create an admin section for managing settings and drop-downs
• Introduce a user interface and menu bars to make the navigating the many worksheets in the file a much more manageable task.
• Add a clean, unified colour scheme and look and feel

To achieve these goals, Olas sought to take an inventive approach which made use of a daily import process through VBA Coding, which in only a matter of seconds would bulk import data from 64 external files in a single go. Day sheets would then be created from hidden template sheets contained in the file for each day in the month. When rolling over to the next day, all calculations from the previous day would be stored as values and the 64 source data import sheets would be cleared out and replaced with the new day’s data. This meant that at any one time the file would only contain raw source data and formula relating to just the current day; thus massively reducing the amount of formula required in the file at any one time. This approach proved extremely successful with the performance of the file going from frustratingly unusable to incredibly quick.

Technology: Microsoft Access, Microsoft SharePoint, Microsoft Word and Microsoft Excel
Client: One of Ireland’s Top Universities
Year: 2020

As part of this redevelopment project, Olas breathed new life into an older MS Access application that had evolved internally in the university over a large number of years. The Thesis database was a central repository for recording students, examiners and Thesis data that then would be used in reporting on and creating all external typed letters from the department.

A key incentive for this project to get the go-ahead was the need to find a new home for the database as the traditional file-server networked drive that it sat on was now being decommissioned in favour of newer cloud based technology. What was now once a standalone MS Access application was now redesigned as a MS Access front-end application (screens and functions) with the data migrated to a SharePoint Lists back-end setup. This new setup was successfully achieved with no loss to performance or function.

In redeveloping the solution, we gave it a new improved interface, search features, security and reporting to MS Excel and MS Word based mail merge functions to create letters. We firstly began by streamlined the system by removing obsolete elements and to redesign the setup of the system to make it much more usable and maintainable for the users going forward. The other key part of this project was to introduce a new, detailed, custom mail merge facility. The new setup led to the creation of 35 separate MS Word letter templates which then would be called upon by the systems custom mail merge process, which would further populate the more individual elements of the letters. These generated letters could in turn be saved as MS Word document, as PDF’s, as email attachments or even as the body of an email itself. For a single student or examiner record it could be the case that the system could generate a single email with up to five separate generated documents attached. The setup of the letter templates as MS Word documents has meant the letters are extremely easy to update and format as required by the users going forward, or to even add new letters to the catalogue of documents that is populated by the system.

The end deliverable was a hugely successful migration of an old MS Access database with a soon to be made legacy environment setup, to that of a new setup utilising a SharePoint backend. While also providing a much needed modernised look and feel with an extensive mail merge facility.

To achieve these goals, Olas sought to take an inventive approach which made use of a daily import process through VBA Coding, which in only a matter of seconds would bulk import data from 64 external files in a single go. Day sheets would then be created from hidden template sheets contained in the file for each day in the month. When rolling over to the next day, all calculations from the previous day would be stored as values and the 64 source data import sheets would be cleared out and replaced with the new day’s data. This meant that at any one time the file would only contain raw source data and formula relating to just the current day; thus massively reducing the amount of formula required in the file at any one time. This approach proved extremely successful with the performance of the file going from frustratingly unusable to incredibly quick.

Situation
Our client, a large Irish FMCG distributor were in a position whereby they had no oversight on annual promotional spend. As a result, they were unable to keep to promotional budgets often causing large over-spends. This left the organisation in a vulnerable financial position, committed to large promotion spends but with limited funds available to meet these.

The organisation had existing ERP systems in place but these were unable to manage promotional budget allocations and usage; a gap existed that, if allowed to remain unaddressed, would have left the business in an extremely vulnerable position.

Solution
Olas discussed the problem with the client, listening to their concerns. With careful planning and detailing, the Goldberg solution was developed to solve the problem.

Goldberg is a bespoke solution using existing in-house Microsoft technologies that allows their brand and customer owners to manage annual promotional budgets and prevent overspend.

Controls to prevent overspend were implemented to ensure that locked reserves can be withheld from budgets until approved and at the same time staff cannot exceed their allocated promotion spends.

The application integrates with the existing business critical ERP applications and provides the company with the “missing link” covering their current promotional spend.

Since it’s introduction, the application has not only provided insight into promotional spend but has also highlighted issues with data in their existing applications.

Key Benefits at a Glance
• Used by all management and sales staff on a daily basis
• Provides valuable insights into promotional spend
• Provision to lock away customer/ brand budget reserves
• Prevents overspend of allocated customer/ brand budgets
• “Ability to sleep peacefully at night”

Key Features:
• Promotion Approval by both Customer and Brand Owners
• Email notification of any changes to each promotion.
• Budget reserves held (released only by senior management)
• Clarity on planned promotion spend & volume Vs actual costs volume

Technologies Utilised
• User friendly MS Access based frontend.
• Data securely held in SQL Server
• Reports to MS Excel
• Data copied to/from the following sources:

• LiquiStat (automated)
• Infor (automated)

• Manual (validated) data entry
• Automated data export to other enterprise systems

Situation
Our client, the Irish office for a large Multi-National beverage producer, importer and distributor needed a solution that would assist their staff in planning their annual base, promotional price and volume forecasts and to compare these forecasts against actual sales figures.

This solution would also need to be capable of managing Annual Operating Plan (AOP) and Latest Estimate (LE) forecasts and to manage sales data coming from both an internal system as well as external sources.

The company wanted to manage their sales forecasts as accurately as possible.

The organisation had existing software systems in place but none had the capabilities to lend themselves towards forecast accuracy or to manage the actual’s data from disparate systems.

Having an existing long term business relationship with Olas, their Strategy & Planning Director contacted us.

Solution
Olas discussed the requirements with the client, offering honest advice and realistic options for a solution.

When our client decided to proceed with the Croí solution, Olas worked with them to ensure that they would receive a tool that would be simple to use yet scalable for future business growth.

Possibly unsurprising given its functionality, but to date, there has been complete buy-in from all staff on the solution. Croí has become the standard reporting tool used by all management and sales staff on a daily basis.

Now, senior management always know where the business stands at any point in time and how each rep has been performing.

Key Benefits at a Glance
• Used by all management and sales staff on a daily basis
• The organisation now has a vastly improved forecast accuracy
• Satisfaction with the solution since its launch has been such that it is now planned to roll out into other EU regions.

Key Features:
• Cloud based application that can be accessed by staff remotely
• Allows each ‘customer owner’ to plan monthly sales cost and volumes
• Allows reporting of monthly plans versus monthly actual’s
• Business Critical reporting capabilities:

• Year on Year P&L Reports
• Customer Discount Reports
• National & Regional Top Line Sales Reports

• Allows for scenario planning of sales volumes & returns

Technologies Utilised
• Cloud based solution
• Utilising existing Microsoft software.
• Usable on hand-held devices
• Reports to MS Excel
• Data copied to/from the following sources:

• Internal SAP systems
• Sales data supplied from external partners

Company Background
Set up in 1975, our client is headquartered in USA and has 2 locations in Ireland and many other locations across the world. They have approximately 95,000 people currently employed worldwide.

Project Background
In 2013, our client engaged Olas to document a process and deliver training to managers on this process and the corresponding transaction in SAP. These managers would not be using the system on a day-to-day basis, but it was important for them to understand the process so they were self-sufficient running their own queries and reports.

Olas Approach to the Project
Initially Olas sat with various members of the data entry team to ascertain the overall process. From there we documented the process and developed training materials for this. We delivered a ½ day session to the client in Dublin and the training materials were shared with their colleagues in the USA. They were especially pleased as this project was completed under budget.

Every so often a new form of challenge arrives on our desk. Recently what we have been finding is that more and more of our clients want to incorporate maps both in their route planning and also in their business reporting.

Over the next two posts, we will explain how some of these challenges have been approached.

With the economic downturn, rising fuel costs and also the need to ensure staff out on the road are spending as much time with customers, Route Planning Optimisation has become more and more scientific.

We geocoded our client’s customer details into a database that interfaces with a simple web app.
When a staff member wishes to travel to a number of customers, they choose a start and end location and up to eight stop off points. It is possible to have more stop off points but Google charge for this. This should easily allow your staff to develop either full day or half day route plans.

The benefit of using Google Maps is that it results in a high accuracy with low cost application. Google Maps already has overcome the “Travelling Salesman Problem” (see http://en.wikipedia.org/wiki/Travelling_salesman_problem ) using complex algorithms and formulae.

What we have found is that many of our clients are now better placed to make customer visits by ensuring that time spent travelling is kept to a minimum. The algorithms used by Google ensure that regardless of the chosen outlets, the most optimum route will be selected.

Furthermore, unlike many of the “off-the-shelf” mapping software applications, Google automatically updates itself with new roads, etc. Therefore, there is no administration or ongoing cost involved in keeping the maps up to date.

Each route plan can be linked to a specific staff member. Optimised route plans can also be retained for future reference (e.g. for mileage expense calculation purposes).

Within Google Maps, it is also possible to provide summary information about your customers, including address information, Google Street View images, specific customer information (manager, turnover, customer investment, etc.).

If required, different coloured map markers can be used to represent your different customer groups.

All of this information can be held on an internal intranet, secure from prying eyes or can be hosted on our secure cloud based servers.

Our next case study will show you how maps can be used to represent your data within your reports!

Call us on 01-279 0020 today for a chat on how we can map your business.

In the previous case study we mentioned how we would be showing you how to use maps to represent your data within your reports. Now we will show you how to achieve this.

Over the last year, more and more of our clients have been asking us how they can plot their data on a map as easily as possible but at a minimal cost.

Utilising Microsoft Excel 2013, this task has been made quite straight forward.

By allowing users to render their data with Microsoft Bing Maps (Microsoft’s alternative to Google Maps), a simple but highly effective charting solution is provided.

The following visualisation allows us to display sales by location for April, May and June.

As part of Microsoft’s Business Intelligence offering, Microsoft Power View (along with Microsoft Power Pivot) provide a means to report on data from small to extremely large enterprise level datasets, thus allowing for quick but effective analysis.

Microsoft Power View is an interactive data exploration, visualisation, and presentation experience that will encourage intuitive ad-hoc reporting. Users can easily develop their own reports against their Business Intelligence (BI) data without the need for expensive reporting software.

Within a few minutes, your users can have an effective location based reports displaying the strength of your metric in proportion to other values by the size of the circle – the larger the value, the larger the circle.

Your data doesn’t need to be one dimensional however – in the following example, we’re plotting sales for April, May and June against each other on the same map.
We believe that you’ll agree that the visualisation is effective.

Call us on 01-279 0020 today for a no charge chat on how we can map your business needs.