CHAPTER TWELVE
Financial Forecasting, Planning, and Budgeting
FINANCIAL FORECASTING: THE PERCENT-OF-SALES METHOD
What is financial forecasting and why is it done?
Financial forecasting, an essential element of planning, is the basis for budgeting activities. It is also needed when estimating future financing requirements. The company may look for financing either internally or externally. Internal financing refers to cash flow generated from the company’s normal operating activities. External financing refers to funds provided by parties external to the company. You need to analyze how to estimate external financing requirements. Basically, forecasts of future sales and related expenses provide the firm with the information to project future external financing needs.
The four basic steps in projecting financing needs are:
1. Project the firm’s sales. The sales forecast is the initial step. Most other forecasts (budgets) follow the sales forecast.
2. Project additional variables, such as expenses.
3. Estimate the level of investment in current and fixed assets to support the projected sales.
4. Calculate the firm’s financing needs.
How does the percent-of-sales method work?
The most widely used method for projecting the company’s financing needs is the percent-of-sales method. This method involves estimating the various expenses, assets, and liabilities for a future period as a percentage of the sales forecast and then using these percentages, together with the projected sales, to construct forecasted balance sheets. Example 12.1 illustrates how to develop a pro forma balance sheet and determine the amount of external financing needed.
Example 12.1
Assume that sales for 2X11 equal $20, projected sales for 2X12 equal $24, net income equals 5% of sales, and the dividend payout ratio is 40 percent. Exhibit 12.1 illustrates the method, step by step. All dollar amounts are in millions.
The five steps for the computations are:
1. Express those balance sheet items that vary directly with sales as a percentage of sales. Any item such as long-term debt that does not vary directly with sales is designated “n.a.,” or “not applicable.”
2. Multiply these percentages by the 2X12 projected sales of $24 to obtain the projected amounts as shown in the last column.
3. Insert figures for long-term debt, common stock, and paid-in capital from the 2X11 balance sheet.
4. Compute 2X12 retained earnings as shown in footnote a.
5. Sum the asset accounts, obtaining total projected assets of $7.20, and add the projected liabilities and equity to obtain $7.12, the total financing provided. Since liabilities and equity must total $7.20, but only $7.12 is projected, we have a shortfall of $0.08 “external financing needed.”
Although the forecast of additional funds required can be made by setting up pro forma balance sheets as described here, it is often easier to use the following formula:
External funds needed (EFN) = Required increase in assets – Spontaneous increase in liabilities – Increase in retained earnings
EFN = (A/S)
S – (L/S)S – (PM)(PS)(1 – d)
where
A/S = Assets that increase spontaneously with sales as a percentage of sales
L/S = Liabilities that increase spontaneously with sales as a percentage of sales
DS = Change in sales
PM = Profit margin on sales
PS = Projected sales
d = Dividend payout ratio
In this example,
A/S = $6/$20 = 30%
L/S = $2/$20 = 10%
DS = ($24 – $20) = $4
PM = 5% on sales
PS = $24
d = 40%
Plugging these figures into the formula yields:
Thus, the amount of external financing needed is $800,000, which can be raised by issuing notes payable, bonds, stocks, or any combination of these financing sources.
EXHIBIT 12.1 Pro Forma Balance Sheet (in Millions of Dollars)
The major advantage of the percent-of-sales method of financial forecasting is that it is simple and inexpensive to use. One important assumption behind the use of the method is that the firm is operating at full capacity. This means that the company has no sufficient productive capacity to absorb a projected increase in sales and thus requires additional investment in assets. Therefore, the method must be used with extreme caution if excess capacity exists in certain asset accounts.
BUDGETING
What is a budget?
A comprehensive (master) budget is a formal statement of the CFO’s expectation regarding sales, expenses, volume, and other financial transactions of an organization for the coming period. Simply put, a budget is a set of pro forma (projected or planned) financial statements. It consists basically of a pro forma income statement, pro forma balance sheet, and cash budget.
A budget is a tool for both planning and control. At the beginning of the period, the budget is a plan or standard; at the end of the period, it serves as a control device to help the CFO measure its performance against the plan so that future performance may be improved.
With the aid of computer technology, budgeting can be used as an effective device for evaluation of what-if scenarios. Such scenarios allow management to move toward finding the best course of action among various alternatives through simulation.
If management does not like what it sees on the budgeted financial statements in terms of various financial ratios such as liquidity, activity (turnover), leverage, profit margin, and market value ratios, it can always alter the contemplated decision and planning set.
What are the types of budgets?
The budget is classified broadly into two categories:
1. Operating budget, reflecting the results of operating decisions
2. Financial budget, reflecting the financial decisions of the firm
What is an operating budget?
The operating budget consists of:
- Sales budget
- Production budget
- Direct materials budget
- Direct labor budget
- Factory overhead budget
- Selling and administrative expense budget
- Pro forma income statement
What does the financial budget contain?
The financial budget consists of:
- Cash budget
- Pro forma balance sheet
How do you prepare a budget?
The five major steps in preparing the budget are:
1. Prepare a sales forecast.
2. Determine expected production volume.
3. Estimate manufacturing costs and operating expenses.
4. Determine cash flow and other financial effects.
5. Formulate projected financial statements.
Exhibit 12.2 shows a simplified diagram of the various parts of the comprehensive (master) budget, the master plan of the company.
EXHIBIT 12.2 Comprehensive (Master) Budget
To illustrate how all these budgets are developed, we focus on a manufacturing company called the Delta Company, which produces and markets a single product. We assume that the company develops the master budget in contribution format for 2X12 on a quarterly basis. Throughout the illustration, we highlight the variable cost–fixed cost breakdown.
Sales Budget
The sales budget is the starting point in preparing the master budget, since estimated sales volume influences nearly all other items appearing throughout the master budget. The sales budget ordinarily indicates the quantity of each product expected to be sold. After sales volume has been estimated, the sales budget is constructed by multiplying the expected sales in units by the expected unit sales price. Generally, the sales budget includes a computation of expected cash collections from credit sales, which will be used later for cash budgeting. The sales budget is illustrated in Exhibit 12.3.
EXHIBIT 12.3 Delta Company Sales Budget for the Year Ending December 31, 2X12
Production Budget
After sales are budgeted, the production budget can be determined. The production budget sets forth the number of units expected to be manufactured to meet budgeted sales and inventory requirements. The expected volume of production is determined by subtracting the estimated inventory at the beginning of the period from the sum of the units expected to be sold and the desired inventory at the end of the period. The production budget is illustrated in Exhibit 12.4.
EXHIBIT 12.4 Delta Company Production Budget for the Year Ending December 31, 2X12
Direct Material Budget
When the level of production has been computed, a direct material budget should be constructed to show how much material will be required for production and how much material must be purchased to meet this production requirement. See Exhibit 12.5.
EXHIBIT 12.5 Delta Company Direct Material Budget for the Year Ending December 31, 2X12
The purchase will depend on both expected usage of materials and inventory levels. The formula for computation of the purchase is:
The direct material budget is usually accompanied by a computation of expected cash payments for materials.
Direct Labor Budget
The production requirements as set forth in the production budget also provide the starting point for preparing the direct labor budget. To compute direct labor requirements, expected production volume for each period is multiplied by the number of direct labor hours required to produce a single unit. The direct labor hours to meet production requirements is then multiplied by the direct labor cost per hour to obtain budgeted total direct labor costs. See Exhibit 12.6.
EXHIBIT 12.6 Delta Company Direct Labor Budget for the Year Ending December 31, 2X12
Factory Overhead Budget
The factory overhead budget should provide a schedule of all manufacturing costs other than direct materials and direct labor. Using the contribution approach to budgeting requires the development of a predetermined overhead rate for the variable portion of the factory overhead. In developing the cash budget, we must remember that depreciation does not entail a cash outlay and therefore must be deducted from the total factory overhead in computing cash disbursement for factory overhead. See Exhibit 12.7.
EXHIBIT 12.7 Delta Company Factory Overhead Budget for the Year Ending December 31, 2X12
To illustrate the factory overhead budget, we assume that
- Total factory overhead budgeted = $6,000 fixed (per quarter), plus $2 per hour of direct labor.
- Depreciation expenses are $3,250 each quarter.
- All overhead costs involving cash outlays are paid for in the quarter incurred.
Ending Inventory Budget
The desired ending inventory budget provides us with the information required for constructing budgeted financial statements. Specifically, it will help compute the cost of goods sold on the budgeted income statement. It will also give the dollar value of the ending materials and finished goods inventory to appear on the budgeted balance sheet. See Exhibit 12.8.
EXHIBIT 12.8 Delta Company Ending Inventory Budget for the Year Ending December 31, 2X12
Selling and Administrative Expense Budget
The selling and administrative expense budget lists the operating expenses involved in selling the products and in managing the business. In order to complete the budgeted income statement in contribution format, variable selling and administrative expense per unit must be computed. See Exhibit 12.9.
EXHIBIT 12.9 Delta Company Selling and Administrative Expense Budget for the Year Ending December 31, 2X12
Cash Budget
The cash budget is prepared for the purpose of cash planning and control. It presents the expected cash inflow and outflow for a designated time period. The cash budget helps management keep cash balances in reasonable relationship to its needs. It aids in avoiding unnecessary idle cash and possible cash shortages. The cash budget typically consists of four major sections:
1. The receipts section, which is the beginning cash balance, cash collections from customers, and other receipts
2. The disbursements section, which comprises all cash payments made by purpose
3. The cash surplus or deficit section, which simply shows the difference between the cash receipts section and the cash disbursements section
4. The financing section, which provides a detailed account of the borrowings and repayments expected during the budgeting period
To illustrate, we make these assumptions:
- The company desires to maintain a $5,000 minimum cash balance at the end of each quarter.
- All borrowing and repayment must be in multiples of $500 at an interest rate of 10 percent per annum.
- Interest is computed and paid as the principal is repaid.
- Borrowing takes place at the beginning of each quarter and repayment at the end of each quarter.
An example cash budget is provided in Exhibit 12.10.
EXHIBIT 12.10 Delta Company Cash Budget for the Year Ending December 31, 2X12
Budgeted Income Statement
The budgeted income statement summarizes the various component projections of revenue and expenses for the budgeting period. However, for control purposes, the budget can be divided into quarters or even months depending on the need. See Exhibit 12.11.
EXHIBIT 12.11 Delta Company Budgeted Income Statement for the Year Ending December 31, 2X12
Budgeted Balance Sheet
The budgeted balance sheet is developed by beginning with the balance sheet for the year just ended and adjusting it, using all the activities that are expected to take place during the budgeting period. The budgeted balance sheet must be prepared for several reasons:
- It could disclose some unfavorable financial conditions that management might want to avoid.
- It serves as a final check on the mathematical accuracy of all the other schedules.
- It helps management perform a variety of ratio calculations.
- It highlights future resources and obligations.
To illustrate, we show the balance sheet for the year 2X11 in Exhibit 12.12 and the budgeted balance sheet for the year 2X12 in Exhibit 12.13.
EXHIBIT 12.12 Delta Company Balance Sheet December 31, 2X11
EXHIBIT 12.13 Delta Company Budgeted Balance Sheet December 31, 2X12
SOME FINANCIAL CALCULATIONS
To see what kind of financial condition the Delta Company is expected to be in for the budgeting year, a sample of financial ratio calculations is in order. (Assume 2X11 after-tax net income was $15,000.)
| 2X11 | 2X12 | |
| Current ratio | $23,254/$6,200 | $51,993/$11,219 |
| (Current assets/Current liabilities) | = 3.75 | = 4.63 |
| Return on total assets | $15,000/$113,254 | $35,020/$153,293 |
| (Net income after taxes/Total assets) | = 13.24% | = 22.85% |
Sample calculations indicate that the Delta Company is expected to have better liquidity as measured by the current ratio. Overall performance will be improved as measured by return on total assets. This could be an indication that the contemplated plan may work out well.
BUDGETING SOFTWARE
Much user-oriented software has been specifically designed for corporate planners, treasurers, budget preparers, managerial accountants, CFOs, and business analysts.
Spreadsheet software and computer-based financial modeling software are widely utilized for budgeting and planning in an effort to speed up the budgeting process and allow nonfinancial managers to investigate the effect of changes in budget assumptions and scenarios.
USING AN ELECTRONIC SPREADSHEET TO DEVELOP A BUDGET PLAN
This chapter shows a detailed procedure for formulating a master budget. However, in practice, a shortcut approach to budgeting is quite common using spreadsheet technology. For an illustration of a shortcut method, we show how to develop a projected income statement using Excel.
JKS Furniture Co., Inc., expects the following for the coming 12 months, 2X12:
- Sales for 1st month = $60,000
- Cost of sales = 60% of sales
- Operating expenses = $10,000 plus 5% of sales
- Income taxes = 25% of net income
- Sales increase by 5% each month
Based on this information, we develop a spreadsheet for the pro forma income statement for the next 12 months and in total, which is given in Exhibit 12.14. Using a spreadsheet program such as Excel, financial managers will be able to evaluate various what-if scenarios.
EXHIBIT 12.14 JKS Furniture Co., Inc., Pro Forma Income Statement for the Period Ending December 31, 2X12
LATEST GENERATION OF BUDGETING AND PLANNING SOFTWARE AND E-BUDGETING
While research shows that two-thirds of U.S. companies still rely on Microsoft Excel for their budgeting process, some companies are evolving to a more technologically advanced approach. As more and more companies operate globally, the Internet is playing an ever-greater role in the budgeting process. E-budgeting is an increasingly popular Internet- or intranet-based budgeting tool that can streamline and speed up an organization’s budgeting process. The e in e-budgeting stands for both electronic and enterprise wide; employees throughout an organization, at all levels and around the globe, can submit and retrieve budget information electronically via the Internet. Budgeting software is utilized and made available on the Web (in a cloud computing environment), so that budget information electronically submitted from any location is in a consistent companywide format. Managers in organizations using e-budgeting have found that it greatly streamlines the entire budgeting process. In the past, these organizations have compiled their master budgets on hundreds of spreadsheets, which had to be collected and integrated by the corporate controller’s office. One result of this cumbersome approach was that a disproportionate amount of time was spent compiling and verifying data from multiple sources. With e-budgeting, both the submission of budget information and its compilation are accomplished electronically by the Web-based budgeting software. Thus, e-budgeting is just one more area where the Internet has transformed how the workplace operates in the era of e-business.
The new budgeting and planning (B&P) software represents a giant step forward for accountants. Finance managers can use these robust, Web-enabled programs to scan a wide range of data, radically speed up the planning process, and identify managers who have failed to submit budgets. More often known as active financial planning software, this software includes applications and the new level of functionality that combine budgeting, forecasting analytics, business intelligence, and collaboration. Exhibit 12.15 lists popular B&P software.
EXHIBIT 12.15 Popular Budgeting and Planning Software
The models help not only build a budget for profit planning but answer a variety of what-if scenarios. The resultant calculations provide a basis for choice among alternatives under conditions of uncertainty. Financial modeling can also be accomplished using spreadsheet programs such as Microsoft’s Excel and Quattro Pro.