The importance of planning the Balance Sheet

Most people outside of finance don’t understand the importance of the Balance Sheet, as the Income Statement contains the information that they want to see. However, finance professionals can often derive the most useful information by interpreting data on a company’s Balance Sheet.

Apart from the analytical aspects, forecasting the Balance Sheet is important for four main reasons:

1. Cash is king. In today’s business environment, many companies need accurate cash forecasts. They may require these for several reasons, such as the need to determine whether they have sufficient cash available to fund a new project, or simply to make sure the company doesn’t encounter financial difficulties.
2. More accurate cash forecasting enables a company’s treasury department to better plan cash arrangements, maximizing interest income and minimizing non-productive cash balances.
3. When a company has debt with a bank, there are often associated covenants that require the company to meet certain values in measures like a debt service coverage ratio or a current ratio. In most cases, an accurate Balance Sheet forecast reassures management that they will meet these measures.
4. An inaccurate Balance Sheet forecast can cause complacency and prevent management from taking the necessary steps to improve the financial health of the company.

However, despite the Balance Sheet’s importance, many companies face challenges when it comes to producing accurate Balance Sheet forecasts.

Traditional methods of forecasting often fall short

Nearly every budget forecast starts with the Income Statement, with common accounts such as sales, cost of sales, and operating expenses. There are many reasons to plan an Income Statement. Management wants to see the Income Statement and in many companies the budget is treated not only as a forecast but also as an approval to spend.

Forecasting the Income Statement, whether in a spreadsheet or using CPM software like Prophix 11, is usually straightforward. Forecasts often feature a combination of bottomup planning (where users submit their plans in a workflow-based environment) and topdown planning (where adjustments are made at a corporate level and these adjustments percolate down to individual accounts and departments). They can also involve driverbased planning, where revenues and cost drivers (such as units sold or hours worked) are used to calculate both revenues and the costs required to deliver the goods and services associated with such revenues.

Some companies don’t bother to forecast the Balance Sheet. This might be the case because they don’t regard it as important or because in the past they have struggled to plan accurately. However, those companies that do usually employ the following methodology:

1. First they forecast the Income Statement using the techniques described above. This will give plan values that correspond to the company will store in the general ledger. This is useful because future variance reporting will compare the plan with the general ledger and there is no need to plan at a greater level of detail. The plan may include data at the departmental level, but without a great deal of detail within accounts. For example, in most cases the revenues are held in relatively few accounts.
2. After the Income Statement, they plan the Balance Sheet using the Income Statement forecast and the opening balances of the Balance Sheet accounts. Organizations forecast accounts like receivables and payables using methods such as the following:

   [Download PDF to see Tables]

3. Other continuity schedules such as accounts payable are also calculated using similar methods. Fixed assets are forecast based on the depreciation of existing assets and the depreciation of planned asset acquisitions. Adjustments are also made to forecast such accounts as accruals and debt.
4. However, at this point the Balance Sheet doesn’t balance. To fix this problem, a ‘plug’ has to be used and the most common plug is cash. The cash for each month is forecast to be the amount that will make the Balance Sheet balance.

Common limitations with the traditional method of planning the Balance Sheet

Unreliable forecasts

Plugging the Balance Sheet with cash solves the problem of an unbalanced Balance Sheet, but it doesn’t reliably explain how the cash balances are derived. If one of the previous steps has been calculated incorrectly or inadvertently left out, the cash value calculated is meaningless.

Cash is king, but, because cash is calculated as a plug, it is impossible for the company to adequately explain how they arrived at the cash forecast. If cash is forecast to change dramatically in a certain month, there is no real explanation of why this is so. It’s a bit like running an accounting system without doing any reconciliations and just having blind faith that the numbers are correct.

Basically, the traditional method produces Balance Sheet forecasts that companies cannot rely on for important decision making.

Need for detailed data

It is usually difficult or impossible to forecast the Balance Sheet accurately based only on the Income Statement accounts in the general ledger. As an example, consider a company that has a single account for widget sales that represents sales through four channels.

[Download PDF to see Tables]

The blended days of sales value changes dramatically because of seasonality in the business. Yet, the general ledger of most companies does not track sales based on channel, especially when new sales channels (e.g., web sales, new types of channel partner, or new channel partners) are constantly being created.

It is possible to forecast receivables with a different days of sales value for each month. The problem is how to derive these monthly values. This is difficult when there are no historical values on which to base them, sales forecasts are tentative (for new channels), or sales forecasts (and hence blended days of sales) change frequently.

The traditional method uses the Income Statement. To accurately forecast receivables and payables, however, most companies need to plan at a greater level of detail than that available in the general ledger. In the example above, forecasting sales by channel would be essential for accurate Balance Sheet forecasting. The same is true of other components of the Balance Sheet such as payables and fixed assets as well as accounts related to revenue recognition.

Prophix’s operational planning alternative

Prophix has developed a component of their software called Detailed Planning. This tool enables the development of much more accurate Balance Sheet and Income Statement forecasts than the traditional method allows. The following steps explain how it works.

[Download PDF to see Image]

Forecasting requirements analysis

Accurate forecasting requires analysis of how accounting transactions affect the Balance Sheet. It usually requires planning at a greater level of detail than that which is available in most general ledger Income Statements. However, companies have different general ledgers and, depending on their business models, different data requirements for accurate Balance Sheet planning.

The data requirements that are necessary for accurate forecasting are often associated with the revenue side of the business and can be used to predict, for example, cash receipts, revenue recognition, commissions, and direct costs. But the data requirements can also be associated with payables (where payment terms and/or fulfillment can be different for different vendors) or personnel costs (where salaries are paid immediately to staff, payroll taxes are paid later, and bonuses may be accrued).

Analyzing a company’s business model will identify how data is organized, clarifying the requirements for detailed data and how that data can be sourced – i.e., which people in the business can contribute the relevant data. In many cases, companies ask departmental management to give monthly or even weekly forecasts.

Data requirements typically fall into categories that relate to the source of data that organizations require to forecast the business. Some of these categories can be included together in a single Prophix model that enables users to enter detailed data. Others require specialized information and/or calculations to forecast the Balance Sheet and so they can be modeled independently. Typical examples of the latter categories include billings, fixed asset acquisitions, personnel costs, expenses, and contractual purchases.

[Download PDF to see Spider Diagram]

Modeling each category

Companies usually build a Prophix model for each category of transaction that they have identified. The model is a vehicle for users to enter data as well as a repository for the calculations that operate on that data. There is no requirement for programming or writing macros; Prophix offers a point-and-click graphical user interface for all of this. However, most companies use Prophix implementation staff to build these models for the first time.

Data

Data for the models can come from two sources. One way is for users to enter data directly. For example, a data entry template for collecting expense forecasts might look like this:

[Download PDF to see Table]

In this example, users can enter monthly forecasts for established vendors as well as add new expenses from new vendors and estimate employee expenses. Each line can be considered a transaction, but the data usually appears in a much more summarized form than the transactions in the accounting system or ERP. In this example, estimated sales rep mileage expenses might relate to several expense forms in the accounting system.

Alternatively, data can be imported from other systems. This often happens with personnel planning, when companies import data for existing employees. Users can then make adjustments or add new employees.

Calculations

Each Prophix model can calculate many accounts. A billings model, for instance, can give rise to calculations of receivables, cash receipts, prepaid revenues, direct costs, and so on. The data entered by users can drive many accounts. For example, in a personnel model users can enter annual salaries and can forecast overtime and benefits packages. The model calculates a forecast of monthly salaries, overtime costs, payroll taxes, etc.

Calculations convert the user-entered or imported data into data that relates to the plan. Some data (as in the expense forecast example above) can be entered by users as monthly or weekly values. Other data can be entered as annual values and then spread across months. For example, a manager may want to pay an annual salary to an employee; Prophix’s software will calculate the amounts for each pay period.

For accurate Balance Sheet planning, Prophix will calculate both the debit and the credit sides of any transaction. For example, if a user forecasts $1,000 of travel expenses that are paid immediately to an employee in February, the model will forecast in February a debit in the travel expenses account and a credit of $1,000 in cash. If the expense has terms (e.g., net 60 days) then the model will forecast a transaction between travel expenses and payables in February and a transaction between payables and cash in April. Prophix effectively simulates the double-entry bookkeeping aspects of an accounting system; this way, the Balance Sheet always balances.

Reporting

When users enter data, the results of calculations are usually placed in two models.

[Download PDF to see Images]

The detailed model is the one that is usually built for each transaction category – billings, expenses, personnel, etc. Here there will be detailed information that relates to each transaction. Data can be reported by transaction, by vendor (for expense planning), or by employee (for personnel planning). This gives finance a detailed audit trail that shows how each account has been calculated.

The second model is the financial model that has less detail and relates more closely to the general ledger. Here, data is typically summarized by month, account, and department, depending on how a company’s ERP system works. If the ERP system also has other entities (e.g., project or channel) built into the chart of accounts, then these can also be present in the financial model.

Unlike most CPM software, Prophix supports merging data from multiple models in a single report. Therefore, reports like the following are easy to generate:

[Download PDF to see Image]

Here, data from personnel, components, and expenses models combine to give a total forecast of disbursements. Reports like this, from the detailed models, can be used to reconcile the data in the financial model, and hence the complete forecast has a reconciliation. In addition, reports can include non-numeric data such as payment terms or comments that users have entered.

Conclusion

Most Corporate Performance Management software products allow companies to forecast their Balance Sheets using a traditional method that involves calculating cash as a plug. Using this method organizations struggle to accurately plan the Balance Sheet and this means that cash forecasting is unreliable.

Prophix provides much more accurate forecasts than those produced using the traditional method. Prophix expands a company’s planning to create accurate Balance Sheet forecasts, including better forecasting of cash. Organizations can update their forecasts monthly or weekly to reflect changing business conditions.

Producing more understandable and more accurate forecasts of the Balance Sheet means that the office of finance can predict cash much more accurately, enabling better cash management and maximizing the company’s return on its cash investments.