Deferred Revenue Schedule

amartino44

Board Regular
Joined
Dec 12, 2012
Messages
56
Hello. How would you create a earned revenue schedule formula for the following:
In row A, I have 24 months of sales. In row B, I would like the TOTAL EANRED sales amount. All sales are are earned at 8.33% per month. So, in January of the first year, 8.33% of January sales are earned. In February, 8.33% of January sales and 8.33% of February sales are earned (etc. etc). The sales from January stop earning out after 12 monts (12*8.33= 100%) I would like to create a formula for row B. Thank you :)
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
It's long to display so much data so I've attached a workbook sample. Basically you can just do some simple sums. However, it's tricky with the first month, unless you have 12 months of dummy data (zero value) ahead of it. Otherwise, you need to seed the first 12 months, or otherwise use some more sophisticated formulas to determine how far back to go. With dummy data for the first year, its just a simple formula to always bring in the prior 11 months of revenue. Keep the same sheet across multiple years - so many people try to make a "new tab" every month or every year and its just so much more work that way. See sheet 1 in the sample workbook.

However, I would prefer a larger grid that you put the actual monthly amount to recognize into each month's bucket. This will allow you to change your defferal allocation percent at will, without any loss of prior month's allocations or need to create any new formulas. It also provides a semi-graphical display of where the $$ are coming from each month, which is useful for auditing (less chance of errors and easier to see what is happening, plus more opportunities to analyze the data - sum down, sum across, or sum different areas of the grid as needed). See Sheet 2 in the sample workbook.

<a href="http://northernocean.net/etc/mrexcel/20140115_DefferredRevenue.zip">SAMPLE WORKBOOK</a>
sha256 checksum (zip file): 77c7df6cc5eac665f98e3163ba27b58b3a84b018708cd6694067152ac3e30c13
 
Upvote 0
Ok. This makes sense, but in "sheet 2" you put deferred revenue for January as $1,191.67. This isn't correct because in January the deferred revenue is $100-$8.33, not $1,191.67
 
Upvote 0
I see, yes that is correct. My formula for deferred in Sheet2 is wrong. This would be another try at that:
In cell D3: =SUM(E6:INDEX($V$6:$V$35,MATCH(D5,$C$6:$C$35,0)))

new sample workbook:
<a href="http://northernocean.net/etc/mrexcel/20140116_DefferredRevenue.zip">SAMPLE WORKBOOK</a>

Notice that I use column V as a "border" column. It is included in my formula, so if you add rows and *insert* before this border column, the formula will always include the columns you add. That's a trick to avoid having to update the formula whenever you add new columns (without going to the trouble of using a dynamically sizing formula). Perhaps I've gotten lazy because I do that a lot with edges of tables that can grow or shrink on the sides or bottom - when at the bottom, I also like to format the cells with zeros displayed as hyphens and filled across so they appear as a nicely demarcated line which can also be included in any sums.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,266
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top