Forum Discussion
Revenue Recognition Calculation Issue
Hi Ibendin,
Really thankful for your help but the numbers seem to be off. Based on the table below, let me try explaning.
| Project Code | P 1000 | |
| Total Expected Revenue (Over Life Of The Project) | 1,39,000 | |
| Period | % | Revenue Recognized |
| Oct-23 | 7.00% | $9,730.00 |
| Nov-23 | 12.00% | $15,512.40 |
| Dec-23 | 18.00% | $20,476.37 |
| Jan-24 | 24.00% | $22,387.50 |
| Feb-24 | 32.00% | $22,686.00 |
| Mar-24 | 51.00% | $24,585.95 |
| Apr-24 | 47.00% | $11,102.24 |
| May-24 | 38.00% | $4,757.43 |
| Jun-24 | 60.00% | $4,657.27 |
| Jul-24 | 100.00% | $3,104.85 |
Let us say the project started in Oct 2023. This is the first month of the project. The project has a forecast from the beginning of the project until the end. We are in November.
For Oct the calculation is as such:
Cost of October (Since it is a past month, this cost is actual) divided by the total cost (Total Cost measure takes the actual cost of any month and adds this to the forecasted cost of all future months). Formula is Cost for the month divided by total cost
The above is a %
Month of October: Since this is the first month of the project, the revenue recognized is a simple multiplication of the Cost % by 139,000. as per the table above it is 7% and the revenue recognized should be $9,730
Month of November. The Cost Percent for this month is 12% (Cost OF November divided by total cost which is November plus forecasted cost until end of the project).
Revenue for November has to be 12% multiplied by 139000-9730 (9730 is the revenue recognized in Oct) and this amount is $15,512
Revenue for December: Let us say the cost percent is 18% (Cost of Dec divided by Total cost which is cost of december plus cost of all future months).
Revenue Recog for Dec is 18% multiplied by 139000 Less (9730 (Oct Rev) plus 15512 (Nov Rev)) which is $20476.
This will continue for all future months until the end of the project.
The total of all the Cost Percent Measure is never 100% as a sum
In short: We multiply the current months Cost Percent by the Net BudgetRevenue which is (Totalbudgetrevenue less revenue recognized until the current period).
Cost measure is working fine for me. I am unable to figure out how to derive the net budget revenue for the current period. The exact place i am stuck is this: Since the revenue is not captured in any table/column i was trying to figure out how to sum the revenue of the previous months via the measure
Hope i have better explained the requirement.
Regards
It i not clear to me what your expected outcome is other than the table above (which matches my proposal exactly)
Hi,
I think the issue is here.. You have stored the Percentages in a table and then use a measure Perc to sum this up. The problem is that the Percentage is a measure in my situation =cost of the month divided by the sum of that month and future forecasted costs. This is dynamic as the forecasts could change and so would this percentage. I have a measure defined for the above and this is working fine. Now this % has to be multiplied by 139000 (less sum of revenue calculated for earlier months). Since the Cost percent is a measure and not stored in a table, i was wondering how to sum this as you have done using your perc measure. That i think is one of the issues
For Oct the calculation is as such: Cost of October (Since it is a past month, this cost is actual) divided by the total cost (Total Cost measure takes the actual cost of any month and adds this to the forecasted cost of all future months). Formula is Cost for the month divided by total costYour sample data does not include a cost column.
The sample data has a cost percentage which is a measure and calculates the percentage as below.
What is do is the following:
One measure called Actual Cost: This measure looks at the time entered by each employee in the time entry table and then looks at the blended cost rate of each employee and calculates the actual cost for the month.
One measure called Forecasted Cost: This looks at the forecast table (Where time is forecasted by Employee per period). Sums the time after the max date of actual time (That way it only looks at cost for the future) and then looks at the blended cost rate of each employee and calculates the forecasted cost for the remaining portion of the project duration.
One measure called Total Cost: This adds the above two measures Actual cost for the period and the forecasted cost for the future periods to get the total remaining cost as on the period (Does not look at past cost)
Then finally i derived the Cost Percent Measure where the Actual cost for the period is divided by the Total Cost. This percentage is what i have shown in the sample data in the Cost Percent Column.The above measures are working fine.
Why are these measures and i didnt try calculated columns? Reason is forecast data changes every month and hence the %'s for cost in the future periods also keep changing. What has happened in the past is based on actuals and these dont change. That is why forecasted cost is calculated only after the Max of the time entry date in the time entry table.