SERVICES

Pricing Management Tool Project  - Charles Tyrwhitt Limited

Scope:

Charles Tyrwhitt was using an huge Excel spreadsheet to manage pricing. It was slow to use, unreliable (often crashing), inflexible (they couldn't add new Territories) and only one person could use it at a time. The project was to develop a tool where users could still use Excel to maintain pricing but without the limitations.

Process:

Discovery and requirements gathering.

Built SQL Server backend (tables, views and stored procedures).
Dynamic table structures to enable additional Territories.
Built Excel front end to present data from SQL.
Macros to enable users to perform DML statements.
Full auditability of database.
Documentation and training.

Technology:

Microsoft SQL Server

Excel (VBA)

Results:

The Price File, still in Excel, but used purely as a GUI to interact with the SQL database. SQL tables set up so that dynamic for Territories, Price Points, and other dimensions to ensure flexibility and scalability. File a fraction of the size of the original and more stable. Ability for concurrent users. 

Business Intelligence Project - Mimi and Lula Ltd

Scope:

Mimi and Lula is a children's accessories retailer. There are two main channels, e-commerce and B2B, both selling internationally. These elements are on two seperate solutions and there was no consolidated reporting. The aim of the project was to deliver a BI solution which included a data warehouse and visualisation tool. The primary requirement was to deliver an automated  Weekly Stock, Sales and Intake (WSSI) in Excel.

Process:

Discovery and requirements gathering.

Decision on what systems to implement (based on functionality and cost).

Set up cloud based ETL (Keboola), Data warehouse (Snowflake) and visualisation (Looker).

Developed flows from e-commerce (Shopify) to popualate various tables within Snowflake.

Built scheduled reports within the B2B system (Zedonk) to send to a company Gmail account.

Developed flows to extract zipped file attachments form the Gmail account, unzip the files, transform the csvs contained within them and then load into Snowflake.

Built flows to to consolidate the inouts into single tables for reporting in WSSI.

Built the Excel WSSIs with ODBC connection to Snowflake (the target PCs were Macs so had to do configuration on these).

Built Looker dashboard so that the WSSI data could be drilled into with self-service functionality.

Documentation.

Technology:

Keboola

Snowflake

Looker

Excel

Results:

WSSI data is automatically available by 7am every Monday with a simple Excel front end. The company now has a holistic view of category performance and is able to pre-empt any challenges or opportunities.

Dynamic Replenishment - Paperchase

Scope:

Paperchase maintained stock levels in stores and website using their Merchandising system (Merret). This system used historical sales and weeks cover to calculate ideal stock holdings. This did not taken into account any sales fluctuations such as seasonality and events and was labour intensive to maintain. A system was required to predict future sales to ensure stock availability.

Process:

Normalise historical sales by calculating missed sales (where stock would have been sold if it was in stock) and identifying anomalous sales.

Formulate a future sales curve for potential sales volumes for various levels within the product hierarchy down to SKU.

Created various user interfaces for maintaining required days cover, exceptions, distribution/grading, sales curve amendments and other amendments to ensure full flexibility for the users.

Calculate future sales forecasts and then reforecast based on actual sales on a daily basis.

Output a suggested stock holding with the ability to validate and upload.

Technology:

SQL Server

Alteryx

Tableau

Excel

Results:

At peak, maintenance by users was reduced from 8-10 hours per day to 1 hour.

LFL sales increase by 1 - 2% due to improved stock availability.

Forecasted sales volume by SKU, Site and Date for up to 26 weeks in the future.

Various data calculations that could be used for other reports such as dispatches and deliveries.

Business Intelligence System Implementation - EAT Ltd.

Scope:

To implement a tool which could produce a Balanced Scorecard dashboard to drive key behaviours within the business. There were new data sources to be taken into account and the total project had to be delivered within 3 months with a limited budget.

Process:

Project requirements definition.

Vendor and Product selection - final product was Birst as it provided an end to end solution (from ETL to visualisation).

Server set up and installation.

Datawarehouse design - selected the 'Appliance' version of software so used SQL Server as Datawarehouse.

ETL development of available data sources - using SQL views & stored procedures to streamline loads using Birst.

Source new data - liaising with 3rd party companies to gather new data via Web Services and FTP files.

Development of visualisation layer - Dashboard of required information with the ability to drill down to other levels.

Publication methodology - limited number of licences so files published on Google Drive using Google App scripting.

Technology:

Birst

Microsoft SQL Server

Google App Scripting

DOS Batch Scripting

Results:

A scalable and flexible solution which provided more than what was in the original requirement. System was used to produce some business critical reporting (such as the Daily Sales Report). The Customer Success Manager from Birst was so impressed by how the software had been used that I was asked to present our Birst infrastructure and visualisation at their annual conference.

Operational Budgeting and Forecasting Process - EAT Ltd.

Scope:

To enable the Financial Budgets (at weekly and period level) to be presented and reported upon at a daily level for the operational teams. Put in a process whereby the operational teams could enter their own forecasts and to use this to drive actions on key behaviours (such as labour scheduling and ordering stock).

Process:

Project requirements definition.

Budgeting

Mapping of data structure from Financial Budgeting system (Anaplan) to Operational data.

Design SQL Server database structure.

Develop methodology for breaking week/period data down to day.

Develop SQL Server stored procedures to populate daily tables from Finance system.

Built Excel front end for users to load and validate budgeted data.

Designed output for 'Budget Packs' for end users to validate and change data where required to feedback into the Budgeting process.

Final ETL to load data into reporting SQL database.

Sales Forecasting

Used the company's 'intranet' (Google Drive) to develop forms which could be populated by each location.

Data from these forms loaded into SQL with validation layer.

Reports/dashboards built to identify changes required on labour to hit budgets based on these forecasts.

Final ETL to load data into reporting SQL database.

Technology:

Microsoft SQL Server

Google App scripting

Birst

Crystal Reports

Results:

Production of the 'Budget Packs' at end of Financial budgeting process reduced from 4 days to 10 minutes. Improved accuracy of forecasted location performance enabling proactive actions to be taken. Step change in operation behaviour.

Business Objects Development - Monsoon and Accessorize

Scope:

To maintain and develop information requirements for the whole company (both visualisation via Business Objects but also the datawarehouse ETL via Informatica). To provide insight and tools to make effective decisions.

Process:

Hired and trained a team to manage the requirements.

Instigated more exception reporting and dashboards as opposed to data heavy reports.

Developed the Business Intelligence strategy for the company.

Conversion from OLAP to Datawarehouse reporting.

Upgrading and migration of Business Objects (from 4 up to XI R2).

Built a Business Objects User training programme (both Basic and Advanced).

Redesigned the Weekly Reporting suite.

Technology:

Business Objects

Informatica

Excel

Oracle

Results:

A Business Intelligence solution that met and exceeded the company's needs. A team of BI and Datawarehouse Analysts who could respond quickly to the fast moving environment, providing true insight to business KPIs and driving success.

Primary Skill Set

Qualifications

Sue Innes - Project Manager / IT Business Partner at Paperchase 2007 - 2023

Mat worked with us for several years resolving many issues and developing a variety of new systems. He’s an absolute genius; he really transformed how the business worked and did it with great intuition and understanding. He picked up new apps like Alteryx and Tableau really quickly, and became the go-to expert. His SQL and database skills are second to none. I wouldn’t hesitate to recommend Mat to anyone, and look forward to working with him again!

Pablo Nayager - Programme Manager at The Body Shop 2015 - 2016

A outstanding BI retail consultant who is highly productive and personable. I found working with Matthew to be a positive experience, he would give the information I needed in a very timely manner and without fault. As a consultant working with key members of the business he would support them so well, that he was nominated several times for awards despite the fact that he was not an employee. He picks up new technologies and concepts very easily, meaning that I would not hesitate to bring him on board onto any BI Programme that I will running in the future, regardless of whether he was a specialist in the technology or not.

Ed Godwin - People Director at EAT Ltd. 2006 - 2017

I have worked with Matt for over 15 years in different businesses, most lately EAT. Matt is an exceptional analyst and a pleasure to work with. Able to quickly grasp issues and understand end user requirements, Matt turns complex data into action driving information. Matt delivers projects on time and on budget and his solutions have had a direct impact on business performance time and time again.

I have no hesitation in recommending Matt and hope our paths cross again in the future.

Pete Byrne - Head Of IT at Monsoon Accessorize 2005 - 2014

It was my pleasure to work alongside Matt for 9 years at Monsoon. He has expert knowledge of Business Intelligence systems and methodologies as well as the technical expertise to put these into practice. In his time at Monsoon, he built up a BI team which he managed very well, leading by example throughout.

Above all the technical skills and expertise that he brings to bear, Matt truely owns the work he does, taking true responsibility for everything he delivers and taking ownership above and beyond his role.

He also has exceptional business analysis and project management skills. One of Matthew's obvious talents is his penchant for problem solving and lateral thinking. I have no hestiation in recommending Matthew.

Shea Hindman - Territory Manager at Business Objects 2000 - 2006

Matthew's key strength is an in-depth understanding of business drivers, needs of different users and how they could be better empowered and enabled through Business Intelligence solutions. He is hard-working and someone who puts a great deal of passion into his role. As a result, he is always exploring new ideas, looking to innovate and take responsibility in striving to support business change. He has very strong business partner engagement skills to ensure he gets value for money and service.

Get in Touch

If you would like to discover how BI Insight Solutions can help you and your business, please fill out the fields and click send.