Trying to figure total unearned amount billed 45 days in advance...

[HeatherK]

HELP! Excel beginner....exported spreadsheet from Access to Excel. I have a column with a renewal date for each customer (billing based on the renewal date, bills 45 days in advance), a column with the amount billed, and a column with frequency of billing (1-annually billed, 4-quarterly, 12-monthly)
EXAMPLE:
CUSTOMER RENEWAL DATE AMOUNT FREQUENCY
abc company 7/15/07 $1,950.00 4

so, they are billed 6/1, 9/1, 12/1, and 3/1 (as of 6/1 billing, the entire amount is "unearned")

Need a spreadsheet to calculate how much is unearned at any given time.

Any suggestions? Does this even make sense to anyone? Any help is MUCH appreciated.

 

[DonkeyOte]

could you provide some more sample data with projected results... ie can billing dates be weekends or must you calculate 45 days back to nearest working day etc ? Or was your ex. just convenient -- would you always bill say on 1st or 15th of the month etc...

Re: revenue recognition -- can you elaborate ... are you saying unearned revenue is simply sum(billings) where billing date < renewal date or is it more complex ? RevRec normally is...

If you could come back with more data I'm sure we could help out - with one example it's difficult -- if you could give say ex of annual & monthly scenarios with different renewal dates that would help us provide a possible solution.