How to reverse amortize sales based on gains and losses?

[sheidari]

I have a set of data that includes sales, and gains/losses but i want to adjust the sales based on the gains/losses in order to smooth out spikes and dips in my monthly sales #s. 

Been trying to use a multi-row tool to amortize it but it's not working as i want.  Below is the original sample data and the expected output:

Any help would be greatly appreciated!

[Luke_C · Accepted solution]

Hi @sheidari 

Here's one approach:

1. Add record ID, reverse by sorting
2. Multi-row to fill in gain/loss of 3M for every record (Multi-row is great for filling down, not so much filling up)
3. Sort back and perform the calculation. My numbers are off by 5 which I assume is because your %s have more numbers than are visible. 

Let me know if this works for you

[sheidari]

@Luke_C Thanks for the help. This did the trick!