1、1The Constrained Optimization Problem on Financial Management Solved by Using SolverAbstract. 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
2、 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 unconstraine
3、d 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
4、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 2optimization problems within 200 decision variables 1. Because of the limited space， The following desc
5、ribes 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 Thr
6、ough 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 speci
7、fied 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 3B products in Dongguan，the i
8、nformation 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
9、 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 o
10、f 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 Analys
11、is 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 4produc
12、tion 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. q1x
13、1 + 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，
14、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
15、 monthly total profit. input q1， q2 two arbitrary value （ such as 700，3800 ） of A， B two products monthly 5sales 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 t
16、he 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，
17、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
18、 “add constraint“ dialog box，locate the cursor in the“ cell reference position“， select the range of $D$3：$D$5 with the mouse， select “=“ operator， ac-tivate the “ constrained values“ input box，use the mouse to select “$E$3：$E$5“ area， click “OK“ button，return the “solver parameters “dialog box. 4 （
19、In the 6same way， there are increase of other constraints，such as： product sales limited integer value， continue clicking the “add“ button in the“ constraint“ column， pop-up “add constraint“ dialog box， locate the cursor in the“ cell reference position“， select the range of $B$2：$C$2 with the mouse，
20、select the “int“ in the operator drop-down list box， “constrained values“ column automatically appear “integer value “.Click “OK“ button， return the “solver parameters “ dialog box）.Click “Options“ button， pop-up “solver options“ dialog box， select using a linear model， “assuming non-negative“ check
21、 box， and other settings unchanged， click “OK“ button， return the “solver parameters “ dialog box again，click the“ solution“ button， the maximum value of total profits appeared in D8， a pair of the best combination values of q1 ， q2 displayed in B2， C2 at the same time. This reflects： according to t
22、he subject given conditions，when A， B， two kinds of product sales is respectively 736.84 pieces， 4863.16 pieces，the total profits of the enterprise is reached maximum 27263.16 yuan，in this case， labor hours and sales ability are made full use of， there is some surplus machine time. 3.5 Saving the mo
23、del Click the “options“ button in the “ solver parameters“ 7dialog 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
24、“ 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，
25、 the formula is “=$D$3：$D$5=Sheet1！$E$3：$E$5“；D13 showed the various settings of “ solver options“ dialog box， the formula is “= 100，100，0.000001，0.05， TRUE， FALSE， FALSE， 1，1，1，0.0001， TRUE“， in the formula， the fifth parameter and the last parameter represent respectively whether the“ using a line
26、ar model“，“ assumed to be non-negative“ two check box is“ selected“ or not， selected for“ TRUE“， otherwise “FALSE“. 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 t
27、hat each business parameter change on 8the 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， t
28、he 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 contributio
29、n 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 increas
30、e 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， th
31、e maximum profit increase or decrease 15.79 yuan（i.e. the shadow price 9of 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 ot
32、her 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
33、 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 generat
34、ed. 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，
35、 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 10plot of the resource use of the product and its shadow price. for example，the decreasing cost of product A is “4-（4
36、6*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， dont select the “linear model“ check box in
37、 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 metho
38、d 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， 398?433. （in Chinese） 2. J. Banks，J. Carson，et al （ 2009）. Discrete-Event System Simulation. Prentice- Hall： Upper Saddle River， 5rd
