商務(wù)數(shù)據(jù)分析與應(yīng)用 課件 項(xiàng)目八 商品庫(kù)存分析

商務(wù)數(shù)據(jù)分析與應(yīng)用重慶大學(xué)出版社尹愛飛，杜轉(zhuǎn)萍主編本書目錄項(xiàng)目一商務(wù)數(shù)據(jù)分析基礎(chǔ)項(xiàng)目二認(rèn)識(shí)數(shù)據(jù)表項(xiàng)目三

商務(wù)數(shù)據(jù)錄入與整理項(xiàng)目四

商務(wù)數(shù)據(jù)分析工具項(xiàng)目五

商務(wù)數(shù)據(jù)可視化項(xiàng)目六商品銷售情況統(tǒng)計(jì)與分析項(xiàng)目七商品銷售費(fèi)用分析項(xiàng)目八

商品庫(kù)存分析項(xiàng)目九

銷售市場(chǎng)預(yù)測(cè)分析項(xiàng)目八

商品庫(kù)存分析賣家對(duì)于商品庫(kù)存的分析有助于正確判斷銷售商品的供給能力，同時(shí)也可以判斷商品銷售的暢銷與滯銷狀況，及時(shí)調(diào)整商品的進(jìn)貨種類，處理積壓的庫(kù)存商品，補(bǔ)給缺貨的商品，保證線上線下商品銷售的順利進(jìn)行。請(qǐng)根據(jù)店鋪“商品庫(kù)存表”相關(guān)數(shù)據(jù)計(jì)算庫(kù)存周轉(zhuǎn)率、存銷比、動(dòng)銷率等指標(biāo)，判斷庫(kù)存商品的結(jié)構(gòu)與供給狀況，同時(shí)制定庫(kù)存商品查詢表，以便快速查詢庫(kù)存商品的基本信息。目錄1庫(kù)存指標(biāo)分析商品庫(kù)存數(shù)據(jù)分析28.1.1單一庫(kù)存商品信息查詢8.1.2商品庫(kù)存數(shù)量統(tǒng)計(jì)分析STEP1：打開素材文件/項(xiàng)目8/單一商品查詢表，在表格空白處編制單一商品庫(kù)存信息查詢表的框架，包含“產(chǎn)品名稱”、“采購(gòu)金額”、“廠商”、“保質(zhì)期”、“成本單價(jià)”等需要查詢和顯示的庫(kù)存商品信息，如圖8-1-1所示。8.1.1單一庫(kù)存商品信息查詢STEP2：進(jìn)行數(shù)據(jù)有效性的設(shè)置。選中“產(chǎn)品名稱”下面空白單元格A24，點(diǎn)擊“數(shù)據(jù)”選項(xiàng)卡，選擇“數(shù)據(jù)驗(yàn)證”，如圖8-1-2所示。在彈出的對(duì)話框里面設(shè)置驗(yàn)證條件。點(diǎn)擊“允許”下拉菜單，選擇“序列”，點(diǎn)擊“來源”處箭頭，選中“A6：A20”區(qū)域范圍，即選中產(chǎn)品名稱一列，如圖8-1-3所示。點(diǎn)擊A24單元格，產(chǎn)品名稱全部已經(jīng)列在下拉菜單中。8.1.1單一庫(kù)存商品信息查詢STEP3：進(jìn)行相關(guān)信息的匹配。在A24單元格中任選一種產(chǎn)品名稱如“蜜桃汁飲料”，在B24單元格中輸入“=VLOOKUP”，依次輸入函數(shù)的各個(gè)部分，如圖8-1-4所示。函數(shù)語(yǔ)法第一部分中“A24”為查找的值；函數(shù)第二部分語(yǔ)法為查找的區(qū)域范圍“A2：J20”，查找的區(qū)域范圍首列應(yīng)為包含“A24”產(chǎn)品名稱所在的列數(shù)；函數(shù)第三部分為匹配的列數(shù)。由于B24單元格對(duì)應(yīng)的商品信息為“成本單價(jià)”，而“成本單價(jià)”位于選中區(qū)域的第7列，因此函數(shù)語(yǔ)法第三部分為“7”；函數(shù)第四部分為精確匹配，即“FALSE”。按“Enter”鍵，則單元格“B24”顯示結(jié)果為“40.5”元。8.1.1單一庫(kù)存商品信息查詢STEP4：進(jìn)行公式復(fù)制。選中B24單元格，將公式中的前兩部分語(yǔ)法鎖定。然后按“Enter”回車鍵，如圖8-1-5所示。選中B24單元格向右拖動(dòng)鼠標(biāo)，進(jìn)行公式的初始復(fù)制，并依次修改復(fù)制的函數(shù)中匹配的列數(shù)，結(jié)果如圖8-1-6所示。8.1.1單一庫(kù)存商品信息查詢STEP5：進(jìn)行信息查詢。點(diǎn)擊A24單元格，任選一類產(chǎn)品，當(dāng)產(chǎn)品名稱變化時(shí)，表中的信息也進(jìn)行了相應(yīng)的變化，單一商品查詢表就制作完成了，結(jié)果如圖8-1-7所示。8.1.1單一庫(kù)存商品信息查詢STEP1：打開素材文件/項(xiàng)目8/“庫(kù)存數(shù)據(jù)分析”.xlsx/庫(kù)存數(shù)據(jù),點(diǎn)擊“數(shù)據(jù)”選項(xiàng)卡，選擇“分類匯總”功能。在彈出的對(duì)話框中分類字段選擇“產(chǎn)品名稱”，匯總方式選擇“求和”，匯總項(xiàng)選擇“期末庫(kù)存數(shù)量”，如圖8-1-8所示。分類匯總的結(jié)果如圖8-1-9所示。8.1.2商品庫(kù)存數(shù)量統(tǒng)計(jì)分析STEP2：打開素材文件/項(xiàng)目8/“庫(kù)存數(shù)據(jù)分析”.xlsx/庫(kù)存狀態(tài)，根據(jù)分類匯總的結(jié)果統(tǒng)計(jì)各個(gè)產(chǎn)品系列的期末庫(kù)存數(shù)量，與標(biāo)準(zhǔn)庫(kù)存量進(jìn)行比較判斷各產(chǎn)品系列的庫(kù)存狀態(tài)。在H8單元格中輸入IF公式：

=IF(G8>F8,"庫(kù)存充足","庫(kù)存不足"),按Enter鍵結(jié)束。選中H8單元格，按住鼠標(biāo)左鍵向下拖動(dòng)，進(jìn)行公式的復(fù)制。每種產(chǎn)品系列的庫(kù)存狀態(tài)則顯示在單元格中，如圖8-1-10所示。8.1.2商品庫(kù)存數(shù)量統(tǒng)計(jì)分析STEP3：選中E7：G11表格區(qū)域，插入柱形圖，如圖8-1-11所示。從圖中可知，飲料產(chǎn)品系列的庫(kù)存嚴(yán)重不足，要馬上進(jìn)行補(bǔ)貨；自熱火鍋產(chǎn)品系列庫(kù)存相對(duì)充足；火鍋調(diào)料和腌制品產(chǎn)品系列的庫(kù)存量接近標(biāo)準(zhǔn)庫(kù)存量，要注意及時(shí)補(bǔ)貨。8.1.2商品庫(kù)存數(shù)量統(tǒng)計(jì)分析目錄1庫(kù)存指標(biāo)分析商品庫(kù)存數(shù)據(jù)分析2庫(kù)存周轉(zhuǎn)率是反映企業(yè)庫(kù)存周轉(zhuǎn)快慢程度的一個(gè)指標(biāo)，指的是庫(kù)存在一定周期內(nèi)周轉(zhuǎn)的次數(shù)。庫(kù)存周轉(zhuǎn)率越高，說明存貨的周轉(zhuǎn)速度越快，從成本投入到商品銷售再到資金回流的周期越短，銷售情況越好。庫(kù)存周轉(zhuǎn)率是分析庫(kù)存非常重要的一個(gè)指標(biāo)，庫(kù)存周轉(zhuǎn)率提高，庫(kù)存周轉(zhuǎn)加快，庫(kù)存占用資金減少，企業(yè)現(xiàn)金流提高，資金流通速度加快；同時(shí)庫(kù)存持有成本下降，企業(yè)利潤(rùn)得到提升，因此投入資金一定的情況下，庫(kù)存周轉(zhuǎn)率越高，企業(yè)的投資回報(bào)率越高。同時(shí)庫(kù)存周轉(zhuǎn)率是一個(gè)相對(duì)系數(shù)，可以和外部企業(yè)進(jìn)行橫向?qū)Ρ?，從而了解自己企業(yè)庫(kù)存周轉(zhuǎn)的合理性。

庫(kù)存周轉(zhuǎn)率=銷貨成本/平均存貨余額8.2.1庫(kù)存周轉(zhuǎn)率與周轉(zhuǎn)天數(shù)庫(kù)存周轉(zhuǎn)天數(shù)是企業(yè)從取得存貨開始，直到消耗、銷售為止所經(jīng)歷的天數(shù)。庫(kù)存周轉(zhuǎn)天數(shù)=周期天數(shù)/庫(kù)存周轉(zhuǎn)率=（周期天數(shù)*平均存貨余額）/銷貨成本8.2.1庫(kù)存周轉(zhuǎn)率與周轉(zhuǎn)天數(shù)STEP1：打開素材文件/項(xiàng)目8/庫(kù)存周轉(zhuǎn)率與周轉(zhuǎn)天數(shù).xlsx，計(jì)算期末庫(kù)存數(shù)量，期末庫(kù)存數(shù)量=期初庫(kù)存數(shù)量+入庫(kù)數(shù)量-出庫(kù)數(shù)量。在H3單元格輸入公式“=E3+F3-G3”,并按回車鍵。選中H3單元格，當(dāng)光標(biāo)變?yōu)楹谑謺r(shí)向下拖動(dòng)鼠標(biāo)進(jìn)行公式復(fù)制，計(jì)算出不同商品的期末庫(kù)存數(shù)量，結(jié)果如圖8-2-1所示。8.2.1庫(kù)存周轉(zhuǎn)率與周轉(zhuǎn)天數(shù)STEP2：計(jì)算平均庫(kù)存余額，平均庫(kù)存余額=（期初庫(kù)存余額+期末庫(kù)存余額）/2。期初庫(kù)存余額=期初庫(kù)存數(shù)量*成本單價(jià)，期末庫(kù)存余額=期末庫(kù)存數(shù)量*成本單價(jià)，因此，在I3單元格中輸入公式“=(E3*D3+H3*D3)/2”，并按回車鍵。選中I3單元格，當(dāng)光標(biāo)變?yōu)楹谑謺r(shí)向下拖動(dòng)鼠標(biāo)進(jìn)行公式復(fù)制，計(jì)算出不同商品的平均庫(kù)存余額，結(jié)果如圖8-2-2所示。8.2.1庫(kù)存周轉(zhuǎn)率與周轉(zhuǎn)天數(shù)STEP3：計(jì)算銷貨成本，銷貨成本=銷貨數(shù)量*成本單價(jià)。這里的銷貨數(shù)量等于出庫(kù)數(shù)量。在J3單元格中輸入公式“=G3*D3”，并按回車鍵。選中J3單元格，當(dāng)光標(biāo)變?yōu)楹谑謺r(shí)向下拖動(dòng)鼠標(biāo)進(jìn)行公式復(fù)制，計(jì)算出不同商品的銷貨成本，結(jié)果如圖8-2-3所示。8.2.1庫(kù)存周轉(zhuǎn)率與周轉(zhuǎn)天數(shù)STEP4：計(jì)算庫(kù)存周轉(zhuǎn)率，庫(kù)存周轉(zhuǎn)率=銷貨成本/平均庫(kù)存余額。在K3單元格中輸入公式“=J3/I3”，并按回車鍵。選中K3單元格，當(dāng)光標(biāo)變?yōu)楹谑謺r(shí)向下拖動(dòng)鼠標(biāo)進(jìn)行公式復(fù)制，計(jì)算出不同商品的庫(kù)存周轉(zhuǎn)率，結(jié)果如圖8-2-4所示。8.2.1庫(kù)存周轉(zhuǎn)率與周轉(zhuǎn)天數(shù)STEP5：計(jì)算庫(kù)存周轉(zhuǎn)天數(shù)，庫(kù)存周轉(zhuǎn)天數(shù)=周期天數(shù)/庫(kù)存周轉(zhuǎn)率。在L3單元格中輸入公式“=30/K3”，并按回車鍵。選中L3單元格，當(dāng)光標(biāo)變?yōu)楹谑謺r(shí)向下拖動(dòng)鼠標(biāo)進(jìn)行公式復(fù)制，計(jì)算出不同商品的庫(kù)存周轉(zhuǎn)天數(shù)，結(jié)果如圖8-2-5所示。8.2.1庫(kù)存周轉(zhuǎn)率與周轉(zhuǎn)天數(shù)存銷比（庫(kù)銷比）是指在一個(gè)周期內(nèi)，商品平均庫(kù)存或本周期期末庫(kù)存與周期內(nèi)總銷售的比值，是用來反映商品即時(shí)庫(kù)存狀況的相對(duì)數(shù)。而更為精確的法則是使用日均庫(kù)存和日均銷售的數(shù)據(jù)來計(jì)算，從而反映當(dāng)前的庫(kù)存銷售比例。存銷比=期末庫(kù)存金額（or數(shù)量）/周期內(nèi)商品銷售金額（or數(shù)量）公式中的周期可以是周、月、季度等等。存銷比過高，庫(kù)存資金占用過大，存銷比過低，則容易備貨不足，損失銷售機(jī)會(huì)。8.2.2存銷比與動(dòng)銷率動(dòng)銷率是指銷售的商品種數(shù)與倉(cāng)庫(kù)所有商品種數(shù)的比值。動(dòng)銷率=（動(dòng)銷品種數(shù)/倉(cāng)庫(kù)總品種數(shù)）*100%一般情況下，動(dòng)銷率越高，企業(yè)商品周轉(zhuǎn)就越快；動(dòng)銷率越低則代表周轉(zhuǎn)速度越慢。動(dòng)銷率能夠作為商品經(jīng)營(yíng)深度與寬度的參考，動(dòng)銷率低的商品企業(yè)則不需要花費(fèi)大量時(shí)間和精力去經(jīng)營(yíng)。8.2.2存銷比與動(dòng)銷率STEP1：第一種計(jì)算方式：存銷比=期末庫(kù)存數(shù)量/周期內(nèi)商品銷售數(shù)量打開“素材文件/項(xiàng)目8/存銷比與動(dòng)銷率.xlsx，在L3單元格中輸入公式“=K3/J3”，然后按Enter鍵，結(jié)果顯示為1.8，如圖8-2-6所示。選中L3單元格，按下鼠標(biāo)左鍵拖動(dòng)公式至L4:L20區(qū)域，通過公式的復(fù)制計(jì)算每種產(chǎn)品的存銷比，結(jié)果如圖8-2-7所示。8.2.2存銷比與動(dòng)銷率STEP2：第二種計(jì)算方式：存銷比=期末庫(kù)存金額/周期內(nèi)商品銷售金額打開“素材文件/項(xiàng)目8/庫(kù)存數(shù)據(jù)分析.xlsx，在M3單元格中輸入公式“=(K3*G3)/(J3*E3)”，然后按Enter鍵，如圖8-2-8所示。選中M3單元格，按下鼠標(biāo)左鍵拖動(dòng)公式至M4:M20區(qū)域，通過公式的復(fù)制計(jì)算每種產(chǎn)品的存銷比，結(jié)果如圖8-2-9所示。其中，K3*G3為期末庫(kù)存金額的計(jì)算，等于期末庫(kù)存數(shù)量與產(chǎn)品成本單價(jià)的乘積；J3*E3為周期內(nèi)商品銷售金額的計(jì)算，等于出庫(kù)數(shù)量與銷售單價(jià)的乘積。在此，進(jìn)行了出庫(kù)數(shù)量等于銷售數(shù)量的假設(shè)。8.2.2存銷比與動(dòng)銷率STEP1：首先，點(diǎn)擊“開始”選項(xiàng)卡下面的“條件格式”，在下拉菜單中選擇“突出顯示單元格規(guī)則”中的“等于”選項(xiàng)。設(shè)置值為0，顏色可選擇默認(rèn)顏色或者自行進(jìn)行顏色的設(shè)置，如圖8-2-10所示。點(diǎn)擊“確定”，庫(kù)存數(shù)據(jù)分析表中出庫(kù)數(shù)量為0的對(duì)應(yīng)單元格則被標(biāo)記出來，結(jié)果如圖8-2-11所示。