# Commercial Credit Risk Analysis of the sensitivity of decision-making control

[Abstract] In the commercial credit risk decisions, the relevant parameters are usually assumed constant, this assumption is actually difficult to set up this paper, with Excel software were used to solve a single variable, simulation, table, program manager, form controls and computer simulations to explore the dynamic environment for the commercial credit risk decisions to provide a new solution path.

[Keywords] commercial credit, risk control, Excel

Commercial credit is the oldest form of credit, usually refers to the trading of goods between businesses, the physical form in order to provide direct credit from the economic sense, commercial credit can be seen as a quasi-money (quasi-money, in the circulation of commodities in the regulation of capital in time and space on the distribution of the imbalance, but also a commercial credit, "double-edged sword," which on the one hand to promote economic development, on the other hand there is a huge risk of traditional business credit decisions assume that the external economic parameters are static, such as the well-known American scholar Stanley B. Block, eds authoritative textbook <<Foundations of Financial Management>>. But this assumption is unrealistic, this lack of ideas for the above analysis, using Excel related software analysis tools, dynamic economic environment of the proposed commercial credit decision-making methods.

## First, the use of Single Variable sensitivity analysis tool for risk

Single Variable tool has launched based on the results because the anti-role, that is capable of handling assume (if necessary to get results, then what are the reasons (what kind of problem features. Application Single Variable tool is through the "Tools" menu. " Single Variable "command to achieve. the specific operation in the" changing cells "or" target cell "in reference to a cell type location or name," changing cells "in the cell references must be included in "target cell" into the formula, but also pay attention to "changing cells" can not contain formulas, when the Single Variable work is completed, Excel will display the results in the "Single Variable" dialog box, its operation interface shown in Figure 1.

Case 1: A small business annual credit of \$ 16 million, the current annual costs for the collection of accounts receivable of \$ 500 bad debt loss rate of 1.5%, average collection period of 30 days the company is considering easing collection action amounts to an annual reminder of the cost down to 2 accounts of \$ 200 after the change is expected to make the bad debt loss rate rose to 2.5%, the average collection period to 45 days, and make it the year rose to 162 500 credits element, if the firm's cost of capital is 16%, 75% variable costs, and asked whether the company should ease receivable collection action? If the answer is no, to make such changes to be implemented to reduce collection costs lower limit of the number ? If the receivable collection costs is difficult to compress, then they would speed up the loan recovery rate, time accounts receivable recovery period should be reduced to how many days?
First, define the problem-solving formula, in which the opportunity cost of accounts receivable is difficult, according to the formula: = opportunity cost of maintaining accounts receivable credit business capital × capital cost required rate, while maintaining the necessary funds business credit = (annual amount of credit ÷ 360 × × number of days the average variable cost collection rate.

Second, the relevant data input spreadsheet calculations, after which the credit program to change the opportunity cost of accounts receivable were 1 600, 2 437.50 yuan, consolidated earnings after credit costs were obtained for 32 500, 31,925 yuan, shows changes in credit policy is unreasonable.

Finally, adjust the credit policy, assuming the credit after the change of strategy to make possible, according to the meaning of the questions or reduce collection costs, or speed up the accounts receivable recovery speed. Using the Goal Seek tool for analysis, when other factors constant, close account the cost down to 1 625 yuan, or under the recycling rate of less than 34 days accounts receivable, changes in credit strategy is feasible.

## Second, the use of simulation, risk sensitivity analysis table

Simulation, the table is provided in Excel can be done simply with changes in operations analysis tools in the specific analysis, the use of simulation tools for computing the table "what if" analysis to observe changes in certain factors affect the results of the target. simulation, the table has the following features: First, the calculation provides a convenient means to define the process in a simple operation, the complete operation between different sets of data, and second, to provide a display and compare table style, in the simulation worksheet can display and compare the results of various numerical changes.

Case 2: A company is developing its credit policy, according to past experience, the annual income of 80 million credit, 80% variable costs, average collection period of 45 days, the cost is 500 million collection, bad debt losses rate of 4.5%, 16% cost of capital due to external economic environment may change, according to expert estimates, income and credit based on the original collection costs will be 15% -15% magnitude of changes, test the use of simulation computing the credit programs to analyze the table. Links to free download http://eng.hi138.com

According to the meaning of problems, assuming that revenues and collection costs credit two factors also change, to provide the simulations using Excel table, its operating results shown in Figure 2 and Table 1, Table 1 is the intercept part of the Excel data, especially in addition to the first unit, The first line in the table that the changes in credit income range, the first column indicates the change in collection costs range from Table 1 that under normal circumstances, the credit income is 620 million (that is, the first unit, when the credit and collection costs up to the lowest income , this time to obtain the minimum value 3.77 million yuan of credit proceeds; when the income credit and collection costs up to a minimum, the time to obtain the maximum value 8.63 million yuan of credit proceeds.

## Third, the program manager for risk using sensitivity analysis

Excel software program manager is to provide a powerful financial analysis tool that can manage multiple programs, each program can create multiple assumptions, and accordingly the results to generate a variety of forms to save the program summary report, program by reading the summary report, one can clearly see the effects of variables on the target situation, the details shown in Figure 3 and Figure 4.

Case 3: A company projected annual net income for the 3600 credit million, 60% variable costs, cost of capital rate (or interest rate of 10% of the portfolio, assuming corporate collection policy remains unchanged, the total fixed costs the same. The company has prepared five options for credit terms: the options estimated credit level, the percentage of bad debts and collection costs and other relevant data in Table 2.

Require the use of the program manager for credit decisions.
Figure 4 shows the program summary, changes in factor analysis are: Annual amount of credit, accounts receivable turnover period, bad debt loss rate, collection costs, through the program manager for the relevant set, you can directly get the final credit benefits, according to the operation result, Option C is the best option.

## Fourth, use the toolbar's "form" risk sensitivity analysis

Excel provides a variety of graphical control tools, the activation of these control icons, such as the window's toolbar, establish links between related elements, drag these controls, you can visually reflect changes in relevant factors and target parameters.

Case 4: A company is developing its credit policy, if the annual income of 60 million credit to 10 000 million, 75% variable costs, average collection period of 30 to 60 days, the collection cost is 200 million 300 million, the cost of capital rate of 8% to 12%, 4.5% bad debt losses, try to use the control tool to analyze the credit program.

## Fifth, the use of Monte Carlo sensitivity analysis of stochastic simulation for risk

Monte Carlo (Monte Carlo stochastic simulation, unlike the deterministic numerical method is used to solve engineering and economic problems in the non-deterministic, the method by thousands of experiments to cover the corresponding probability distribution space to obtain a certain probability of different outcomes and frequency of distribution, through the analysis of a large number of sample values, the accuracy of the results must be met.

Case 5: Case 4 on the basis of discussion, assume that the credit income, average collection period, collection costs, the cost of capital rate in accordance with the uniform distribution on the corresponding interval, the other parameters remain unchanged after the earnings test analysis of credit likely probability distribution.

The use of Excel software provides statistical functions, changes in the use of the four parameters RAND set, experiments were conducted in this simulation 10,000 times, through statistical COUNTIF to get the results shown in Table 4, we can see from the experimental data, credit gains appear In 1 000 ~ 1 600, more likely, but less than 1000 or greater than 1 600 likely relatively small.

## VI Conclusion

Commercial credit decisions affecting many factors, both macroeconomic factors, such as the national economic situation, the social credit system, there are micro factors, such as corporate marketing and inventory strategy, and Excel provides a Single Variable, simulation, table, program manager, form controls, such as stochastic simulation tool for overcoming the shortcomings of traditional analysis, response to dynamic business environment is very effective credit decisions, including the Goal Seek tool for objective control of credit, program manager for the credit program optimization, stochastic simulation for credit risk assessment, its scope of application are summarized as shown in Table 5.

## Main References

[1] Zhang Ruijun. Computerized financial management [M]. Beijing: China Renmin University Press, 2009.

[2] Hong-Hai Wang, Zhao Yanlei. Optimal cash holdings of the dynamic modeling and optimization [J]. China's management of information technology, 2008 (7:42-44. Links to free download http://eng.hi138.com

Links to free download http://eng.
hi138.com