Model Audit with Excel Check: A Case Study

Rosemont Pharmaceutical, UK.

THE STORY

Rosemont Pharmaceuticals (www.rosemontpharma.com) has been a leading manufacturer and supplier of liquid medicines for over 40 years.

In 2015 Rosemont manufactured 4 million bottles across a range of 140 different medicines.

Rosemont is based in Leeds, West Yorkshire, UK and has 220 employees.

THE CHALLENGE

The Excel Check brief was to assess the accuracy and usability of a series of spreadsheet models that Rosemont were using for their monthly reporting.  The system had been built by Rugged Logic, a provider of custom-built financial spreadsheet systems, and the combined size of what they had was around 100MB.  By any measure this is a lot of logic.

Although accessible, the spreadsheets were producing results but were not functioning properly.  Rosemont were also anticipating a management buyout, so the work had some urgency attached to it.

It was clear that this brief also included an assessment of the actual usability of the existing spreadsheet models.

THE SOLUTION

Lazuli performs a model audit by reperformance of the existing spreadsheet models and builds an Excel Check re-performance model for Rosemont Pharmaceutical.

THE RESULTS

To check the accuracy of the original models, our solution was to rebuild the existing models into one financial and reporting model.  This also provided Rosemont with a new model.

In effect, the model audit by re-performance method called Excel Check was adapted to the requirements of the situation.   Lazuli created one single model from a series of unwieldy models.

The practical result for Rosemont was one new, 7MB planning model with full actual, budget and forecast financial statements.   The new model also included a sensitivity analysis manager and a reference and difference tool for instant period-by-period and line item-by-line item change comparison.

For monthly reporting, the model produces a profit & loss, cash flow statement and balance sheet in various formats, including current month, year-to-date, 3-month forecast, 3-month budget and full-year outlook.

The audit trail manager in the model enables Rosemont to track back through input changes and measure the resulting output changes.

The management buy-out subsequently happened, and with the new Lazuli model, Rosemont turnover continued to grow throughout the following 5-year period at around 15% a year.