Commercial Credit is the oldest form of credit, usually refers to commodity trading between businesses, the physical form in order to provide direct credit. From an economic sense, commercial credit can be seen as a quasi-currency (quasi-money), in regulating the circulation of commodities funds distributed in time and space imbalance. But commercial credit is a "double-edged sword", which on the one hand to promote economic development, on the other hand there is a huge risk. Traditional commercial credit decision is assumed that the external economic parameters are static, such as the famous American scholar Stanley B. Block et compiled the authoritative textbook "Foundations of Financial Management". But this assumption is unrealistic, this lack of ideas for the above analysis, correlation analysis with Excel software tools that provide a dynamic economic environment of the commercial credit decision making.
商業(yè)信用是最古老的信用形式，通常是指工商企業(yè)之間進(jìn)行商品交易時(shí)，以實(shí)物形態(tài)提供的直接信用。從經(jīng)濟(jì)意義上來看，商業(yè)信用可以看作是一種準(zhǔn)貨幣（ｑｕａｓｉ－ｍｏｎｅｙ），在商品流通中調(diào)節(jié)資金在時(shí)間與空間上分布的失衡。但是商業(yè)信用也是一把“雙刃劍”，它一方面推動(dòng)經(jīng)濟(jì)的發(fā)展，另一方面也存在巨大的風(fēng)險(xiǎn)。傳統(tǒng)的商業(yè)信用決策是假設(shè)外部經(jīng)濟(jì)參數(shù)是靜止的，如美國著名學(xué)者Ｓｔａｎｌｅｙ Ｂ． Ｂｌｏｃｋ等編著的權(quán)威教材《Ｆｏｕｎｄａｔｉｏｎｓ ｏｆ Ｆｉｎａｎｃｉａｌ Ｍａｎａｇｅｍｅｎｔ》。但這樣的假設(shè)前提是不現(xiàn)實(shí)的，本文針對以上分析思路的不足，利用Ｅxcel軟件的相關(guān)分析工具，提出動(dòng)態(tài)經(jīng)濟(jì)環(huán)境下的商業(yè)信用決策方法。

   
First, the use of single-variable risk sensitivity analysis tools for solving
 一、利用單變量求解工具進(jìn)行風(fēng)險(xiǎn)敏感性分析

   
Single Variable tool has launched based on the results against the role of reason, that is capable of handling assumptions (if) needed to get results, then what is the reason (what) the function of such problems. Univariate solving tool is through the "Tools" menu on the "Single Variable" command to achieve. Specific operation process can be in the "changing cells" or "target cell" enter the cell reference location or name, "changing cells" in the cell references must be included in the "target cell" formula among , but also pay attention to "changing cells" can not contain formulas, when the work is completed Single Variable, Excel will display the results in the "Single Variable" dialog box.
 單變量求解工具具有根據(jù)結(jié)果反推出原因的作用，即具有處理假設(shè)（ｉｆ）需要得到的結(jié)果，那么原因是什么（ｗｈａｔ）這類問題的功能。應(yīng)用單變量求解工具是通過“工具”菜單上的“單變量求解”命令來實(shí)現(xiàn)的。具體操作過程中可以在“可變單元格”或“目標(biāo)單元格”中輸入單元格的引用位置或名稱，“可變單元格”中的單元引用必須包含在“目標(biāo)單元格”的公式之中，同時(shí)還要注意“可變單元格”不能包含公式，當(dāng)單變量求解工作完成后，Ｅxcel會(huì)將結(jié)果顯示在“單變量求解”.

   
Case 1: A small business for the year amounted to 160,000 yuan credit, the current annual cost of $ 3,500 receivable collection million, bad debt loss rate of 1.5%, with an average collection period of 30 days. The company is considering easing accrual of action in order to urge the annual accounts expense decreased to 2,200 yuan, after the change is expected to cause the bad debt loss rate rose to 2.5%, the average collection period to 45 days, and make annual credits rose to 162,500 yuan, if the company cost of capital of 16%, 75% variable costs, and asked whether the company should ease receivable collection actions? If the answer is no, to make these changes to be realized, collection fees reduce the lower limit of how many? If the costs of collection accounts difficult to compress, then they would expedite loan recovery rate, payback time should be reduced to accounts receivable how many days? http://www.400190.com/kjzy/

   
First, define problem solving formula, in which debtors opportunity cost is difficult, according to the formula are: accounts receivable credit business opportunity cost = maintain the funds needed × cost of capital rate, while maintaining business credit funds needed = (annual credit sales ÷ 360) × average collection days × variable cost ratio.

   
Secondly, the associated data entry spreadsheet calculations, including credit programs before and after the change in the opportunity cost of receivables were 1 600, 2 437.50 yuan, the consolidated credit costs calculated revenue was 32,500 yuan, 31,925 yuan, described the credit policy changes are unreasonable.

   
Finally, the credit policy to be adjusted. Assuming the credit policy to make after the change feasible, according to the meaning of the questions either to reduce collection costs, either accelerate receivable recovery speed. Univariate analysis of solving tool, when other factors constant, collection fees dropped to below $ 1625 or receivable recovery slower than 34 days, the credit policy change is feasible.

   
Second, the use of analog computing risk sensitivity analysis table

   
Excel data table is provided simply change operation can be completed factor analysis tools. In the specific analysis, the use of analog computing table tools "what if" analysis to observe changes in certain factors impact on the target results. Data table has the following functions: One is to provide a convenient means of calculation, the operational definition of the process in a simple, complete operation between different sets of data; two is to provide a tabular display and compare the simulation worksheet can be displayed simultaneously and compare several different values ??change the results.

   
Case 2: A company is developing its credit policy, according to past experience, the annual credit sales of 8,000 million yuan, the variable cost rate of 80%, the average collection period of 45 days, collection fees of 500 million yuan, bad debts 4.5%, the cost of capital was 16%. As the external economic environment may change, according to expert estimates credit sales and collection fees will be based on the original 15% to 15% of the variation margin, try using simulated data table for analysis of credit schemes.

   
According to the meaning of problems, assuming that credit sales and collection fees on two factors change simultaneously, provided by Excel data table, the operation results shown in Figure 2 and Table 1, Table 1 is the interception of Excel data section, except the first one extra unit , the first row in the table represents the change interval credit sales, the first column represents the change in the cost of collection interval. Table 1 shows that under normal circumstances, the credit income 6,200,000 yuan (ie one unit), while the lowest-income credit and collection charges up, this time to obtain the minimum value of 3.77 million yuan of credit income; when credit and received the highest income account lowest rates, the time to obtain the maximum value of 8.63 million yuan of credit income.

   
Third, the program manager for the use of risk sensitivity analysis

   
Excel software program management is to provide for the financial staff a powerful analysis tool that can manage multiple programs, each program can create multiple assumptions, and results in a variety of programs which generate a summary report is saved, By reading program summary report, you can clearly see the impact of factors that change the situation on the target, the details shown in Figure 3 and Figure 4.

   
Case 3: a company projected annual net income for the 3600 credit yuan, variable cost was 60%, the cost of capital rate (or interest rate securities) was 10%. Assuming corporate collection policy remains unchanged, total fixed costs remain unchanged. The company prepared a five credit conditions alternatives: various alternatives estimated credit level, the percentage of bad debts and collection fees and other relevant data in Table 2.

   
Require the use of program manager for credit decisions.

   
Seen from Figure 4, program summaries, analysis variables: annual credit sales, accounts receivable turnover, bad debt loss rate, collection fees, program manager through the relevant settings, you can directly get the final credit income, according to the result of the operation, Option C is the best option.

   
Fourth, use the tool bar of the "form" of risk sensitivity analysis

   
Excel provides a variety of graphical control tool, activate these control icons, such as the window's toolbar, and establish links between related units, drag these controls, you can directly reflect changes in underlying factors, as well as the impact on the target parameters.

   
Case 4: A company is developing its credit policy, credit if the annual income of 6,000 million to 10,000 million variable cost rate of 75%, the average collection period of 30 to 60 days, collection fees are 2,000,000 ~ 3 million yuan, the cost of capital rate of 8% to 12%, bad debt loss rate of 4.5%, the test tool using the controls on the credit program for analysis.

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

   
Monte Carlo (Monte Carlo) stochastic simulation, unlike deterministic numerical method is used to solve engineering and economic problems in the non-deterministic, the method through thousands of experiments, can cover the corresponding probability distribution space, thereby obtaining different results under certain probability and frequency distribution, a large number of sample values ??by the analysis of the results obtained to meet certain accuracy.

   
Case 5: In the case of four on the basis of discussion, assume that one's credit sales, average collection period, collection fees, the cost of capital rate in the corresponding interval in accordance with the uniform distribution of the remaining parameters remain unchanged after the earnings test analysis of credit possible probability distributions.

   
Use of Excel software provides statistical functions, use RAND movements on four parameters can be set in this simulation were carried out 10,000 times, through COUNTIF statistics, obtained the results shown in Table 4, from the experimental data shows that credit gains appear at 1 000 to 1 600, more likely, and less than or greater than 1 600 1 000 relatively unlikely.

   
VI Conclusion

   
Commercial credit decisions affecting many factors, both macroeconomic factors, such as the country's economic situation, social credit system, there are micro factors, such as corporate marketing and inventory strategies, and Excel provides Single Variable, data tables, program manager, form controls, stochastic simulation and other tools for overcoming the shortcomings of traditional analysis, conform to the dynamic environment of the commercial credit decisions are very effective tools for solving single variable which applies to credit target control, program manager for credit program optimization, stochastic modeling for credit risk assessment.