Daily revenue recognition calculation?

[sgupta76]

I want to recognize revenue using daily convention for maintenance revenue in Access but it's very challenging and complex. Not sure if anyone has done it before.

example:

Order# StartDate EndDate ProductID Price Jan-06 Feb-06...
1000 1/5/2006 1/4/2007 110 $120 $8.87 9.20

In the above example, the maintenance contract started on 1/5/2006 and ends on 1/4/2007(1 yr contract) which gives 27 days in January and 28 days in February and so on.
Applying the daily rate we get the above 2 figures for Jan-06 and Feb-06.

Basically I want this to be done automatically in Access for each order which may have 1 year contract or mutiple years(2 yr, 3 yrs....). The months will spread out starting from Jan 2004 all the way to the end of the last month of the order which has the highest end date which will fall somewhere in 2011. Each month should be a column and the system should automatically detect which column to start and end the amortization. If there's another easier way to do this please let me know. There will be atleast a 1million-2million rows of orders that needs to be amortized based on their start and end dates.

Thanks for any help you can provide!!

 

[Remou]

As far as I can see, this is beyond the scope of these fora. You will need to pay someone to carry out such work.

Please read point 10 in
How can I maximize my chances of getting an answer?
faq181-2886

 

[TheAceMan1]

How are ya sgupta76 . . .

According to what you shown [blue]your on your way to making a spreadsheet[/blue] . . . not a database . . .

See Ya! . . . . . .

 

[CaptainD]

Question on your example

Order# StartDate EndDate ProductID Price Jan-06 Feb-06...
1000 1/5/2006 1/4/2007 110 $120 $8.87 9.20

I assume the price is the initial cost of the contract "$120.00" and are the other charges a monthly fee that goes up with each month of the contract?

If so, is it based off a set percentage increase that might be used in a query to return results?

 

[sgupta76]

I was able to create the logic in Access by calculating days in a month and then adding values to the corresponding month and year columns dynamically in a loop for the total number of months between the start and end dates.