# Deferred Revenue Schedule

#### amartino44

##### Board Regular
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

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

#### xenou

##### MrExcel MVP
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

#### amartino44

##### Board Regular
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

#### xenou

##### MrExcel MVP
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:

Replies
0
Views
93
Replies
3
Views
86
Replies
1
Views
143
Replies
1
Views
152
Replies
8
Views
183

1,196,010
Messages
6,012,843
Members
441,733
Latest member
MartijnB

### 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.

### Which adblocker are you using?

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

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