新編Excel在財(cái)務(wù)中的應(yīng)用（第4版）課件 項(xiàng)目六-Excel在進(jìn)銷存管理中的應(yīng)用

EXCEL在進(jìn)銷存管理中的應(yīng)用目錄CONTENT01采購與付款業(yè)務(wù)02銷售與收款業(yè)務(wù)03出入庫管理04編制總賬和明細(xì)賬04應(yīng)收款分析01PARTONE采購與付款業(yè)務(wù)建立商品列表及期初數(shù)據(jù)表、采購數(shù)據(jù)清單1、新建一個(gè)

Excel

工作簿，命名為“項(xiàng)目六Excel進(jìn)銷存管理”。2、把Sheet1命名為“商品列表及期初數(shù)據(jù)”，3、建立材料采購數(shù)據(jù)清單，根據(jù)自身需要設(shè)置項(xiàng)目名稱這要注意：在輸入信息時(shí)，為了提高信息輸入速度，防止錯誤發(fā)生，可以對部分項(xiàng)目進(jìn)行有效性設(shè)置，例如供應(yīng)商。）4、利用開始-條件格式-突出顯示單元格規(guī)則-等于

命令把結(jié)清貨款的單元格設(shè)置背景顏色。5、輸入表體內(nèi)容，使用VLOOKUP函數(shù)，內(nèi)容可通過Sheet1商品列表對應(yīng)關(guān)系自動顯示。（商品名稱、商品編碼、型號、規(guī)格等）注意：

VLOOKUP函數(shù)建立付款業(yè)務(wù)數(shù)據(jù)清單6、建立付款業(yè)務(wù)數(shù)據(jù)清單，設(shè)置項(xiàng)目名稱這要注意：在輸入信息時(shí)，為了提高信息輸入速度，防止錯誤發(fā)生，可以對部分項(xiàng)目進(jìn)行有效性設(shè)置，結(jié)算方式主要包括現(xiàn)金支票、轉(zhuǎn)賬支票、銀行匯票、銀行本票、匯兌、信用證等。供應(yīng)商有美月公司、前進(jìn)公司，注意設(shè)置時(shí)序列中各項(xiàng)之間用英文的逗號隔開）7、輸入表體內(nèi)容，“=SUMIF(材料采購業(yè)務(wù)!K$2:K$25,付款業(yè)務(wù)!D2,材料采購業(yè)務(wù)!J$2:J$25)”。注意：

SUMIF函數(shù)的熟練使用0PARTtwo銷售與收款業(yè)務(wù)

建立銷售業(yè)務(wù)數(shù)據(jù)清單1、建立銷售業(yè)務(wù)數(shù)據(jù)清單

，設(shè)置項(xiàng)目名稱。注意：在輸入信息時(shí)，為了提高信息輸入速度，防止錯誤發(fā)生，可以對部分項(xiàng)目進(jìn)行有效性設(shè)置，2、設(shè)置條件格式

，利用開始-條件格式-突出顯示單元格規(guī)則-等于

命令把結(jié)清貨款的單元格設(shè)置背景顏色。3、輸入表體內(nèi)容，使用VLOOKUP函數(shù)，內(nèi)容可通過商品列表對應(yīng)關(guān)系自動顯示。（商品名稱、商品編碼、型號、規(guī)格、單位信息利用VLOOKUP函數(shù)自動獲?。┳⒁猓篤LOOKUP函數(shù)的熟練使用4、為方便計(jì)算每筆銷售業(yè)務(wù)的銷售成本和銷售毛利，在銷售業(yè)務(wù)表中增加2列，即“銷售成本”“銷售毛利”。通過與庫存管理的配合使用，可實(shí)時(shí)獲得每筆銷售業(yè)務(wù)的利潤。5、為方便計(jì)算每筆銷售業(yè)務(wù)的銷售成本和銷售毛利，在銷售業(yè)務(wù)表中增加2列，即“銷售成本”“銷售毛利”。通過與庫存管理的配合使用，可實(shí)時(shí)獲得每筆銷售業(yè)務(wù)的利潤。

建立收款業(yè)務(wù)業(yè)務(wù)數(shù)據(jù)清單1、打開“項(xiàng)目六Excel進(jìn)銷存管理”工作簿，插入一個(gè)新的工作表，重命名為“收款業(yè)務(wù)”2、設(shè)置條件格式

，利用開始-條件格式-突出顯示單元格規(guī)則-等于

命令把結(jié)清貨款的單元格設(shè)置背景顏色。3、輸入項(xiàng)目名稱4、輸入表體內(nèi)容。直接輸入收款日期、結(jié)算方式、結(jié)算票據(jù)號和客戶等信息，應(yīng)收貨款和已收貨款等信息可通過“產(chǎn)品銷售業(yè)務(wù)”工作表計(jì)算得到。在E2單元格中輸入函數(shù)“=SUMIF(產(chǎn)品銷售業(yè)務(wù)!C2:C9,收款業(yè)務(wù)!D2,產(chǎn)品銷售業(yè)務(wù)!J2:J9)”，在F2單元格中輸入函數(shù)“=SUMIF(產(chǎn)品銷售業(yè)務(wù)!C2:C9,收款業(yè)務(wù)!D2,產(chǎn)品銷售業(yè)務(wù)!K2:K9)”。5、通過計(jì)算，收款業(yè)務(wù)表0PARTTHREE出入庫業(yè)務(wù)

建立完工入庫數(shù)據(jù)清單1、為計(jì)算方便，完工入庫表的項(xiàng)目名稱沿用采購業(yè)務(wù)的項(xiàng)目名稱。新建一個(gè)工作表，直接輸入完工入庫的信息建立車間領(lǐng)用材料數(shù)據(jù)清單1、直接輸入工作表的項(xiàng)目名稱和數(shù)據(jù)，記錄材料和商品的收發(fā)情況。

建立出庫業(yè)務(wù)總數(shù)據(jù)清單1、打開“項(xiàng)目六Excel進(jìn)銷存管理”工作簿，插入一個(gè)新的工作表，重命名為“出入庫業(yè)務(wù)”。2、輸入項(xiàng)目名稱。3、輸入表體內(nèi)容。按日期先后輸入出入庫業(yè)務(wù)匯總情況，商品名稱、型號規(guī)格、單位等信息可以用VLOOKUP函數(shù)從“商品列表及期初數(shù)據(jù)”工作表中引入。4、通過計(jì)算，生成出入庫業(yè)務(wù)匯總表。

注意：出入庫業(yè)務(wù)匯總數(shù)據(jù)清單也可通過復(fù)制期初數(shù)據(jù)、材料采購業(yè)務(wù)、產(chǎn)品銷售業(yè)務(wù)、完工入庫、車間領(lǐng)用材料幾個(gè)數(shù)據(jù)清單中的數(shù)據(jù)生成，但粘貼時(shí)應(yīng)注意只粘貼數(shù)值。因考慮到這幾個(gè)數(shù)據(jù)清單的使用部門不同，所以此處采用直接輸入的方式生成出入庫業(yè)務(wù)匯總表。0PARTfour編制總賬和明細(xì)賬

建立庫存商品原材料總賬1、打開“項(xiàng)目六Excel進(jìn)銷存管理”工作簿，插入一個(gè)新的工作表，重命名為“總賬”，輸入項(xiàng)目名稱。在實(shí)際工作中，企業(yè)可根據(jù)自身業(yè)務(wù)的需要來設(shè)置項(xiàng)目名稱。2、利用條件格式對存貨數(shù)據(jù)進(jìn)行監(jiān)控，“開始”|“條件格式”|“突出顯示單元格規(guī)則”|“小于”或“大于”命令，對“小于200”的單元格設(shè)置背景色為黃色，對“大于2000”的單元格設(shè)置為淺紅填充色深紅色文本，并將條件格式向下復(fù)制到其他單元格。3、輸入表體內(nèi)容，除商品名稱和規(guī)格型號外，所有數(shù)據(jù)均可利用公式自動生成，熟練使用SUMIF函數(shù)。

建立庫存商品原材料明細(xì)賬1、既可使用數(shù)據(jù)透視表，也可使用篩選的方法。利用數(shù)據(jù)透視表生成明細(xì)賬的方法。2、新建工作表，分別命名為“內(nèi)墻涂料MT-3”“外墻涂料VOC-5A”“粉劑原料FJ-N9100”“粉劑原料FJ-G1080”“固態(tài)原料X310-I”

根據(jù)實(shí)際情況，建立各工作表數(shù)據(jù)清單列字段名

。3、自動篩選“出入庫業(yè)務(wù)”工作表。打開“出入庫業(yè)務(wù)”工作表，單擊“數(shù)據(jù)”|“篩選”按鈕。然后單擊“商品名稱”右側(cè)的下拉按鈕，出現(xiàn)篩選條件，在篩選條件中選中“內(nèi)墻涂料”復(fù)選框，篩選內(nèi)墻涂料MT-3的信息，把篩選結(jié)果復(fù)制到“內(nèi)墻涂料MT-3”工作表中。4、通過計(jì)算，生成信息表。

0PARTfive應(yīng)收賬款分析應(yīng)收賬款分析計(jì)提壞賬準(zhǔn)備進(jìn)行應(yīng)收賬款賬齡分析建立應(yīng)收賬款數(shù)據(jù)清單應(yīng)收帳款分析

建立應(yīng)收賬款數(shù)據(jù)清單1、新建一個(gè)工作表，重命名為“應(yīng)收賬款”輸入表體內(nèi)容。直接輸入公司名稱、發(fā)票號、應(yīng)收賬款金額、開票日期、收款期限信息，其他數(shù)據(jù)信息通過函數(shù)公式自動生成。

公式：在“到期日”列的F2單元格中輸入公式“=D2+E2”；

在“是否到期”列的G2單元格中輸入公式“=IF(F2<$N$2,"是","否")”，其中N2單元格存放收款的“當(dāng)前日期”2、應(yīng)收賬款按賬齡進(jìn)行分類，

公式：

在“未到期”列的H2單元格中輸入公式“=IF($N$2-F2<0,C2,"/")”，

在“0～60天”列的I2單元格中輸入公式“=IF(AND($N$2-F2>0,$N$2-

F2<=60),C2,"/")”，

在“60～120天”列的J2單元格中輸入公式“=IF(AND($N$2F2>60,$N$2-F2<=120),C2,"/")”，

在“121～365天”列的K2單元格中輸入公式“=IF(AND($N$2-F2>120,$N$2-F2<=366),C2,"/")”，

在“366天以上”列的L2單元格中輸入公式“=IF($N$2-F2>366,C2,"/")”；

第10行為“合計(jì)”數(shù)據(jù)，所以在H10單元格中輸入公式“=SUM(H2:H9)”。3、設(shè)置單元格的條件格式

建立應(yīng)收賬款數(shù)據(jù)清單

進(jìn)行應(yīng)收賬款賬齡分析1、插入一新工作表，重命名為“應(yīng)收賬款賬齡分析表”，并輸入表頭信息。2、從“應(yīng)收賬款”工作表中復(fù)制相關(guān)信息。選中“應(yīng)收賬款”工作表的H2:L2單元格區(qū)域后右擊，選擇“復(fù)制”命令。然后在“應(yīng)收賬款賬齡分析表”工作表中選中A4單元格后右擊，選擇“粘貼選項(xiàng)”中的命令，即可把以前橫向顯示的信息改為縱向顯示。3、應(yīng)收賬款”工作表的H10:L10單元格區(qū)域后在“應(yīng)收賬款賬齡分析表”工作表中選中B4單元格右擊，選擇“選擇性粘貼”命令。在打開的“選擇性粘貼”對話框中選中“數(shù)值”單選按鈕和“轉(zhuǎn)置”復(fù)選框即可把各賬齡區(qū)間的應(yīng)收賬款金額復(fù)制過來4、在B9單元格縱向求所有應(yīng)收賬款的和5、在“百分比”列的C4單元格中輸入公式“=B4/B$9