第5篇財務(wù)篇學(xué)習(xí)目標主要項目項目18制作員工工資管理表項目19制作投資決策分析表項目20制作往來賬務(wù)管理表項目18制作員工工資管理表項目背景思維導(dǎo)圖項目實施項目拓展項目訓(xùn)練案例小結(jié)項目背景員工工資管理是每個企業(yè)財務(wù)部的基礎(chǔ)工作，財務(wù)人員要清晰明了地列出員工的工資明細，統(tǒng)計員工的扣款項目，核算員工的工資收入等。制作工資表通常需要綜合大量的數(shù)據(jù)，如基本工資、績效工資、補貼、扣款項等。本項目通過制作“員工工資管理表”來介紹Excel2016在員工工資管理方面的應(yīng)用，效果如圖5.1和圖5.2所示。思維導(dǎo)圖項目實施任務(wù)18-1新建工作簿，重命名工作表（1）啟動Excel2016，新建一份空白工作簿。（2）將新建的工作簿重命名為“員工工資管理表”，并將其保存在“D:\公司文檔\財務(wù)部”文件夾中。（3）將工作簿中的“Sheet1”工作表重命名為“工資基礎(chǔ)信息”。任務(wù)18-2導(dǎo)入“員工信息”（1）選中“工資基礎(chǔ)信息”工作表。（2）單擊“數(shù)據(jù)”→“獲取外部數(shù)據(jù)”→“自文本”按鈕，打開“導(dǎo)入文本文件”對話框，在“查找范圍”中找到位于“D:\公司文檔\人力資源部”文件夾中的“員工信息”文件。項目實施（3）單擊“導(dǎo)入”按鈕，彈出“文件導(dǎo)入向?qū)?第1步，共3步”對話框，在“原始數(shù)據(jù)類型”處選中“固定寬度”單選按鈕；在“導(dǎo)入起始行”文本框中保持默認值“1”不變；在“文件原始格式”下拉列表中選擇“936：簡體中文（GB2312）”。（4）單擊“下一步”按鈕，設(shè)置字段寬度（列間隔）。在圖5.6中可見，部分列間缺少分列線，如“部門”和“身份證號碼”，“入職時間”和“學(xué)歷”，“職稱”和“性別”，需要在相應(yīng)位置單擊以建立分列線。拖曳水平和垂直滾動條，將所有需要導(dǎo)入的數(shù)據(jù)檢查一遍，使數(shù)據(jù)分別處于對應(yīng)的分列線之間。（5）單擊“下一步”按鈕，設(shè)置每列的數(shù)據(jù)類型，默認設(shè)置“列數(shù)據(jù)格式”為“常規(guī)”。這里，將“身份證號碼”設(shè)置為“文本”，將“入職時間”和“出生日期”設(shè)置為“日期”，其余列使用默認類型“常規(guī)”。項目實施（6）單擊“完成”按鈕，打開“導(dǎo)入數(shù)據(jù)”對話框。設(shè)置數(shù)據(jù)的放置位置為“現(xiàn)有工作表”的“=$A$1”單元格。

（7）單擊“確定”按鈕，返回“工資基礎(chǔ)信息”工作表，文本文件“員工信息”的數(shù)據(jù)被導(dǎo)入到工作表中。任務(wù)18-3編輯“工資基礎(chǔ)信息”工作表（1）選中“工資基礎(chǔ)信息”工作表。（2）刪除“身份證號碼”、“學(xué)歷”、“職稱”、“性別”和“出生日期”列的數(shù)據(jù)。（3）分別在E1、F1、G1單元格中輸入標題字段名稱“基本工資”“績效工資”“工齡工資”。項目實施（4）參照圖5.13輸入“基本工資數(shù)據(jù)”。（5）計算“績效工資”。（6）計算“工齡工資”。任務(wù)18-4創(chuàng)建“加班費結(jié)算表”（1）復(fù)制“工資基礎(chǔ)信息”工作表，將復(fù)制后的工作表重命名為“加班費結(jié)算表”。（2）刪除“入職時間”、“績效工資”和“工齡工資”列。（3）在E1、F1單元格中分別輸入標題“加班時間”和“加班費”。項目實施（4）輸入加班時間。（5）計算加班費。任務(wù)18-5創(chuàng)建“考勤扣款結(jié)算表”（1）復(fù)制“工資基礎(chǔ)信息”工作表，將復(fù)制后的工作表重命名為“考勤扣款結(jié)算表”。（2）刪除“入職時間”“績效工資”“工齡工資”列。（3）在E1:K1單元格區(qū)域中分別輸入標題“遲到”“遲到扣款”“病假”“病假扣款”“事假”“事假扣款”“扣款合計”。（4）參照圖5.18輸入“遲到”“病假”“事假”列的數(shù)據(jù)。項目實施（5）計算“遲到扣款”。（6）計算“病假扣款”。（7）計算“事假扣款”。（8）計算“扣款合計”。任務(wù)18-6創(chuàng)建“員工工資明細表”（1）插入一張新工作表，將新工作表重命名為“員工工資明細表”。（2）參見圖5.20創(chuàng)建“員工工資明細表”的框架。（3）填充“編號”“姓名”“部門”列的數(shù)據(jù)。（4）導(dǎo)入“基本工資”的數(shù)據(jù)。項目實施（5）使用同樣的方式，分別導(dǎo)入“績效工資”和“工齡工資”的數(shù)據(jù)。（6）導(dǎo)入“加班費”的數(shù)據(jù)。（7）計算“應(yīng)發(fā)工資”。（8）計算“養(yǎng)老保險”。（9）計算“醫(yī)療保險”。（10）計算“失業(yè)保險”。（11）導(dǎo)入“考勤扣款”數(shù)據(jù)。（12）計算“應(yīng)稅工資”。（13）計算“個人所得稅”。（14）計算“實發(fā)工資”。項目實施任務(wù)18-7格式化“員工工資明細表”（1）將工作表標題的對齊方式設(shè)置為“合并后居中”，設(shè)置標題的格式為“黑體、22磅”，標題行的行高為“50”。（2）將列標題的格式設(shè)置為“加粗、居中”，行高設(shè)置為“30”。（3）將表中所有的數(shù)據(jù)項的格式設(shè)置為“會計專用”格式，保留2位小數(shù)，無貨幣符號。（4）為表格添加內(nèi)細外粗的藍色邊框。（5）為“應(yīng)發(fā)工資”“應(yīng)稅工資”“實發(fā)工資”列的數(shù)據(jù)添加“藍色，個性色1，淡色80%”的底紋。項目實施任務(wù)18-8制作“工資查詢表”（1）插入一張新工作表，將新工作表重命名為“工資查詢表”。（2）創(chuàng)建圖5.32所示的“工資查詢表”的框架。（3）顯示員工“姓名”。（4）采用類似的方法，使用VLOOKUP函數(shù)構(gòu)建查詢其他數(shù)據(jù)項的公式。（5）取消顯示網(wǎng)格線。單擊“視圖”選項卡，在“顯示”組中，取消選中“網(wǎng)格線”復(fù)選框。項目拓展（1）制作“各部門工資匯總表”，效果如圖5.34所示。（2）制作各部門的平均工資收入數(shù)據(jù)透視表和數(shù)據(jù)透視圖，效果如圖5.35所示。項目訓(xùn)練設(shè)計并制作“差旅核算表”。案例小結(jié)本項目通過制作“員工工資管理表”，主要介紹了工作簿的創(chuàng)建，工作表的重命名，外部數(shù)據(jù)的導(dǎo)入，以及使用函數(shù)ROUND、SUM等構(gòu)建“工資基礎(chǔ)信息”“加班費結(jié)算表”“考勤扣款結(jié)算表”工作表的操作方法。同時，本項目介紹了使用公式和VLOOKUP函數(shù)，以及IF函數(shù)的嵌套創(chuàng)建“員工工資明細表”工作表，并使用VLOOKUP函數(shù)制作“工資查詢表”工作表的操作方法，實現(xiàn)了對員工工資的輕松、高效管理。此外，通過制作“各部門工資匯總表”和“差旅核算表”，進一步鞏固了數(shù)據(jù)透視表、數(shù)據(jù)透視圖以及IF函數(shù)的使用。項目19制作投資決策分析表項目背景思維導(dǎo)圖項目實施項目拓展項目訓(xùn)練案例小結(jié)項目背景企業(yè)在項目投資過程中，通常需要貸款來加大資金的周轉(zhuǎn)量。進行投資項目的貸款分析，可使項目的決策者更直觀地了解企業(yè)的貸款和經(jīng)營情況，以分析項目的可行性。利用長期貸款基本模型，財務(wù)部在分析投資項目的貸款時，可以根據(jù)不同的貸款金額、貸款年利率、貸款年限、每年還款期數(shù)中任意一個或幾個的變化，來分析每期償還金額的變化，從而為企業(yè)管理層決策提供相應(yīng)依據(jù)。本項目通過制作“投資決策分析表”來介紹Excel2016中的財務(wù)函數(shù)及模擬運算表在財務(wù)預(yù)算和分析方面的應(yīng)用。本項目假設(shè)公司計劃購進一批設(shè)備，需要資金120萬元，要向銀行貸款部分資金，年利率假設(shè)為4.9%，采取每月等額還款的方式?，F(xiàn)需要分析不同貸款數(shù)額（100萬元、90萬元、80萬元、70萬元、60萬元以及50萬元）、不同還款期限（5年、8年、10年和15年）下對應(yīng)的每月應(yīng)還貸款金額?！巴顿Y決策分析表”效果如圖5.42所示。思維導(dǎo)圖項目實施任務(wù)19-1新建工作簿、重命名工作表（1）啟動Excel2016，新建一個空白工作簿。（2）將新建的工作簿重命名為“投資決策分析表”，并將其保存在“D:\公司文檔\財務(wù)部”文件夾中。（3）將“投資決策分析表”工作簿中的“Sheet1”工作表重命名為“貸款分析表”。任務(wù)19-2創(chuàng)建“貸款分析表”（1）如圖5.43所示，輸入貸款分析的基本數(shù)據(jù)。（2）計算“總還款期數(shù)”。項目實施任務(wù)19-3計算“每月償還金額”（1）選中C7單元格。（2）單擊“公式”→“函數(shù)庫”→“插入函數(shù)”命令，打開“插入函數(shù)”對話框。（3）在“選擇函數(shù)”列表框中選擇“PMT”，打開“函數(shù)參數(shù)”對話框。（4）在“函數(shù)參數(shù)”對話框中輸入圖5.44所示的PMT函數(shù)參數(shù)。（5）單擊“確定”按鈕，計算出給定條件下的“每月償還金額”。項目實施任務(wù)19-4計算不同“貸款金額”的“每月償還金額”這里設(shè)定貸款金額分別為100萬元、90萬元、80萬元、70萬元、60萬元及50萬元，還款期限為5年，貸款利率為4.9%，可以使用單變量模擬運算表來分析適合公司的每月償還金額。（1）創(chuàng)建貸款分析的單變量模擬運算數(shù)據(jù)模型。（2）計算“每月償還金額”。項目實施任務(wù)19-5計算不同“貸款金額”和不同“總還款期數(shù)”的“每月償還金額”

這里，設(shè)定貸款數(shù)額分別為100萬、90萬、80萬、70萬、60萬以及50萬，還款期限分別為5年、8年、10年及15年，即設(shè)計雙變量決策模型。（1）創(chuàng)建貸款分析的雙變量模擬運算數(shù)據(jù)模型。（2）計算“每月償還金額”。項目實施任務(wù)19-6格式化“貸款分析表”（1）按住“Ctrl”鍵，同時選中E3:E8、C11:F11及B12:B17單元格區(qū)域，將對齊方式設(shè)置為“居中”。（2）分別為B2:C7、E2:F8及A11:F17單元格區(qū)域設(shè)置內(nèi)細外粗的表格邊框。（3）單擊“視圖”，在“顯示”工具組中取消勾選“網(wǎng)格線”復(fù)選框，隱藏工作表網(wǎng)格線。項目拓展（1）制作“不同貸款利率下每月償還金額貸款分析表”（單變量模擬運算表），效果如圖5.55所示。（2）制作“不同貸款利率、不同還款期限下每月償還金額貸款分析表”（雙變量模擬運算表），效果如圖5.56所示。項目訓(xùn)練制作“本量利分析”方案，效果如圖5.57所示。案例小結(jié)本項目通過制作“投資決策分析表”，介紹了Excel中的PMT函數(shù)、模擬運算表和方案管理器等。這些函數(shù)和方法都可以用來分析當(dāng)變量不是唯一的一個值而是一組值時的結(jié)果，或變量為多個，即存在多組值甚至多個變化因素時的結(jié)果。財務(wù)部工作人員可以直接利用Excel中的這些函數(shù)和方法進行數(shù)據(jù)分析，為企業(yè)管理提供準確、詳細的數(shù)據(jù)。項目20制作往來賬務(wù)管理表項目背景思維導(dǎo)圖項目實施項目拓展項目訓(xùn)練案例小結(jié)項目背景往來賬是指企業(yè)在生產(chǎn)經(jīng)營過程中發(fā)生業(yè)務(wù)往來而產(chǎn)生的應(yīng)收和應(yīng)付款項。在企業(yè)的財務(wù)管理中，往來賬務(wù)管理是一項很重要的工作。往來賬作為企業(yè)總資產(chǎn)的一個重要組成部分，直接影響到企業(yè)的資金使用、財務(wù)狀況結(jié)構(gòu)、財務(wù)指標分析等方面。本項目通過制作“往來賬務(wù)管理”工作簿介紹Excel2016在往來賬務(wù)管理方面的應(yīng)用，效果如圖5.71和圖5.72所示。思維導(dǎo)圖項目實施任務(wù)20-1新建工作簿，重命名工作表（1）啟動Excel2016，新建一個空白工作簿。（2）將新建的工作簿重命名為“往來賬務(wù)管理”，并將其保存在“D:\公司文檔\財務(wù)部”文件夾中。（3）將“Sheet1”工作表重命名為“應(yīng)收賬款明細表”。任務(wù)20-2創(chuàng)建“應(yīng)收賬款明細表”（1）選中“應(yīng)收賬款明細表”。（2）設(shè)置A1:G1單元格區(qū)域為“合并后居中”，輸入表格標題“應(yīng)收賬款明細表”，設(shè)置字體為“華文中宋”，字號為“18”。（3）按照圖5.73所示輸入表格的標題字段和基礎(chǔ)數(shù)據(jù)。項目實施任務(wù)20-3顯示應(yīng)收賬款期限這里，設(shè)定收款期為90天。（1）選中E3單元格。（2）輸入公式“=A3+90”，按【Enter】鍵確認。（3）選中E3單元格，鼠標拖曳填充柄至E19單元格，將公式復(fù)制到E4:E19單元格區(qū)域中，顯示出每筆賬務(wù)的“應(yīng)收款期限”，如圖5.74所示。項目實施任務(wù)20-4判斷應(yīng)收賬款是否到期（1）選中F3單元格。（2）單擊“公式”→“函數(shù)庫”→“插入函數(shù)”按鈕

，打開“插入函數(shù)”對話框。（3）從“選擇函數(shù)”列表中選擇“IF”函數(shù)，單擊“確定”按鈕，打開“函數(shù)參數(shù)”對話框。（4）輸入如圖5.76所示的參數(shù)。（5）單擊“確定”按鈕。（6）選中F3單元格，鼠標拖曳填充柄至F19單元格，將公式復(fù)制到F4:F19單元格區(qū)域中，判斷出每筆賬務(wù)應(yīng)收賬款是否到期。項目實施任務(wù)20-5統(tǒng)計“未到期金額”（1）選中G3單元格。（2）輸入公式“=IF(TODAY()>E3,0,D3)”，按【Enter】鍵確認。（3）選中G3單元格，鼠標拖曳填充柄至G19單元格，將公式復(fù)制到G4:G19單元格區(qū)域中，統(tǒng)計出每筆賬務(wù)“未到期金額”。任務(wù)20-6設(shè)置“應(yīng)收賬款明細表”表格式。（1）設(shè)置“應(yīng)收金額”和“未到期金額”兩列的數(shù)據(jù)為“貨幣”格式，且無貨幣符號。其余列的數(shù)據(jù)的對齊方式為“居中”。（2）設(shè)置第2行的標題字段的格式為“加粗、居中”，并為其設(shè)置“藍色，強調(diào)文字顏色1，淡色80%”的底紋。項目實施（3）設(shè)置第1行的行高為30、第2行的行高為22，其余各行的行高為18。（4）為A2:G19單元格區(qū)域添加“所有框線”的邊框。任務(wù)20-7賬款賬齡統(tǒng)計分析（1）選中Sheet2工作表，并將工作表重命名為“賬款賬齡分析”。（2）創(chuàng)建如圖5.79所示的“賬款賬齡分析”表框架。（3）定義名稱。（4）切換到“賬款賬齡分析”工作表，在D2單元格中輸入公式“=TODAY()”。按【Enter】鍵確認，獲取系統(tǒng)當(dāng)前日期。項目實施（5）計算信用期內(nèi)的客戶數(shù)量。在B4單元格中輸入公式“=SUM(IF(應(yīng)收賬款期限>=D2,1,0))”，然后按下【Ctrl】+【Shift】+【Enter】組合鍵計算數(shù)組公式的結(jié)果。（6）計算信用期內(nèi)的應(yīng)收金額。在C4單元格中輸入公式“=SUM(IF(應(yīng)收賬款期限>=D2,應(yīng)收金額,0))”，然后按下【Ctrl】+【Shift】+【Enter】組合鍵計算數(shù)組公式的結(jié)果。（7）計算超過期限1-30