Calculate contract value per month

The question is how we can calculate the value of a contract. We have the value per month, as an example:

12 Months with 100 euros per month.

January 100 euros
February 100 euros
March 100 euros
etc.

In Board I would like to see as a result

January 1200 euro (sum January to December)
February 1100 euros (sum February to December)
March 1000 euros (sum March to December)

The start and end dates are flexible, can be several months but also several years.

How to set this up in Board

Find more posts tagged with

Accepted answers

All comments

unknown

Hi Bas Koomen,

That's an interesting challenge. There are a few wrinkles I see that need to be addressed.

Needs a mapping of Contract to Month to identify active months for a given contract
Needs summation across time to get contract remaining
Needs to calculate a remaining value, rather than YTD

I would probably create an integer cube as mapping cube cube (Contract,Month) to address (1). Each time a contract is active in a given month, the integer cube intersection would be 1, else 0. Next, I would need a new cube, with the same entities as your current cube, but use a reference to TOTAL BY Year or All time. This would give the total contract value, only in active periods. This addresses (2). Now that we have the total contract value in each period, we need to subtract off the YTD sum of the source. This should address (3) and give us the remaining value in a contract, in a particular period.

These other cubes may help simplify it too:

Annual opening contract balances - You'll need this in conjunction with YTD for multi-year calculations
Contract value - Full value of a contract
YTD contract spent
LTD contract spent

Good luck,

Bas Koomen

Thank you for your extensive answer, I will try to set this up.

Björn Reuber

Maybe also one of the built in algorithm from BOARD Layout Algorithm Syntax can help you. If I understood you case correctly it could be PV: Returns the present value of an annuity, considering a series of constant payments made over a regular payment period.

Regards

Björn

Bas Koomen

Thank you, sorry but i forgot to mention that the value per month can vary, it's not lineair.

Björn Reuber

Ok,

than you can do this with one extra cube.

Cube1: Structured on Contract/Month: Monthly Value

Cube2: Structured on Contract: Total Value (calculated within a DataFlow using Cube 1)

than you can calulate the value quit easy :

Block A: Monthly Value (Cube1)

Block B: Total Vallue (Cube 2)

Block C: Monthly Value with Option Yearly Cumulated Value, Cycle 999 and a Period Offset

Block B-C

	Jan	Feb	March	April	May	June	July	August	September	October	November	December	Jan
Block A	100	100	100	100	100	100	100	100	100	100	100	100
Block B	1200	1200	1200	1200	1200	1200	1200	1200	1200	1200	1200	1200
Block C		100	200	300	400	500	600	700	800	900	1000	1100	1200
Block D	1200	1100	1000	900	800	700	600	500	400	300	200	100

Federica Antonelli

Hi Bas,

In addition to what Björn explained you can find several applications of the time functions here Sample: Time Functions, this sample shows the Yearly Cumulated Value combined with cycle and the offset options. I think could be a good inspiration.

Hope this helps

Federica