How to Put Actuals in Prior Forecast Periods

Document created by Bob Gill on Jan 16, 2018
Version 1Show Document
  • View in full screen mode

When building a financial planning model, it's often important to have budget, actual and forecast versions. Budget is the annual plan, which may be multi-year. Actuals are the history of actual transactions, often loaded from an ERP database periodically. Forecast is a blend of the two. During a given year, a forecast version shows actuals for closed periods and budget for the remaining periods of the fiscal year. For example, JuneForecast would include actuals in January to May and then budget for June to December. Some organizations take it one step further and allow users to edit the remaining forecast periods. This helps managers react to events and adjust the remaining periods of the plan to see how the year will end. This database process shown below copies Actuals to appropriate (Version, Month) combinations. 


June ForecastJan..MayJune..Dec
August ForecastJan..JulAug..Dec
October ForecastJan..SepOct..Dec
December ForecastJan..NovDec



This procedure also relies on a custom time entity and custom time relationship. Here we call it Month Static. Month Static maps each month in Board to the generic month of the year. This allows us to build the procedure once and have it reused each year.


Below are the contents of the Month Static custom time entity, as it relates to a month entity.



Below is the graphical relationship between Month Static and other time entities.


With this design approach, a procedure to actualise closed periods of a forecast can be written once and run anytime.


Thanks to Coulton Carrol for sharing this design approach with me.

1 person found this helpful