Greetings all. I'm currently trying to construct a deferred revenue / revenue recognition schedule for a start-up.
Background Info. The business model is straight-forward: we offer an SaaS service in which customers can purchase via annual contract. For each order, customers pay a recurring annual subscription cost and also a one-time implementation fee.
So after failed attempts at building my own deferred revenue / revenue recognition schedule where I could track the revenue that should be recognized each month, I was able to track down the attached template in an older thread here. The schedule is AWESOME and is almost exactly what I'm looking for. In it's current iteration, the worksheet enables the user to calculate (and track) the revenue it should be recognizing each month for each customer invoice.
Help Needed. While the attached schedule gives me the ability to see the annual subscription amount on a monthly basis (amount in column D), the schedule does not provide the ability to integrate the one-time implementation fee associated with each customer's purchase. I added Column E to this workbook to show what a hypothetical implementation fee would look like for each invoice. The issue I'm having ...... the appropriate accounting treatment for our specific sales model would see the subscription cost spread out over the life of the contract (as it is currently doing in the schedule) AND it would see that the one-time implementation fee was fully recognized in the first month which service occurs (so month #1 of the contract). I cannot conceive a formula that would add the 'implementation fee' in column E to the first month of the contract. If I were able to do this correctly, Cell G4 would read $1,053.89 and the rest of line 4 would be the same as they are now (H4 = $100.60, i4 = $111.38 et cetera). Another example of what this should look like would be: Cell H6 now = $592.05, and the rest of the cells in the row remain unchanged.
I hope all of this makes sense, and I have no idea what this solution would look like. I'm fairly competent with Excel but I'm by no means a VBA proficient user. I apologies for the verbose nature of my post, I come from a world where giving too much information is preferred to giving not enough. Most importantly, I sincerely thank everyone for their time.
Bookmarks