一、薪酬管理系统介绍

本学习情境主要根据学习情境一创建的薪酬管理基本表格采用Excel的函数和数据处理功能完成薪酬管理表和工资条的生成，再根据薪酬管理表进行简单的薪酬管理分析。学习情境一为薪酬管理系统提供了完成的薪酬管理基本表格，包括“职工基本情况表”、“职工基本工资表”、“职工福利表”、“职工社会保险”、“职工考勤表”、“职工绩效考核表”总共六个基本表格。由学习情境一提供的薪酬管理基本表格和个人所得税标准建立薪资表、工资条以及薪酬管理分析构成了一个普通企事业单位的简单薪酬管理系统。整个系统结构如图5－1所示。

图5－1 薪酬管理系统

二、薪酬管理基本表格引入

新建工作簿【教职工薪资管理系统．xls】，复制学习情境一中制作的【Excel基本操作系统．xls】中的基本表格到工作簿【教职工薪资管理系统．xls】中。

（1）教职工基本情况表

该表由人力资源管理部门在员工入职时根据员工的简历建立员工基本信息汇总表，包括每个员工的工号、姓名、所属部门、职务、职称、性别、参加工作时间、联系电话、身份证号、银行账号等信息构成。新员工入职后进行增加同时员工离职要及时删除，因此该表格应随时更新。如图5－2所示。

图5－2 教职工基本情况表

（2）教职工基本工资表

员工的基本工资由企事业单位自行确定，在该案例中，教职工的基本工资包括岗位工资、资质工资、年资三部分构成总的基本工资。如图5－3所示。

图5－3 教职工基本工资表

备注：岗位工资与职称挂钩；

资质工资与职务挂钩；

年资按每增加一年增加100元的标准，如图5－4所示。

图5－4 基本工资标准

（3）教职工考勤表

员工考勤表是用来反映每个员工本月的病事假以及加班情况，以便对员工的工资进行相应的增减调整。如图5－5所示。

图5－5 教职工考勤表

备注：旷工一天扣100元，病假或事假一天扣50元，加班一天补100元。

三、引入其他基本表格

（1）教职工福利表

该表由各企事业单位自行制定，种类非常多。在该案例中，教职工的福利包含住房补贴、伙食补贴、话费补贴三部分构成。按照学习情境一中介绍的方法制作“教职工福利表”，如图5－6所示。

备注：住房补贴与职称挂钩；

伙食补贴按每月300元／人发放；

话费补贴每月按50元／人发放，如图5－7所示。

（2）教职工绩效考核表

该校绩效考核每学期考核一次，因此绩效奖金每年发放两次，分别随1月份和7月份工资发放，其他月份工资不计算绩效奖金。绩效奖金直接与绩效考核结果挂钩：绩效考核为“优”的奖1000元，“良”的奖800元，“中”的奖600元，“合格”的奖400元。按照学习情境一中介绍的方法制作如图5－8所示“教职工绩效考核表”。

图5－6 教职工福利表

图5－7 福利标准

图5－8 教职工绩效考核表

（3）教职工社会保险表

员工保险由国家制定要求单位执行，但是不同单位可能根据自身情况在国家制定的可调整范围内进行确定，一般单位按照国家规定的最低标准缴纳，通常所说的福利“五险一金”由于工伤保险和生育保险全部由单位缴纳，因此在该案例中，个人所缴纳的部分包括养老保险、失业保险、医疗保险、住房公积金四部分构成。按照学习情境一中介绍的方法制作如图5－9所示“教职工社会保险缴纳一览表”。

图5－9 教职工社会保险表

备注：武汉市住房公积金和各项社会保险缴纳规定概况如图5－10所示。

图5－10 武汉市住房公积金和各项社会保险缴纳规定概况

根据武汉市住房公积金和各项社会保险缴纳规定概况，该校按照最低比例为员工缴纳住房公积金和社会保险，缴纳基数为员工基本工资。如图5－11所示。

图5－11 各项福利标准

（4）个人所得税标准表

个人所得税是调整征税机关与自然人（居民、非居民）之间在个人所得税的征纳与管理过程中所发生的社会关系的法律规范的总称。凡在中国境内有住所，或者无住所而在中国境内居住满一年的个人，从中国境内和境外取得所得的，以及在中国境内无住所又不居住或者无住所而在境内居住不满一年的个人，从中国境内取得所得的，均为个人所得税的纳税人。2011年9月1日实施，中国内地个税免征额调至3500元。在【教职工薪资管理系统．xls】工作簿【教职工考勤表】后插入新的工作表并重命名为【个人所得税标准表】，然后根据2011年9月1日起实施的新的个人所得税扣除标准制定如图5－12所示的“个人所得税标准表”。

图5－12 个人所得税标准表

特别提醒：“个人所得税标准表”中的速算扣除数的计算公式：本级速算扣除额＝上一级最高所得额＊（本级税率－上一级税率）＋上一级速算扣除数

四、创建“薪资管理表”

薪资管理表是由“职工基本情况表”、“职工基本工资表”、“职工福利表”、“职工社会保险”、“职工考勤表”、“职工绩效考核表”六个基本表格中的数据组合得到“应发工资”，并根据“个人所得税标准表”计算“应扣所得税”，从而由“应发工资”扣除“应扣所得税”得到“实发工资”。

①在【教职工薪资管理系统．xls】工作簿【个人所得税标准表】后插入新的工作表并重命名为【薪资管理表】。

②单击A1单元格，输入标题“薪资管理表”，如图5－13所示。

图5－13 薪资管理表标题

③输入【薪资管理表】中表格的表头，即在A2：Q2单元格中分别输入“工号”、“姓名”、“所属部门”、“基本工资”、“住房补贴”、“伙食补贴”、“话费补贴”、“绩效奖金”、“加班补助”、“请假扣款”、“应发工资”“养老保险”、“医疗保险”、“失业保险”、“住房公积金”、“应扣所得税”、“实发工资”，如图5－14和图5－15所示。

图5－14 薪资管理表表头1

图5－15 薪资管理表表头2

④标题和表头格式设置如下所述：

a．标题格式设置：选择A1：Q1单元格区域，单击菜单栏中的【格式】︱【单元格】，在弹出的【单元格格式】对话框中选择【对齐】选项卡，选择“水平对齐”︱“跨列居中”，“垂直对齐”︱“居中”，如图5－16所示；单击【字体】选项卡，“字体”选择“黑体”，“字形”选择“加粗”，“字号”选择“24”，“下画线”选择“会计用双下画线”，如图5－17所示；单击【确定】退出【单元格格式】对话框，并将单元格高度根据字体大小进行调整。完成标题设置后如图5－18所示。

图5－16 单元格对齐格式设置

图5－17 单元格字体格式设置

图5－18 薪资管理表标题

b．表头及数据区域格式设置：选择A2：Q2单元格区域，单击菜单栏“字体”下拉框选择“宋体”，“字号”选择“11”，如图5－19所示；单击菜单栏中的【格式】︱【单元格】，在弹出的【单元格格式】对话框中选择【边框】选项卡，“线条”选择“粗黑实线”单击“外边框”︱“内部”，如图5－20所示；单击【确定】退出【单元格格式】对话框，完成上述设置后如图5－21所示。(www.daowen.com)

⑤输入员工基本信息：

a．薪资管理表中的基本信息A3：E12单元格的数据通过同一工作簿不同工作表的数据链接方式得到。单击A3单元格，输入“＝教职工基本情况表！A3”，如图5－22所示。

b．利用填充柄快速拖动复制完成A3：C12单元格的数据，首先选择A3单元格，将鼠标放置在A3单元格的右下角，当出现十字形时，单击鼠标左键，向右拖动至C3单元格；然后选择A3：C3单元格，利用同样的方式向下拖动至C12单元格，松开鼠标左键后，A3：C3单元格数据全部自动显示，如图5－23所示。

图5－19 表头及数据区字体设置

图5－20 表格边框设置

⑥基本表格定义名称：

a．单击菜单栏【插入】︱【名称】︱【定义】。

b．在弹出的【定义名称】对话框中的“在当前工作簿的名称”下输入“教职工基本工资表”，鼠标移动到“引用位置”处，单击工作表“教职工基本工资表”选中A3：K12单元格区域或者是直接在“引用位置”输入“＝教职工基本工资表！＄A＄3：＄K＄12”。

图5－21 表格添加边框

图5－22 薪资管理表

图5－23 自动填充教职工基本信息

c．单击【添加】按钮。如图5－24所示。

d．按上述“教职工基本工资表”名称定义的方法分别对“教职工福利表”、“教职工社会保险表”、“教职工绩效考核表”、“教职工考勤表”进行名称定义，完成定义后如图5－25所示。单击【确定】，退出【定义名称】对话框。

图5－24 教职工基本工资表定义名称

图5－25 基本表格定义名称

⑦“基本工资”、“住房补贴”、“伙食补贴”、“话费补贴”、“绩效奖金”、“加班补助”、“请假扣款”数据的自动显示方法，以“教职工基本工资表”中提取“基本工资”数据为例：

a．选中“D3”单元格，从菜单栏单击【插入】︱【函数】，在弹出的【插入函数】对话框中，在“或选择类别”下拉列表中选择“查找与引用”，在“选择函数”中选择“VLOOKUP”函数，如图5－26所示单击【确定】按钮退出【插入函数】对话框。

b．在弹出的【函数参数】对话框中，分别输入如图5－27所示的参数，单击【确定】退出【函数参数】对话框，则在“D3”单元格中显示第一位员工“张博文”的基本工资“7800”，如图5－28所示。

图5－26 插入VLOOKUP函数

图5－27 输入VLOOKUP函数的参数

图5－28 运用VLOOKUP函数自动显示“基本工资”

c．利用利用填充柄快速拖动复制完成D3：D12单元格的数据，首先选择D3单元格，将鼠标放置在D3单元格的右下角，当出现十字形时，单击鼠标左键，向下拖动至D12单元格；松开鼠标左键后，D3：D12单元格数据全部自动填充，如图5－29所示。

图5－29 自动填充“基本工资”

d．运用上述“基本工资”自动显示同样的方法完成“住房补贴”的自动显示， VLOOKUP函数各参数如图5－30所示。

图5－30 运用VLOOKUP函数自动显示“住房补贴”

e．选中E3单元格复制公式“＝VLOOKUP（A3，教职工福利表，5，0）”后回车，选中F3单元格粘贴公式后将公式中的第三个参数“5”更改为“6”：“＝VLOOKUP（A3，教职工福利表，6，0）”回车即可得到“伙食补助”数据。

f．选中E3单元格复制公式“＝VLOOKUP（A3，教职工福利表，5，0）”后回车，选中G3单元格粘贴公式后将公式中的第三个参数“5”更改为“7”：“＝VLOOKUP（A3，教职工福利表，7，0）”回车即可得到“话费补贴”数据。

特别提醒：复制公式注意按回车键后再单击相应的单元格粘贴公式并回车单击原单元格复制公式——回车键——单击新单元格粘贴公式——回车键。

g．运用上述“基本工资”自动显示同样的方法完成“绩效奖金”的自动显示， VLOOKUP函数各参数如图5－31所示。

图5－31 运用VLOOKUP函数自动显示“绩效奖金”

h．运用上述“基本工资”自动显示同样的方法完成“加班补助”的自动显示， VLOOKUP函数各参数如图5－32所示。

图5－32 运用VLOOKUP函数自动显示“加班补助”

i．选中I3单元格复制公式“＝VLOOKUP（A3，教职工考勤表，39，0）”后回车，选中G3单元格粘贴公式后将公式中的第三个参数“39”更改为“40”：“＝VLOOKUP （A3，教职工考勤表，40，0）”回车即可得到“请假扣款”数据。

⑧计算“应发工资”，应发工资＝基本工资＋住房补贴＋伙食补贴＋话费补贴＋绩效奖金＋加班补助＋请假扣款，在“K3”单元格输入“＝D3＋E3＋F3＋G3＋H3＋I3－J3”回车，则自动计算出该单元格的数据，如图5－33所示。利用填充柄功能快速填充K3：K12单元格区域的数据。

图5－33 计算应发工资

⑨教职工社会保险“养老保险”、“失业保险”、“医疗保险”、“住房公积金”数据的自动显示方法。

a．运用上述“基本工资”自动显示同样的方法完成“养老保险”的自动显示， VLOOKUP函数各参数如图5－34所示。

图5－34 运用VLOOKUP函数自动显示“养老保险”

b．选中L3单元格复制公式“＝VLOOKUP（A3，教职工社会保险表，5，0）”后回车，选中F3单元格粘贴公式后将公式中的第三个参数“5”更改为“6”：“＝VLOOKUP （A3，教职工社会保险表，6，0）”回车即可得到“失业保险”数据。

c．选中L3单元格复制公式“＝VLOOKUP（A3，教职工社会保险表，5，0）”后回车，选中G3单元格粘贴公式后将公式中的第三个参数“5”更改为“7”：“＝VLOOKUP （A3，教职工社会保险表，7，0）”回车即可得到“医疗保险”数据。

d．选中L3单元格复制公式“＝VLOOKUP（A3，教职工社会保险表，5，0）”后回车，选中G3单元格粘贴公式后将公式中的第三个参数“5”更改为“8”：“＝VLOOKUP （A3，教职工社会保险表，8，0）”回车即可得到“住房公积金”数据。

⑩计算“应扣所得税”，根据“个人所得税标准”利用“IF”函数得到“应扣所得税”的数据。

特别提醒：个税免征额是3500，使用超额累进税率的计算方法如下：

应扣所得税＝全月应纳税所得额×税率－速算扣除数

实发工资＝应发工资－四金－应扣所得税

全月应纳税所得额＝（应发工资－四金）－3500

扣除标准：个税按3500元／月的起征标准算

a．计算应纳税所得额，在“应扣所得税”前插入一列“应纳税所得额”，“P2”中输入“应纳税所得额”，应纳税所得额＝应发工资－养老保险－失业保险－医疗保险－住房公积金－个税起征点，即在“P3”中输入“＝K3－L3－M3－N3－O3－3500”，如图5－35所示。利用填充柄功能快速填充P3：P12单元格区域的数据。

图5－35 计算应纳税所得额

b．计算应扣所得税，单击P3单元格输入公式“＝IF（P3＜1500，P3＊0．03，IF（P3＜4500，P3＊0．1－105，IF（P3＜9000，P3＊0．2－555，IF（P3＜35000，P3＊0．25－1005，IF（P3＜55000，P3＊0．3－2755，IF（P3＜80000，P3＊0．35－5505，P3＊0．45－13505））））））”，利用填充柄功能快速填充Q3：Q12单元格区域的数据如图5－36所示。

图5－36 计算应扣所得税

计算“实发工资”，实发工资＝应发工资－四金－应扣所得税，在R3单元格中输入“＝K3－L3－M3－N3－O3－Q3”，利用填充柄功能快速填充R3：R12单元格区域的数据，如图5－37所示。

图5－37 计算实发工资