Highlighted
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
Highlighted
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.

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.

All Replies
Highlighted
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.

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.

Highlighted
White Belt

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

Ah, thanks! That makes sense

Announcements
Coming Soon: Weekly Virtual User Meet-ups to answer all your questions! Stay tuned for more details to come this week.