6

CASH FUNDING MODEL

Closely connected to the operational activity model is the cash funding model, which calculates cash requirements based on future revenues and expenses. This chapter explains how to create this model, with examples based on the XYZ, Inc. case study.

MODEL PURPOSE

The purpose of the cash funding model (CFM) is to assess the organisation’s need for financial resources. Some of those resources will be used to support operating expenses, and others will be required for capital investment or strategic initiatives. This model is intrinsically linked to the operational activity model (OAM) and detailed forecasting model (DFM) in order to predict future cash flows. This can help management to assess the best source for any cash shortfalls.

Although it is true that most internal financial systems can hold data relating to the actual flow of cash, what they do not allow is for management to ‘play around’ with the data from a planning point of view (for example, to see a revised cash flow based on new supplier credit terms or a change to customer payment profiles, to consider the cost of funding an increase in production capacity to meet the projected demand for new products, or to assess the impact on resources by outsourcing a particular function).

Similarly, financial systems do not hold the key assumptions that affect cash flow. For instance, inflation has a major impact on cash resources, yet the underlying data supporting any inflation assumptions is not contained within those systems.

This is where the CFM comes in; it lets management gauge the impact of change on financial resources without having to commit to those changes within the supporting transaction systems.

The versions held by the model include the following:

• Actual. This enables a historic view of cash balances and will be directly loaded from an organisation’s general ledger.

• Budget. This is generated as part of the budget process to gain an idea of cash requirements.

• Forecast. This takes a realistic, forward look at the best estimates for cash in the short term (that is, the next couple of months). Depending on the complexity of the organisation, the data used will come either from the DFM or the OAM.

DEFINING THE MODEL

Model Content

Modelling cash flows and balances require different sets of information, as depicted in figure 6-1.

Figure 6-1: The Flow and Sources of Cash Within the Planning Framework

The dotted line indicates information stored within the CFM, and the data flows from other models in the framework. These can be summarised as follows:

• Customer and supplier payment terms. The CFM contains details about each major supplier and customer where the cash flow effect is to be calculated. Depending on how payment terms are defined (for example, in weeks or months), the time intervals in this model may be at a shorter increment than that of the OAM.

• Cash supply. Cash is modelled for budgets and forecasts. The supply side of cash is taken from the OAM and the DFM. Data from these models will need to be at a level of granularity where individual supplier or customer mov ements can be identified so they can match up with the appropriate customer details.

• Cash demand. Similarly, the demand side for cash is also taken from the OAM and the DFM. This takes into account all operational expenses, which for a manufacturer would include the supply of raw materials and manufacturing costs. It also includes any cash flows that arise in relation to capital expenditure. As with supply, these outflows should be at a level where they can be linked to the pay ment profiles held within the CFM.

• Net funding requirements. Rules within the CFM are used to ‘time-shift’ the imported cash supply and demand data into the time periods in which cash will flow in and out of the organisation’s treasury bank account(s). To this other cash consumers and income streams not cov ered are added. This may include items such as interest payments and dividend accruals. To capture these, we could either create a separate support model or they can be entered directly into the CFM. By subtracting the demand for cash from the supply, management can review the financial resources required.

To address any cash shortfall, or to reduce the amount of borrowings, budget and forecast data within the OAM can be reassessed to see what activities might change. The model would also allow management to gauge the impact of changing customer and supplier payment terms. Assuming this has been done, the model can now be used to assess how any cash shortfalls should be financed with the two obvious financing sources being debt and equity.

When reporting actual results, much of the data within the cash flow model will be entered directly from the underlying transaction systems, so there is little need for modelling other than to produce a comparison between budget and forecast versions.

Additional Data Requirements Within the OAM

Generating a cash flow from the OAM and DFM as previously defined requires two things:

1. New information will need to be held in those models that links individual suppliers or customers to the P&L data.

2. Data held within these models will need to be at the level of detail where interactions with major suppliers and customers can be identified. With this in mind, a few comments need to be made on the practicalities of designing the CFM and associated models.

Depending on the industry, it may not be possible to forecast sales by individual customer. In this case, grouping customers into categories, such as sales channels, that reflect their payment profile is adequate for our needs.

Similarly, it could be that invoices are only raised at the end of the month with a standard payment profile of 30-day terms. In this case, from a planning point of view, cash will theoretically be received 1 month following the invoice date. As this is equivalent to a 1-period delay between P&L and cash flowing, there is no point in calculating this at a day level.

The level of detail in both content and time should be determined by the impact severity that any time delay on a particular measure has on overall cash flow. For example, if invoices for particular customers or suppliers are raised mid-month that have a 1 percent or greater impact on cash flow, then it could be argued that it is worth modelling cash flow by week, and worth modelling at that customer or supplier level. This will almost certainly warrant a more detailed forecast model for the measures involved.

If there is no such requirement, then modelling cash flow could be accommodated within the OAM.

Identifying Cash Payment Profiles Within the OAM

In order to generate the cash flow from P&L data, the model will need to identify the payment profile for both customers and suppliers. Where it is not practical to identify these at a third-party organisation level, such as with office expenses where the supplier may vary from month-to- month, then some broad assumptions can be made as to when cash flows out of the organisation. For example, we could assume that cash involved with personnel costs will always be in the same period in which the costs are incurred from a P&L standpoint. Other items could be assumed to have a one-period delay from when they are incurred.

To identify individual profiles, we can add a new attribute against the measures in the OAM where that P&L data is held. This provides a ‘look up’ inquiry capability that indicates who the third party is for the related income or expense. Remember, these are for income and expenditure items that do not have a DFM. Consider the following example from our case study, depicted in table 6-1.

Table 6-1: P&L Third Party Measure Example

In this example, the attribute ‘customer or supplier’ is used to forge the link between an account and the third party involved. Salaries and wages, overtime, and travel and entertainment have the customer or supplier attribute of ‘employee’; rent is linked to ‘S. Atkins’; heat, light, and power is linked to the supplier ‘EDF’; and so on. These attributes appear as members within the CFM as will be explained in the next point.

Defining Payment Profiles Within the CFM

One of two methods can be used to store and calculate the cash effect of individual payment profiles within the CFM:

• When there are only a few suppliers or customers, each could be set up as individual measures in the CFM. These measures directly relate to the individual customer or supplier attributes as defined in the OAM, and the value contained within the measure is set to the period delay (that is, the number of periods that occur between order and cash flowing). Table 6-2 gives an example.

Table 6-2: P&L Period Delay Measure Example

In the table 6-2, the value in measure ‘EDF’ and ‘S. Atkins’ signals that cash flows out of the organisation one period after any expense that has this attribute. However, any expense associated with employee occurs in the same period.

• Where there are multiple suppliers or customers, each could be set up as individual members of a new dimension within the CFM. These members directly relate to the individual customer or supplier attributes as defined in the OAM. Using the preceding example, this dimension (table 6-3) would consist of the following:

Table 6-3: P&L Individual Member of New Dimension Measure Example

To go with this dimension, we require a measure within the OAM that indicates the time delay between the value appearing in the P&L and cash flowing into a bank account. The value of this measure is set for each dimension member to reflect the payment term.

Other Cash Measures and Rules

As well as the P&L measures identified so far, additional measures may be required to cover other cash flows not contained within the OAM (for example, loan repayments, dividends, bank balances, interest charges, and interest rates). A measure will also be required to hold exchange gains or losses, should the organisation be involved with multiple currencies.

The attributes, dimension members, and measures as they relate to customers and suppliers are used to ensure that the correct data is pulled through from the OAM to the right place within the CFM. They are also used in rules to calculate the cash impact on any pulled-through values, ensuring they are suitably time-shifted.

For the XYZ, Inc. case study, these measures and rules were set at the following:

Table 6-4: Additional Cash Flow Measures Example

As table 6-4 shows, there needs to be a process that transfers the data from the OAM and other models into the CFM. How this is done depends on the planning technology being used. In a spread sheet, this would be via cell links that reference both the model and the attribute so the CFM receives the appropriate values.

Although this example has concentrated on the link between the OAM and the CFM, the same concept also applies to any DFM and strategic improvement models.

REPORTING FROM THE CFM

By implementing the CFM as described, it is possible to track how individual P&L items impact cash flow, as can be seen in the following examples.

Displaying Cash Requirements by Department

For our case study, we have defined customers and suppliers as a separate dimension member. Customers have also been linked to particular organisation departments by adding an attribute to the customer member that identifies which department is responsible for them. This is then used to filter the customer list depending on the department selected on the report. We could also have gone one step further and introduced products into the profile, which would then allow us to have multiple prices and payment profiles.

In the first report, shown in figure 6-2, the user can select the department (provided they have the correct security access rights), the version (that is, budget or forecast), and the year to be displayed. This then selects the appropriate measures, which in our example is the US sales division. The values shown are the cash effect of P&L data taken from the OAM. Alongside each account, we have displayed the customer or supplier attribute and the payment term that was used to time-shift the P&L data. The report then shows the cash supply and demand as being forecast for the next six periods. At the bottom is a summary of individual month requirements and a cumulative view.

Figure 6-2A: Report Showing Cash Requirements by Department

Figure 6-2B: Report Showing Cash Requirements by Department

In the report, payment terms are stored as periods. If the CFM was set up as a weekly model, then the values would represent the number of weeks between supply and receiving cash. Payment profiles are kept by month and version. This allows the profiles to be modified over time and to be applied to different budget or forecast scenarios.

By selecting total company as opposed to an individual department, the report would accumulate the cash impact from indiv idual departments to give a total cash picture.

Evaluating Sources of Cash

This next report, shown in table 6-5, has the total cash requirements as produced in the last report, which is then compared with potential cash sources. The user can select budget or forecast versions.

Table 6-5: Report Showing Cash Requirements and Sources

The report displays the forecast bank cash balance along with projected cash income and expenditure to give the net cash flow from operations. From this the other cash consumers and cash needed for capital expenditure are deducted. This calculates a net cash balance. If positive, the organisation has enough cash to operate. If negative, cash sources need to be found unless forecast expenditures can be reduced (or income increased).

The source of funds section contains various ways of funding any cash shortfall. Here management can enter the amount to be funded, which can be split amongst a number of providers. These sources also have an impact on cash flow and the net cash requirement at the bottom of the report calculated to take into account.

Scenario Analysis

By using versions within the CFM, we are able to run different payment scenarios as well as try out alternative sources of funding. For example, we could set up a forecast version to show a worst position (for example, low revenues and high expenses), and a best forecast version (for example, high revenues and low costs).

Data from the current forecast version can be copied into these versions where revenues and expenditures are adjusted. The resulting cash impact for each version can then be displayed side-by-side so the extent of any borrowings can be seen and compared.

We have now completed the description of the two central models of the planning framework. Supporting these are a range of models that provide detail behind the OAM and CFF. The first two we will examine—detailed history model and performance measures model—are the subject of the next chapter.