Overview

Alphamega is the largest Cypriot growing supermarket chain in the country with 16 stores and 1700 employees in December 2019 (with 16 stores and 2000 employees during the year 2020)

Since there are a lot of stores in different locations, the monthly detailed analysis of the performance of each store is crucial. More certainly, this procedure is useful for efficiently managing the employees, to monitor what the clients prefer to buy in each location per season or month, to do effective cost-benefit analysis etc.

It is therefore important for the management of Alphamega to have monthly management accounts for the company as a whole and for each store separately, for making proper financial monitoring of the performance of each store.

This process was difficult to maintain manually as there was a need to daily monitor their financial performance for each individual store.

Approach

Using SQL SSIS, we extracted the data from the program that was used by the client (Infor Aurora), we then transformed the data, and finally loaded them into a single Data Warehouse (SQL Server Database Engine).

We then created a multi-dimensional cube (SSAS). An Analysis Services multi-dimensional solution uses cube structures for analysing business data across multiple dimensions and calculations.

For example, two of the dimensions are the ‘Cost Centre Code’ and ‘Cost Centre Description’ where they defined the name and the specific code of each store. One of the most valuable dimensions is the ‘Account Dimension’ which is the account code for each category of the accounts and each account. Also, ‘Period Dimension’ and ‘Department dimension’ are essential, as they show the departments of each store, ‘Supplier Dimension’ through which they can see their supplier, as well as other particularly useful dimensions.

Besides, there are measures/calculations such us the account movement per month or year, and the prior year movements per month and year.

The tools that we used to make this analysis in this case is easy, being the Cube Formulas in Excel.

Results

We created a tool in Excel, where the client can automatically see for any given period and without any human effort, the profit and loss of each store, the consolidated profit and loss and balance sheet, as well as the prior year comparatives.

Also, the client can use this tool to automatically have access to the profit and loss of any new store within seconds, and without any interruption by us.

Consequently, the monitoring of the financial performance of each store can be improved, since the managers have access to the results immediately and can act directly, preventing any possible damages.

Likewise, with this tool, the client can easily analyse the cost and the revenue per location.

Moreover, the client can monitor the charges and the revenue related to each of their suppliers. For example, they can use the rental account which be automatically broken to the various suppliers. More certainly, there is a specific place in each store that each supplier rents to put his products, and the charges for renting the place were included in the rental account. As a result, the managers of Alphamega can identify the exact amount that was charged from each of their suppliers for renting the place, without any additional work.

Conclusion

The client saves a lot of time of manual work as the tool can be easily used and accessed anytime. Hence, there is more available time for making proper analysis and taking effective strategic decisions.

Additionally, the client can monitor the financial performance of each store, of each department, the revenue and cost, as well as anything related to their suppliers and so on.

Finally, all the data continue to be consistent and organized over time.