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. |