Is it possible to override a formula in a line item? If not, how can I enter a starting amount?

kdoan

I want to be able to enter a starting amount and from there on have a formula driven on the line item.

 

Example.

 

Beginning ARR manual enter amount in Apr, then it will drive the calculation to get the ending ARR. The beginning ARR will equal to previous months Ending ARR.

 

I included several screenshot. The excel file highlighted in yellow is the manual input amount. I was wondering whats the best way to approach this? I know I can just enter a line item to add to the total to make it work. 

kavinkumar

HI @kdoan ,

I have 2 thoughts on this. 

Approach 1: If your time period starts with Apr (as you mentioned for the entry) then go with this approach. Create another input line item and make use of Offset formula.

 Approach 2 :  

 Thanks,

Kavin.

DavidSmith

@kavinkumar @kdoan 

I trick I use to avoid the extra line item for "opening balances" is to have a module (without time) to hold the opening balance or input

I then use my time settings module to highlight the first period of the timescale (as per below)

then the formula in the target module is:

IF Monthly Time Settings.First Period of timescale THEN Opening Balance.Data ELSE PREVIOUS(Closing Balance)

 

I hope this is useful

David

kdoan

@DavidSmith Thanks for your reply. I was wondering can you provide more details? I am new to this. I understand that you created a separate module (without time) to hold the opening balance or input.

 

However I got confuse by your screenshot below. I just see a check mark or boolean value so I am not so sure how you use separate module to the time setting module. 

kdoan

@kavinkumar Your approach 2 works for me. Thank you. But your approach 1, with the offset formula doesn't work. I was wondering why?

rob_marshall

@kdoan ,

 

Not David but will respond for him.  Essentially, he has a SYS Time module where he has a line item called "First Period of Timescale" formatted as a Boolean.  That line item has a formula of OFFSET(1, -1,0)=0 which evaluates to true for the first period of the timescale.  Once that is set up, your formula is just evaluating if the current time period checked in the SYS Time module (First Period of Timescale), then get the opening balance, else get the previous closing amount.

 

Hope this helps,

 

Rob

DavidSmith

@kdoan 

Sorry for the confusion.  What I didn't show was the "Opening Balance" module.  This module would have the relevant dimensions but without timescale.

I hope this helps

David