As a seasoned Qlik developer, you’ve certainly had to deal with financial dashboards. It could be P&L, costs analysis, or financial controlling. Finance is a huge world, with lots of things to know and understand. If you are new in the data analytic or finance world, welcome!
I’ll explain some concepts in this and future articles and how to implement them in Qlik Sense.
What better users than financial controllers to demonstrate the power of Qlik. They have masses of data to analyze with significant and highly observed financial impacts.
Do you scream for ice cream?
Even the examples are deliberately simplified for a better understanding, the concepts remain valid.
Imagine you are working for an ice cream producer, and you have to help the manufacturing better understand what’s happening and how to improve their financial performance.
Our factory produces three different flavours, Chocolate, Vanilla and Strawberry.
The plant manager’s first request is to know the financial performance by product.
Production figures are quite simple and we admit that every ice cream produced is sold :
Variable, fixed and costs allocation
Our first challenge is to affect costs to the right product.
Direct costs are already known by products. It’s actually true for ingredients like milk, sugar, and flavors measured for each batch produced.
Ingredient’s quantity by product:
These direct costs can also be defined as Variable because they fully depend on the quantity produced.
Three machines are used in the manufacturing process :
These costs are fixed because even if we stop producing ice cream, they remain in the accounting system. They are also Indirect costs because we don’t know exactly how to split them by product. This is where a distribution key should be applied.
Here is an example for the blender costing 2 000 with quantity for January:
It’s certainly easy to calculate it in the front end, but what we need is to create it in our data model. That’s where scripting comes to help.
The first step is to recreate our distribution key for each period and product using a Group By instruction in the Left Join Load:
The second step is to create new lines for each machinery and split cost by product with the distribution key Quantity_Ratio :
Analytical accounting and why we use it
Affecting cost allows you to have this kind of analysis where you obtain the product’s margin on variable and fixed costs.
In my example, the first thing to notice is that Strawberry, even if is the most produced flavor, has a negative margin at the end. It could be explained by its margin ratio in percentage that is significantly lower than Chocolate and Vanilla. But also by the important part of fixed cost affected to this flavor because of our distribution key based on Quantity.
How to help our plant manager?
More than just giving numbers about margin and costs, he needs to take action to get better!
Two things he can act on quite easily are Quantity and Sales unit price. To make Strawberry a beneficiary, we can calculate the Break-even point.
Quantity to reach is :
Fixed Cost / (Sales unit price – Variable unit cost)
That Sales unit price to reach is :
Fixed Cost /Quantity + Variable Cost
Real-life is a bit more complicated because you have many products and different costs, but the same logic works!
Do not make hasty decisions!
Seeing these results, a shortcut would be to decide to stop Strawberry cause it loses money. Wrong!
Especially in an industry based on fixed costs where every margin on variable cost helps to cover fixed costs. The right balance to find is the margin on variable cost and the saturation of the production tools. But that’s another story…
Two scenarios that illustrate my purpose :
We completely stop Strawberry production, our other products now both lose money :
We decide to add a new flavor, Coconut, to our product range.
Even if the margin on variable is low and the product is loss-making, our overall benefit has increased.
Do you still find finance boring?
This is how this analysis allows us to measure what is currently, what we need to act on to become better and to evaluate the impact of future decisions.
Wouldn’t your boss be happy with that?
My future articles will aim to add new concepts to your palette to make you a finance artist with Qlik.
Thanks for reading and reach me out or comment if you have questions or if you want to talk about it!
Cheers
You can download my sample app with the data source to look at the script and expression further
Thank you Christophe, I think my financial colleague will be pushing me harder giving him developer rights learning this.
Thank you Christophe, my financial colleague will be happy with this one.
Thank you Christophe