Amortization in Notes DB

Hello All,

I'm tying to find a way to track revenue in notes db form.  

X dollar is price, but revenue is recognized over 12 months.  So I need to recognize x/12 each month, based on start date.  

Is there anyway to do this in the form or in the view?

Thanks in advance for any assistance.
LVL 1
hu8myphoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SysExpertCommented:
Yes.
In the column field use a formula to take the value and divide it by 12.

Make sure you use a number. If it is a string, convert to a number first.

You may need to use a temp value also.

I will look for sample.

I hope this helps !
SysExpertCommented:


Sample

REM "If modified inside 10 Minutes still consider Entry as New!";
Diff := @Modified - @Created;
Status := @If(Diff > 600; "Updated";"New");
@If(Status ="Updated"; Status + " - " + @Subset(@Name([CN];$UpdatedBy);-1); Status)


you should simply try

AMort=Amotrfield/12;

or simialar.

I will look for a beeter sample.
hu8myphoAuthor Commented:
Hi,

I  can simply divide by 12, but the part that i'm trying to figure out how to do is, get a rolling sum each month following the "start date",
so ex.

if record 1 start_date = 8/24/2006  price = "$144.00"

in form or view( if record is amort then (, August Rev = 12, Sept Rev = 24, Oct rev = 36, etc.... ), else if not amort then Aug Rev = 144)
 my top goal is trying to get the Amort peice working right now..

I hope that makes sense, thanks in advance for any assistance.
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

hu8myphoAuthor Commented:
Update:

So each month would be  $12.00, but be able to have a Total field that will continue to sum as the months pass.  
SysExpertCommented:
Sorry. Still not clear.

I need specific expample of data, and how you want it to look in the view.

hu8myphoAuthor Commented:
ok,

Customer 1 purchase waranty plan  $144.00 January.  
Customer 2 purchase waranty plan $288.00 June.
Customer 3 purchase waranty plan $72.00 August.

Regardless of Dollar amount, Waranty plan is for 12 months.  Revenue from customer is recognize monthly (dollar amount/12).  

Customer 1 pays $12 per month for 12 months
Customer 2 pays $24 per month for 12 months
Customer 3 pays $6 per month for 12 months
In view, revenue recognize
2006
Jan - $12 total (12 for cust1)
Feb - $12 total (12 for cust1)
Mar - $12 total (12 for cust1)
Apr - $12 total (12 for cust1)
May - $12 total (12 for cust1)
Jun - $36 total (12 for cust1 + 24 for cust2)
Jul - $36 total (12 cust1 +24 cust2)
Aug - $42 total (12 cust1 + 24 cust2 + 6 cust3)
Sep - $42 total (12 cust1 + 24 cust2 + 6 cust3)
Oct - $42 total (12 cust1 + 24 cust2 + 6 cust3)
Nov - $42 total (12 cust1 + 24 cust2 + 6 cust3)
Dec - $42 total (12 cust1 + 24 cust2 + 6 cust3)
Jan 07 $30 (24 cust2 + 6 cust3)  (0 from Cust1, expired)
Feb 07 $30 (24 cust2 + 6 cust3)

an so on and so on.. please let me know if this makes sense
SysExpertCommented:
OK, now it make sense.

Now, do you want just a monthly summary as shown or do you want a breakdown  showing the details and at the end, the monthly sum.

In any case, you will need to access the Date the customer started his warranty.

Is this for one year oly ( meaning Warranties that were done just in 2006 ), or are there older ones as well ( 2005 ) that need to be considered, since it runs for 12 months ?

SysExpertCommented:
I would create a view  sorted by start of warranty in col 1.

In col2 would be the amount /12

I would then add a summary for the 2nd column by change in month.

Then I would limit the view to only the LAST 12 months !!

that should do it.

I hope this helps !
hu8myphoAuthor Commented:
I have the start date, the amount field,

In view i have col1 as start date, Col2 i have amout/12, with total.   But all this does is gives me the three start dates in col1, and  three amount/12 in col2 with a total.  

What i was looking for is how to calculate if the cust has expired (start_date +12 months), if it is not expired and then take amount/12.  with the summary, I will total up that 1/12 portion  for  the records that is not expired in that month
SysExpertCommented:
Why not just eliminate anything older than 12 months from the view. Add a date limitation in the View selection.
Change the first column to be just the Month portion of the start date.

This will give you a summary by month of all customers < 12 months old.

I hope this helps !

hu8myphoAuthor Commented:
ok, that makes sense, I can eliminate customers that are expired.  I'm going to try the view with the month of start_date categorized.  but I'm thinking that it would only give me one summary of 1/12 revenue from all customers by month of their start date as of today.

What I was looking for is something similar to this but accross the 12 months of their contracts, as some contracts will expire and/or start before or after others.  So I would be expecting varying amount of dollars total each month.  

I was thinking about something like this.  
@If((@Today-start_date)/86400>366;"";price/12) ---> then have a total.  
Now this works to capture 1/12 as it stands today.  But I need to figure out how to get it to be identified into a column based on a month.   and spread across the months of the year and so onward...

I don't know if that makes sense?
SysExpertCommented:
You need to total your total monthly column so that you can have a running total, not just per month.

Make the first column categorized, stardate, but just the Month portion of it.

Use @Month(startdate)
See also the help for @month.

hu8myphoAuthor Commented:

I'm using:

col1: @month(start_date) - sorted A
col2: @If((@Today-start_date)/86400>366;"";price/12) - Totaled

Using same scenario as above:

Customer 1 purchase waranty plan  $144.00 January.  
Customer 2 purchase waranty plan $288.00 June.
Customer 3 purchase waranty plan $72.00 August.

This is my result:

Col1     Col2    
****|*****
1      |12
6
hu8myphoAuthor Commented:
Col1     Col2    
******|*****
1          |12
******|*****
6          |24
******|*****
8          |6
******|*****
*total*  |42

This is just totaling 1 time based of the month of the start date.  I'm imagining something more of:

Col1(Cust_Num), Col2(Jan06), Col3(Feb06).....Col7(Jun06), Col8(Jul06), Col9(Aug06), Col10(Oct06), etc
Cust1               | 12             | 12             |....| 12           | 12            | 12             | 12               |....
***********|*********|*********|**|********|********|*********|**********|**
Cust2               | 0              | 0                |....| 24           | 24            | 24             |24                |....
***********|*********|*********|**|********|********|*********|**********|**
Custt3              | 0              | 0                |....| 0             | 0             | 6              | 6                  |....
***********|*********|*********|**|********|********|*********|**********|**
*Total*            | 12            | 12              |....| 36            | 36            | 42            | 42               |....

or something that will show a monthly summary, across the life time of the 12 months.  I want to eventually recognize the entire amount.  But I would like to go into a month and see how much i am going to or is recoginizing from all customer for that month.
SysExpertCommented:
You will need to create additional columns.

You have to decide whether you want to start in January, or the last 12 months 9 sep 05 -> sep 06 ).

Also, do you want to show the customer info or just the monthly total ?
SysExpertCommented:
I would put the

@If((@Today-start_date)/86400>366
in the View selection and not in the column.
The columns should be based on just the start date - Check if Month >- 1 for Jan col,
Month >=2 for Feb column etc,

I hope this helps !


hu8myphoAuthor Commented:
Hi,

Thank you for your help.   I ended up using the end date, calculated from the start_date, as the driver for each column.   I have 12 spanning columns using formulae:
@If(@Adjust(start_date;0;+12;0;0;0;0)  >=@Date(2006;2;1) & (@Adjust(start_date;0;+12;0;0;0;0))  <=@Date(2007;1;31);amount/12 ;0).  I was able to use this formula to get my columns workings.

On another note, would you happen to have any sample of lotus script that I can use:

I'm trying to find something that would  read each line of a import file, and then lookup that customer document and update the appropriate field(s)
SysExpertCommented:
Please open a new question so that others may also contribute.

I do have all sorts of import procedures. It depends on what you need.
SysExpertCommented:
Please note that it would be best to post here :

http://www.experts-exchange.com/Applications/Email/Lotus_Notes_Domino/

rather than in the Admin TA.

I hope this helps !

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Lotus IBM

From novice to tech pro — start learning today.