《Excel在會計與財務管理中的應用》 教案 項目五 工資管理

Excel在會計與財務管理中的應用課程教案課程名稱：Excel在會計與財務管理中的應用課程號：課程學分：2參考學時：32課程性質：專業(yè)必修課適用專業(yè)：經管類專業(yè)上課時間：授課教師：負責人：審核單位：年月日-1-一、課程基本信息課程名稱Excel在會計與財務管理中的應用課程號課程性質專業(yè)必修課開課部門課程負責人課程團隊授課學期學分/學時2/32授課語言漢語課程簡介Excel是使用極為廣泛的電子表格軟件，其以操作簡便、功能強大而著稱，非常適合缺乏計算機編程語言知識和對數據處理要求不高的財務管理人員使用。本書以Excel2010為基礎，通過實例詳細介紹了Excel在會計電算化中的應用，并應用Excel對企業(yè)運營中的會計流程和財務管理問題進行成因分析。本書應用實例典型，內容豐富，充分利用宏、MicrosofQuery組件、數據透視表組件等完成會計電算化的設計。書中各項目詳細介紹了實例的操作步驟，學習者只需按照書中介紹的步驟一步步地實際操作，就能完全掌握本書的內容。授課班級

二、課程授課教案項目五講課主題工資管理學時3學時教學目標(1)理解員工組成部分、個稅、工資的計算等基本內容；(2)掌握在Excel中建立管理單據表，計算員工工資和制作工資條；(3)掌握查詢和分析工資數據的操作；(4)學會應用Excel解決實際案例中遇到的員工工資問題。任務目標(1)在Excel中建立員工檔案表；(2)在Excel中創(chuàng)建考勤表；(3)在Excel中創(chuàng)建“五險一金”表；(4)在Excel中創(chuàng)建獎金、津貼表；(5)在Excel中創(chuàng)建個人所得稅表。教學設計授課提綱及重難點分析教學方法及課程思政設計教學時間任務一固定資產的管理知識準備通常在核算員工工資之前，要先獲取員工的基本信息，建立員工檔案。員工工資管理除了基本檔案之外，還涉及基本工資、考勤、獎金、社保、福利和加班等項目。本任務首先利用Excel建立基礎表格，然后利用Excel的公式和函數對這些項目進行統計和計算。1.建立員工檔案表根據企業(yè)需求，在人事錄用員工開始就要建立員工檔案，方便后續(xù)管理。員工檔案一般應包括以下信息：員工編號、姓名、所屬部門、職位等級、聯系電話、郵箱、婚姻狀況、身份證號碼、家庭地址、基本工資、工資卡所屬銀行、工資卡卡號等，需根據公司的需求進行選擇。首先建立Excel表格，然后將員工的基本信息錄入表格中，此項工作即完成。2.創(chuàng)建考勤表員工考勤表是用于記錄和統計員工出勤狀態(tài)的表格。出勤狀態(tài)包括正常、遲到、早退、請假(病假、事假、婚假、喪假)等。依據國家相關法律法規(guī)和企業(yè)的規(guī)章制度，請假狀態(tài)對于工資核算的影響程度是不一樣的。通過創(chuàng)建考勤表，可為后續(xù)人事和財務在核算工資時提供考勤依據。3.創(chuàng)建“五險一金”表“五險”就是通常說的社會保險，主要包括養(yǎng)老保險、醫(yī)療保險、失業(yè)保險、工傷保險和生育保險。2016年3月23日“十三五”規(guī)劃綱要提出，將生育保險和基本醫(yī)療保險合并實施。這意味著未來隨著生育保險和基本醫(yī)療保險的合并，人們熟悉的“五險一金”或將變?yōu)椤八碾U一金”。4.創(chuàng)建獎金、津貼表獎金和津貼是企業(yè)為了鼓勵員工采取的獎勵措施。獎金是支付給員工的超額勞動報酬和增收節(jié)支的勞動報酬，它與員工的表現息息相關。津貼是指補償職工在特殊條件下的勞動消耗及生活費額外支出的工資補充形式，如常見的生活補貼、交通補貼、住房補貼、高溫補貼等。5.創(chuàng)建個人所得稅表本任務中的案例采用的是收入超過5000元起征應納稅額。應納稅額的計算公式如下：應納稅額＝應納稅所得額×適用稅率－速算扣除數任務資料福源公司是一家生產制造企業(yè)，目前公司員工的情況如表5-2所示。（完整表格查看教材98頁）任務操作1.在Excel中建立員工檔案表(1)打開Excel，單擊“保存”按鈕，將Excel工作薄命名為“員工檔案表.xlsm”。(2)在Sheet1工作表中輸入如圖5-2所示列標題。(3)選中A1:L1單元格區(qū)域，選擇“開始”選項卡，執(zhí)行“對齊方式”組中的“合并后居中”命令，如圖5-3所示。(4)在A3單元格中輸入“0001"，將鼠標指針放在A3單元格右下角，當其呈實心十字時，按住Ctr鍵，向下拖動鼠標,自動填充序號(如果企業(yè)員工較多,則可以分部門建立,也可設置多位數編號)，如圖5-4所示。(5)輸入表格中需要登記的員工基本信息。手動輸入姓名;其后的信息可以先設置后直選擇，也可以手動輸入;重復的信息可以采用復制粘貼方式輸入。(6)將后面選擇有限的幾列進行上面的操作,手動輸入獨立存在的信息。2.在Excel中創(chuàng)建考勤表(1)打開Excel，將Sheet2工作表命名為“考勤”，在考勤表中輸入工作表標題、行標題、列標題以及員工的出勤情況，并對工作表格式進行簡單的設置。(2)在計算事假、曠工、病假和實際出勤天數時，需要使用COUNTIF函數進行自動求和。例如，要求實際出勤天數，可選中AN4單元格，選擇“公式”選項卡，執(zhí)行“函數庫”組中的“插入函數”命令，打開“插入函數”對話框，在“或選擇類別”下拉列表中選擇“統計”，找到COUNTIF函數，單擊“確定”按鈕。(3)打開“函數參數”對話框，“Range”處選擇要進行統計的單元格區(qū)域，“Criteria”處為“√”(注意輸入英文格式的雙引號。另外，如果要統計的是字符，則須加雙引號；如果是數字，則不用)，單擊“確定”按鈕，即可計算出結果。(4)將鼠標指針放在AN4單元格右下角，當其呈實心十字時，按住鼠標左鍵向下拖動，進行數據的填充。事假、曠工、病假三列按照同樣的方法計算出結果。3.在Excel中創(chuàng)建“五險一金”表(1)打開員工檔案表，將$heet3命名為“五險一金”，將員工信息檔案表中的“序號”和“姓名”列粘貼復制到“五險一金”工作表中，添加“繳費基數”“應扣保險費”“應扣公積金”列，如圖5-11所示。(2)打開“五險一金”工作表,輸入“繳費基數”“應扣保險費”“應扣公積金”三列數據。每個單位的繳費基數由其繳費標準確定，案例中假定繳費基數是3397.6元:應扣保險費按照福源最低工資標準計算，應扣金額為414.91元;應扣住房公積金按照福源最低工資標準計算，應扣金額為158元。將以上數據輸入表格中，如圖5-12所示。4.在Excel中創(chuàng)建獎金、津貼表(1)打開員工信息檔案表，將$heet4重命名為“獎金、津貼表”，將員工信息檔案表中的“序號”和“姓名”列復制粘貼到“獎金、津貼表”工作表中,添加“獎金”“津貼”列,如圖5-13所示。(2)在“獎金、津貼表”工作表中,根據公司人事制度,假設全勤獎獎金為500元,曠工、請假、遲到、早退均沒有獎金。選中C3單元格，選擇“公式”選項卡，執(zhí)行“函數庫”組中的“插入函數”命令，彈出“插入函數”對話框，找到正函數，單擊“確定”按鈕,如圖5-14所示。(3)打開“函數參數”對話框，在Logicaltest中輸入“考勤!AM4=考勤!AN4”;在Valueiftrue中,如果應出勤天數等于實際出勤天數,則應該獎勵500元,所以輸入“500”;在Valueiffalse中,如果應出勤天數不等于實際出勤天數,則沒有獎金，入“0”，如圖5-15所示。(4)單擊“確定”按鈕，得出計算結果。將鼠標指針放在C3單元格右下角，當鼠標指針呈實心十字時，向下拖動鼠標，填充單元格，得出表格數據，則“獎金”列計算完畢，如圖5-16所示。(5)假設根據人事規(guī)章制度，經理級員工津貼的幅度為基本工資的10%，其他級員工津貼的幅度為基本工資的5%。選中D3單元格，選擇“公式”選項卡，執(zhí)行“函數庫”中的“插入函數”命令，打開“插入函數”對話框，找到正函數，單擊“確定”按鈕，如圖5-17所示。(6)打開“函數參數"對話框,在Logicaltest中輸入“員工信息檔案表!D3=”經理”在Value_iftnue中，如果是經理，則津貼為基本工資的10%，所以輸入“員工信息檔案表!L3*10%”;在Vlueiffalse中,如果不是經理,則津貼為基本工資的5%,所以輸入“員工信息檔案表!L3*5%”，如圖5-18所示。(7)單擊“確定”按鈕，得出計算結果。將鼠標指針放在D3單元格右下角，當鼠標指針呈實心十字時，向下拖動鼠標，填充單元格，得出表格數據，則“津貼”列計算完畢,如圖5-19所示。5.在Excel中創(chuàng)建個人所得稅表(1)在“員工檔案表”中新建工作表，命名為“個稅稅率表”，將Sheet5重命名為“個人所得稅計算表”。(2)選擇“個人所得稅計算表”工作表,將“員工信息檔案表”工作表中的“序號”和“姓名”列復制粘貼到工作表中,添加“工資合計”“應納稅所得額”“稅率”“速算扣除數”“代扣個人所得稅額”列。(3)計算工資合計。這里的工資合計指的是應發(fā)工資，包括基本工資、獎金、津貼和扣除的款項。在C3單元格中輸入公式“=員工信息檔案表!L3+考勤!A04+獎金、津貼表!C3+獎金、津貼表!D3-五險一金!D3-五險一金!E3”，按Enter鍵得出結果。(4)計算應納稅所得額(本案例中不涉及專項扣除因素)。在D3單元格中輸入公式“=IF(C3<5000,0,C3-5000)”，按Enter鍵，得出結果。(5)計算稅率(本案例中不涉及專項扣除因素)。在E3單元格中輸入公式“=IF(個人所得稅計算表!D3=0,0,LOOKUP(個人所得稅計算表!D3,個稅稅率表!$C$4:$C$11,個稅稅率表!$D$4:$D$11))”，按Enter鍵，得出結果。將鼠標指針放在E3單元格右下角，當其呈實心十字時向下拖動填充數據。(6)計算速算扣除數。在F3單元格中輸入公式“=F(個人所得稅計算表!E3=0,0.LOOKUP(個人所得稅計算表!E3,個稅稅率表!$D$4:$D$11,個稅稅率表!$F$4:$F$11))”，按Enter鍵，得出結果。將鼠標指針放在F3單元格右下角，當其呈實心十字時向下拖動填充數據。(7)計算代扣個人所得稅額。在G3單元格中輸入公式“=D3*E3*0.01-F3”(輸入的公式中之所以乘以0.01，是因為稅率在表格中沒有用“%”表示)，按Enter鍵，得出結果。將鼠標指針放在G3單元格右下角，當其呈實心十字時向下拖動填充數據。40min任務二制作工資條知識準備工資條是伴隨著工資的發(fā)放而產生的，是員工工資組成的明細表，是發(fā)放給員工供員工查看的工資的明細條。職工工資構成往往有很多項，如姓名、職稱、基本工資、崗位工資等;每月發(fā)工資時要向職工提供一個包含工資各構成部分的項目名稱和具體數值的工資條，且打印工資條時要求在每個職工的工資條間有一空行便于彼此裁開。使用Excel制作工資條有很多方法，如排序法制作工資條、插行法制作工資條、函數法制作工資條等。本任務主要介紹函數法制作工資條。這里主要介紹三個函數，即VLOOKUP所數、MOD和ROW所數。VLOOKUP函數是指在表格或數值數組的首列查找指定的數值，并由此返回表格或數組當前行中指定列處的數值。函數語法:LOOKUP(Lookupvalue,Lookupvector,Resultvector)參數說明:·Lookup_value:要查找的值?！ookupvector:要查找的范圍?！esultvector:要獲得的值。需要注意的是，VLOOKUP函數的查詢方式為二分法查詢。MOD函數是一個求余函數，即求兩個數值表達式做除法運算后的余數。函數語法:MOD(Number,Divisor)參數說明:·Number:被除數?！ivisor:除數。在Oracle中，如果Divisor為0，則函數直接返回Number。特別注意:在Excel中，MOD函數用于返回兩數相除的余數，返回結果的符號與除數(Divisor)的符號相同。ROW函數用來確定光標的當前行位置，并返回光標的當前行位置。函數語法:ROW(Reference)參數說明:Refrence:需要得到其行號的單元格或單元格區(qū)域。如果省略Reference，則假定是對ROW函數所在單元格的引用。如果Reference為一個單元格區(qū)域，并且ROW函數作為垂直數組輸入，則ROW函數將Reference的行號以垂直數組的形式返回。Reference不能引用多個單元格區(qū)域。任務資料福源公司員工的基本工資情況如圖5-27所示，公司有嚴格的考勤、獎金、津貼制度，同時只要入職通過試用期的員工，企業(yè)都給員工購買五險一金，繳納基數和比例在具體的每一步業(yè)務操作中。根據下面業(yè)務操作完成學習任務。任務操作1.利用VLOOKUP函數制作工資條(1)打開員工檔案表，在“個稅稅率表”工作表右側新建一個“工資明細表”工作表,將序號、姓名、基本工資、獎金、津貼、代扣個人所得稅額、五險一金費、考勤扣款等前面計算過的涉及工資核算的所有信息復制粘貼到本工作表中，增加“應發(fā)工資”和“實發(fā)工資”列。(2)計算應發(fā)工資，應發(fā)工資=基本工資+獎金+津貼-考勤扣款。在L3單元格中輸入公式“=E3+F3+I3+J3”，按Enter鍵，得出結果。將鼠標指針放在L3單元格右下角，當其呈實心十字時向下拖動填充數據。(3)計算實發(fā)工資，實發(fā)工資=應發(fā)工資-代扣的五險一金-代扣的個稅。在M3單元格中輸入公式“=ROUND(L3-G3-H3-K3.2)"(ROUND函數用于設置保留的小數點位數)，按Enter鍵，得出結果。將鼠標指針放在M3單元格右下角，當其呈實心十字時向下拖動填充數據。(4)在“工資明細表”工作表右側新建一個“工資條”工作表，在該工作表中插入工資發(fā)放信息和工資條標題行，并進行格式的設置。(5)在A3單元格中輸入“0001",在B3單元格中輸入VLOOKUP函數公式“=VLOOKUP($A3,工資明細表!$A:B,COLUMN(B:B),FALSE)”，按Enter鍵，得出結果。。(6)選中A4:M4單元格區(qū)域，將鼠標指針放在M4單元格右下角，當其呈實心十字時向下拖動填充，完成工資條的數據填充。2.利用MOD和ROW函數制作工資條(1)同VLOOKUP函數一樣，首先設置完成“工資明細表”工作表，然后在其右側新建一個“MOD工資條”工作表。(2)在“MOD工資條”工作表A1單元格中輸入公式“=F(MOD(ROWO,3)=0,””I(MOD(ROWO),3)=1,工資明細表!A$2,INDEX(工資明細表!$A:$M,INT((ROWO)+8)/3),COLUMNO)))”，該公式的含義是能被3整除的行為空行，被3除余1的行為標題，被3除余2的行為包括職工編號、姓名等各項工資數據的行。(3)向下拖動A1:M1單元格區(qū)域，直到顯示所有員工的工資條數據。40min任務三查詢和分析工資數據知識準備為了方便公司財務人員查詢每一個員工的工資詳情，可以建立一個員工工資查詢表，只要在工資查詢表中輸入員工的某一個信息，就可以查看員工的所有工資信息。所以，本任務的第一個內容是建立一個可供查詢的員工工資查詢表。員工工資是企業(yè)成本的重要組成部分。本任務的第二個內容是在現有的工資基礎上，采用圖表的形式對員工工資進行分析，提高公司對員工工資的管理效率，完善公司的工資管理制度，找到節(jié)約公司成本的途徑，從而提高公司的利潤空間。任務資料本任務的任務資料是任務一和任務二的操作結果。任務操作1.查詢員工工資數據(1)打開員工檔案表，在最后一個工作表的右側新建一個“工資查詢表”工作表，如圖5-36所示(該表格中的行標題可以從工資明細表中進行復制粘貼，標題行要進行“合并單元格”設置)。(2)在美化過程中，可以在“開始”選項卡的“字體”組進行字體、大小、顏色、加粗、斜向、表格方框等基礎設置，在“對齊方式”組進行字體是左對齊還是右對齊或是居中等基本設置。(3)建立了工資查詢表的基本框架后，接下來就是引入具體數據。在B3單元格中輸入公式“=VLOOKUP($B$2,工資明細表!$A$3:$M$24.2.FALSE)”,并向下填充至B14單元格,如圖5-38所示。(4)將公式“=VLOOKUP($B$2.工資明細表!$A$3:$M$24.2.FALSE)”中的數字“2依次改為3~13，如圖5-39所示。(5)上述表格設置好之后，只要在B2單元格中輸入員工對應的序號，按Enter鍵，即可查詢相應序號的員工的工資具體情況，如圖5-40所示。2.分析員工工資數據1)對員工工資數據排序(1)復制“工資明細表”工作表，將新工作表命名為“工資排序表”。選中該工作表中的任意單元格,選擇“數據”選項卡,執(zhí)行“排序和篩選”組中的“排序”命令,如圖5-41所示。(2)打開“排序”對話框，設置主要關鍵字為“實發(fā)工資”，排序依據為“數值”，次序為“升序”,單擊“確定”按鈕(如圖5-42所示),即可得出實發(fā)工資的升序排序結果(也可以選擇降序排序)。(3)單擊“添加條件”按鈕，可以設置次要關鍵字，如圖5-43所示。設置次要關鍵字為“姓名”，排序依據為“數值”，次序為“升序”，單擊“確定”按鈕，即可得到以實發(fā)工資為主要關鍵字，以姓名為次要關鍵字的工資排序情況。2)建立員工工資統計圖該操作可以在“工資明細表”工作表中進行,也可以在“工資排序表”工作表中進行,這里選擇在“工資明細表”工作表中進行。(1)打開工資排序表，選中需要用圖形進行分析的數據。由于這里只反映兩項(實