Calculate the delta in a balance sheet account to get the cash flow

Reply
White Belt

Calculate the delta in a balance sheet account to get the cash flow

I'm trying to convert balance sheet data (static balances) into flow data (or the delta change month over month)

 

e.g. on the balance sheet I have date:

Jan: 1000

Feb: 1200

Mar: 1150

Apr: 1400

 

I want to convert that into a cashflow calculation that is the delta of that account each month, to output:

Feb: +200

Mar: -50

Apr: +250

 

Any ideas? All the date calculations and month-over month examples I can find just help you calculate a delta vs TODAY, or vs teh current date, not just a generic "Month C = Month C - Month B"


Accepted Solutions
Yellow Belt

Re: Calculate the delta in a balance sheet account to get the cash flow

Actual value between the last year period after that minus one, after that per 100 to represent the change in percentage

 

Example:

February 2016 sales $100

February 2015 sales $50

 

then

((100/50)-1)*100=100% or $100-$50=$50

 

This mean that your delta is increase the doble. is a positive delta.

 

If the result is negative means that you sale less than the same last period.

 

If the result is cero means that you sale the same amount.

 

So, you could use Data flow option.

 

Here is your data set

Jan: 1000

Feb: 1200

Mar: 1150

Apr: 1400

 

Firt you need to create a new date calculate column, removing one month to your date column, so you will have something like this

 

2016-01-01   $1000    2015-12-01

2016-02-01   $1200    2016-01-01

...

2016-04-01   $1400    2016-03-01

 

After that you just re-use the same table (with alias) in a left join query to get this result

2016-02-01   $1200    2016-01-01  2016-01-01   $1000    2015-12-01.

 

the rest is a simple calculation with this new data set.

 

Cheers.

View solution in original post


All Replies
Yellow Belt

Re: Calculate the delta in a balance sheet account to get the cash flow

Actual value between the last year period after that minus one, after that per 100 to represent the change in percentage

 

Example:

February 2016 sales $100

February 2015 sales $50

 

then

((100/50)-1)*100=100% or $100-$50=$50

 

This mean that your delta is increase the doble. is a positive delta.

 

If the result is negative means that you sale less than the same last period.

 

If the result is cero means that you sale the same amount.

 

So, you could use Data flow option.

 

Here is your data set

Jan: 1000

Feb: 1200

Mar: 1150

Apr: 1400

 

Firt you need to create a new date calculate column, removing one month to your date column, so you will have something like this

 

2016-01-01   $1000    2015-12-01

2016-02-01   $1200    2016-01-01

...

2016-04-01   $1400    2016-03-01

 

After that you just re-use the same table (with alias) in a left join query to get this result

2016-02-01   $1200    2016-01-01  2016-01-01   $1000    2015-12-01.

 

the rest is a simple calculation with this new data set.

 

Cheers.

View solution in original post

White Belt

Re: Calculate the delta in a balance sheet account to get the cash flow

Ah, thanks! That makes sense

Announcements
Check out these great solution videos! The new iSolveditWithDomoboard is live, share yours and earn higher rankings in Dojo click here!