Step 1: Draw the calculation area

Draw the “Input Data” form for entering the parameters, and enter the variables in Table 3. Draw the “Formula Computing” form for calculation (Table 4):

Step 2: Calculate the element

a) Calculate the housing fund

Enter “=IF(A2<=Input Data!C5,Input Data!C5,IF(Formula Computing!A2>=Input Data!C4,Input Data!C4,Formula Computing!A2))” in D9

Enter “=D9*Input Data!C8” in D2，Enter “=D9*Input Data!C9” in D3

b) Calculate social insurances

Enter “=IF(A2=Input Data!C6,Input Data!C6,Formula Computing!A2))” in D10

Enter “=D10*Input Data!C10” in D6

Enter “=D10*Input Data!C11” in D7

c) Calculate the taxable income

Enter “=IF((A2-D3-D7-3500)>=0,(A2-D3-D7-3500),0)” in A4

Enter “=IF(A2>=3500,A6/12,(A6-3500+A2)/12)” in A8

d) Calculate the tax rate

Enter “=IF(A4<=1500,0.03,IF(A4<=4500,0.1,IF(A4<=9000,0.2,IF(A4<=35000,0.25,IF(A4<=55000,0.3,IF(A4<=80000,0.35,0.45))))))” in G6

Enter “=IF(A8<=1500,0.03,IF(A8<=4500,0.1,IF(A8<=9000,0.2,IF(A8<=35000,0.25,IF(A8<=55000,0.3,IF(A8<=80000,0.35,0.45))))))” in G7

e) Calculate the quick deduction

Enter “=IF(A4<=1500,0,IF(A4<=4500,105,IF(A4<=9000,555,IF(A4<=35000,1005,IF(A4<=55000,2755,IF(A4<=80000,5505,13505))))))” in G9

Enter “=IF(A8<=1500,0,IF(A8<=4500,105,IF(A8<=9000,555,IF(A8<=35000,1005,IF(A8<=55000,2755,IF(A8<=80000,5505,13505))))))” in G10

f) Calculate the personal income tax

Enter “=G6*A4-G9” in G2

Enter “=G7*A6-G10” in G3

Step 3: Adding constraints

Add constraints in the “Formula Computing” form.

Enter “=(A2+D2-D7-G2)*12+A6-G3” in F12

Enter “=(A2+D2+D6)*12+A6” in F13

Enter “=Input Data!C2” in H13

Enter “=Input Data!C3” in H14

Step 4: Solve the programming

Clicking the “Plan Solve” command in the “Data” menu to bring up the “Solve Parameters” dialog box. In the dialog box, select the radio button in front of the maximum value, set the target cell to $F$12, and the variable cell to $A$2, $A$6. Clicking the “Add” button in the “Solver dialog box” to open the “Add Constraints” dialog box. Click the cell reference box to select the A2 cell of the worksheet and “$A$2” will be displayed in the text box, then select “≥” constraint, and F13 cell is selected in the Constraint value text box. Add a second conditional constraint based on the same principle. Click the “Solve” button in the “Solve Parameters” dialog box and the “Solver Results” dialog box pops up. Click the radio button before “Save Plan Solving Results”. Click the “OK” button in the “Solve Results” dialog box, and the worksheet shows the results of the solution.