Hi
I have doing a forecast for subscription revenue and need to forecast out 2 years however the subscription can be anywhere from 1 month out to 3 years. So I need to calculate once the subscription has ended it will start again.
For example
Start date: 1/7/16
End date: 30/6/17
Amount: 1,200
Therefore at the end of July 16 the amount should be 1,101.37 then August 999.45 and so on until June 17 when the amount will be zero. Then July 17 shows 1,101.37 August 17 999.45 and so on. I am trying to find out the closing balance each month of my unearned revenue account in the balance sheet so need to "re-issue" the invoice once the end date has happened.
I have different starts dates and end dates for example to be able to do the calculation
I have attached a workbook which shows the revenue each month, closing balance and what it should look like.
The problem is with the closing balance formula I have in there it is using MOD which is causing the issue.
Your help would be much appreciated.
Thanks
Jon
I have doing a forecast for subscription revenue and need to forecast out 2 years however the subscription can be anywhere from 1 month out to 3 years. So I need to calculate once the subscription has ended it will start again.
For example
Start date: 1/7/16
End date: 30/6/17
Amount: 1,200
Therefore at the end of July 16 the amount should be 1,101.37 then August 999.45 and so on until June 17 when the amount will be zero. Then July 17 shows 1,101.37 August 17 999.45 and so on. I am trying to find out the closing balance each month of my unearned revenue account in the balance sheet so need to "re-issue" the invoice once the end date has happened.
I have different starts dates and end dates for example to be able to do the calculation
I have attached a workbook which shows the revenue each month, closing balance and what it should look like.
The problem is with the closing balance formula I have in there it is using MOD which is causing the issue.
Your help would be much appreciated.
Thanks
Jon
