Cash Flow Budgeting

budget variance analysisCash Flow Statement
4 posts / 0 new
Last post
Timothy Bizos A+ 2
Cash Flow Budgeting

Hi All,

We’re getting more frequent requests to build budget variance analysis for Cash Flow as well as Balance Sheet areas – as a supplement to the standard Budget Income Statement modules already contained in the monthly historical forecast libraries.

This isn’t an issue for the Budget Balance Sheet, that’s more a decision on suitability, but there are some structural issues for a Cash Flow Statement that undermine the ease of creating budget analysis.

To recap the module-by-module structure of our Income Statement Budget mechanism, the same categories are in use across all modules:

  1. Historical Income Statement
  2. Forecast Revenue (for example)
  3. Income Statement
  4. Budget Income Statement
As such, when historical revenue data for category 1 is imported from an accounting package, this is the same line items that was locked down for the original budget, is in use for on-going forecasting, and so on.  Variances can be determined as we have all data points (history, budget and forecast) for the same category.

In the case of a Balance Sheet Budget approach, the same consistency of categories is also available across the historicals balance sheet and associated forecast / budget modules.  So for a particular debtor, we can determine variances as we have all related data points.

However, in the case of any Cash Flow Statement Budget, such consistency of data points doesn’t exist – there are different mechanisms and categories for historical versus forecast analysis.

For example, in the all periods cash flow statement, the forecast capital expenditure is based on dedicated forecast categories. By comparison, historical capital expenditure is a derivation based being the historical movement in fixed assets, as adjusted for depreciation:

The historical depreciation could be allocated to the individual line items to work out specific historical capital expenditure, but trial balances typically lack this level of granularity. Indeed, Modano treats the modelling of fixed assets on the same basis – capital expenditure modules allow for a great deal of granularity to create accurate cash flows but then aggregates these into more material fixed asset categories for presentation in the financials.
For the cash flow, particularly capital expenditure, budget variance analysis our solution is typically to build this content into the applicable capital expenditure (or other) module itself. This process if largely one of inserting new components to cover:
  1. Forecast assumptions and outputs – contained already
  2. Historical Data
  3. Budget Data
  4. Variance Analysis

This allows the user to leverage the detail of the capital expenditure modules (relative to the aggregated fixed asset modules). The Modano Import Assumptions Tool automates the internal transfer of the forecast outputs into the stored budget component.  The only unknown process is likely to be getting the detailed historical capital expenditure into the historical component. Again, Modano’s import assumptions tool will automate this process – the bigger question is typically where this data is being sourced from.

Related to this, we don’t tend to recommend actually Budgeting for balance sheets. Benchmarking is a much more powerful analysis tool. 

So rather than budget for a certain debtors figure, the variance of which would be based on a mixture of revenue (sales team accountability) and debtor collection (accounts receivable team performance), the smarter approach is to benchmark a Debtors Days figure, with alerts included.  This is a natural extension of bank covenants.  Budgeted balance sheets don’t tend to aid accountability or interpretation of performance.

Kevin Ballinger A+ 8

Hi Timothy, I am picking up this old thread as I have been asked for some specific Cash-based variance reporting from a Client. I'm comfortable enough explaining to Client that P&L (Budget) variance + Creditor Days benchmarking = more refined view on Cash management (as it relates to costs), much as you have elaborated above, BUT I've stumbled upon an old Module reference that I really want to use, and that is "Capital Payables" - i.e. this Client will be heavy on Capex as well as Opex and I want to bring the Capex AP management into the frame.

I can't see it in the current Library.....or at least not available in my example template model (Modano-Single Entity Model-COA1, Budgeting-VAT-SC-M~HF-UK-

Can you tell me if Capital Payables is still supported, or whether I would need to create this from fresh Module myself?




Jun Yan A+ 124

Hey Kevin,

Just to jump in as I came across the post.

The template you're referring doesn't have Capital Payables as part of the COA, so the necessary modules won't be there.

Quickest current way to view Chart of Accounts is to use the new financial model wizard and click through until the COA selection screen comes up, you'll see that COA6 is the only one that contains Capital Payables. You'll need to refer to Chart of Account 6 libraries in order to see content related to Capital Payables separated out.

Hope that helps.



Kevin Ballinger A+ 8

Jun, thanks for the quick response. Thoughtless of me to not consider the COA....though i've never delved into the BS to choose COA before!

This looks great and I'm looking forward to playing with this Module for the first time.