What Are the Best Tips for Budgeting and Forecasting?
The financial forecasting method refers to a method of scientifically predicting and calculating the future financial activities and financial results of an enterprise based on historical data of financial activities, taking into account realistic requirements and conditions.
Financial forecasting method
(I) Problems in the selection of prediction methods [2]
There are many financial forecasts, including sales revenue forecast, capital demand forecast, and profit forecast. Here, the forecast of capital demand is taken as an example for illustration. [3]
The forecast of capital demand is the quantity basis for raising funds and the basis of financial planning. It must be scientifically and reasonably forecasted. The quantitative methods commonly used for forecasting include the sales percentage method and the linear regression method. These two methods can also be used in combination.
Generally speaking, financial forecasts of capital requirements are often made by means of financial statement forecasts. Therefore, the basic statement-income statement and balance sheet forecast is combined here with the sales percentage method.
1. Sales percentage method
Finance is at the center of a company's planning activities. Simulated financial statements are the most widely used type of plans in company planning. They do not focus on a particular aspect of the company like production planning and personnel planning. Simulated financial statements are a view of the company's financial statements at the end of the forecast period. What to expect. These estimates may be highly concentrated and extremely detailed business plans and budgets, or they may be crude. However, the main purpose of the simulated financial statement forecasting method is to estimate the future external funding requirements.
To know what the company's future operating results and financial situation will be, first of all, make a sales forecast, then estimate the costs and expenses, and estimate the trend of the company's assets and equity. Prediction is always quite difficult, especially for the future. A simple and effective method is to link a large amount of data in the balance sheet with future sales. This is the sales percentage method. The basis is that all variable costs and most current assets have a tendency to change with sales. Although this does not apply to all items of the company's financial statements, it provides a simple and logical way to solve the problem.
(1) Basic steps for sales percentage forecast
The first step is to review the historical data to determine which financial statement items change proportionally to sales. This enables forecasters to determine which items are a good estimate of sales ratios and which must be predicted based on other information.
The second step, according to the trend or need to estimate the proportion of each item that changes proportionally to sales income, such as measuring the future cash balance will be 6% of future sales.
The third step is to forecast sales. If the simulated financial statements are completed based on predicted sales, it is best to perform a sensitivity test on reasonable changes in sales forecasts.
The fourth step is to use the latest estimated sales to infer historical patterns to estimate the amount of a single financial statement item. For example, if the historical inventory is 20% of sales, and the estimated sales in the next year are 10 million yuan, it can be inferred that the inventory in the next year will be 2 million yuan. Other sales-related items can also be calculated based on this.
The fifth step is to estimate the estimated external capital requirements based on the calculations of the simulated balance sheet and income statement items.
(2) Application of sales percentage method forecast
[Example 1] ABC company is a retailer of electronic equipment and a long-time customer of a bank. The company maintains an average of about 300,000 yuan in deposits and has been using a short-term repayable loan of 40,000 yuan for three years. At the end of 2006, the company's financial manager requested the bank to increase the short-term loan to 500,000 yuan in 2007. The reason is: Although the company's sales have been growing, accounts receivables are also increasing, cash balances have been reduced, and suppliers are under pressure to collect money. But he just felt that he should borrow the money. Banks would not approve such large short-term loans without detailed financial forecasts. So the two parties made a forecast of the loan amount based on the company's previous financial statements and sales forecast data. Table 1 is ABC's income statement and balance sheet from 2003 to 2006. Please estimate the company's sales revenue in 2007.
Income statement |
| 2003 | 2004 | 2005 | 2006 |
First, the main business income | 1119 | 1376 | 1610 | 2062 |
Less: cost of main business | 940 | 1170 | 1368 | 1773 |
Second, the sales profit | 179 | 206 | 242 | 289 |
Less: administrative and operating expenses | 102 | 124 | 161 | 227 |
Financial expenses | 1O | 11 | 12 | 9 |
3. Operating profit | 67 | 71 | 69 | 53 |
Less: Income tax | 30 | 32 | 31 | twenty four |
Fourth, net profit | 37 | 39 | 38 | 29 |
Brief balance sheet |
assets |
|
|
|
|
Current assets |
|
|
|
|
Cash and short-term investments | 67 | 55 | 64 | 41 |
accounts receivable | 144 | 189 | 219 | 298 |
stock | 117 | 143 | 198 | 232 |
Prepaid expenses | 1 | 1 | 1 | 2 |
Subtotal of current assets | 329 | 388 | 482 | 573 |
net value of fixed assets | 13 | 12 | 29 | 29 |
Total assets | 342 | 400 | 511 | 602 |
Liabilities and owner's equity |
|
|
|
|
Current liabilities |
|
|
|
|
short-term loan | 4 | 4 | 4 | 4 |
accounts payable | 102 | 144 | 242 | 321 |
Long-term liabilities due | 6 | 5 | 5 | 10 |
Payable | 0 | 1 | 1 | 2 |
Subtotal of current liabilities | 112 | 154 | 252 | 337 |
Long-term debt | 96 | 92 | 86 | 77 |
Paid-in capital | 30 | 30 | 30 | 30 |
retained earnings | 104 | 124 | 143 | 158 |
Total debt and owner's equity | 342 | 400 | 511 | 602 |
(1) Analysis: The first step is to look at the company's financial statements from 2003 to 2006 (see Table 2) and find a fixed pattern. The results of the ratio analysis are shown in Table 2. The company s income statement items include main business costs, administrative expenses and operating expenses that vary in proportion to sales, and cash and short-term investments, accounts receivable, and inventory The "Accounts Payable" item changes in proportion to sales.
Table 2 Percentage of selected financial statement items and sales of ABC company from 2002 to 2005 (%)
years | 1998 | 1999 | 2000 | 2001 * | 2002 + |
Increase in sales | | twenty three | 17.5 | 28 | 25 |
Sales percentage |
|
|
|
|
|
Cost of main business | 84 | 85 | 85 | 86 | 86 |
Management and operating expenses | 9 | 9 | 10 | 11 | 12 |
Cash and short-term investments | 6 | 4 | 4 | 2 | 5 |
accounts receivable | 13 | 14 | 14 | 14 | 15 |
stock | 10 | 10 | 12 | 11 | 10 |
accounts payable | 9 | 10 | 15 | 16 | 14 |
Income tax / operating profit | O. 45 | 0.45 | 0.45 | 0.45 | 0.45 |
Dividend / Net profit | O. 50 | O. 50 | 0.50 | O. 50 | O. 50 |
* Estimate; + forecast.
The second step is to estimate the percentage of items that change in proportion to sales. The estimated results are shown in Table 2. According to the trend, the possible percentages of each item are calculated in the future.
It is easy to see from Table 2 that the liquidity of funds is decreasing and the payables for purchases are increasing; cash and short-term investments have decreased from 6% of sales to 2%, and accounts payable have increased from 9% to 16%. The payable period is the amount of payables / cost of sales per day. The payables have increased from 39 days [9% / (84% / 365)] to 68 days [16% / (86% / 365)]. Another trend is that while the cost of sales and general administrative expenses have risen, profits have not kept pace with sales.
The last column in Table 2 is the forecast reached by ABC's financial manager and the bank. Based on experience in recent years (qualitative forecasting and trend analysis can also be used), sales in 2007 are expected to increase by 25% over 2006. Due to the company's continued rise in management expenses, financial managers believe that cash and securities will rise to at least 5% of sales [or equivalent to 18 days of sales revenue (5% x 365 days)]. ABC believes that given a payables turnover of 59 days [14% / (86% × 365), payables / cost of sales per day], payables will fall to no more than 14 sales %. The tax rate and the payment of dividends are expected to remain unchanged.
The third step is to forecast sales. There are many methods for forecasting sales. Here according to the trend and the actual situation of the company, its sales in the future will be increased by 25% over the previous year. Since the estimated sales in 2006 are 20.62 million yuan, the sales in 2007 It will be 25.78 million yuan.
The fourth step is to first calculate the amount of the items in each table that are proportional to the sales change according to the sales amount, which can be performed by the Excel spreadsheet system. The results are shown in Table 3.
Table 3 ABC Company's simulated financial forecast on December 31, 2007 Unit: 10,000 yuan
project | A | B | C | D |
1 |
|
|
|
|
2 | years | Actual 2006 | 2007 | 2008 |
3 | Net sales | 2062 |
|
|
4 | Net sales growth |
| 25% |
|
5 | Cost of main operations / net sales |
| 86% |
|
6 | (Administrative expenses + operating expenses) Net sales |
| 12% |
|
7 | Long-term liabilities | 76 | 66 |
|
8 | Maturity portion of long-term liabilities | 10 | 10 |
|
9 | interest rate |
| 5% |
|
10 | tax rate |
| 45% |
|
1l | Dividend / profit after tax |
| 50% |
|
12 | Net assets / sales in proportion to sales |
| 30% |
|
13 | Prepaid expenses |
| 2 |
|
14 | Net fixed assets | 29 | 28 |
|
15 | Current liabilities / net sales proportional to sales |
| 14% |
|
16 | Payable |
| 2 |
|
17 | Owners' equity | 188 |
|
|
18 | Income statement |
|
|
|
19 |
| 2007 Formula | 2007 forecast | 2008 forecast |
20 | Net sales | = B3 + B3 + C4 | 2578 |
|
twenty one | Cost of main business | = C5 * C20 | 2217 |
|
twenty two | Profit from principal operations | = C20-C21 | 361 |
|
twenty three | Management and operating expenses | = C6 * C20 | 309 |
|
twenty four | Financial expenses | = C9 * (B7 + B8 + C40) | 13 |
|
25 | Total profit | = C22-C33-C24 | 39 |
|
26 | Income tax | = C10 * C25 | 18 |
|
27 | Net profit | = C25-C26 | twenty one |
|
28 | Pay dividends | = C11 * C27 | 11 |
|
29 | New retained earnings | = C27-C28 | 11 |
|
30 | Balance sheet |
|
|
|
3l | Current assets | = C12 * C20 + C13 | 775 |
|
32 | net value of fixed assets | = C14 * C27 | 28 |
|
33 | Total assets | = C31 + C32 | 803 |
|
34 |
|
|
|
|
35 | Current liabilities | = C15 * C20 + C16 + C8 | 373 |
|
36 | Long-term liabilities | = C7 | 66 |
|
37 | Owners' equity | = B17 + C29 | 199 |
|
38 | Total Liabilities and Owner's Equity | = C35 + C36 + C37 | 638 |
|
39 |
|
|
|
|
40 | External funding requirements | = C33-C38 | 166 |
|
Note: The upper part of Table 3 is the hypothetical number, which is given according to your own needs or known data. The lower part of the table is the predicted value. To calculate the predicted value, the key is to know the calculated values. The calculation formula is defined in column B. In fact, if you enter this formula in a spreadsheet, the value in column C will usually come out. When calculating the external capital demand, the external capital demand is a function of interest, and interest itself is a function of the external capital demand, so there will be an infinite loop in the table. This can be solved by using the circular calculation tool in the excel table. The basic operation is: move the cursor to the formula column for calculating the interest expense, press excel's "tool", "options", "recalculation" and click "manual recalculation" Box, click the "Repeat operation" box, and set the "Maximum Iterations" to the number of times required. If it is 100 times here, press F9 on the keyboard again, and the computer will calculate the interest expenses and the amount of external funds required. Value, if you modify which data in the future, you can get new external financing requirements by pressing the F9 key again.
Then, based on other information, predict the amount of other items in Table 3 that have no fixed proportion to sales. The amount of unpaid expenses in ABC is the same as the salary payable. It is a small amount. The approximate estimate (such as the occurrence of repair costs, the use of low-value consumables, etc.) is 20,000 yuan, and the payable salary is 20,000 yuan; The company has approved a capital budget of 40,000 yuan for 2007, and because the depreciation fee for the current year is 50,000 yuan, the net fixed assets in 2007 will be reduced by 10,000 yuan to 280,000 yuan. (29 + 4-5); As reloaning is assumed, short-term borrowing is assumed to be zero. "Long-term liabilities due within one year" only refer to the principal repayable due in 2007, which can be known from the loan contract as 100,000 yuan. Such liabilities are current liabilities.
The fifth step is to estimate the estimated external capital requirements based on the projected profit statement and balance sheet.
According to calculations, due to the increase in sales, total assets in 2007 will reach 8.03 million yuan, an increase of 2.01 million yuan over the previous year. By increasing accounts payable and retained earnings, the total equity in 2007 without external funding reached 638 At this time, there is still a gap of 1.66 million yuan in funding that needs to be raised from outside. Because the company has no other way to raise funds, it can only raise this part of the fund through borrowing. Obviously, it is too different from the original expectation of 500,000 yuan in loans. Because the company's inventory and receivables are large, it can be used as a guarantee for loans to a certain extent, but it is unlikely that banks will agree to a loan amount of 1.66 million yuan. The above simulated financial statements show only the financial aspects of the company's business plan, only the preliminary financial plan. If the company is to prepare a financial plan, it must review the previous forecast, such as considering whether to modify the production and operation plan. The financial plan can only be prepared with sufficient estimates of various situations.
(2) Adjust the prediction data. Obviously, to meet the needs of both parties, the company's business plan and other projects need to be readjusted.
For example, if ABC Company wants to reduce its dependence on external funds, it may test the impact of the following changes in the production and operation plan on the company's external capital requirements:
Slow down liquidity growth so that cash and securities have changed from 5% of sales to 4%.
Step up the collection of accounts receivable so that accounts receivable becomes 12% of sales instead of 14%.
Reduce inventory by 1%, and reduce it from 10% of sales to 9.5%.
The credit purchase of accounts payable was required to be slightly improved, so that accounts payable increased from 14% to 15% of sales.
The above results reduce assets and increase liabilities. The combination of the two will reduce ABC's external funding requirements by exactly 1.16 million yuan [(4.5% of assets reduced + 1% of liabilities increased) × sales = 1.16 million].
Although each of the above measures will affect ABC's need for external funds, each measure has the disadvantage of offsetting it. For example, if liquidity growth slows down, it will face many poor payments; accounts receivable policy The toughness of sales will affect sales and lose customers; the delay in accounts payable will attract pressure from suppliers, and even stricter collection policies. Therefore, the revised plan is not necessarily better than the original plan, but in any case, the simulated financial statements have an irreplaceable role, because the two-phase alternative business strategy can be evaluated.
(3) Sensitivity analysis. The so-called sensitivity analysis refers to the question of "what if ...", which includes regularly changing certain assumptions on which the simulated financial statements are based to observe changes in forecast values. This analysis method has at least two uses:
First, it provides information on the range of possible outcomes. For example, a sensitivity analysis may reveal that, depending on the amount of sales that can be achieved in the future, the company's demand for external funds is roughly between 1.6 million and 1.9 million yuan. This is equivalent to telling managers that their plan is best to be flexible enough to add an additional new debt of $ 400,000 with future development.
Second, sensitivity analysis encourages management to retain differing opinions. It enables managers to determine which assumptions have the greatest impact on predictions, and which ones are secondary, which prompts managers to focus their efforts to collect data and predictions on those key assumptions. Later, during the implementation of the financial plan, the same information enables managers to focus on those key forces that are conducive to success.
In addition, in order to make the analysis results consistent with reality, the sensitivity analysis must be revised. Scenario analysis needs to be further conducted, mainly to consider the impact of one hypothesis on another hypothesis. For example, assuming that sales are lower than expected, the fact may be that inventory will increase first, and as sales fall below expected levels, in order to maintain sales, the company will cut prices and profit margins will decline.
Failure to include these complementary effects will result in underestimation of external financing.
2. Sales Percentage Forecast Summary
If financing needs are determined based on total sales, the following steps can be taken:
(1) Determine sales percentage. Find out the relationship between each item and sales in the previous year. Generally speaking, there is a fixed proportional relationship between current assets such as cash and receivables and sales, and current liabilities also often have a fixed proportional relationship. Others often do not have a fixed proportional relationship, that is, there is a certain percentage relationship between sales. In order to find the relationship between each project in the forecast year and the forecast annual sales.
(2) Assets and liabilities under projected sales.
(3) Expected increase in retained earnings.
(4) Calculate external financing needs. Its calculation formula is
External financing needs = estimated total assets-estimated total liabilities-estimated shareholder equity
If external financing needs are determined based on sales increases:
Financing needs = increase in assets-increase in liabilities-increase in retained earnings = (percentage of asset sales × new sales-(percentage of debt sales × new sales)-(planned net profit of sales × sales) × (1- dividend payment rate )]
New sales = sales growth rate x base period sales
Because the amount of external funds predicted through the simulated financial statements is determined according to the company's production and operation plan, and the company's sales are not fully cashed, the financial statement simulated prediction has limitations. In fact, the company should also based on the cash income and expenditure Make a budget for money. This budget can even be subdivided into monthly basis to be operable.
