时间：2022-06-12 02:43:14
Abstract. Excel Solver is a good tool that can solve the smaller constrained and unconstrained optimization problems. Use Solver to build the model framework， input parameters， solve， generate operational reports and sensitivity analysis re-ports. The case results show， the Solver in solving constrained optimization prob-lems of financial management is more simple and accurate than the Excel XD modeling method.
Key words： Excel， Solver， Linear programming problems
1 Introduction
The constrained and unconstrained optimization problems are often encountered in Financial Management. Such as the investment utility maximum optimal stock portfolio coefficient determination problem， The total cost of the product produc-tion， storage， sales minimization problem on the optimal production， and so on. Because using the simplex method solve the above problems manually require quite a complicated iterative calculation， Excel Solver has become the most convenient tool to solve the constrained and unconstrained optimization problems within 200 decision variables [1]. Because of the limited space， The following describes the Solver application in a constrained optimization problem （including linear programming problem and nonlinear programming problem）.
2 The solving steps
2.1 Create a model framework
Create simple mathematical model and input the pre-parameters in the worksheet.
2.2 Setting the parameters
Through the "Solver" command of “Tool” menu， set the target cell， maximize val-ue， minimize value or specific value， variable cells， constraint cells， constraints， whether adopt the linear model or not.
2.3 Solving
After Solver of running， the target cell shows the original maximum， minimum， or a specified value， the other variable cells show the decision optimal value， and ge-nerates the results reports of operations， the sensitivity reports and the limit value reports [2].
3 Solving linear programming problems
Example： A company mainly product and sale A products and B products in Dongguan，the information of A products ：the unit price is P1 = 11 yuan / piece， the unit variable cost is v1 = 7 yuan / piece， the per hour of labour is x1 = 46 hours / pieces， the per machine hour is y1 = 72 hours / piece； the information of B prod-ucts ： the unit price is P2 = 10 yuan / piece， the unit variable cost is v2 = 5 yuan / piece， the per hour of labour is x2 = 65 hours / pieces， the per machine hour is y2 = 53 hours / piece； the company can provide the total of labor hours is X = 350000 hours per month， and the total of machine hours available is Y=560000 hours per month in the company.Because of the production capacity and warehouse space Limitation， the total sales of two products can not exceed S = 5600 pieces monthly. the sales and production of A， B two products are equal monthly.Please calculate respectively two products monthly production of profit maximization.
Solution：
3.1 Analysis
The total profit of the product =∑product sales income-∑the total product cost， Due to the fixed costs of A， B both products has nothing to do with their production in this case， the impact of fixed costs may not consider on the mixed problem，in this condition，the total product profit =Σ（the production of each product * marginal contribution per unit of each product ） [3].If the yield of A product expressed by q1 and the yield of B product expressed by q2， then the total profit formula： M = q1 （p1-v1） + q2 （p2-v2）.And its Linear model can be expressed：
max M = q1（p1-v1） + q2（p2-v2） （1）
s.t. q1x1 + q2x2 ≤ X
q1y1 + q2y2 ≤ Y
q1s1 + q2s2 ≤ S
q1s1 + q2s2 ≤ S
（Among them s1 = s2 = 1）.
3.2 Creating an Excel model framework
According to the subject meaning，the above-mentioned content will be input to the worksheet， as shown in Fig. 1.Input the formula "= C3 * $C$2 + B3 * $B$2" to D3，drag the fill handle，copy the formula to D4， D5 cells， Obtain two products labor hours， machine hours and sales ability required consumption monthly. Input the formula " = B6 - B7" to B8， and copy it to C8， calculate the product unit contribution margin； input the formula " = C8 * $C$2 + B8 * $B$2 " to D8， calculate the monthly total profit. input q1， q2 two arbitrary value （ such as 700，3800 ） of A， B two products monthly sales to B2， C2，in this case， the total profit is 21，800 yuan， the usage of labor hours， machine hours， sales ability are lower than the capacity available， that is to say， the capacity of of the enterprise can not be made full use of.
Fig. 1 Modeling framework
3.3 Model framework analysis
Fig. 1 solve the problem is that under the conditions of the resources demand （D3，D4 and D5） is less than or equal to the available volume （E3， E4 and E5） ， and B2， C2≥ 0， find the specific value of B2， C2 for the total profit D8 maximizing.
3.4 Solving
Select [tools] / [Solver] command， open the" solver parameters" dialog box， the $D$8 is set to a target cell；select the " maximum value " radio button； input " $B$2： $C$2" cells area in the variable area；click "add" button in the "bound" col-umn， pop-up "add constraint" dialog box，locate the cursor in the" cell reference position"， select the range of $D$3：$D$5 with the mouse， select "
3.5 Saving the model
Click the "options" button in the " solver parameters" dialog box， open the "solver options" dialog box，click the" save model" button，select the area need to save in the popup dialog box，for exmaple， " $D$10：$D$13"， click "OK" button， return the "solver parameters" dialog box， click the "Solve" button.In Fig. 2，D10 display the maximize value of target cell，the formula is "= MAX （$D$8） "；D11 show the number of variable cells， the formula is" =COUNT（$B$2：$C$2） "；D12 return to whether D3： D5 values are respectively less than or equal to the value of the cor-responding cell in E3： E5 or not， the formula is "=$D$3：$D$5
Fig. 2 Solver Modeling
3.6 Sensitivity analysis
After having finded the optimal solution of linear programming problem， conduct a sensitivity analysis， namely the degree of influence that each business parameter change on the optimal solution.These parameters include： first， the coefficient of the objective function，such as A， B product marginal contribution in cases above；second， the various constraints constant in the right side，as mentioned above，labor hours， machine hours， the limit of sales ability （i.e. resource gross）； third， the coefficient of the left expression of various constraint [5]. such as the unit labor hours and the unit machine hours of A， B products in cases above.When the unit variable cost is constant， product unit price changes is the unit contribution marg changes. Under the conditions of A， B product unit price unchanged， the resource gross changes have influence on the maximum profit M max as shown in Fig. 3：
Fig. 3 Sensitivity analysis
As seen from table 3，When the total labor hours increase or decrease 1000 hours， the maximum profit increase or decrease 52.63 yuan（i.e. the shadow price of labor hours is 0.05263 yuan / hour）； When the total machine hours increase or decrease 1000 hours， the maximum profit is unchanged （i.e. the shadow price of machine hours is 0 yuan / hour）； When the sales ability increase or decrease 100 pieces， the maximum profit increase or decrease 15.79 yuan（i.e. the shadow price of sales ability is 0.1579 yuan / piece）.Therefore，when the cost of enhancive labor hours allocated to the value of each new an labor hour is less than its shadow price of 0.05263 yuan / hour through recruitment， overtime and other measures， it is worthy of recruitment， overtime and other measures， and vice versa is not worth；because the shadow price of machine hours is 0， no matter what measures to increase the total resources of the machine hours cannot increase revenue；when the cost of increasing sales ability allocated to each new product value is less than its shadow price of 0.1579 yuan / piece through the expansion of production scale and sales volume and other measures，the measures of improving sales ability is feasible， the converse is not feasible.
After using Solver to solve， various reports can be generated. The process is as follows：click the " solving" command in the" solver parameters" dialog box， popup "solver results" dialog box， there are "operation result report"，" sensitivity report "， "limit value report" in the " report （R）" list. Such as choosing "sensitivity report"，clicking "OK" button， then insert "sensitivity report" worksheet in the cur-rent workbook， as shown in Fig. 4 [6]. In Fig. 4， the reduced cost is the product profit marginal contribution per unit minus the plot of the resource use of the product and its shadow price. for example，the decreasing cost of product A is “4-（46*0.05+72* 0+1*1.58）”.
Fig. 4 Sensitivity reports
4 Solving nonlinear programming problems
As the basic train of thought to solve linear programming problems， the use of Solver to solve nonlinear programming problems also follow the three steps， how-ever， don’t select the "linear model" check box in the second step of "Solver Op-tions" dialog box [7]. Because there may be multiple local optimal solution， using a variety of different initial conditions to solve repeatedly， and pick out an optimal answer from the local optimal solution as the global optimal solution.
5 Conclusions
Follow the method and steps above， a solver model for solving and analyzing the optimization problems can be set up.
References
1. W. Xing De （2008） XD Modeling Method Based on Excel. 1rd ed.， Tsinghua University Press： Beijing， 398433. （in Chinese）
2. J. Banks，J. Carson，et al （2009）. Discrete-Event System Simulation. Prentice- Hall： Upper Saddle River， 5rd ed.， 421462
3. The Chinese Institute of Certified Public Accountants （2011）， Financial and Cost Manage-ment， 1rd ed.， China Financial & Economic Publishing House： Beijing， 216239. （in Chinese）
4. Z. Li Yuan， F. yan （2007）， Excel Application in Financial Management， 1rd ed. Dongbei University of Finance & Economics press： Dalian， 126135. （in Chinese）
5. W.L. Winston， S. C. Albright， M.N. Broadie （2001）， Practical Management Science. 2rd ed.， Duxbury： Clarendon， 556573
6. Y.Yu Huan （2009）， Constructing a Excel Template Cost-Volume-Profit Analysis. Friends of Accounting， 5759. （in Chinese）
7. G. Zeng Jun （2009）， The Application of Excel Programming Tools in Accounting. Commu-nication of Finance and Accounting， 109110. （in Chinese）