Most people outside of finance don’t understand the importance of the Balance Sheet, asthe Income Statement contains the information that they want to see. However, financeprofessionals can often derive the most useful information by interpreting data on acompany’s Balance Sheet.
Apart from the analytical aspects, forecasting the Balance Sheet is important for four mainreasons:
- Cash is king. In today’s business environment, many companies need accuratecash forecasts. They may require these for several reasons, such as the need todetermine whether they have sufficient cash available to fund a new project,or simply to make sure the company doesn’t encounter financial difficulties.
- More accurate cash forecasting enables a company’s treasury departmentto better plan cash arrangements, maximizing interest income and minimizingnon-productive cash balances.
- When a company has debt with a bank, there are often associated covenantsthat require the company to meet certain values in measures like a debtservice coverage ratio or a current ratio. In most cases, an accurate BalanceSheet forecast reassures management that they will meet these measures.
- An inaccurate Balance Sheet forecast can cause complacency and preventmanagement from taking the necessary steps to improve the financial healthof the company.
However, despite the Balance Sheet’s importance, many companies face challenges when it comes to producing accurate Balance Sheet forecasts.
Nearly every budget forecast starts with the Income Statement, with common accounts suchas sales, cost of sales, and operating expenses. There are many reasons to plan an IncomeStatement. Management wants to see the Income Statement and in many companies thebudget 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 likeProphix 11, is usually straightforward. Forecasts often feature a combination of bottomupplanning (where users submit their plans in a workflow-based environment) and topdownplanning (where adjustments are made at a corporate level and these adjustmentspercolate down to individual accounts and departments). They can also involve driverbasedplanning, where revenues and cost drivers (such as units sold or hours worked) areused to calculate both revenues and the costs required to deliver the goods and servicesassociated with such revenues.
Some companies don’t bother to forecast the Balance Sheet. This might be the casebecause they don’t regard it as important or because in the past they have struggled to planaccurately. However, those companies that do usually employ the following methodology:
- First they forecast the Income Statement using the techniques describedabove. This will give plan values that correspond to the company will storein the general ledger. This is useful because future variance reporting willcompare the plan with the general ledger and there is no need to plan at agreater 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 casesthe revenues are held in relatively few accounts.
- After the Income Statement, they plan the Balance Sheet using the IncomeStatement forecast and the opening balances of the Balance Sheet accounts.Organizations forecast accounts like receivables and payables using methodssuch as the following:
[Download PDF to see Tables]
- Other continuity schedules such as accounts payable are also calculatedusing similar methods. Fixed assets are forecast based on the depreciation ofexisting assets and the depreciation of planned asset acquisitions. Adjustmentsare also made to forecast such accounts as accruals and debt.
- 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 eachmonth is forecast to be the amount that will make the Balance Sheet balance.
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 previoussteps 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 toadequately explain how they arrived at the cash forecast. If cash is forecast to changedramatically in a certain month, there is no real explanation of why this is so. It’s a bit likerunning an accounting system without doing any reconciliations and just having blind faiththat the numbers are correct.
Basically, the traditional method produces Balance Sheet forecasts that companies cannotrely on for important decision making.
Need for detailed data
It is usually difficult or impossible to forecast the Balance Sheet accurately based only on theIncome Statement accounts in the general ledger. As an example, consider a companythat 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 thebusiness. 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 newchannel partners) are constantly being created.
It is possible to forecast receivables with a different days of sales value for each month. Theproblem is how to derive these monthly values. This is difficult when there are no historicalvalues on which to base them, sales forecasts are tentative (for new channels), or salesforecasts (and hence blended days of sales) change frequently.
The traditional method uses the Income Statement. To accurately forecast receivables andpayables, however, most companies need to plan at a greater level of detail than thatavailable in the general ledger. In the example above, forecasting sales by channel wouldbe essential for accurate Balance Sheet forecasting. The same is true of other components ofthe Balance Sheet such as payables and fixed assets as well as accounts related to revenuerecognition.
Prophix has developed a component of their software called Detailed Planning. This toolenables the development of much more accurate Balance Sheet and Income Statementforecasts 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 BalanceSheet. It usually requires planning at a greater level of detail than that which is availablein most general ledger Income Statements. However, companies have different generalledgers and, depending on their business models, different data requirements for accurateBalance Sheet planning.
The data requirements that are necessary for accurate forecasting are often associated withthe 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 alsobe associated with payables (where payment terms and/or fulfillment can be different fordifferent vendors) or personnel costs (where salaries are paid immediately to staff, payrolltaxes are paid later, and bonuses may be accrued).
Analyzing a company’s business model will identify how data is organized, clarifying therequirements for detailed data and how that data can be sourced – i.e., which people inthe business can contribute the relevant data. In many cases, companies ask departmentalmanagement to give monthly or even weekly forecasts.
Data requirements typically fall into categories that relate to the source of data thatorganizations require to forecast the business. Some of these categories can be includedtogether in a single Prophix model that enables users to enter detailed data. Others requirespecialized information and/or calculations to forecast the Balance Sheet and so they canbe modeled independently. Typical examples of the latter categories include billings, fixedasset 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 haveidentified. The model is a vehicle for users to enter data as well as a repository for thecalculations that operate on that data. There is no requirement for programming or writingmacros; Prophix offers a point-and-click graphical user interface for all of this. However, mostcompanies 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 newexpenses 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 thetransactions 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 personnelplanning, when companies import data for existing employees. Users can then makeadjustments or add new employees.
Calculations
Each Prophix model can calculate many accounts. A billings model, for instance, can giverise 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 modelusers can enter annual salaries and can forecast overtime and benefits packages. Themodel 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 monthlyor weekly values. Other data can be entered as annual values and then spread acrossmonths. 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 creditsides of any transaction. For example, if a user forecasts $1,000 of travel expenses that arepaid immediately to an employee in February, the model will forecast in February a debitin 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 expensesand payables in February and a transaction between payables and cash in April. Prophixeffectively simulates the double-entry bookkeeping aspects of an accounting system; thisway, 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 eachtransaction. Data can be reported by transaction, by vendor (for expense planning), or byemployee (for personnel planning). This gives finance a detailed audit trail that shows howeach account has been calculated.
The second model is the financial model that has less detail and relates more closely to thegeneral 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 inthe financial model.
Unlike most CPM software, Prophix supports merging data from multiple models in a singlereport. 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 totalforecast of disbursem*nts. Reports like this, from the detailed models, can be used to reconcilethe data in the financial model, and hence the complete forecast has a reconciliation. Inaddition, reports can include non-numeric data such as payment terms or comments thatusers have entered.
Conclusion
Most Corporate Performance Management software products allow companies to forecasttheir 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 thismeans that cash forecasting is unreliable.
Prophix provides much more accurate forecasts than those produced using the traditionalmethod. Prophix expands a company’s planning to create accurate Balance Sheetforecasts, 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 meansthat the office of finance can predict cash much more accurately, enabling better cashmanagement and maximizing the company’s return on its cash investments.
