Is it possible to roll forward balance within the same List?

sharmagh

Hi,

 

I have Deal List and Funds List with their Top Levels and Beg. Available Fund, Fund Utilization for the deal, and Remaining Fund as the Line Items.

 

In module, Deal list and Funds List appearing in the Rows (Each deal has the same Funds list appearing again) and Line items in columns.The scenario is, one by one each Fund will utilize their available amount for all the deals so whatever left after spending on Deal 1 the Remaining Fund will become Available Fund for Deal 2 and so on.

 

Is there any way I can roll forward  the remaining amount deal by deal?

 

Thanks! 

JaredDolich

@sharmagh 

Never tried that but sounds like a good use case for Closing Balance summary.

Try setting up an account that has additions and reductions. Assuming you're scenario is time based you can simply use the ending balance at the beginning of your next deal. You'll also have a way to replenish the account if you have additions.

Very similar to an Open to Buy (for retail)

sharmagh

Thank! However, I am not using any time scale

einas.ibrahim

@sharmagh 
could you clarify something for me, please?
Are Deal and Fund 2 hierarchies with Fund being the Parent of Deal? 
So I have Fund 1 with an available $ of $1M
 For Example:

Fund 1
----- Deal 1.1
-----Deal 1.2

Fund 2

-----Deal 2.1

 

I have 2 Deals within the Fund Fund 1 -Deal 1.1 and Deal 1.2 and you want to distribute the $1M between these 2 Deals?

sharmagh

.@einas.ibrahim
No Funds and Deals are two diferent list with Active fund and Pipeline deals as top levels

sharmagh

@einas.ibrahim 

No Funds and Deals are two diferent lists with Active fund and Pipeline deals as their respective top levels.

 

einas.ibrahim

@sharmagh 

I believe I understand your issue, but it is more of a design issue rather than a technical issue.
One technical fact though that will help us think through this is the following:

A line item can not be a user input and contain a formula at the same time. it is an either-or situation. **

 

There are a few ways to distribute a finite amount (fund $ for example) over a list of deals, but as I said it depends on the design.

 

Based on what you said about the Fund and Deal lists, a deal can be funded from any fund and a fund can fund any deal. Is that your intention?
Please see the attached image for clarification.

 

 

 

 

Or you have a fund 1 that should fund Deal1 and Deal2 while fund 2 to fund Deal3 and Deal4?

** There are 2 workarounds to this situation:
1. You can use a process to update an input Line Item

2. If you use version formulas.

Neither of these is applicable to this situation but I added them for completion.

sharmagh

Can i use offset function without time period or time scale in module?

einas.ibrahim

@sharmagh 
No.
OFFSET is a Time and Date function.

 

Have you seen this post by another contributor?
https://community.anaplan.com/t5/Anaplan-Platform/PREVIOUSITEM-PREVIOUS-on-a-list/td-p/24575

I think you will still have a circular reference. 

sharmagh

Didn't work either.

einas.ibrahim

@sharmagh 

You have to think outside the box. As I mentioned there are multiple ways in Anaplan to achieve what you are trying to do (distribute a fixed amount on list items). You don't have to implement it in Anaplan the way you'd implement it in Excel.
Not knowing the structure and relationships of the lists, it's hard to provide a concrete suggestion. 

sharmagh

@einas.ibrahim 

 

https://community.anaplan.com/t5/Anaplan-Platform/Is-Lookup-within-same-module-possible/m-p/69877#M13641 

 

I have given all the screen shot here, kindly advise.

let me no if anything else is required.

 

Thank you! 

einas.ibrahim

@sharmagh 

1. You could consider adding a line item in a different module that sums the values used for the different deals and if it exceeds the fund opening balance then use conditional formatting to alert the user.
   
   
2. Another way is to use the hold and override technique. It basically requires that the fixed value be distributed among the list items initially using any logic (equal %, $ value, ratio related to a prior time period,.....) then allow the user to override the individual list item values. The hold and override logic will ensure the total value won't exceed the original value.
   
   Download the model Planning, Budgeting, and Forecasting for SaaS and check the dashboard Annual Plan - Cascade Booking by Stream (screenshot) and study how they implement the hold and override.
   
    
    

   

3. You could use a process to update the balances. So before user can start "funding" the 

einas.ibrahim

Hi @sharmagh 

Have you figured a way to work around your Fund/Deal issue?
I came across this post- which is very similar to your situation- and I thought I'd share it with you
https://community.anaplan.com/t5/Anaplan-Platform/Refering-to-data-in-previous-item-of-a-list/td-p/931/page/2

When you arrive at a satisfactory solution please share it with the community.

sharmagh

Hi @einas.ibrahim 

 

Basis my understanding after going through all the potential solutions i came that this can only be achieved either by using Time Scale where we can use Previous Function to get the Previous Closing Balance or by putting hard coded values in an another line item which can be referenced by the Op Bal of the next list item.

 

The Second option isn't looks great because it is blocking the automatic update which is the prime purpose of using any technology.