Skip to main content

Hello,

 

I need your help on the following.

Some of our project have this type of setup

For each sales invoice,the system is calculating a preliminary cost of sales based on the contribution margin rate.

We noticed that if we update the contribution margin the new rate is applying only to next preliminary cost of sales calculated when new invoices are issued and there is no catch up calculation for the previous preliminary cost of sales.

 

Example :

Period 1

Contribution margin 25%

Invoice 1 for 500 000

preliminary COS : 375 000

 

Period 2 :

update the contribution margin to 15%

Invoice 2 for 400 000

preliminary COS : 340 000

 

YTD situation

Sales 900 000

COS 715 000

Margin : 185 000 (20.55%)  whereas we are expecting  only 135 000 (15%)

 

How to force the recalculation of the preliminary cost of sales generated on previous periods ?

 

Thank you

Alain 

Hi Alan,

just saw, that nobody answered yet. :-)
I’m not shure if “my way” is of some help…

You mentioned “invoice”:
Is it a CustomerInvoice(Line) or a InstantInvoice?

We created a solution where we used the following tables.

 

The FACT_CustomerInvoiceLines is used as a “data-layer” with other “layers” like CustomerOrderLines (COL) (where you see ExpectedInvoiceDate, ExcpectedTurnOver, etc from COLs not yet invoiced)
This “layers” are combined to one FACT_Sales as THE base for a sales cube within MS Analysis Services.
To give you a hint here the calculation of the first (of 3) ContributionMargin(s):

Because there are “part” invoices (2 or more invoices for the same customer order) we calculate InvoicedQty to COL.BuyQtyDue.
Some AccountGroup(s) are “nulled” because the customer don’t want them within the calculation of the contribution margin.

If you want to have a talk to the responsible person there (head of controlling department)
I can arrange that. If you have any more questions, just give me a call.

All the best and good luck with your challenge.

Michael

 

Hi Alan,

I forgot to mention:
Because of the structure of the ETL process (we fetch data every night, transform it into the FACT_ and DIM_ tables and process/load data into the cubes) you have a fresh calculation every morning with all the (Invoice)data available.

 

HTH

Michael

 

 

 

Reply


Terms & ConditionsCookie settings