We are having some slight issues with switchover dates in our financial model. We have outlined the issue in detail below:
The structure of our model is that we have 5 different versions, of which 4 are currently being used (Actual, LIVE Forecast, Previous Forecast and Budget). The screenshot below gives a summary:
The LIVE Forecast has a switchover date of June 2020 since that signifies the point at which our Q1 forecasting process started.
We are now going into Q2 and will need to change the switchover date on the LIVE Forecast version to allow an additional three months of actuals to come into the model.
At the same time, we want to store our Q1 Forecast on Previous Forecast version and an updated budget (Q1 forecast + a few additional transactions) in the Budget version. We thought that both can be achieved by taking bulk copies and changing the switchover date to June 2020 however we ran into difficulties as outlined below.
Actuals are posted retrospectively. So for instance something may be posted in Q2 forecast period that relates to April (which is in Q1). These new actuals come into our model from the data hub and are causing prior period changes to the Budget and Previous Forecast versions.
Ideas on possible fixes we have come up with include:
Doing a true up say in the month after a given forecast period. The issue with this is that subsequent adjustments to actuals may cause our true up to change / have to be recalculated.
Creating a static view of the budget / previous forecasts however this could be quite labour intensive.
If anyone may have had similar experiences / come up with any novel solutions, we would be very happy to hear from you!
Have you tried creating a separate module(acts as staging) with a custom version list to store all the previous forecast or budget version and then you push this to the version dimensioned module based on some import action?