Case Studies
Mosaic FP&A System Implementation
Helped Glean with the implementation of their Strategic Finance Platform from Mosaic that makes reporting, analysis and planning a breeze.
Setup the data connections and architecture to enable the mapping of departments and account categories from the Xero accounting system and the HR System into Mosaic system.
2023 Payroll journals were reallocated to departments and fully reconciled to the payroll system. Added departments in Xero, and recoded historic transactions by assigning departments, having aligned the departments to the revised organisation structure.
Devised new revenue account codes to capture the sales channel; product and geography as well as the corresponding cost allocations relating to customer acquisition and costs to serve.
Recoded Xero chart of accounts for the two main entities and added additional deferred revenue codes, and updated the account code mapping in Mosaic for the new P&L reporting formats.
Tested the Financial Model to be used for the 2024 Budget, and prepared departmental dashboards in Mosaic
Reporting
Improvement
Project
Setting up a new reporting suite for the PE Investors and Senior Management. This consisted of clearer management accounts packs for 5 main business units and a Consolidated Group Pack. Integrated reporting of the P&L, Balance Sheet and Cash Flow statements; working capital charts, business improvement plans, customer margin analysis. Additionally carried out a review of overhead spend, leading to a grouping simplification and improved trend analysis reporting. |
Consolidation project to support further acquisition funding
Consolidated a portfolio of 12 companies into a 3-statement financial model to be presented to the potential investors. This included 3 years of historical data together with a 36-month rolling forecast.
To enable future reporting to be more automated and streamlined, commenced work on using MYGIDE financial modelling software so that updating the future valuations can be easily obtained with the plan to connect each of the portfolio companies accounting systems into MYGIDE.
CFO MI Pack Improvements project
Simplified the extraction of monthly data from each company across the 5 divisions, creating data load files to be read by MicroStrategy for high-level summary, cost of goods sold, and overheads data. By capturing the data type, this enabled easy comparisons of actual vs budget, forecast, or prior year data.
Developed prior month dashboards, year-on-year summary charts, and budget comparison charts, available at both divisional and operating unit levels. In addition, produced summary tables and charts that highlighted the cost of goods sold and overheads as a percentage of sales.
FP&A Reporting Setup
Set-up the FP&A outputs of the newly established Group. Creating the Month End Finance Performance Packs to be used by both companies in the Group and consolidated these into a single pack that then fed into the CFO inputs of the Board Packs.
Maintained and updated the Finance Model in terms of projected referrer performance and cost forecasts, having held commercial pipeline meetings with the Account Management and Commercial teams.
Supported the integration of the Moving Minds business into the monthly reporting obligations of the Group, and prepared the FY20 Group Budget template models and analysis, providing the final outputs for the investors.
Developed and updated the capacity planning models for both companies, liaising with HR and the Operations teams.
Better Customer Service From Better Information
Background: The data was available, but it wasn’t easily accessible and in one place for those who would benefit the most. A customer may have rented a van for the last 3 years and shortly be due to return it and replace for a further 3 years. In order so that the customer’s account manager could check in and see that this was their intention, or whether they wanted a different model with different customisations wasn’t a straight forward process to determine.
Solution: In collaboration with the BI team, Asset Management and Sales teams, I streamlined the Defleet process by creating a SharePoint site that could be updated behind the scenes using MS Access and some append/update/delete queries.
The Results: This enabled Northgate to engage better with its customers, determine upcoming procurement requirements and be more aware of future disposals as the relevant information was all in one place. Being Proactive not Reactive.
Business Objects Suite of Reports
Background: A newly installed Exceedra Sales Planning software required the development of a suite of Business Objects reports customised for Bettys & Taylors needs.
Results: Created reports for the Commercial Finance Team from both product line and customer group perspectives, enabling comparisons to last year, the original budget, the latest quarter forecasts, and closed period end forecasts.
- Reports containing month end data to assist with the month end commentary
- Reports to enable better control over promotion and marketing spend
- Production planning reports in regards to the number of weeks that certain Yorkshire Tea packs containing free extra fill are on promotion
- Reports to assist with the new budget preparation and planning
- Reforecast reports that incorporated new quarterly forecasts or period end forecasts
- Quarterly Sales report that compared the projected revenue and associated promotion and advertising spend.
Developed reports to assist the Sales Team with their retailer joint business planning, ensuring that the reports fitted their user needs as well as the retail customers.
Liaising with Head of New Product Development, devised suitable reports showing projected sales and contribution of new product lines to key customer accounts on a year-to-date and year-to-go basis, and provided a comparison to budget/latest quarterly forecast.
Performance Management Dashboards
Background: The blood collection process is very labour intensive and so this heavily influences the overall productivity measured in terms of units of blood collected per FTE.
The existing monthly productivity reporting process was being carried out in Excel with numerous lookup worksheets involved.
Solution: By simplifying the process and streamlining the reporting I created an Access database to replace the numerous lookup worksheets. This also made it possible to produce collection team scorecards very easily that could highlight key variables that were impacting on collection productivity and be shared with the operational teams to clearly explain what the key issues were that needed to be addressed and acted upon.
The monthly report was refined into a Dashboard that highlighted team performances and the overall productivity trends.
The Results: As well as producing and interpreting the data, I also became involved in the performance improvement projects working with numerous operational teams and partnering senior leaders to drive strategic improvements by identifying key performance issues and recommending actions to address, such as implementing a maximum travel distance between a team base and venue.
EBA Benchmarking Scorecard
Background: The NHSBT Finance leadership is responsible for co-ordinating the annual benchmarking data survey of key metrics that enables the European Blood Alliance organisations to collaborate and learn best practices from one another. This had always been done in excel historically, but I was asked if I could develop a standard database that could be used by each of the 22 participating countries.
Solution: Built a standard MS Access database that captured the 78 pieces of data for each of countries, making some adjustments to allow for the different numerical formats that exist between the countries. Each country was sent a blank database for them to complete which also contained a nice summary of the results on a one-page report for them to review prior to returning the data. Once all data had been received back, this was consolidated into a central database so that the output of the data could then be easily compared and the data properly interrogated.
The Results: This allowed for greater consistency in the data received and simplified the consolidation process. Much time was saved in the data capture process to enable more time for in depth analysis and charting of the results.
Biggest Customer isn’t always that profitable
Background: The largest customer appeared to always create extra demands on the business, and I decided to investigate the true profitability levels, now that a new management team had arrived.
Solution: By analysing the last 12 months data for all customers by product category and reviewing the margins, I then made an adjustment for the rebates given, and then finally apportioned the warehousing and delivery costs in relation to the net number of units delivered to each customer.
The Results: This one piece of analysis exposed that our biggest customer wasn’t as profitable as expected. By clarifying true profit margins post retros and allocated warehousing and distribution costs, our biggest customer who although generated £4.6m in turnover, was only contributing £134k (3%)!
3 System Consolidation to Provide Global Project Margins
Background: The new CFO posed the challenge of creating a monthly global project margin report
Challenges: The group had 3 different accounting systems across the globe: Deltek CostPoint, Oracle, Tetra CS/3
Solution: By combining database and spreadsheet technologies I was able to build and structure the underlying MS Access database and use the PowerOLAP tool to successfully consolidate the global reporting of project margin reports.
The European and Asian system data was mapped to match the US system’s chart of accounts and making the necessary currency conversions each month into the US$.
By developing the appropriate formulas inside PowerOLAP such as Project Margin $; Project Margin%; Average Billing Rates, once the monthly data had been loaded into the database the monthly reporting and analysis could be carried out with ease in Excel as there was an add-in feature that connected to the database which was easily refreshed.
The Results: An example of one of the insights that this global reporting system provided was that senior management was able to recognise more easily that they were dealing with the same client globally but the margins being made could be very different as one of the projects was taking place in New York City whereas the other was in Hong Kong and mainly sourced by a large portion of the team being from Manilla. This then led to future New York projects having a larger portion of the team either working offshore in Manilla or travelling to the US to collaborate locally. And because of the salary differentials, this then helped to significantly improve margins.