From Excel to Executive Dashboard: BI for a Canary Islands Distribution Company

There comes a point in the life of every distribution company when Excel stops being a management tool and becomes a management problem. The symptoms are always the same: each department head has their own spreadsheet, the figures never reconcile between departments, and senior management makes decisions on information that is anywhere from two to seven days old. That was exactly where our client stood when they got in touch.

The challenge: flying blind in a company awash with data

The frozen food distributor operates across three islands — Gran Canaria, Tenerife and Lanzarote — with annual turnover of just over €14 million. Its range includes more than 600 active product lines: fish, seafood, meat, poultry and ready meals. It has around 280 active customers spanning hotels, restaurants, supermarkets and institutional catering.

When we arrived, the technology landscape looked like this: the main ERP was Oracle SIGES, used for purchasing, sales, invoicing and accounting. Alongside it sat an in-house PostgreSQL database where the warehouse team managed stock in real time (limitations in the SIGES stock module had prompted this parallel development). And then there were the spreadsheets: at least eight different Excel files that various members of the finance and sales teams updated by hand every week from ERP exports.

The core problem was a lack of visibility over margins. Management knew how much it was selling and buying overall, but could not answer basic questions: which product category has the best margin this month? Which island is most profitable? Which customers generate the most volume but the least margin? Answering any one of these took the finance manager three to five days of manual work.

Diagnosis: three technical problems and one organisational one

Problem 1: Scattered data sources

Sales and purchases lived in Oracle SIGES. Actual stock lived in PostgreSQL. Weighted average costs (WAC) — a critical metric in distribution for calculating the real margin on each sale — lived in Excel. None of the systems talked to each other. To work out the margin on a sale, someone had to manually match the selling price from SIGES against the WAC in the relevant spreadsheet.

Problem 2: Information latency

At best, the data available in management dashboards was 24–48 hours out of date. SIGES exports were run by hand and processed in Excel the following morning. In practice, most reports were between three and seven days old.

Problem 3: No margin KPIs by dimension

There was no systematic report of margin by product category, customer, island or sales rep. The calculation existed as a concept in the ERP, but the SIGES table structure (in particular the TCABMOVISTOCKS table and its CDCLADOC document types) made it difficult to extract a per-sale margin directly without a dedicated ETL process.

The organisational problem: reliance on individuals

Ninety per cent of the knowledge about how to interpret the data sat in the heads of two people: the finance manager and the purchasing manager. If either was away, the information ground to a halt. There was no process documentation, no automated validation and no audit trail of changes made to the spreadsheets.

The solution: a modern data warehouse with executive-level reporting

We proposed an architecture built on three components: a managed PostgreSQL data warehouse on AWS as the storage and transformation layer, ETL pipelines using Python and Apache Airflow to extract and load data from the sources, and Power BI Embedded as a browser-based reporting layer with no need for desktop licences.

Extracting data from Oracle SIGES

Extracting from SIGES was the most delicate part of the project. The Oracle database has a legacy table structure with cryptic naming, and stock movement types are encoded in the CDCLADOC field of TCABMOVISTOCKS, with values that determine whether a movement is a sale, a return, a transfer between warehouses or a stock adjustment. It was essential to pin down exactly which CDCLADOC values represented genuine sales (to end customers, excluding internal transfers) in order to build the management P&L correctly.

The extraction pipeline uses a JDBC connection to Oracle and runs through Airflow with hourly refreshes, incrementally loading the movements from the previous hour. Dashboard latency fell from 24–48 hours to under an hour. Data is loaded into a staging layer and then transformed, using version-controlled processes, into the dimensional tables of a star schema.

Calculating WAC: the heart of the margin

Weighted average cost is the single most important metric for a distributor: it reflects the true cost of each unit sold, weighted across the different purchases that make up the available stock. The formula is simple in theory (WAC = (Previous stock × Previous WAC + Quantity received × Purchase price) / (Previous stock + Quantity received)), but in practice it depends on being updated correctly on every inbound movement.

In SIGES, WAC is updated automatically with every goods-received note, but some situations distort it: customer returns put back into stock at selling price, stock adjustments, and purchases with retrospective discounts. The ETL pipeline includes a WAC validation layer that detects and flags statistical anomalies (changes of more than 15% in a product's WAC in a single day).

Project timeline: 8 weeks from signature to go-live

The executive dashboard: what management sees

The end result is a set of four main views in Power BI, accessible from the browser with corporate sign-on:

• Monthly management P&L: gross sales, returns, net sales, cost of sales (calculated on WAC), gross margin and margin percentage, compared with the previous month and with the same month last year.
• Margin by category: a breakdown across the five main categories (fish, seafood, meat, poultry, ready meals) with month-by-month trends. It was in this view that, in the second week of use, they discovered that ready meals were running at a negative margin on two of the three islands.
• Customer and island analysis: customers ranked by sales volume, gross margin and margin-to-sales ratio, filterable by island and by channel (hospitality, retail, institutional catering).
• Stock management: current stock by product line and warehouse, days of cover (stock / average sales over the last 30 days), and alerts for overstock (more than 60 days' cover) and stock-outs (less than 7 days' cover).

Results: the unknown unknowns

The most immediate impact was not the time saved but the discoveries. In the first week of using the dashboard, management identified three situations that had been invisible under the old set-up:

• Ready meals on Lanzarote and Tenerife had been loss-making for the previous three months. The cause was a combination of purchase prices that had risen by 12% and selling prices that had not been updated on those islands. Correcting the prices delivered an estimated positive impact of €38,000 a year.
• Three hospitality customers on Gran Canaria accounted for 22% of sales volume but only 8% of gross margin, because they were being given excessive trade discounts that did not offset their high volume of returns.
• Seafood stock in the Tenerife warehouse had average cover of 87 days, well above any reasonable threshold. Part of the overstock consisted of lines that had not moved in more than 45 days.

The month-end close went from five days of manual work by the finance manager to two hours of reviewing and validating the dashboard. The project paid for itself within four months, counting only the savings from correcting ready-meal prices and optimising seafood stock.

Lessons from the project

We knew SIGES was complex before we started, but its true scale only becomes clear once you are working with the data model. The table naming (TCABMOVISTOCKS, TDETMOVISTOCKS, TARTIGEN, TCLIENTES) follows a logic of its own that takes time to understand before any reliable extraction can be built. Properly documenting which CDCLADOC to use for each movement type was critical to making the management P&L reconcile with the statutory accounts.

The second lesson was the importance of involving the finance team from day one — not as sign-off reviewers at the end of the project, but as co-designers of the data model and the KPIs. The margins that matter to the finance manager are not the same as those that matter to the managing director. Having that conversation in week one rather than week six saved two weeks of redesign.