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

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

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

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