I need to upgrade my current Excel consolidated monthly cashflow forecast model (18 month horizon) to include:
a) forecast Profit / loss
b) Forecast balance sheet
c) automated update of prior month forecast with actuals, once month end is finalised
d) Summary reports including Variance Analysis; YTD and updated FY forecasts.
e) design of fit for purpose input sheets which can be distributed to each department and then included in the consolidated plan.
I also need to be able to update assumptions on a monthly basis, for future periods. Currently this is done manually via direct input to feeder excel tabs. I would like to streamline these inputs without impacting past periods.
CURRENT STATUS OF THE MODEL
I have a very manual excel cashflow forecast model for a consolidated group of entities (two entities in the group). On a monthly basis, forecast inf is manually replaced in feeder excel tabs with actual information from our accounting system. This requires two steps:
1. Consolidate the two entities into one dataset from our ERP
2. Input this data into the feeder excel tabs in the forecast model, over-writing the previous forecast with actuals.
Once this is done, I manually amend a summary output sheet to report on the revised Financial Year forecast.
FRUSTRATIONS / areas for improvement
1. The process is very manual, disjointed and subject to human error; it also takes a lot of time
2. There is not currently an easy way to do a variance analysis of monthly / periodic actuals v previous budget on a consolidated basis.
3. The summary reports are very basic and can be improved with visualisation / graphs etc.