Excel在財會管理中的應用第3篇

Excel在財務(wù)管理中的應用第三篇789Excel在工資管理中的應用Excel在存貨管理中的應用Excel在應收賬款管理中的應用Excel在固定資產(chǎn)管理中的應用1007Excel在工資管理中的應用1.掌握如何制作員工工資表2.熟悉工資數(shù)據(jù)的查詢3.了解工資數(shù)據(jù)的發(fā)放7.1.1背景資料宏運有限責任公司是一家小型制造業(yè)企業(yè),生產(chǎn)的產(chǎn)品主要有MG、MC兩種產(chǎn)品,年利潤為25萬元。該公司主要有管理部、生產(chǎn)部、銷售部三個部門。本單位共有15名員工,每名員工的工資項目有基本工資、崗位工資、住房補貼、獎金、事假扣款、病假扣款、養(yǎng)老保險扣款和醫(yī)療保險扣款等。除基本工資因人而異外(要求必須一一輸入),其他的工資項目將根據(jù)員工職務(wù)類別和部門決定,而且隨時間變化而變化。

2017年10月宏運有限責任公司員工基本工資情況與出勤情況見表7-1。其他工資項目的發(fā)放情況及有關(guān)規(guī)定如下:崗位工資:根據(jù)員工類別進行發(fā)放,管理人員(公司管理、生產(chǎn)管理、采購管理)為3000元,銷售人員2700元,生產(chǎn)工人為2300元。住房補貼:根據(jù)員工類別進行發(fā)放,生產(chǎn)工人為300元,銷售人員為400元,管理人員為500元。獎金:獎金根據(jù)部門的效益決定,本月管理部每人獎金為800元,生產(chǎn)部每人獎金為500元,銷售部獎金與個人銷售額相關(guān),完成基本銷售額30萬元的獎金1000元,超額完成的按超出金額的百分之一提成,未完成基本銷售額的沒有獎金。事假扣款規(guī)定:如果事假不超過14天,將應發(fā)工資平均到每天(每月按22天計算),按天扣錢;如果事假多于14天,扣除應發(fā)工資的百分之八十。病假扣款規(guī)定:如果病假不超過14天,工人扣款400元,非工人扣款600元,如果病假多于14天,工人扣款600元,非工人扣款1000元。養(yǎng)老保險扣款:按基本工資加崗位工資的百分之八扣除。醫(yī)療保險扣款:按基本工資加崗位工資的百分之二扣除。個人所得稅:依據(jù)個人所得稅稅率表(表7-2)扣除。7.1.2基本工資項目和數(shù)據(jù)的輸入7.2.1“崗位工資”項目根據(jù)宏運有限責任公司的規(guī)定,崗位工資根據(jù)員工類別的不同而不同,具體要求見表7-3。第一步:將光標移到G2單元格,輸入嵌套的IF函數(shù),如圖7-6所示。如果E2單元格的值為“生產(chǎn)工人”,IF()函數(shù)的值為2300;如果為“銷售人員”,IF()函數(shù)的值為2700;如果為“管理人員(公司管理、生產(chǎn)管理、銷售管理)”,IF()函數(shù)的值3000。第二步:將G2單元格的公式復制到G列的其他單元格,結(jié)果如圖7-7所示。7.2.2“住房補貼”項目

宏運有限責任公司員工住房補貼是由員工類別決定的,具體見表7-4。第一步:將光標移到H2單元格,輸入嵌入的IF函數(shù)。如果E2單元格的值為“生產(chǎn)工人”,IF()函數(shù)的值為300;如果為“銷售人員”,IF()的函數(shù)值為400;如果為“管理人員(公司管理、生產(chǎn)管理、銷售管理)”,IF()的函數(shù)值為500。第二步:將H2單元格的公式復制到H列的其他單元格,結(jié)果如圖7-8所示。7.2.3“獎金”項目根據(jù)宏運有限責任公司的規(guī)定,獎金根據(jù)部門的效益決定,具體要求見表7-5。假設(shè)銷售部本月的銷售情況見表7-6?！蔼劷稹表椖吭O(shè)置的具體操作步驟如下:第一步:將I2單元格的公式設(shè)置為“=IF(C2=“理”,800,IF(C2=“生產(chǎn)”,500,“銷售”))”。第二步:將I2單元格的公式復制到I列的其他單元格,結(jié)果如圖7-9所示。第三步:第一個顯示“銷售”的單元格為I5單元格,將該單元格的公式設(shè)置為“IF(AND(C5=“銷售”,銷售額表!B2>=30),1000+100*(銷售額表!B2-30),0)”,如圖7-10所示。將I5單元格的公式復制到I列“銷售部”的單元格。(該步驟需要用到銷售額表7-6)。7.2.4“應發(fā)合計”項目“應發(fā)合計”項目為基本工資、崗位工資、住房補貼和獎金的合計數(shù)。第一步:選中J2單元格,單擊“自動求和”按鈕或直接在J2單元格輸入公式“=SUM(F2:I2)”。第二步:將J2單元格的公式復制到J列的其他單元格,結(jié)果如圖7-11所示。7.2.5“事假扣款”項目

宏運有限責任公司,事假扣款與事假天數(shù)有關(guān),具體要求見表7-7。第一步:將L2單元格的公式設(shè)置為“=IF(K2>14,J2*0.8,J2/22*K2)”。第二步:將L2單元格的公式復制到L列的其他單元格,結(jié)果如圖7-12所示。7.2.6“病假扣款”項目在宏運有限責任公司,“病假扣款”由病假天數(shù)和員工類別來確定,具體內(nèi)容見表7-8。第一步:將N2單元格的公式設(shè)置為“=IF(M2=0,0,IF(M2<=14,IF(E2=“生產(chǎn)工人”,400,600),IF(E2=“生產(chǎn)工人”,600,1000)))”。第二步:將N2單元格的公式復制到N列的其他單元格,結(jié)果如圖7-13所示。7.2.7“扣款合計”項目

扣款合計為事假扣款、病假扣款和其他扣款的合計。假設(shè)本月沒有發(fā)生其他扣款,具體操作步驟如下:第一步:將P2單元格公式設(shè)置為“=L2+N2+O2”。第二步:將P2單元格的公式復制到P列的其他單元格,結(jié)果如圖7-14所示。7.2.8“養(yǎng)老保險”和“醫(yī)療保險”項目在宏運有限責任公司,養(yǎng)老保險按照基本工資與崗位工資之和的8%扣除,醫(yī)療保險按照基本工資與崗位工資之和的2%扣除。具體操作步驟如下:第一步:將Q2單元格公式設(shè)置為“=(F2+G2)*0.08”。第二步:將Q2單元格的公式復制到Q列其他單元格,結(jié)果如圖7-15所示。第三步:將R2單元格的公式設(shè)置為“=(F2+G2)*0.02”。第四步:將R2單元格的公式復制到R列的其他單元格,結(jié)果如圖7-16所示。7.2.9“應扣社保合計”項目應扣社保合計是養(yǎng)老保險和醫(yī)療保險的合計。

第一步:將S2單元格公式設(shè)置為“=Q2+R2”。第二步:將S2單元格的公式復制到S列的其他單元格,結(jié)果如圖7-17所示。7.2.10“應發(fā)工資”項目應發(fā)工資是應發(fā)合計與扣款合計和應扣社保合計的差額。

第一步:將T2單元格公式設(shè)置為“=J2-P2-S2”。第二步:將T2單元格的公式復制到T列的其他單元格,結(jié)果如圖7-18所示。7.2.11“代扣稅”項目代扣稅根據(jù)應發(fā)工資的數(shù)額而定,具體要求見表7-9。第一步:將U2單元格的公式設(shè)置為“=IF(T23500<=0,0,IF(T2-3500<=1500,(T2-3500)*0.03,IF(T2-3500<=4500,(T2-3500)*0.10-105,IF(T2-3500<=9000,(T2-3500)*0.20-555,IF(T2-3500<=35000,(T2-3500)*0.25-1005,IF(T2-3500<=55000,(T2-3500)*0.3-2755,IF(T2-3500<=80000,(T2-3500)*0.35-5505,(T2-3500)*0.45-13505)))))))”。第二步:將U2單元格的公式復制到U列的其他單元格,結(jié)果如圖7-19所示。7.2.12“實發(fā)合計”項目實發(fā)合計即實發(fā)工資,為應發(fā)工資與代扣稅的差值。第一步:將V2單元格公式設(shè)置為“=T2-U2”。第二步:將V2單元格的公式復制到V列的其他單元格,結(jié)果如圖7-20所示。7.3.1利用篩選功能進行工資數(shù)據(jù)查詢010203以員工姓名為依據(jù)進行查詢以部門為依據(jù)進行查詢以員工類別和實發(fā)工資為依據(jù)進行查詢7.3.2依據(jù)部門和員工類別統(tǒng)計分析7.3.3生成工資發(fā)放條7.3.4打印工資發(fā)放條08Excel在存貨管理中的應用1.了解存貨管理中數(shù)據(jù)之間的關(guān)系2.掌握如何建立并登記進銷存業(yè)務(wù)表3.掌握如何建立并登記材料和商品明細賬4.掌握庫存量的控制方法8.1.1存貨管理的概念及內(nèi)容1.存貨及存貨管理的概念存貨是指企業(yè)在生產(chǎn)經(jīng)營過程中為了生產(chǎn)和銷售而儲備的物資。存貨管理就是對企業(yè)的存貨進行管理,主要包括存貨的信息管理和在此基礎(chǔ)上的決策分析,最后進行有效控制,達到存貨管理的最終目的———提高經(jīng)濟效益。2.存貨管理的內(nèi)容本章主要根據(jù)存貨管理的業(yè)務(wù)流程,對存貨進銷存進行管理,具體指:進；銷；存。8.1.2存貨管理的必要性1.保證生產(chǎn)正常進行2.有利于銷售3.便于維持均衡生產(chǎn)4.降低存貨取得成本5.防止意外事件的發(fā)生8.1.3存貨的計價方法個別計價法先進先出法月末一次加權(quán)平均法移動加權(quán)平均法8.2.1輸入期初數(shù)據(jù)

永昌公司要實現(xiàn)利用Excel管理進銷存業(yè)務(wù),首先要把期初商品和材料數(shù)據(jù)輸入Excel工作表,作為原來手工賬和新的進銷存管理系統(tǒng)的銜接。通過整理永昌公司存貨手工賬及對公司業(yè)務(wù)的分析,永昌公司存貨期初數(shù)據(jù)應包含內(nèi)容為:商品編碼、商品名稱、型號規(guī)格、計量單位、期初庫存數(shù)量、期初單位成本和期初余額等信息。8.2.2編制采購業(yè)務(wù)表第一步:

建立“采購業(yè)務(wù)表”工作表。第二步:

設(shè)置采購業(yè)務(wù)表格式。第三步:

輸入采購業(yè)務(wù)相關(guān)信息。8.2.3編制付款業(yè)務(wù)表第一步:

建立“付款業(yè)務(wù)表”工作表。第二步:

設(shè)置付款業(yè)務(wù)表格式。第三步:輸入付款業(yè)務(wù)相關(guān)信息。8.3.1編制產(chǎn)品銷售業(yè)務(wù)表第一步：建立“產(chǎn)品銷售業(yè)務(wù)表”工作表。第二步：設(shè)置產(chǎn)品銷售業(yè)務(wù)表格式。第三步：輸入產(chǎn)品銷售業(yè)務(wù)相關(guān)信息。第四步：計算銷售成本和毛利潤。8.3.2編制領(lǐng)用材料表第一步:

建立“領(lǐng)用材料表”工作表。第二步:

設(shè)置領(lǐng)用材料表格式。第三步:

輸入材料領(lǐng)用情況相關(guān)信息。8.3.3編制收款業(yè)務(wù)表第一步:

建立“收款業(yè)務(wù)表”工作表。第二步:

設(shè)置領(lǐng)用材料表格式。第三步:

輸入收款業(yè)務(wù)相關(guān)信息。8.4.1編制庫存管理工作表第一步：建立“庫存管理表”工作表。第二步：設(shè)置庫存管理表格式。第三步：輸入庫存管理相關(guān)信息。第四步：利用條件格式對庫存數(shù)量進行監(jiān)控。8.4.2登記材料和商品明細賬2.根據(jù)進銷總記錄表生成存貨明細賬1.輸入法構(gòu)建存貨明細賬8.4.3經(jīng)濟訂貨批量基本模型1.存貨成本

(1)取得成本(TCa)取得成本分為訂貨成本和購置成本。訂貨成本分為訂貨固定成本和訂貨變動成本兩部分,公式如下:

其中,F1為固定成本;D為存貨年需求量;Q為每次進貨量;K為每一次的變動成本。購置成本由數(shù)量和單價決定,假設(shè)單價為U,年需求量為D,則以DU表示購置成本。公式如下:

其中,TCa

是取得成本。(2)儲存成本(TCc)儲存成本是指為保持存貨而發(fā)生的費用,分成儲存固定成本和儲存變動成本。公式如下:其中,TCc是儲存成本;F2是固定成本;Kc為單位成本;Q為存貨量。(3)缺貨成本(TCs)缺貨成本是因中斷而造成的損失,如停工損失、緊急外購成本等。(4)總公式總成本為TC,其構(gòu)成由上面三部分構(gòu)成,公式如下:2.經(jīng)濟訂貨量基本模型的假設(shè)經(jīng)濟訂貨量基本模型假設(shè)企業(yè)能夠及時補充存貨;能集中到貨;不允許缺貨,即無缺貨成本(TCs=0);需求量穩(wěn)定,并且能預測;存貨單價不變;存貨現(xiàn)金充足;所需存貨市場供應充足。3.經(jīng)濟訂貨量基本模型依據(jù)EOQ模型的基本假設(shè),公式如下:此外,還有如下公式演變:每次最佳訂貨次數(shù):與批量有關(guān)的存貨總成本:最佳訂貨周期:經(jīng)濟訂貨量占用資金:4.經(jīng)濟訂貨量基本模型的舉例

【例8-1】某企業(yè)每年消耗某種材料3600公斤,該材料單位成本為10元,單位儲存成本為2元,一次訂貨成本為25元,則有如下計算:09Excel在應收賬款管理中的應用1.掌握如何建立并登記應收賬款明細賬2.掌握如何對各債務(wù)人應收賬款進行統(tǒng)計分析3.掌握如何分析逾期應收賬款4.掌握如何分析應收賬款賬齡5.掌握如何計算應收賬款的壞賬準備9.1.1應收賬款管理的概念、目標及內(nèi)容1.應收賬款及應收賬款管理的概念應收賬款是指企業(yè)因銷售產(chǎn)品、提供勞務(wù)等經(jīng)營活動,應向購貨單位或接受勞務(wù)單位收取的款項,主要包括企業(yè)銷售商品或提供勞務(wù)等應向有關(guān)債務(wù)人收取的價款及代購貨單位墊付的包裝費、運雜費等。形成應收賬款的直接原因是賒銷,應收賬款實質(zhì)是由于賒銷向客戶提供的信用。企業(yè)通過提供商業(yè)信用,采取賒銷、分期付款等方式可以擴大銷售,增強競爭力,獲得利潤。應收賬款管理就是分析賒銷的條件,使賒銷帶來的盈利增加大于應收賬款投資產(chǎn)生的成本增加,最終使企業(yè)現(xiàn)金收入增加,企業(yè)價值上升。2.應收賬款管理的目標應收賬款管理的目標是:在發(fā)揮應收賬款擴大銷售、減少存貨、增加競爭力的同時,制定合理的應收賬款信用政策,強化應收賬款管理,減少壞賬損失。3.應收賬款管理的內(nèi)容應收賬款管理的基本內(nèi)容包括客戶(債務(wù)人)管理和應收賬款賬齡分析。9.1.2應收賬款管理的必要性010203擴大銷售,提高企業(yè)市場份額有效減少庫存,降低存貨風險及管理成本完成資金循環(huán),保障企業(yè)繼續(xù)運營發(fā)展9.2.1應收賬款明細賬的建立第一步:

建立“應收賬款管理”工作表。第二步:

設(shè)置應收賬款明細賬格式。第三步:輸入企業(yè)應收賬款詳細信息。9.2.2統(tǒng)計各債務(wù)人應收賬款金額分類匯總法函數(shù)法數(shù)據(jù)透視表法9.2.3建立統(tǒng)計圖進行對比分析

第一步:打開“第9章”工作簿,選擇“各債務(wù)人應收賬款統(tǒng)計”工作表。第二步:插入餅圖。9.3.1計算分析應收賬款是否到期第一步：創(chuàng)建“逾期應收賬款分析”工作表。第二步：建立“開源公司應收賬款管理”表。第三步：利用IF函數(shù)判斷現(xiàn)有各項應收賬款是否到期。第四步：利用IF函數(shù)計算未到期金額。9.3.2應收賬款逾期天數(shù)計算分析第一步:

計算逾期天數(shù)。第二步:

建立逾期天數(shù)分析表。第三步:利用IF函數(shù)計算不同逾期天數(shù)的應收賬款。9.4.1不同賬齡應收賬款計算分析第一步：創(chuàng)建“應收賬款賬齡分析”工作表。第二步：建立應收賬款賬齡分析表。第三步：計算不同賬齡的應收賬款未收金額。第四步：計算不同賬齡的應收賬款占應收賬款總額的百分比。9.4.2計算應收賬款壞賬準備金額

企業(yè)的應收賬款可能會因購貨人拒付、破產(chǎn)、死亡等原因而無法收回。這類無法收回的應收賬款就是壞賬。壞賬是應收賬款帶來的最大損失,企業(yè)必須加以重視。估計壞賬準備比例時具體應考慮以下因素:

(1)函證情況,每次函證發(fā)出后,對方是否及時、準確地回函;

(2)歷史上應收款項回收的情況,包括回收時間和歸還應收賬款是否呈現(xiàn)周期性;

(3)債務(wù)單位歷史上是否存在無法支付的情況;

(4)某一債務(wù)單位近期內(nèi)是否有不良記錄;

(5)債務(wù)單位目前發(fā)生的財務(wù)困難與過去已發(fā)生的財務(wù)狀況是否存在類似的情形;

(6)債務(wù)單位的債務(wù)狀況有否好轉(zhuǎn)的可能性,包括債務(wù)單位的產(chǎn)品開發(fā),現(xiàn)產(chǎn)品的銷售、回款,市場需求以及資產(chǎn)質(zhì)量狀況,有否呈現(xiàn)出好轉(zhuǎn)態(tài)勢等;

(7)債務(wù)單位所處的經(jīng)濟、政治和法制環(huán)境;

(8)債務(wù)單位的內(nèi)部控制、財務(wù)、生產(chǎn)、技術(shù)管理等情況,以及其他有利于判斷可收回性的情況。用賬齡分析法時,壞賬準備計提比例較簡單,通常賬齡越長,發(fā)生壞賬的可能性越大,估計的壞賬準備計提比例就越高。假設(shè)開源公司根據(jù)歷史經(jīng)驗估計:未到期的應收賬款發(fā)生壞賬的可能性是0,逾期0~30天的應收賬款發(fā)生壞賬的可能性約為1%,逾期30~60天的應收賬款發(fā)生壞賬的可能性約為3%,逾期60~90天的應收賬款發(fā)生壞賬的可能性約為6%,逾期90天以上的應收賬款發(fā)生壞賬的可能性約為10%。具體操作步驟如下:10Excel在固定資產(chǎn)管理中的應用1.掌握如何建立并登記固定資產(chǎn)卡片賬2.掌握利用財務(wù)函數(shù)計算固定資產(chǎn)累計折舊的方法3.掌握計算固定資產(chǎn)賬面價值的方法4.掌握固定資產(chǎn)數(shù)據(jù)的篩選、匯總分析方法10.1.1固定資產(chǎn)管理的概念、特點及內(nèi)容1.固定資產(chǎn)及固定資產(chǎn)管理的概念固定資產(chǎn)是指同時具有以下特征的有形資產(chǎn):

(1)為生產(chǎn)商品、提供勞務(wù)、出租或經(jīng)營管理而持有的;

(2)使用壽命超過一個會計年度。固定資產(chǎn)管理是一種過程,是通過對固定資產(chǎn)的管理活動以提高資產(chǎn)利用率、減少資產(chǎn)的無謂損失,最終使固定資產(chǎn)的效用達到最大化的一種活動。2.固定資產(chǎn)管理的特點長期性廣泛性多樣性3.固定資產(chǎn)管理的內(nèi)容首先嚴格管理固定資產(chǎn)卡片,對固定資產(chǎn)卡片進行管理,包括卡片的增加、刪除、查詢、打印、按月匯總、分類匯總等。其次正確、全面、及時地記錄固定資產(chǎn)的增加、減少、使用等情況,保護生產(chǎn)資料安全完整。企業(yè)增加固定資產(chǎn)可以通過購置、建造等方式進行;通過出售、報廢等途徑減少固定資產(chǎn)。最后正確計算固定資產(chǎn)的折舊和修理費用,并進行固定資產(chǎn)折舊和修理的核算,保證固定資產(chǎn)簡單再生產(chǎn)的實現(xiàn)。10.1.2固定資產(chǎn)管理的必要性

(1)通過加強對固定資產(chǎn)的管理,單位能隨時快速地了解從組建以來每年購置的資產(chǎn)狀況。

(2)加強對購入固定資產(chǎn)的管理,避免重復購置和浪費;對閑置資產(chǎn)和使用效率較低的固定資產(chǎn)進行處置(如調(diào)配、變賣、出租等),提高資金利用效率。

(3)核查、盤點以及折舊計算不僅快速而且準確,為單位資產(chǎn)評估、決策提供更為可靠的依據(jù),避免單位在固定資產(chǎn)管理環(huán)節(jié)上可能造成的遺漏和隱患。

(4)員工離職或工作變動時,可以快速、完整地進行資產(chǎn)交接,避免不必要的資產(chǎn)流失。

企業(yè)對固定資產(chǎn)的日常核算通常采用卡片賬形式。卡片賬是指將賬戶所需的格式印刷在硬卡上,詳細登記固定資產(chǎn)的相關(guān)信息,能夠?qū)潭ㄙY產(chǎn)進行獨立、詳盡的記錄?？ㄆ~是一種明細活頁賬,只不過它不是裝在活頁賬夾中,而是裝在卡片箱內(nèi)。卡片賬通常一式三份,分別由設(shè)備管理