Forecast
The capacity of any corporation to be able to accurately forecast their financial result for the next few months can quickly become critical.
Revenue Forecast
Payroll Forecast
Cash flow Forecast
In order to regain financial control and manage the business I introduced a revenue forecast process to allow the CEO, Head of the Region and Finance department to understand the current market and to allow adjustment.
Direct Payroll costs are one of the main expenses in most corporations, and surprisingly one of the expenses that Finance spends usually less time on to analyze and to forecast.
The capacity of an corporation to accurately forecast their financial result for the next few months can quickly become critical. By having accurate revenue and direct payroll cost, it becomes easier to manage the cash flow and to anticipate any cash shortfall (usually due to business cycle and performance).
Revenue Forecast Summary:
1. Shows current month and YTD actual vs budget
2. Shows full year forecast vs budget
3. Explain variance by type of reasons
4. Graph to make the forecast vs budget more visual
Once all the information required is gathered the critical part is to prepare, the revenue forecast and to send them to each department for their review and completion. In order for the forecast to be relevant and to get the most out of this, the forecast needs to be completed at the beginning of the month within the first couple of days.
By having an accurate extract of the operational system, I created a process and macro to automatically add all the actuals by client, month and department. The forecast file used was the same as in the previous month to make sure that no comments added previously were lost. All the files were then automatically saved and renamed in an appropriate folder. After performing a review of the files created, Finance was updating the deadline in the template and an email was then automatically sent to each Head of Department with their revenue forecast file.
As the revenue numbers were reviewed on a weekly basis, the preparation time was very minimal on Finance; Finance was then able to carry on working on the month-end close.
By end of day 2, all the revenue forecasts were sent back to Finance and consolidated (automatically). A report was then created and sent to the CEO. This allowed the CEO to work closely with the Head of Department and to take action if needed.
fig 1a Monthly Detailed Forecast
fig 1b Revenue Forecast Summary
fig 1c Reason for variance by client
fig 2a Direct Payroll Summary
Direct Payroll Summary (see fig 2a) gives an overall picture of the cost per department and variance against budget, and provides a view on full year cost (forecast) vs the budgeted full year cost.
fig 2b Direct Payroll Details per department
Direct Payroll analysed by staff and by department (see fig 2b), allowing to monitor staff expense by department, and to control/question any increase/decrease in salary.
fig 2c Direct Payroll Forecast
Direct Payroll forecast is prepared by staff and by department (see fig 2c); showing the impact on any increase/decrease in salary and of any new team or new hire.
Sample Output
-
No revenue forecast
-
No management report
-
Management does its own reports
Situation
-
Head of department prepared forecast in 2 days
-
Management managed its team more efficiently
-
Finance reported accurate variance reason to HQ
-
Revenue forecast used for cash flow and full year forecast
-
Facilitated the budget process
Findings/Results
-
Designed revenue forecast by client
-
Automated the monthly revenue update by client
-
Automated consolidation of all department forecasts
-
Automated the distribution of the revenue forecasts
Role
-
Monthly revenue forecast by client (see fig 1a)
-
Revenue forecast summary (see fig 1b)
-
Reason for variance by client (see fig 1c)
In order to make this in place a few preliminary tasks needed to be done:
1. reconcile operational systems with general ledger on a weekly & monthly basis
2. reconcile the revenue by department
3. extract the revenue by client, month and department
Designed and implemented payroll cost reconciliation prepared by staff, month and departments. The forecast template allows to:
- analyse actual vs budget by department
- forecast increase, decrease of salary and impact on new joiners by department
Sample Output
-
No payroll report or forecast
-
No management report
-
Management cannot see the impact of any increase/decrease in headcount and/or salary
-
Direct Payroll is out of control
Situation
-
Reports are prepared during month end
-
Prepare financial impact on staff increase/decrease
-
Finance reports accurate variance reason to HQ
-
Facilitates the budget process
Findings/Results
-
Designed payroll forecast by staff and by department
-
Worked with HR to get all the information required
-
Created payroll summary by department
Role
-
Direct Payroll Summary by department (see fig 2a)
-
Payroll cost by staff - actual vs budget (see fig 2b)
-
Payroll forecast by staff (see fig 2c)
Results
-
No cashflow report prepared
-
Management does not understand what's happening with cash
-
Cash issues in few countries
Situation
-
General ledgers data is not easily usable
-
Lack of accurate revenue forecast/vision
-
Direct payroll cost out of control
-
Cash inflow poorly managed
-
Lack of system to monitor sales/invoice paid
Findings
-
Designed cash flow template
-
Re-worked output from general ledger to get an actual cash flow
-
Implemented revenue and payroll forecast process
-
Created cash collection profile by division
Role
-
Capacity to anticipate any cash shortfall
-
Senior Management more in control with cash
-
Allowed to focus on business growth