C/S ratio

How good are the sales results? There are many indicators that can be used to control sales, and one of them is a C/S ratio. It represents the relationship between the contribution (aka difference in price, earnings, margin) and the amount of sales. By monitoring this indicator we can track change of earnings through time and that can show us how well a trade margin is formed.

We will use an example to show the effectiveness of the PowerPivot table reports. At the same time, you will be able to see how to use data from your business information system to create such report. We have imported three tables: Articles, Objects (Partners designated as Delivery Points) and Transactions from IS to Excel. The first two are sales dimensions, while the latter contain sales transactions. We added them into Data Model, and then linked them.

The C/S ratio will be calculated by creating a new dimension by entering the DAX formula:

C/S racio:=SUMX(Transakcije,RELATED(Artikli[RUC])/([KOL]*RELATED(Artikli[PC])))

Further we will create a PowerPivot chart in which we follow the C/S ratio in relation to the dimension DATUM (DATE) on the x axis, and we will also add a few slicers: PROIZVOĐAČ (MANUFACTURER), ROBNA MARKA (BRAND),  KLASIFIKACIJA (CLASSIFICATION), and OBJEKAT (OBJECT). If we narrow down the display of data, for example by choosing the manufacturer “FRUVITA” in slicer PROIZVOĐAČ (MANUFACTURER),  we will see how the C/S ratio is being changed and our report will display data only for chosen manufacturer.

This is a fairly simplified example. Keep in mind that purchasing and sales prices are often changing, and that traders often give discounts or carry out sales promotion actions. All this should be taken into account when calculating C/S ratio.