Excel電商數(shù)據(jù)分析與應(yīng)用 課件 10.2.1庫存績效分析 10.2.2補(bǔ)貨模型

產(chǎn)品運(yùn)營分析10.2.1庫存績效分析&10.2.2補(bǔ)貨模型1庫存績效分析2CONTENTS補(bǔ)貨模型庫存績效分析PARTONE庫存績效分析庫存分析是對企業(yè)的庫存績效進(jìn)行分析的過程，包括庫存預(yù)警和補(bǔ)貨數(shù)量的分析，幫助企業(yè)提高倉庫管理能力，提高庫存績效，降低不良庫存。庫存績效分析主要是監(jiān)控庫存量、庫存周轉(zhuǎn)率、售罄率和動銷率，是庫存分析的基本項(xiàng)。存貨周轉(zhuǎn)速度越快，存貨占用水平越低，流動性越強(qiáng)，存貨轉(zhuǎn)化為現(xiàn)金或應(yīng)收賬款的速度就越快，這樣會增強(qiáng)企業(yè)的短期償債能力及獲利能力。1．庫存量2．庫存周轉(zhuǎn)率庫存量是指倉庫中存放貨物的數(shù)量，一般庫存量要根據(jù)SKU（最小庫存單位）進(jìn)行統(tǒng)計(jì)，商品在正常銷售的過程的庫存量太大或?yàn)榱愣疾皇呛芎玫默F(xiàn)象。庫存周轉(zhuǎn)率是在某一時間段內(nèi)庫存貨物周轉(zhuǎn)的次數(shù)，是反映庫存周轉(zhuǎn)快慢程度的指標(biāo)。周轉(zhuǎn)率越大表明銷售情況越好。在商品保質(zhì)期及資金允許的條件下，可以適當(dāng)增加其庫存控制目標(biāo)天數(shù)，以保證合理的庫存。反之，則可以適當(dāng)減少其庫存控制目標(biāo)天數(shù)。庫存周轉(zhuǎn)率計(jì)算公式為：庫存績效分析減少存貨周轉(zhuǎn)天數(shù)可以提高存貨周轉(zhuǎn)率，從實(shí)際運(yùn)營來分析，需要減少存貨在庫時間。例10-8：某企業(yè)在2019年Q1的期初庫存量為32149，期末庫存量為23910，期間銷售量為8374，請計(jì)算該企業(yè)在2019年Q1的庫存周轉(zhuǎn)天數(shù)和庫存周轉(zhuǎn)率。解：2019年Q1可以認(rèn)為是90天，代入公式：售罄率=2.4萬÷3萬×100%=80%庫存績效分析3．售罄率售罄率是指一定時間段某種商品的銷售占總進(jìn)貨量的比例，售罄率是衡量商品收回銷售成本和費(fèi)用的一個考核指標(biāo)，便于確定商品銷售到何種程度可以進(jìn)行折扣銷售清倉處理的一個合理的標(biāo)準(zhǔn)。計(jì)算公式為：售罄率=實(shí)際銷售成本÷總進(jìn)貨成本×100%售罄率=實(shí)際累積銷售數(shù)量÷總進(jìn)貨數(shù)量×100%或者售謦率計(jì)算期間通常為一周、一個月或一個季度。例10-9：某企業(yè)的某商品在2019年Q1的總進(jìn)貨量是3萬，實(shí)際銷售量是2.4萬，計(jì)算售罄率。解：售罄率大于80%小于等于100%的為備貨命中。庫存績效分析4．動銷率動銷率是反映階段期間企業(yè)銷售商品的占比，反映了企業(yè)的商品運(yùn)營能力，是考驗(yàn)采銷部門和運(yùn)營部門的核心指標(biāo)之一。商品動銷率計(jì)算公式為：商品動銷率有別于電商的網(wǎng)店動銷率，電商的網(wǎng)店動銷率只考慮在線銷售的品種數(shù)，不考慮倉庫的總品種數(shù)，往往倉庫的總品種數(shù)要大于在線銷售的品種數(shù)。電商的網(wǎng)店動銷率為：例10-10：某企業(yè)在2019年1月的倉庫總品種數(shù)為131個，其中有98個品種在期間產(chǎn)生了銷量，計(jì)算動銷率。解：補(bǔ)貨模型PARTTWO補(bǔ)貨模型哪個SKU需要補(bǔ)貨？需要補(bǔ)多少貨？這是運(yùn)營或者采購經(jīng)常遇到的場景。本小節(jié)介紹該場景的業(yè)務(wù)建模，提高采購的工作效率和補(bǔ)貨數(shù)量命中率（實(shí)際售罄誤差在15%內(nèi)為命中）。1．補(bǔ)貨業(yè)務(wù)邏輯補(bǔ)貨要考慮現(xiàn)有庫存量、未來可能產(chǎn)生的銷量、安全庫存量和供應(yīng)鏈的補(bǔ)貨周期，在買家下單時現(xiàn)有庫存量能夠支撐等待補(bǔ)貨的時間。比如補(bǔ)貨周期時14天，要提前預(yù)留超過14天的庫存，在剩余14天庫存時就要給工廠下單。補(bǔ)貨模型2．補(bǔ)貨數(shù)量測算例10-11：創(chuàng)建模型計(jì)算需要補(bǔ)貨的商品和數(shù)量。數(shù)據(jù)采集自商家后臺的訂單報表和寶貝報表。解：在后臺生成訂單報表和寶貝報表，如圖10-81所示。生成報表后，分別下載訂單報表和寶貝報表，報表下載界面如圖10-82所示。補(bǔ)貨模型另外建立一張庫存統(tǒng)計(jì)報表，記錄現(xiàn)時庫存量和補(bǔ)貨周期如圖10-83所示，庫存統(tǒng)計(jì)表的“商家編碼”對應(yīng)寶貝報表的“商家編碼”，補(bǔ)貨周期是指補(bǔ)貨所需的天數(shù)。補(bǔ)貨模型如圖10-84所示，打開Excel，在【PowerPivot】選項(xiàng)卡中單擊【管理】選項(xiàng)，轉(zhuǎn)到PowerPivot窗口，進(jìn)入PowerPivot管理界面。如圖10-85所示，在PowerPivot窗口的【主頁】選項(xiàng)卡中單擊【從其他源】選項(xiàng)，進(jìn)入“表導(dǎo)入向?qū)А睂υ捒?，選擇【Excel文件】，然后單擊【下一步】按鈕。圖10-84圖10-85補(bǔ)貨模型在圖10-86所示的對話框中，先單擊【瀏覽】按鈕選擇好文件路徑，再勾選復(fù)選框【使用第一行作為列標(biāo)題】，單擊【下一步】按鈕。注：文中已經(jīng)把原來下載好的表格從CSV格式改為EXCEL文本格式。如圖10-87所示，在寶貝報表中新建匯總的度量值，這個度量值要用于后面的運(yùn)算。補(bǔ)貨模型如圖10-88所示，在PowerPivot的【主頁】選項(xiàng)卡中單擊【關(guān)系圖視圖】建模，將“訂單報表”的“訂單編號”字段連接到“寶貝報表”的“訂單編號”，“寶貝報表”的“商家編碼”連接到“庫存統(tǒng)計(jì)表”的“商家編碼”。

回到庫存報表，鍵入公式“=SUMX(FILTER('寶貝報表','寶貝報表'[商家編碼]='庫存統(tǒng)計(jì)表'[商家編碼]&&DATEDIFF(RELATED('訂單報表'[訂單付款時間]),Date(2022,1,30),day)<='庫存統(tǒng)計(jì)表'[補(bǔ)貨周期]),[購買數(shù)量的總和])”，求出的近N天銷量是基于補(bǔ)貨周期的天數(shù)計(jì)算的銷量，結(jié)果如圖10-89所示。圖10-88圖10-89SUMX函數(shù)說明如下。函數(shù)功能：返回為表中每一行計(jì)算的表達(dá)式之和。函數(shù)語法：SUMX（<表名>,<表達(dá)式>）DATEDIFF函數(shù)說明如下。函數(shù)功能：返回兩個日期的單位間隔（可指定單位）。函數(shù)語法：DATEDIFF（起始時間，結(jié)束時間，時間單位）。RELATED函數(shù)說明如下。RELATED函數(shù)說明如下。函數(shù)功能：返回與當(dāng)前表相關(guān)的列的值，應(yīng)用在表間已經(jīng)創(chuàng)建的關(guān)系，從關(guān)系表中查找相關(guān)數(shù)據(jù)。補(bǔ)貨模型圖10-90圖10-91鍵入公式“=IF(CEILING(DIVIDE([庫存],[近N天銷量]),1)-1<0,0,CEILING(DIVIDE([庫存],[近N天銷量]),1)-1)*[補(bǔ)貨周期]”，計(jì)算出現(xiàn)有的庫存可以銷售幾天，在多少天后需要補(bǔ)貨，結(jié)果如圖10-90所示。鍵入公式“=IF([多少天后要補(bǔ)貨]=0,[近N天銷量]-[庫存],0)”，計(jì)算出最小補(bǔ)貨量，最小補(bǔ)貨量是指可以靈活調(diào)配的補(bǔ)貨量，可能并不需要完整的補(bǔ)貨周期，結(jié)果如圖10-91所示。CEILING函數(shù)說明如下。函數(shù)功能：將數(shù)字向上舍入到最接近的整數(shù)，或者基數(shù)的最接近倍數(shù)。函數(shù)語法：CEILING（數(shù)值，基數(shù)）。DIVIDE函數(shù)說明如下。函數(shù)功能：處理除數(shù)為零或者為空的情況。函數(shù)語法：DIVIDE（分子，分母）。補(bǔ)貨模型圖10-92圖10-93鍵入公式“=IF('庫存統(tǒng)計(jì)表'[多少天后要補(bǔ)貨]=0,'庫存報表'[最小補(bǔ)貨量]+'庫存統(tǒng)計(jì)'[近N天銷量],0)”，計(jì)算庫存一周期的備貨量，至少保留一個備貨周期的庫存，結(jié)果如圖10-92所示。如圖10-93所示，在PowerPivot【主頁】選項(xiàng)卡中單擊【數(shù)據(jù)透視表】選項(xiàng)，在“創(chuàng)建數(shù)據(jù)透視表”對話框中勾選【新工作表】，單擊【確定】按鈕，在數(shù)據(jù)透視表上單擊鼠標(biāo)右鍵，在菜單中選擇【顯示字段列表】。補(bǔ)貨模型圖10-94圖10-95如圖10-94所示，將“庫存統(tǒng)計(jì)表”中的字段設(shè)置在相應(yīng)的行和列。如圖10-95所示，一張完整的庫存監(jiān)