One of the requirements during a recent Performance Architects Oracle Planning and Budgeting Cloud Service (PBCS) implementation project was to create a solution for their projection process. During discussions with the Budget Office, we learned that requirements included:
- Projections occur three times a year:
- After Q1 actuals are complete
- After Q2 actuals are complete
- After Q3 actuals are complete
- Historical actuals and budget data must be used to seed the projection scenario
- Revenues need to be seeded using one methodology, while expenses are seeded using another methodology
- After the projection scenario is seeded, additional adjustments may need to be made by department, fund, program, etc.
The calculation to seed the “Projection” scenario contains several components:
- Q1 revenue and expense actuals were copied to “Q1 Projection”:
Using prior year actuals, “% of YearTotal” was calculated for all revenue accounts for each month. See the example below for October (500 / 9,750 = 5.1%):
The monthly “% of YearTotal” is then multiplied by the “Budget YearTotal” value to calculate each month’s revenue. See the example below for October (5.1% x 8,100 = 415):
Expense accounts are calculated by taking the average of “Q1 Actuals” and loading that value into all of the out months:
After the business rule is launched, the results are displayed:
After the “Q1 Projection” is calculated, the Budget Office wanted the option to make additional adjustments. This was accomplished by leveraging the hierarchy in the “Version” dimension. We created a sibling of “Projection Calculated” and named the member “Projection Adjustments.” The parent of those two members is “Projection Total”.
Need help with your PBCS implementation project? Contact us at firstname.lastname@example.org and we can help you out!