Excel在會計與財務(wù)管理中的應(yīng)用 課件 項目5、6 工資管理、往來款項管理

Excel在會計與財務(wù)管理中的應(yīng)用項目五工資管理(1)理解員工組成部分、個稅、工資的計算等基本內(nèi)容；(2)掌握在Excel中建立管理單據(jù)表，計算員工工資和制作工資條；(3)掌握查詢和分析工資數(shù)據(jù)的操作；(4)學(xué)會應(yīng)用Excel解決實際案例中遇到的員工工資問題。學(xué)習(xí)目標工資管理涉及企業(yè)的人事管理和財務(wù)管理兩方面。當企業(yè)錄用一名員工后，企業(yè)的員工信息就會在人事管理部門進行登記，即該員工的基本情況，其中某些數(shù)據(jù)就會反映在工資管理中。這些數(shù)據(jù)可以由財務(wù)部門單獨建立，由人事部門提供。在不同的企業(yè)，工資管理方式是不相同的，員工信息表的創(chuàng)建方法也不盡相同，需要切合企業(yè)的生產(chǎn)特點。隨著經(jīng)濟社會的發(fā)展，很多企業(yè)的規(guī)模在不斷擴張，同時擁有的員工數(shù)量也日益龐大，如果采用原始的人工記錄和計算工資的方式，那么財會人員和人事人員的工作量就會非常大，工作難度加大，而在信息化電子時代背景下，可以采用計算機軟件進行工資的記錄和管理。情景引入如果公司采用釘釘軟件或者指紋打卡機等工具對員工進行考勤，則考勤數(shù)據(jù)可以在后臺導(dǎo)出，一鍵生成。這就減輕了工作負擔(dān)，提高了工資核算的效率，規(guī)范了工資的核算。本項目內(nèi)容從以下三個部分展開：創(chuàng)建工資管理單表、制作工資條、查詢和分析工資數(shù)據(jù)。情景引入目錄CONTENTS創(chuàng)建工資管理單表1制作工資條2查詢和分析工資數(shù)據(jù)3任務(wù)一創(chuàng)建工資管理單表知識準備通常在核算員工工資之前，要先獲取員工的基本信息，建立員工檔案。員工工資管理除了基本檔案之外，還涉及基本工資、考勤、獎金、社保、福利和加班等項目。本任務(wù)首先利用Excel建立基礎(chǔ)表格，然后利用Excel的公式和函數(shù)對這些項目進行統(tǒng)計和計算。知識準備根據(jù)企業(yè)需求，在人事錄用員工開始就要建立員工檔案，方便后續(xù)管理。員工檔案一般應(yīng)包括以下信息：員工編號、姓名、所屬部門、職位等級、聯(lián)系電話、郵箱、婚姻狀況、身份證號碼、家庭地址、基本工資、工資卡所屬銀行、工資卡卡號等，需根據(jù)公司的需求進行選擇。首先建立Excel表格，然后將員工的基本信息錄入表格中，此項工作即完成。1.建立員工檔案表知識準備員工考勤表是用于記錄和統(tǒng)計員工出勤狀態(tài)的表格。出勤狀態(tài)包括正常、遲到、早退、請假(病假、事假、婚假、喪假)等。依據(jù)國家相關(guān)法律法規(guī)和企業(yè)的規(guī)章制度，請假狀態(tài)對于工資核算的影響程度是不一樣的。通過創(chuàng)建考勤表，可為后續(xù)人事和財務(wù)在核算工資時提供考勤依據(jù)。2.創(chuàng)建考勤表知識準備“五險”就是通常說的社會保險，主要包括養(yǎng)老保險、醫(yī)療保險、失業(yè)保險、工傷保險和生育保險。2016年3月23日“十三五”規(guī)劃綱要提出，將生育保險和基本醫(yī)療保險合并實施。這意味著未來隨著生育保險和基本醫(yī)療保險的合并，人們熟悉的“五險一金”或?qū)⒆優(yōu)椤八碾U一金”。3.創(chuàng)建“五險一金”表知識準備《中華人民共和國勞動法》第九章“社會保險和福利”第七十二條規(guī)定：“用人單位和勞動者必須依法參加社會保險，繳納社會保險費?！鄙绫Ｓ蓡T工和企業(yè)共同承擔(dān)，企業(yè)承擔(dān)一部分，員工承擔(dān)一部分。員工承擔(dān)的一部分在企業(yè)發(fā)工資之前要予以扣除，由企業(yè)一并代繳相關(guān)部門。五險一金的繳納額度在每個地區(qū)都不同，以工資總額為基數(shù)?！耙唤稹敝傅氖亲》抗e金，是指國家機關(guān)和事業(yè)單位、國企、城鎮(zhèn)集體企業(yè)、外商投資企業(yè)、城鎮(zhèn)私營企業(yè)及其他城鎮(zhèn)企業(yè)和事業(yè)單位、民辦非企業(yè)單位、社會團體及其在職職工等繳存的長期住房儲蓄。3.創(chuàng)建“五險一金”表知識準備4.創(chuàng)建獎金、津貼表獎金和津貼是企業(yè)為了鼓勵員工采取的獎勵措施。獎金是支付給員工的超額勞動報酬和增收節(jié)支的勞動報酬，它與員工的表現(xiàn)息息相關(guān)。津貼是指補償職工在特殊條件下的勞動消耗及生活費額外支出的工資補充形式，如常見的生活補貼、交通補貼、住房補貼、高溫補貼等。在這里要用到Excel中的IF函數(shù)，其根據(jù)指定的條件判斷“真”“假”，根據(jù)邏輯計算的真假值返回相應(yīng)的內(nèi)容?？梢允褂煤瘮?shù)IF對數(shù)值和公式進行條件檢測。知識準備4.創(chuàng)建獎金、津貼表語法格式：IF(Logical_test,Value_if_true,Value_if_false)參數(shù)說明：?Logical_test：計算結(jié)果為TRUE或FALSE的任意值或表達式。?Value_if_true：Logical_test為TRUE時返回的值。?Value_if_false：Logical_test為FALSE時返回的值。知識準備5.創(chuàng)建個人所得稅表《中華人民共和國個人所得稅法》第一條規(guī)定：“在中國境內(nèi)有住所，或者無住所而一個納稅年度內(nèi)在中國境內(nèi)居住累計滿一百八十三天的個人，為居民個人。居民個人從中國境內(nèi)和境外取得的所得，依照本法規(guī)定繳納個人所得稅。在中國境內(nèi)無住所又不居住，或者無住所而一個納稅年度內(nèi)在中國境內(nèi)居住累計不滿一百八十三天的個人，為非居民個人。非居民個人從中國境內(nèi)取得的所得，依照本法規(guī)定繳納個人所得稅。納稅年度，自公歷一月一日起至十二月三十一日止?！敝R準備5.創(chuàng)建個人所得稅表第三條規(guī)定：“個人所得稅的稅率：(一)綜合所得，適用百分之三至百分之四十五的超額累進稅率；(二)經(jīng)營所得，適用百分之五至百分之三十五的超額累進稅率；(三)利息、股息、紅利所得，財產(chǎn)租賃所得，財產(chǎn)轉(zhuǎn)讓所得和偶然所得，適用比例稅率，稅率為百分之二十?！眰€人所得稅實施的方式是企業(yè)代扣代繳。代扣指的是企業(yè)在發(fā)放工資之前要把員工個人應(yīng)該承擔(dān)的所得稅先扣除，代繳指的是企業(yè)將從員工工資中扣除個稅一并交給稅務(wù)局。按月取得的工資、薪金所得計稅方法是按月預(yù)征，年終匯算清繳，稅率采用的是3%～45%七級超額累進稅率。知識準備5.創(chuàng)建個人所得稅表本任務(wù)中的案例采用的是收入超過5000元起征應(yīng)納稅額。應(yīng)納稅額的計算公式如下：應(yīng)納稅額＝應(yīng)納稅所得額×適用稅率－速算扣除數(shù)個人所得稅稅率表(月度稅率表)如表5-1所示。圖5-1

個人所得稅稅率表(月度稅率表)任務(wù)目標(1)在Excel中建立員工檔案表；(2)在Excel中創(chuàng)建考勤表；(3)在Excel中創(chuàng)建“五險一金”表；(4)在Excel中創(chuàng)建獎金、津貼表；(5)在Excel中創(chuàng)建個人所得稅表。任務(wù)資料福源公司是一家生產(chǎn)制造企業(yè)，目前公司員工的情況如表5-2所示。（完整表格查看教材98頁）任務(wù)操作(1)打開Excel，單擊“保存”按鈕，將Excel工作薄命名為“員工檔案表.xlsm”，如圖5-1所示。1.在Excel中建立員工檔案表

圖5-1新建并保存表格任務(wù)操作(2)在Sheet1工作表中輸入如圖5-2所示列標題。1.在Excel中建立員工檔案表

圖5-2輸入列標題任務(wù)操作(3)選中A1:L1單元格區(qū)域，選擇“開始”選項卡，執(zhí)行“對齊方式”組中的“合并后居中”命令，如圖5-3所示。1.在Excel中建立員工檔案表

圖5-3設(shè)置對齊方式任務(wù)操作(4)在A3單元格中輸入“0001"，將鼠標指針放在A3單元格右下角，當其呈實心十字時，按住Ctr鍵，向下拖動鼠標,自動填充序號(如果企業(yè)員工較多,則可以分部門建立,也可設(shè)置多位數(shù)編號)，如圖5-4所示。1.在Excel中建立員工檔案表

圖5-3設(shè)置對齊方式任務(wù)操作(5)輸入表格中需要登記的員工基本信息。手動輸入姓名;其后的信息可以先設(shè)置后直選擇，也可以手動輸入;重復(fù)的信息可以采用復(fù)制粘貼方式輸入。這里介紹先設(shè)置后根據(jù)需求直接進行選擇的方式。首先選中需要設(shè)置的列，即C3列，選擇“數(shù)據(jù)”選項卡，執(zhí)行“數(shù)據(jù)工具”組中的“數(shù)據(jù)有效性”命令,打開“數(shù)據(jù)有效性”對話框，選擇“設(shè)置”選項卡，在“允許”下拉列表中選擇“序列”,“來源”根據(jù)需求進行設(shè)置，這里假定公司擁有人事、財務(wù)、生產(chǎn)、研發(fā)、銷售和宣傳部門。注意，部門之間用英文逗號“,”隔開。單擊“確定”按鈕，數(shù)據(jù)設(shè)置完畢，即可直接使用單元格右側(cè)的下拉按鈕對員工所屬部門進行選擇，如圖5-5所示。1.在Excel中建立員工檔案表任務(wù)操作

圖5-5設(shè)置數(shù)據(jù)1.在Excel中建立員工檔案表任務(wù)操作(6)將后面選擇有限的幾列進行上面的操作,手動輸入獨立存在的信息。通過以上操作,員工的信息檔案表即建立完成，如圖5-6所示。1.在Excel中建立員工檔案表

圖5-6填充數(shù)據(jù)任務(wù)操作(1)打開Excel，將Sheet2工作表命名為“考勤”，在考勤表中輸入工作表標題、行標題、列標題以及員工的出勤情況，并對工作表格式進行簡單的設(shè)置，如圖5-7所示。2.在Excel中創(chuàng)建考勤表(2)在計算事假、曠工、病假和實際出勤天數(shù)時，需要使用COUNTIF函數(shù)進行自動求和。例如，要求實際出勤天數(shù)，可選中AN4單元格，選擇“公式”選項卡，執(zhí)行“函數(shù)庫”組中的“插入函數(shù)”命令，打開“插入函數(shù)”對話框，在“或選擇類別”下拉列表中選擇“統(tǒng)計”，找到COUNTIF函數(shù)，單擊“確定”按鈕，如圖5-8所示。任務(wù)操作2.在Excel中創(chuàng)建考勤表圖5-7設(shè)置考勤表任務(wù)操作2.在Excel中創(chuàng)建考勤表圖5-8設(shè)置公式任務(wù)操作(3)打開“函數(shù)參數(shù)”對話框，“Range”處選擇要進行統(tǒng)計的單元格區(qū)域，“Criteria”處為“√”(注意輸入英文格式的雙引號。另外，如果要統(tǒng)計的是字符，則須加雙引號；如果是數(shù)字，則不用)，單擊“確定”按鈕，即可計算出結(jié)果，如圖5-9所示。

圖5-9設(shè)置函數(shù)參數(shù)2.在Excel中創(chuàng)建考勤表任務(wù)操作(4)將鼠標指針放在AN4單元格右下角，當其呈實心十字時，按住鼠標左鍵向下拖動，進行數(shù)據(jù)的填充。事假、曠工、病假三列按照同樣的方法計算出結(jié)果。根據(jù)人事制度，在“備注”列可以計算出扣款金額(假設(shè)曠工扣100元，事假扣50元，病假扣30元，遲到早退1min扣1元)，如圖5-10所示。2.在Excel中創(chuàng)建考勤表任務(wù)操作2.在Excel中創(chuàng)建考勤表圖5-10填充數(shù)據(jù)任務(wù)操作(1)打開員工檔案表，將$heet3命名為“五險一金”，將員工信息檔案表中的“序號”和“姓名”列粘貼復(fù)制到“五險一金”工作表中，添加“繳費基數(shù)”“應(yīng)扣保險費”“應(yīng)扣公積金”列，如圖5-11所示。3.在Excel中創(chuàng)建“五險一金”表圖5-11新建工作表任務(wù)操作(2)打開“五險一金”工作表,輸入“繳費基數(shù)”“應(yīng)扣保險費”“應(yīng)扣公積金”三列數(shù)據(jù)。每個單位的繳費基數(shù)由其繳費標準確定，案例中假定繳費基數(shù)是3397.6元:應(yīng)扣保險費按照福源最低工資標準計算，應(yīng)扣金額為414.91元;應(yīng)扣住房公積金按照福源最低工資標準計算，應(yīng)扣金額為158元。將以上數(shù)據(jù)輸入表格中，如圖5-12所示。圖3-28“外部數(shù)據(jù)屬性”對話框3.在Excel中創(chuàng)建“五險一金”表任務(wù)操作(1)打開員工信息檔案表，將$heet4重命名為“獎金、津貼表”，將員工信息檔案表中的“序號”和“姓名”列復(fù)制粘貼到“獎金、津貼表”工作表中,添加“獎金”“津貼”列,如圖5-13所示。4.在Excel中創(chuàng)建獎金、津貼表

圖5-13新建工作表任務(wù)操作(2)在“獎金、津貼表”工作表中,根據(jù)公司人事制度,假設(shè)全勤獎獎金為500元,曠工、請假、遲到、早退均沒有獎金。選中C3單元格，選擇“公式”選項卡，執(zhí)行“函數(shù)庫”組中的“插入函數(shù)”命令，彈出“插入函數(shù)”對話框，找到正函數(shù)，單擊“確定”按鈕,如圖5-14所示。4.在Excel中創(chuàng)建獎金、津貼表

圖5-14選擇公式任務(wù)操作(3)打開“函數(shù)參數(shù)”對話框，在Logicaltest中輸入“考勤!AM4=考勤!AN4”;在Valueiftrue中,如果應(yīng)出勤天數(shù)等于實際出勤天數(shù),則應(yīng)該獎勵500元,所以輸入“500”;在Valueiffalse中,如果應(yīng)出勤天數(shù)不等于實際出勤天數(shù),則沒有獎金，入“0”，如圖5-15所示。(4)單擊“確定”按鈕，得出計算結(jié)果。將鼠標指針放在C3單元格右下角，當鼠標指針呈實心十字時，向下拖動鼠標，填充單元格，得出表格數(shù)據(jù)，則“獎金”列計算完畢，如圖5-16所示。4.在Excel中創(chuàng)建獎金、津貼表任務(wù)操作4.在Excel中創(chuàng)建獎金、津貼表

圖5-15設(shè)置函數(shù)參數(shù)

圖5-16填充數(shù)據(jù)任務(wù)操作4.在Excel中創(chuàng)建獎金、津貼表(5)假設(shè)根據(jù)人事規(guī)章制度，經(jīng)理級員工津貼的幅度為基本工資的10%，其他級員工津貼的幅度為基本工資的5%。選中D3單元格，選擇“公式”選項卡，執(zhí)行“函數(shù)庫”中的“插入函數(shù)”命令，打開“插入函數(shù)”對話框，找到正函數(shù)，單擊“確定”按鈕，如圖5-17所示。(6)打開“函數(shù)參數(shù)"對話框,在Logicaltest中輸入“員工信息檔案表!D3=”經(jīng)理”在Value_iftnue中，如果是經(jīng)理，則津貼為基本工資的10%，所以輸入“員工信息檔案表!L3*10%”;在Vlueiffalse中,如果不是經(jīng)理,則津貼為基本工資的5%,所以輸入“員工信息檔案表!L3*5%”，如圖5-18所示。任務(wù)操作4.在Excel中創(chuàng)建獎金、津貼表

圖5-17選擇公式

圖5-18設(shè)置函數(shù)參數(shù)任務(wù)操作(7)單擊“確定”按鈕，得出計算結(jié)果。將鼠標指針放在D3單元格右下角，當鼠標指針呈實心十字時，向下拖動鼠標，填充單元格，得出表格數(shù)據(jù)，則“津貼”列計算完畢,如圖5-19所示。

圖5-19填充數(shù)據(jù)4.在Excel中創(chuàng)建獎金、津貼表任務(wù)操作(1)在“員工檔案表”中新建工作表，命名為“個稅稅率表”，將Sheet5重命名為“個人所得稅計算表”，如圖5-20所示。5.在Excel中創(chuàng)建個人所得稅表

圖5-1新建并保存表格任務(wù)操作5.在Excel中創(chuàng)建個人所得稅表(2)選擇“個人所得稅計算表”工作表,將“員工信息檔案表”工作表中的“序號”和“姓名”列復(fù)制粘貼到工作表中,添加“工資合計”“應(yīng)納稅所得額”“稅率”“速算扣除數(shù)”“代扣個人所得稅額”列，如圖5-21所示。(3)計算工資合計。這里的工資合計指的是應(yīng)發(fā)工資，包括基本工資、獎金、津貼和扣除的款項。在C3單元格中輸入公式“=員工信息檔案表!L3+考勤!A04+獎金、津貼表!C3+獎金、津貼表!D3-五險一金!D3-五險一金!E3”，按Enter鍵得出結(jié)果。將鼠標指針放在C3單元格右下角，當其呈實心十字時向下拖動填充數(shù)據(jù)，如圖5-22所示。任務(wù)操作5.在Excel中創(chuàng)建個人所得稅表

圖5-21輸入數(shù)據(jù)

圖5-22計算工資合計任務(wù)操作5.在Excel中創(chuàng)建個人所得稅表(4)計算應(yīng)納稅所得額(本案例中不涉及專項扣除因素)。在D3單元格中輸入公式“=IF(C3<5000,0,C3-5000)”，按Enter鍵，得出結(jié)果。將鼠標指針放在D3單元格右下角，當其呈實心十字時向下拖動填充數(shù)據(jù)，如圖5-23所示。

圖

5-23計算應(yīng)納稅所得額任務(wù)操作5.在Excel中創(chuàng)建個人所得稅表(5)計算稅率(本案例中不涉及專項扣除因素)。在E3單元格中輸入公式“=IF(個人所得稅計算表!D3=0,0,LOOKUP(個人所得稅計算表!D3,個稅稅率表!$C$4:$C$11,個稅稅率表!$D$4:$D$11))”，按Enter鍵，得出結(jié)果。將鼠標指針放在E3單元格右下角，當其呈實心十字時向下拖動填充數(shù)據(jù)，如圖5-24所示。任務(wù)操作5.在Excel中創(chuàng)建個人所得稅表

圖5-24計算稅率任務(wù)操作5.在Excel中創(chuàng)建個人所得稅表(6)計算速算扣除數(shù)。在F3單元格中輸入公式“=F(個人所得稅計算表!E3=0,0.LOOKUP(個人所得稅計算表!E3,個稅稅率表!$D$4:$D$11,個稅稅率表!$F$4:$F$11))”，按Enter鍵，得出結(jié)果。將鼠標指針放在F3單元格右下角，當其呈實心十字時向下拖動填充數(shù)據(jù)，如圖5-25所示。(7)計算代扣個人所得稅額。在G3單元格中輸入公式“=D3*E3*0.01-F3”(輸入的公式中之所以乘以0.01，是因為稅率在表格中沒有用“%”表示)，按Enter鍵，得出結(jié)果。將鼠標指針放在G3單元格右下角，當其呈實心十字時向下拖動填充數(shù)據(jù)，如圖5-26所示。任務(wù)操作5.在Excel中創(chuàng)建個人所得稅表

圖5-25計算速算扣除數(shù)

圖5-26計算代扣個人所得稅額任務(wù)二制作工資條知識準備工資條是伴隨著工資的發(fā)放而產(chǎn)生的，是員工工資組成的明細表，是發(fā)放給員工供員工查看的工資的明細條。職工工資構(gòu)成往往有很多項，如姓名、職稱、基本工資、崗位工資等;每月發(fā)工資時要向職工提供一個包含工資各構(gòu)成部分的項目名稱和具體數(shù)值的工資條，且打印工資條時要求在每個職工的工資條間有一空行便于彼此裁開。知識準備因此，工資表的基本形式應(yīng)為三行，即標題、工資數(shù)據(jù)、空白行，如圖5-27所示。

圖5-27工資條使用Excel制作工資條有很多方法，如排序法制作工資條、插行法制作工資條、函數(shù)法制作工資條等。本任務(wù)主要介紹函數(shù)法制作工資條。這里主要介紹三個函數(shù)，即VLOOKUP所數(shù)、MOD和ROW所數(shù)。知識準備VLOOKUP函數(shù)是指在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值，并由此返回表格或數(shù)組當前行中指定列處的數(shù)值。函數(shù)語法:LOOKUP(Lookupvalue,Lookupvector,Resultvector)參數(shù)說明:·Lookup_value:要查找的值?！ookupvector:要查找的范圍?！esultvector:要獲得的值。需要注意的是，VLOOKUP函數(shù)的查詢方式為二分法查詢。MOD函數(shù)是一個求余函數(shù)，即求兩個數(shù)值表達式做除法運算后的余數(shù)。知識準備函數(shù)語法:MOD(Number,Divisor)參數(shù)說明:·Number:被除數(shù)?！ivisor:除數(shù)。在Oracle中，如果Divisor為0，則函數(shù)直接返回Number。特別注意:在Excel中，MOD函數(shù)用于返回兩數(shù)相除的余數(shù)，返回結(jié)果的符號與除數(shù)(Divisor)的符號相同。ROW函數(shù)用來確定光標的當前行位置，并返回光標的當前行位置。知識準備函數(shù)語法:ROW(Reference)參數(shù)說明:Refrence:需要得到其行號的單元格或單元格區(qū)域。如果省略Reference，則假定是對ROW函數(shù)所在單元格的引用。如果Reference為一個單元格區(qū)域，并且ROW函數(shù)作為垂直數(shù)組輸入，則ROW函數(shù)將Reference的行號以垂直數(shù)組的形式返回。Reference不能引用多個單元格區(qū)域。任務(wù)目標(1)利用VLOOKUP函數(shù)制作工資條；(2)利用MOD和ROW函數(shù)制作工資條。福源公司員工的基本工資情況如圖5-27所示，公司有嚴格的考勤、獎金、津貼制度，同時只要入職通過試用期的員工，企業(yè)都給員工購買五險一金，繳納基數(shù)和比例在具體的每一步業(yè)務(wù)操作中。根據(jù)下面業(yè)務(wù)操作完成學(xué)習(xí)任務(wù)。任務(wù)資料任務(wù)操作1.利用VLOOKUP函數(shù)制作工資條(1)打開員工檔案表，在“個稅稅率表”工作表右側(cè)新建一個“工資明細表”工作表,將序號、姓名、基本工資、獎金、津貼、代扣個人所得稅額、五險一金費、考勤扣款等前面計算過的涉及工資核算的所有信息復(fù)制粘貼到本工作表中，增加“應(yīng)發(fā)工資”和“實發(fā)工資”列，如圖5-28所示。

圖5-28輸入數(shù)據(jù)任務(wù)操作1.利用VLOOKUP函數(shù)制作工資條(2)計算應(yīng)發(fā)工資，應(yīng)發(fā)工資=基本工資+獎金+津貼-考勤扣款。在L3單元格中輸入公式“=E3+F3+I3+J3”，按Enter鍵，得出結(jié)果。將鼠標指針放在L3單元格右下角，當其呈實心十字時向下拖動填充數(shù)據(jù)，如圖5-29所示。(3)計算實發(fā)工資，實發(fā)工資=應(yīng)發(fā)工資-代扣的五險一金-代扣的個稅。在M3單元格中輸入公式“=ROUND(L3-G3-H3-K3.2)"(ROUND函數(shù)用于設(shè)置保留的小數(shù)點位數(shù))，按Enter鍵，得出結(jié)果。將鼠標指針放在M3單元格右下角，當其呈實心十字時向下拖動填充數(shù)據(jù)，如圖5-30所示。任務(wù)操作

圖5-29計算應(yīng)發(fā)工資

圖5-30計算實發(fā)工資1.利用VLOOKUP函數(shù)制作工資條任務(wù)操作(4)在“工資明細表”工作表右側(cè)新建一個“工資條”工作表，在該工作表中插入工資發(fā)放信息和工資條標題行，并進行格式的設(shè)置，如圖5-31所示(這里的表格建立在工資明細表基礎(chǔ)上，所以列標題可以直接復(fù)制粘貼)。

圖5-31輸入基礎(chǔ)數(shù)據(jù)1.利用VLOOKUP函數(shù)制作工資條任務(wù)操作(5)在A3單元格中輸入“0001",在B3單元格中輸入VLOOKUP函數(shù)公式“=VLOOKUP($A3,工資明細表!$A:B,COLUMN(B:B),FALSE)”，按Enter鍵，得出結(jié)果。將鼠標指針放在B3單元格右下角，當其呈實心十字時向右拖動填充單元格，完成數(shù)據(jù)的填充，如圖5-32所示。

圖5-32輸入公式1.利用VLOOKUP函數(shù)制作工資條任務(wù)操作(6)選中A4:M4單元格區(qū)域，將鼠標指針放在M4單元格右下角，當其呈實心十字時向下拖動填充，完成工資條的數(shù)據(jù)填充，如圖5-33所示。

圖5-33填充數(shù)據(jù)1.利用VLOOKUP函數(shù)制作工資條任務(wù)操作(1)同VLOOKUP函數(shù)一樣，首先設(shè)置完成“工資明細表”工作表，然后在其右側(cè)新建一個“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的行為包括職工編號、姓名等各項工資數(shù)據(jù)的行。將鼠標指針放在A1單元格右下角，向右拖動填充單元格，如圖5-34所示。2.利用MOD和ROW函數(shù)制作工資條任務(wù)操作2.利用MOD和ROW函數(shù)制作工資條

圖5-34輸入公式任務(wù)操作(3)向下拖動A1:M1單元格區(qū)域，直到顯示所有員工的工資條數(shù)據(jù)，如圖5-35所示。2.利用MOD和ROW函數(shù)制作工資條

圖5-35填充數(shù)據(jù)任務(wù)三查詢和分析工資數(shù)據(jù)知識準備為了方便公司財務(wù)人員查詢每一個員工的工資詳情，可以建立一個員工工資查詢表，只要在工資查詢表中輸入員工的某一個信息，就可以查看員工的所有工資信息。所以，本任務(wù)的第一個內(nèi)容是建立一個可供查詢的員工工資查詢表。知識準備員工工資是企業(yè)成本的重要組成部分。本任務(wù)的第二個內(nèi)容是在現(xiàn)有的工資基礎(chǔ)上，采用圖表的形式對員工工資進行分析，提高公司對員工工資的管理效率，完善公司的工資管理制度，找到節(jié)約公司成本的途徑，從而提高公司的利潤空間。任務(wù)目標(1)建立員工工資查詢表；(2)掌握員工工資查詢表的查詢操作；(3)建立員工工資查詢的圖和表。本任務(wù)的任務(wù)資料是任務(wù)一和任務(wù)二的操作結(jié)果。任務(wù)資料任務(wù)操作(1)打開員工檔案表，在最后一個工作表的右側(cè)新建一個“工資查詢表”工作表，如圖5-36所示(該表格中的行標題可以從工資明細表中進行復(fù)制粘貼，標題行要進行“合并單元格”設(shè)置)。1.查詢員工工資數(shù)據(jù)

圖5-36新建并設(shè)置表格任務(wù)操作1.查詢員工工資數(shù)據(jù)【提示】行標題除了可以采用復(fù)制粘貼的方式輸入外，還可以使用函數(shù)輸入。INDEX函數(shù)的作用是返回表或區(qū)域中的值或?qū)χ档囊?，其有兩種形式:數(shù)組形式和引用形式。其中，數(shù)組形式通常返回數(shù)值或數(shù)值數(shù)組;引用形式通常返回引用，返回特定行和列交叉處單元格的引用。如果該引用是由非連續(xù)選中區(qū)域組成的,則可以選擇要用作查找范圍的選中區(qū)域。（完整提示請查看教材116-117頁）任務(wù)操作(2)在美化過程中，可以在“開始”選項卡的“字體”組進行字體、大小、顏色、加粗、斜向、表格方框等基礎(chǔ)設(shè)置，在“對齊方式”組進行字體是左對齊還是右對齊或是居中等基本設(shè)置。1.查詢員工工資數(shù)據(jù)任務(wù)操作(3)建立了工資查詢表的基本框架后，接下來就是引入具體數(shù)據(jù)。在B3單元格中輸入公式“=VLOOKUP($B$2,工資明細表!$A$3:$M$24.2.FALSE)”,并向下填充至B14單元格,如圖5-38所示。圖5-38輸入公式1.查詢員工工資數(shù)據(jù)任務(wù)操作(4)將公式“=VLOOKUP($B$2.工資明細表!$A$3:$M$24.2.FALSE)”中的數(shù)字“2依次改為3~13，如圖5-39所示。(5)上述表格設(shè)置好之后，只要在B2單元格中輸入員工對應(yīng)的序號，按Enter鍵，即可查詢相應(yīng)序號的員工的工資具體情況，如圖5-40所示。1.查詢員工工資數(shù)據(jù)任務(wù)操作1.查詢員工工資數(shù)據(jù)

圖5-40查詢員工工資結(jié)果

圖5-39填充數(shù)據(jù)任務(wù)操作(1)復(fù)制“工資明細表”工作表，將新工作表命名為“工資排序表”。選中該工作表中的任意單元格,選擇“數(shù)據(jù)”選項卡,執(zhí)行“排序和篩選”組中的“排序”命令,如圖5-41所示。2.分析員工工資數(shù)據(jù)1)對員工工資數(shù)據(jù)排序

圖5-41執(zhí)行“排序”命令任務(wù)操作2.分析員工工資數(shù)據(jù)1)對員工工資數(shù)據(jù)排序(2)打開“排序”對話框，設(shè)置主要關(guān)鍵字為“實發(fā)工資”，排序依據(jù)為“數(shù)值”，次序為“升序”,單擊“確定”按鈕(如圖5-42所示),即可得出實發(fā)工資的升序排序結(jié)果(也可以選擇降序排序)。

圖5-42設(shè)置主要關(guān)鍵字任務(wù)操作2.分析員工工資數(shù)據(jù)1)對員工工資數(shù)據(jù)排序(3)單擊“添加條件”按鈕，可以設(shè)置次要關(guān)鍵字，如圖5-43所示。設(shè)置次要關(guān)鍵字為“姓名”，排序依據(jù)為“數(shù)值”，次序為“升序”，單擊“確定”按鈕，即可得到以實發(fā)工資為主要關(guān)鍵字，以姓名為次要關(guān)鍵字的工資排序情況。

圖5-43設(shè)置次要關(guān)鍵字【提示】除了上述的排列方法外，還可以使用RANK函數(shù)進行排序。RANK函數(shù)最常用于求某一個數(shù)值在某一區(qū)域內(nèi)的排名。函數(shù)語法:RANK(Number,Ref,[Order])參數(shù)說明:·Number:需要排名的那個數(shù)值或者單元格名稱(單元格內(nèi)必須為數(shù)字)。·Ref:排名的參照數(shù)值區(qū)域。·Order:為0和1，默認不用輸入，得到的就是從大到小的排名。若想求倒數(shù)第幾，Order的數(shù)值應(yīng)為1。RANK是比較常用的一個排序函數(shù)。任務(wù)操作2.分析員工工資數(shù)據(jù)1)對員工工資數(shù)據(jù)排序任務(wù)操作2.分析員工工資數(shù)據(jù)2)建立員工工資統(tǒng)計圖該操作可以在“工資明細表”工作表中進行,也可以在“工資排序表”工作表中進行,這里選擇在“工資明細表”工作表中進行。(1)打開工資排序表，選中需要用圖形進行分析的數(shù)據(jù)。由于這里只反映兩項(實發(fā)工資和姓名)，因此選中這兩列。選擇“插入”選項卡，在“圖表”組中可以根據(jù)需求選擇條形圖、折線圖、餅狀圖等。這里以折線圖舉例，如圖5-44所示。任務(wù)操作2.分析員工工資數(shù)據(jù)2)建立員工工資統(tǒng)計圖

圖5-44選中數(shù)據(jù)和圖表形式任務(wù)操作2.分析員工工資數(shù)據(jù)2)建立員工工資統(tǒng)計圖(2)選擇折線圖后,表中就會出現(xiàn)相應(yīng)的圖形。選中圖形,即可激活“圖表工具”選項卡,在該選項卡下有三個子選項卡，即“設(shè)計”“布局”“格式”。在“設(shè)計”子選項卡中可以對已經(jīng)形成的折線圖進行顏色等基本設(shè)置，在“布局”子選項卡中可以對已經(jīng)形成的折線圖進行橫縱坐標、圖例等基礎(chǔ)設(shè)置，在“格式”子選項卡中可以對已經(jīng)形成的折線圖進行形狀樣式、藝術(shù)字等的設(shè)置，如圖5-45所示。在“圖表工具”選項卡中可以對折線圖進行所有的美化工作。任務(wù)操作2.分析員工工資數(shù)據(jù)2)建立員工工資統(tǒng)計圖

圖5-45設(shè)置折線圖任務(wù)操作2.分析員工工資數(shù)據(jù)2)建立員工工資統(tǒng)計圖通過折線圖可以清晰地顯示工資的起伏狀況、工資差距、工資主要集中在哪個水平等，以幫助企業(yè)優(yōu)化工資制度。本項目主要介紹了員工信息的輸入，建立員工檔案;企業(yè)考勤表、五險一金表、獎金津貼表、個稅表、工資明細表、工資查詢表、工資條等的設(shè)置和相關(guān)函數(shù)的運用;企業(yè)對員工工資信息進行查詢和分析的工具。通過本項目的學(xué)習(xí),學(xué)習(xí)者可學(xué)會建立相應(yīng)的表格,并熟練運用VLOOKUP、IF、MOD、ROW、COUNTA、RAND等函數(shù)。項目小結(jié)福興公司2021年2月1日的員工情況如表5-3所示。本公司1月份員工的獎金情況如下:表5-3中的前8名員工獎金是500元，9~11名員工獎金是800元，其余員工獎金是1000元。津貼情況依次是350、250、250、300、250、300、250、250、375、300.300、500、400、400、400元。根據(jù)國家相關(guān)法律規(guī)定，員工需要和企業(yè)共同承擔(dān)的保險部分是養(yǎng)老保險、醫(yī)療保險和失業(yè)保險，這里假設(shè)員工需要承擔(dān)的養(yǎng)老保險是基本工資的8%，醫(yī)療保險是基本工資的2%，失業(yè)保險是基本工資的1%。（完整內(nèi)容查看教材122-123頁）項目練習(xí)項目練習(xí)要求:1.建立員工的檔案表。2.建立員工的獎金表和津貼表。3.建立員工個人部分應(yīng)當承擔(dān)的保險費用表。4.自行設(shè)計和建立企業(yè)考勤表，并將考勤扣款填入表格中。5.計算員工的工資合計，建立個人所得稅表。6.計算員工的實發(fā)工資，建立工資明細表。7.設(shè)置工資條。8.建立工資查詢表。9.建立工資由低到高的排序表，并建立工資分析的條形統(tǒng)計圖。謝謝觀看Excel在會計與財務(wù)管理中的應(yīng)用項目六往來款項管理(1)理解應(yīng)收賬款、壞賬準備和應(yīng)付賬款的基本概念與理論知識；(2)掌握Excel中管理與分析應(yīng)收賬款、應(yīng)付賬款的操作，建立分析表格和分析圖；(3)掌握Excel中提取和分析壞賬的操作。學(xué)習(xí)目標往來款項(accountcurent)是指企業(yè)在生產(chǎn)經(jīng)營過程中發(fā)生的各種應(yīng)收、應(yīng)付款項及預(yù)收、預(yù)付款項。本項目主要介紹應(yīng)收賬款以及相應(yīng)的壞賬準備、應(yīng)付賬款在Excel中的應(yīng)用和操作。應(yīng)收賬款是伴隨企業(yè)的銷售行為發(fā)生而形成的一項債權(quán)，通常要經(jīng)過一段時間之后才能收回。企業(yè)在應(yīng)收賬款收回之前，該筆款項不僅不會增值，而且還可能存在一定的風(fēng)險,這種風(fēng)險指的是因為各種情況導(dǎo)致資金收不回來，如購貨方意外死亡、破產(chǎn)無力支付等。發(fā)生應(yīng)收賬款時就會存在收不回來的情況，這種情況就是壞賬。壞賬準備是指企業(yè)無法收回或收回的可能性極小的應(yīng)收賬款，是應(yīng)收賬款的備抵賬戶。企業(yè)對壞賬損失的核算采用備抵法。在備抵法下，企業(yè)每期末要估計壞賬損失，設(shè)置“壞賬準備”賬戶。情景引入備抵法是指采用一定的方法按期(至少每年末)估計壞賬損失，提取壞賬準備并轉(zhuǎn)作當期費用；實際發(fā)生壞賬時，直接沖減已計提壞賬準備，同時轉(zhuǎn)銷相應(yīng)的應(yīng)收賬款余額的一種處理方法。應(yīng)付賬款是伴隨企業(yè)的購貨行為發(fā)生而形成的一項債務(wù)，用以核算企業(yè)因購買材料、商品和接受勞務(wù)供應(yīng)等經(jīng)營活動經(jīng)過一段時間之后應(yīng)支付尚未支付的款項。這是買賣雙方在購銷活動中由于取得物資與支付貨款在時間上不一致而產(chǎn)生的負債。下面以福源公司在某段時間內(nèi)發(fā)生的應(yīng)收賬款、應(yīng)付賬款明細數(shù)據(jù)為例，介紹如何按賬齡分別統(tǒng)計應(yīng)收賬款和應(yīng)付賬款、如何使用圖表分析應(yīng)收賬款和應(yīng)付賬款、如何根據(jù)賬齡提取壞賬準備并進行壞賬分析等。情景引入目錄CONTENTS應(yīng)收賬款的管理與分析1壞賬提取與分析2應(yīng)付賬款的統(tǒng)計3任務(wù)一應(yīng)收賬款的管理與分析知識準備應(yīng)收賬款是指企業(yè)在正常的經(jīng)營過程中因銷售商品、產(chǎn)品、提供勞務(wù)等業(yè)務(wù)而應(yīng)向購買單位收取的款項，包括應(yīng)由購買單位或接受勞務(wù)單位負擔(dān)的稅金、代購買方墊付的各種運雜費等。加強對應(yīng)收賬款的管理已經(jīng)成為企業(yè)財務(wù)管理的一項重要工作，在進行應(yīng)收賬款管理之前，首先需要建立應(yīng)收賬款明細表;然后建立應(yīng)收賬款賬齡分析表，加強應(yīng)收賬款的管理;最后，為了方便數(shù)據(jù)對比，可以建立賬齡統(tǒng)計分析圖。任務(wù)目標(1)建立應(yīng)收賬款明細表；(2)按賬齡統(tǒng)計應(yīng)收賬款；(3)建立應(yīng)收賬款賬齡統(tǒng)計分析圖。任務(wù)資料福源公司是一家生產(chǎn)制造企業(yè),本公司日常合作的企業(yè)如表6-1所示。自2020年以來，公司的應(yīng)收賬款數(shù)據(jù)如表6-2所示。任務(wù)資料任務(wù)操作(1)啟動Excel2010，創(chuàng)建一個新的工作簿，命名為“應(yīng)收賬款”。將Sheetl工作表命名為“客戶代碼”，將公司的客戶信息輸入該表中，如圖6-1所示。1.建立應(yīng)收賬款明細表

圖6-1新建工作表任務(wù)操作(2)將Sheet2工作表重命名為“應(yīng)收賬款明細表”，根據(jù)實際業(yè)務(wù)發(fā)生情況在其中輸入應(yīng)收賬款明細數(shù)據(jù)，并輸入列標題，即交易日期、客戶代碼、客戶名稱、應(yīng)收金額、本期已收金額、未收款金額、付款期限。將A列設(shè)置為“長日期”格式，將D、E、F三列設(shè)置為“貨幣”格式，將G列設(shè)置為自定義“0”天””格式，如圖6-2所示。根據(jù)實際業(yè)務(wù)發(fā)生情況將數(shù)據(jù)輸入表格中，如圖6-3所示。1.建立應(yīng)收賬款明細表任務(wù)操作

圖6-2設(shè)置數(shù)據(jù)格式1.建立應(yīng)收賬款明細表

圖6-3輸入數(shù)據(jù)任務(wù)操作(3)引用“客戶代碼”工作表中的“公司名稱”列數(shù)據(jù)。在“應(yīng)收賬款明細表”工作表C2單元格中輸入公式“=VLOOKUP(B2,客戶代碼!$A$2:$B$16.2.FALSE)”,按Enter鍵。將鼠標指針放在C2單元格右下角,向下拖動填充柄填充單元格,完成數(shù)據(jù)的錄入,如圖6-4所示(這種方法適用于公司重復(fù)的業(yè)務(wù)較多的情況，如果公司業(yè)務(wù)很少是重復(fù)的，則不用建立客戶代碼，直接錄入即可)。1.建立應(yīng)收賬款明細表任務(wù)操作

圖6-4填充“客戶名稱”列1.建立應(yīng)收賬款明細表任務(wù)操作(4)假設(shè)本期已收金額都是應(yīng)收金額的10%，在E2單元格中輸入“=D2*10%”，按Enter鍵，將鼠標指針放在E2單元格右下角，向下拖動填充柄填充單元格，完成數(shù)據(jù)的錄入，如圖6-5所示。1.建立應(yīng)收賬款明細表

圖6-5填充“本期已收金額”列任務(wù)操作

圖6-6填充“未收款金額”列(5)計算未收款金額。在F2單元格中輸入“=D2-E2”，按Enter鍵，將鼠標指針放在F2單元格右下角，向下拖動填充柄填充單元格，完成數(shù)據(jù)的錄入，此時整張應(yīng)收賬款明細表即完成，如圖6-6所示。1.建立應(yīng)收賬款明細表任務(wù)操作隨著經(jīng)濟的發(fā)展，賒銷經(jīng)濟行為也逐漸興起，企業(yè)應(yīng)收賬款總量呈持續(xù)上升趨勢，并逐漸成為制約企業(yè)健康、穩(wěn)定發(fā)展的主要“瓶頸”，所以企業(yè)應(yīng)當加強對應(yīng)收賬款的管理。為了方便管理，很多企業(yè)會選擇建立應(yīng)收賬款賬齡分析表。賬齡分析表是以賬齡為序?qū)?yīng)收賬款分類列示的表格，可較全面地顯示應(yīng)收賬款賬齡的分布情況，以此來判斷企業(yè)應(yīng)收賬款的總體質(zhì)量以及不同客戶的信用狀況，為企業(yè)后續(xù)賒銷政策和賒銷判斷提供依據(jù)。通過賬齡分析表可以了解不同客戶應(yīng)收賬款發(fā)生時間的長短，反映不同客戶的信用狀況、拖欠情況等。拖欠賬款時間越長，發(fā)生壞賬的可能性越大。2.按賬齡統(tǒng)計應(yīng)收賬款任務(wù)操作建立應(yīng)收賬款賬齡分析表的方式主要有兩種，一種是根據(jù)企業(yè)需求設(shè)置表格，運用函數(shù)公式進行計算;另外一種是制作數(shù)據(jù)透視圖。這里主要介紹采用數(shù)據(jù)透視圖的形式制作應(yīng)收賬款賬齡分析表，感興趣的學(xué)習(xí)者也可以自行研究運用函數(shù)公式來制作應(yīng)收賬款賬齡分析表。2.按賬齡統(tǒng)計應(yīng)收賬款任務(wù)操作(1)計算賬齡。在“應(yīng)收賬款明細表”工作表中新增一列“賬齡”，在H2單元格中輸入公式“=TODAYO)-A2”，按Enter鍵，得出數(shù)據(jù)。將鼠標指針放在H2單元格右下角，向下拖動填充柄填充數(shù)據(jù)，如圖6-7所示(如果計算得出的結(jié)果是日期或者其他形式，則右擊，在彈出的快捷菜單中選擇“設(shè)置單元格格式”命令，把單元格格式改為常規(guī)或者文本即可)。2.按賬齡統(tǒng)計應(yīng)收賬款(2)創(chuàng)建數(shù)據(jù)透視表。選中“客戶名稱”列，選擇“插入”選項卡，執(zhí)行“表格”組中的“數(shù)據(jù)透視表”命令,在打開的“創(chuàng)建數(shù)據(jù)透視表”對話框中設(shè)置數(shù)據(jù)，單擊“確定按鈕，如圖6-8所示。任務(wù)操作2.按賬齡統(tǒng)計應(yīng)收賬款

圖6-7計算賬齡

圖6-8創(chuàng)建數(shù)據(jù)透視表任務(wù)操作2.按賬齡統(tǒng)計應(yīng)收賬款(3)設(shè)置數(shù)據(jù)透視表字段。設(shè)置行字段為“客戶名稱”，列字段為“賬齡”，數(shù)值字段為“未收款金額”，如圖6-9所示。

圖

6-9

設(shè)置數(shù)據(jù)透視表字段任務(wù)操作2.按賬齡統(tǒng)計應(yīng)收賬款(4)設(shè)置完成就可以得到數(shù)據(jù)透視圖，從圖中可以清晰地看出應(yīng)收賬款的欠賬情況(還可以進行數(shù)據(jù)設(shè)置，選中數(shù)據(jù)透視表中賬期為“371”的單元格，右擊，在彈出的快捷菜單中選擇“創(chuàng)建組”命令，設(shè)置賬期區(qū)間，如圖6-10所示)，如圖6-11所示。

圖

6-10選擇“創(chuàng)建組”命令任務(wù)操作2.按賬齡統(tǒng)計應(yīng)收賬款

圖6-11數(shù)據(jù)透視圖效果任務(wù)操作3.建立應(yīng)收賬款賬齡統(tǒng)計分析圖為了使應(yīng)收賬款的信息更為直觀，還可以建立各種圖形。采用圖形不僅使數(shù)據(jù)更直觀，同時可以使數(shù)據(jù)更具有對比性。這里制作兩個圖形，用條形圖反映未收款金額，用餅圖反映賬齡。(1)選中C列和H列,選擇“插入”選項卡,單擊“圖表”組右下角的啟動器,打開“插入圖表”對話框，選擇“餅圖”，單擊“確定”按鈕，如圖6-12所示。任務(wù)操作3.建立應(yīng)收賬款賬齡統(tǒng)計分析圖

圖6-12選擇“餅圖”任務(wù)操作3.建立應(yīng)收賬款賬齡統(tǒng)計分析圖(2)單擊形成的餅圖(形成的餅圖可以隨意移動)，會激活“圖表工具”選項卡，在其下有三個子選項卡,即“設(shè)計”“布局”“格式”。通過“設(shè)計”子選項卡,可以進行圖表顏色、圖表布局等(在圖上是否顯示百分比)的選擇;通過“布局”子選項卡,可以進行圖表標題、數(shù)據(jù)標簽、圖例等的選擇;通過“格式”子選項卡，可以進行字體、顏色、形狀等的選擇,如圖6-13所示。任務(wù)操作3.建立應(yīng)收賬款賬齡統(tǒng)計分析圖

圖6-13美化餅圖任務(wù)操作3.建立應(yīng)收賬款賬齡統(tǒng)計分析圖(3)選中C列和F列,選擇“插入”選項卡,單擊“圖表”組右下角的啟動器,打開“插入圖表”對話框，選擇“條形圖”，單擊“確定”按鈕，如圖6-14所示。

圖6-14選擇“條形圖”任務(wù)操作3.建立應(yīng)收賬款賬齡統(tǒng)計分析圖(4)單擊形成的條形圖，會激活“圖表工具”選項卡，在其下有三個子選項卡，即“設(shè)計”“布局”“格式”。通過“設(shè)計”子選項卡，可進行圖表顏色、圖表布局等(在圖上是否顯示百分比)的選擇;通過“布局”子選項卡，可進行圖表標題、數(shù)據(jù)標簽圖例、橫縱坐標、坐標軸、網(wǎng)格線、背景等的選擇;通過“格式”子選項卡，可進行字體、顏色、形狀等的選擇，如圖6-15所示(這一步主要是美化工作，形成的條形圖可以隨意移動)。任務(wù)操作3.建立應(yīng)收賬款賬齡統(tǒng)計分析圖

圖6-15美化條形圖任務(wù)二壞賬提取與分析知識準備當應(yīng)收賬款收不回時，壞賬就產(chǎn)生了。壞賬損失是指企業(yè)未收回的應(yīng)收賬款、經(jīng)批準列入損失的部分。提取壞賬準備的企業(yè)也在本賬戶反映，發(fā)生壞賬時直接抵減壞賬準備。不提取壞賬準備的企業(yè)發(fā)生壞賬時，直接在本賬戶核算。企業(yè)的壞賬損失應(yīng)根據(jù)《財政部關(guān)于建立健全企業(yè)應(yīng)收款項管理制度的通知》(財企(2002)513號)的規(guī)定確認。壞賬損失及其核算是應(yīng)收賬款核算的一個重要方面。知識準備一筆應(yīng)收賬款在什么時候才能被確認為壞賬，其條件通常是由會計準則或制度給出的。不論會計準則或制度如何變化，在會計實務(wù)中，壞賬的確認都要遵循財務(wù)報告的基本目標和會計核算的一般原則，盡量做到真實、準確,切合本單位的實際。一般來說,應(yīng)收賬款符合下列條件之一的,就應(yīng)將其確認為壞賬:(1)債務(wù)人死亡，以其遺產(chǎn)清償后仍然無法收回的賬款。(2)債務(wù)人破產(chǎn)，以其破產(chǎn)財產(chǎn)清償后仍然無法收回的賬款。(3)債務(wù)人較長時期內(nèi)未履行其償債義務(wù)，并有足夠的證據(jù)表明無法收回或收回可能性極小的賬款。知識準備上述三個條件中的每一個條件都是充分條件，其中第三個條件需要會計人員做出職業(yè)判斷。我國現(xiàn)行制度規(guī)定，上市公司壞賬損失的決定權(quán)在公司董事會或股東大會。壞賬損失的核算方法有兩種:一是直接轉(zhuǎn)銷法，二是備抵法。直接轉(zhuǎn)銷法是指在壞賬損失實際發(fā)生時,直接借記“資產(chǎn)減值損失-壞賬損失"科目，貸記“應(yīng)收賬款”科目。這種方法核算簡單，不需要設(shè)置“壞賬準備”科目。關(guān)于直接轉(zhuǎn)銷法，我們還應(yīng)掌握以下兩個要點:第一,該法不符合權(quán)責(zé)發(fā)生制和配比原則;第二，在該法下,如果已沖銷的應(yīng)收賬款以后又收回,應(yīng)做兩筆會計分錄,即先借記“應(yīng)收賬款科目，貸記“資產(chǎn)減值損失-壞賬損失”科目;再借記“銀行存款”科目，貸記“應(yīng)收賬款”科目。知識準備采用直接轉(zhuǎn)銷法的缺點:①只有實際發(fā)生的壞賬才確認為當期費用，導(dǎo)致資產(chǎn)不實，各期損益不實，不能真實反映企業(yè)的財務(wù)狀況;②在期末資產(chǎn)負債表上，應(yīng)收賬款是按照賬面余額而不是賬面價值反映，采用直接轉(zhuǎn)銷法在一定程度上扭曲了財務(wù)狀況。結(jié)合以上原因，我國會計準則規(guī)定確定應(yīng)收款項減值(信用減值損失)只能采用備抵法，不能采用直接轉(zhuǎn)銷法。知識準備備抵法是指在壞賬損失實際發(fā)生前，就依據(jù)權(quán)責(zé)發(fā)生制原則估計損失，并同時形成壞賬準備，待壞賬損失實際發(fā)生時再沖減壞賬準備。估計壞賬損失時，借記“信用減值損失”科目，貸記“壞賬準備”科目;壞賬損失實際發(fā)生時(符合前述的三個條件之一)，借記“壞賬準備”科目，貸記“應(yīng)收賬款”科目;已確認并轉(zhuǎn)銷的應(yīng)收款項以后又收回時，做相反分錄即可,即借記“應(yīng)收賬款”,貸記“壞賬準備”科目,同時借記“銀行存款”科目，貸記“應(yīng)收賬款”科目。知識準備至于如何估計壞賬損失，則有三種方法可供選擇，即年末余額百分比法、賬齡分析法和銷貨百分比法。應(yīng)用年末余額百分比法時，壞賬準備的計提(壞賬損失的估計)分首次計提和以后年度計提兩種情況。首次計提時，壞賬準備提取數(shù)=應(yīng)收賬款年末余額x計提比例。(1)設(shè)置公式計算壞賬提取比例和應(yīng)提取壞賬金額；(2)創(chuàng)建壞賬統(tǒng)計表格并統(tǒng)計壞賬金額所占比例；(3)創(chuàng)建應(yīng)提取壞賬分析圖表。任務(wù)目標任務(wù)資料福源公司根據(jù)應(yīng)收賬款的賬齡進行壞賬的提取，即在信用期內(nèi)的不提取壞賬，超過信用期1~30天的按2%提取，超過信用期31~90天的按6%提取，超過信用期91~180天的按9%提取，超過信用期181~360天的按14%提取，超過信用期360天以上的按25%提取壞賬。截至2022年2月23日，福源公司應(yīng)收賬款余額情況如表6-3所示。任務(wù)資料續(xù)表任務(wù)操作(1)打開新的Excel工作簿,命名為“壞賬”,將Sheetl工作表命名為“壞賬提取金額”,將上述應(yīng)收賬款余額輸入表格中,并增加兩列,即“壞賬提取比例”“壞賬金額”。選擇“開始”選項卡,執(zhí)行“字體”組中的“所有邊框”命令,給表格加上邊框;選擇“開始”選項卡，執(zhí)行“對齊方式”組中的“合并并居中”命令,對標題欄進行設(shè)置,如圖6-16所示(“日期”列的內(nèi)容可以通過輸入公式“=TODAYO)”得到)。1.設(shè)置公式計算壞賬提取比例和應(yīng)提取壞賬金額任務(wù)操作1.設(shè)置公式計算壞賬提取比例和應(yīng)提取壞賬金額

圖6-16輸入基礎(chǔ)數(shù)據(jù)并設(shè)置格式任務(wù)操作1.設(shè)置公式計算壞賬提取比例和應(yīng)提取壞賬金額(2)選中F列，選擇“開始”選項卡，在“數(shù)字”組中將“壞賬提取比例”列的數(shù)字格式設(shè)置為“百分比”，如圖6-17所示;選中G列，將“壞賬金額”列的數(shù)字格式設(shè)置為“貨幣”。

圖6-17設(shè)置數(shù)字格式任務(wù)操作(3)計算壞賬提取比例。按照任務(wù)資料中設(shè)定的提取比例，在F4單元格中輸入公式“=IF(E4<0,0,IF(E4<=30,2%,IF(E4<=90,6%,IF(E4<=180,9%,IF(E4<=360,14%,25%)))))”,按Enter鍵，得出數(shù)據(jù)。將鼠標指針放在F4單元格右下角，向下拖動填充柄填充整列數(shù)據(jù),如圖6-18所示。(4)計算壞賬金額。在G4單元格中輸入公式“=D4*F4”，按Enter鍵，得出數(shù)據(jù)。將鼠標指針放在F4單元格右下角，向下拖動填充柄填充整列數(shù)據(jù)，如圖6-19所示。1.設(shè)置公式計算壞賬提取比例和應(yīng)提取壞賬金額任務(wù)操作1.設(shè)置公式計算壞賬提取比例和應(yīng)提取壞賬金額

圖6-18計算壞賬提取比例

圖6-19計算壞賬金額任務(wù)操作2.創(chuàng)建壞賬統(tǒng)計表格并統(tǒng)計壞賬金額所占比例(1)打開“壞賬”工作簿，將Sheet2命名為“壞賬分析表”，將“客戶名稱”列從“應(yīng)收賬款”工作簿“客戶代碼”工作表中粘貼復(fù)制過來，增加兩列“壞賬金額”和“比例”最后進行美化工作(標題欄合并單元格，調(diào)大字體,給表格加上邊框)，如圖6-20所示。

圖6-20新建工作表任務(wù)操作2.創(chuàng)建壞賬統(tǒng)計表格并統(tǒng)計壞賬金額所占比例(2)在C3單元格中輸入公式“=SUMIF(壞賬提取金額!$C$4:$C$28,壞賬分析表!$A3壞賬提取金額!$G$4:$G$28)”，按Enter鍵，得出數(shù)據(jù)。將鼠標指針放在C3單元格右下角，向下拖動填充柄填充數(shù)據(jù)，如圖6-21所示。

圖6-21計算壞賬金額任務(wù)操作2.創(chuàng)建壞賬統(tǒng)計表格并統(tǒng)計壞賬金額所占比例(3)在A18單元格增加一行“合計”，添加上邊框。選中B18單元格，選擇“開始選項卡，執(zhí)行“編輯”組中的“自動求和”命令，按Enter鍵，得到壞賬金額的合計，如圖6-22所示(如果自動求和的單元格區(qū)域錯誤，可以手動進行調(diào)整)。

圖6-22計算壞賬金額的合計任務(wù)操作2.創(chuàng)建壞賬統(tǒng)計表格并統(tǒng)計壞賬金額所占比例

圖6-23計算比例(4)計算比例。在C3單元格中輸入公式“=B3/$B$18”，按Enter鍵，得到數(shù)據(jù)。將鼠標指針放在C3單元格右下角，向下拖動填充柄填充數(shù)據(jù)，如圖6-23所示。任務(wù)操作2.創(chuàng)建壞賬統(tǒng)計表格并統(tǒng)計壞賬金額所占比例

圖6-24設(shè)置數(shù)據(jù)一(5)調(diào)整“比例”列數(shù)據(jù)。選中C列,選擇“開始”選項卡,執(zhí)行“數(shù)字”組中的“%命令，得到調(diào)整為百分比的數(shù)據(jù)，如圖6-24和圖6-25所示。

圖6-25設(shè)置數(shù)據(jù)二任務(wù)操作3.創(chuàng)建應(yīng)提取壞賬分析圖表下面為提取的壞賬金額創(chuàng)建折線圖和柱形圖并進行比較。1)折線圖(1)選中要創(chuàng)建圖表的A2:B17單元格區(qū)域，選擇“插入”選項卡，執(zhí)行“圖表”組中的“折線圖”命令，選擇一個合適的樣式，如圖6-26所示。

圖6-26選擇折線圖樣式任務(wù)操作3.創(chuàng)建應(yīng)提取壞賬分析圖表1)折線圖

圖6-27美化折線圖(2)選中折線圖，激活“圖表工具”選項卡，在其下有三個子選項卡，即“設(shè)計”“布局”“格式”。通過“設(shè)計”子選項卡,可進行圖表顏色、圖表布局(在圖上是否顯示百分比)等的選擇;通過“布局”子選項卡,可進行圖表標題、數(shù)據(jù)標簽、圖例等的選擇;通過“格式”子選項卡，可進行字體、顏色、形狀等的選擇，如圖6-27所示。任務(wù)操作3.創(chuàng)建應(yīng)提取壞賬分析圖表2)柱形圖

圖6-28選擇柱形圖樣式(1)選中要創(chuàng)建圖表的A2:B17單元格區(qū)域，選擇“插入”選項卡，執(zhí)行“圖表”組中的“柱形圖”命令，選擇一個合適的樣式，如圖6-28所示。任務(wù)操作(2)選中折線圖，激活“圖表工具”選項卡，在其下有三個子選項卡，即“設(shè)計”“布局”“格式”。通過“設(shè)計”子選項卡,可進行圖表顏色、圖表布局(在圖上是否顯示百分比)等的選擇;通過“布局”子選項卡,可進行圖表標題、數(shù)據(jù)標簽、圖例等的選擇;通過“格式”子選項卡，可進行字體、顏色、形狀等的選擇。雙擊表格的縱軸數(shù)據(jù)，打開“設(shè)置坐標軸格式”對話框，可以在“坐標軸選項”選項卡中更改坐標比例，使圖形更美觀，如圖6-29所示;在“數(shù)字”選項卡中選擇數(shù)字的格式。3.創(chuàng)建應(yīng)提取壞賬分析圖表2)柱形圖任務(wù)操作圖6-29美化柱形圖任務(wù)三應(yīng)付賬款的統(tǒng)計知識準備應(yīng)付賬款用以核算企業(yè)因購買材料、商品和接受勞務(wù)供應(yīng)等經(jīng)營活動應(yīng)支付的款項是指因購買材料、商品或接受勞務(wù)供應(yīng)等而發(fā)生的債務(wù)，這是買賣雙方在購銷活動中由于取得物資與支付貨款在時間上不一致而產(chǎn)生的負債。應(yīng)付賬款主要包括購買貨物的欠款應(yīng)交未交的稅款以及為銷售方代墊的相關(guān)費用。本科目應(yīng)當按照不同的債權(quán)人進行明細核算。企業(yè)應(yīng)當加強對應(yīng)付賬款的管理，以避免發(fā)生財務(wù)危機，維護企業(yè)信譽。任務(wù)目標(1)創(chuàng)建應(yīng)付賬款分析表；(2)創(chuàng)建應(yīng)付賬款分析圖。任務(wù)資料福源公司的應(yīng)付賬款信息如下:2022年2月10日，與虹光有限責(zé)任公司發(fā)生交易，金額總計49000元，約定的付款期限為10天;2022年2月18日，與緣來有限責(zé)任公司發(fā)生交易，金額總計157950元，約定的付款期限為30天;2022年2月25日，與天奇有限公司發(fā)生交易，金額總計119340元，約定的付款期限為30天。任務(wù)操作應(yīng)付賬款分析表主要包括對應(yīng)付金額的分析、應(yīng)付賬款的賬期的分析以及應(yīng)付賬款金額比例的計算等內(nèi)容。(1)新建一個工作簿，命名為“應(yīng)付賬款”，將Sheet1工作表重命名為“應(yīng)付賬款分析表”,在工作表中添加標題和列標題(客戶名稱、交易日期、付款期限、應(yīng)付金額、未到期等)。選擇“開始”選項卡，執(zhí)行“對齊方式”組中的“合并并居中”命令，在“字體”組中調(diào)整標題的字體并給表格加上邊框，如圖6-30所示。1.創(chuàng)建應(yīng)付賬款分析表圖6-30輸入基礎(chǔ)數(shù)據(jù)并設(shè)置格式任務(wù)操作(2)設(shè)置日期。選中H2單元格,輸入公式“=TODAYO)”或者“=NOWO)”,按Enter鍵，即可返回當前的日期和時間，如圖6-31所示。通過公式設(shè)置的日期是隨著系統(tǒng)的時間變化而變化的。1.創(chuàng)建應(yīng)付賬款分析表

圖6-31設(shè)置日期任務(wù)操作1.查詢員工工資數(shù)據(jù)【提示】NOW函數(shù):返回當前日期和時間所對應(yīng)的序列號。函數(shù)語法:=NOWO)TODAY函數(shù):返回系統(tǒng)當前日期的序列號。函數(shù)語法:=TODAY()兩個函數(shù)的區(qū)別在于TODAY函數(shù)返回的是當前日期，NOW函數(shù)返回的是當前日期與時間。任務(wù)操作(3)根據(jù)背景資料將企業(yè)尚未支付的應(yīng)付賬款的數(shù)據(jù)信息輸入相應(yīng)的表格中,如圖6-32所示。2.科目匯總表數(shù)據(jù)圖6-32輸入數(shù)據(jù)任務(wù)操作(4)設(shè)置格式。選中D5:18單元格區(qū)域，選擇“開始”選項卡，單擊“數(shù)字”組中的下三角按鈕，在下拉列表中選擇“會計專用”格式，將“交易日期”和“付款期限”設(shè)置為“長日期”格式，如圖6-33所示。(5)計算未到期金額。在E5單元格中輸入公式“=IF(C5>$H$2.D5.0)”(如果C5>SHS2則返回D5單元格;如果不是，則返回0)，按Enter鍵，得出數(shù)據(jù)。將鼠標指針放在E5單元格右下角，向下拖動填充柄填充數(shù)據(jù)，如圖6-34所示。1.查詢員工工資數(shù)據(jù)任務(wù)操作1.查詢員工工資數(shù)據(jù)

圖6-34計算未到期金額圖6-33設(shè)置格式任務(wù)操作1.查詢員工工資數(shù)據(jù)(6)計算企業(yè)逾期尚未支付的應(yīng)付賬款金額。在F5單元格中輸入公式“=IF(AND(SHS2-$C5>0.$H$2-$C5<=20),$D5,””)”，按Enter鍵，得出數(shù)據(jù)，如圖6-35所示。在G5單元格中輸入公式“=IF(AND($H$2-$C5>20,$H$2-$C5<=40),$D5,”")”,按Enter鍵，得出數(shù)據(jù)。在H5單元格中輸入公式“=IF(AND(SH$2-$C5>40,$H$2-$C5<=60),$D5,”"