「採購知識分享」 | 如何通過EXCEL建立採購成本的分析表

2020-01-10     採購的伯樂

在企業進行活動時,往往需要採購大量的材料,但是材料的成本除了一項價格因素外,還有一個非常重要的因素,就是採購的成本。

  採購成本包括購買價款、相關稅費、運輸費、裝卸費、保險費以及其他可歸屬於存貨採購成本的費用。在這些費用中,可以歸類為2項,其就是採購環節發生的費用和材料儲存發生的費用。購買價款、相關稅費、運輸費、裝卸費、保險費就可以看成是採購環節產生的費用,而儲存採購貨物則是材料儲存發生非費用。

  這兩項費用是相互制約的,例如,在採購量較大的情況下,採購的次數就會減少,就可以減少採購成本,但是其儲存費用就會增加;反之則亦然。



  因此確定採購量和存儲量之間的關係也是一項很重要的工作,通過「採購成本分析」可以幫助企業設置科學合理的採購量和採購次數,從而為降低企業採購成本提供可靠依據。


  首先,我們還是要來看一下本節要使用的公式:

  採購當量=年採購量/年採購批次平均存量=採購數量/2存儲成本=平均存量×單位存儲成本採購成本=年採購批次×採購成本總成本=存儲成本+採購成本


步驟01 新建工作表

  新建一個工作簿,並在工作簿中重命名其中一個工作表,刪除其他的。輸入表格的欄位標題、數據,並設置格式等。


步驟02 輸入公式

  在B2到F2依次輸入下列公式:

  =$B$19/A2

  =B2/2

  =C2*$I$19

  =A2*$E$19

  =D2+E2


 步驟03 複製公式

  選中B2:F2,移動光標到F2右下角,雙擊即可複製上述公式至B3:F13區域。


 步驟04 最低採購成本公式

  在B16單元格輸入公式:=MIN(F2:F13)。


 步驟05 採購批次公式

  在E16單元格輸入公式:=INDEX(A2:A13,MATCH(B16,F2:F13,0))。


 步驟06 採購量公式

  在I16單元格輸入公式:=INDEX(B2:B13,MATCH(B16,F2:F13,0))。關於INDEX函數的使用我們在前面的相關章節已經介紹過。

  知識點:

  MIN函數返回一組值中的最小值。

  函數語法MIN(number1,number2,...)Number1,number2,...是要從中查找最小值的1到255個數字。

  函數說明參數可以是數字或者是包含數字的名稱、數組或引用。邏輯值和直接鍵入到參數列表中代表數字的文本被計算在內。如果參數為數組或引用,則只使用該數組或引用中的數字。數組或引用中的空白單元格、邏輯值或文本將被忽略。如果參數中不含數字,則函數MIN返回0。如果參數為錯誤值或為不能轉換為數字的文本,將會導致錯誤。

  MATCH函數返回在指定方式下與指定數值匹配的數組中元素的相應位置。如果需要找出匹配元素的位置而不是匹配元素本身,則應該使用MATCH函數而不是LOOKUP函數。

  函數語法MATCH(lookup_value,lookup_array,match_type)Lookup_value為需要在Lookup_array中查找的數值。例如,如果要在電話簿中查找某人的電話號碼,則應該將姓名作為查找值,但實際上需要的是電話號碼。Lookup_value可以為數值(數字、文本或邏輯值)或對數字、文本或邏輯值的單元格引用。

  Lookup_array可能包含所要查找的數值的連續單元格區域。Lookup_array應為數組或數組引用。  Match_type為數字-1、0或1。Match_type指明Excel如何在lookup_array中查找lookup_value。如果match_type為1,函數MATCH查找小於或等於lookup_value的最大數值,Lookup_array必須按升序排列:...、-2、-1、0、1、2、...、A-Z、FALSE、TRUE;如果match_type為0,函數MATCH查找等於lookup_value的第一個數值,Lookup_array可以按任何順序排列;如果match_type為-1,函數MATCH查找大於或等於lookup_value的最小數值,Lookup_array必須按降序排列:TRUE、FALSE、Z-A、...、2、1、0、-1、-2、...,等等。如果省略match_type,則假設為1。

  函數說明函數MATCH返回lookup_array中目標值的位置,而不是數值本身。例如,MATCH("b",{"a","b","c"},0)返回2,即「b」在數組{"a","b","c"}中的相應位置。查找文本值時,函數MATCH不區分大小寫字母。如果函數MATCH查找不成功,則返回錯誤值#N/A。如果match_type為0且lookup_value為文本,可以在lookup_value中使用通配符、問號(?)和星號(*)。問號匹配任意單個字符;星號匹配任意一串字符。如果要查找實際的問號或星號,請在該字符前鍵入波形符(~)。


 步驟07 添加年採購量滾動條

  我們前面已經在自定義快速訪問工具欄上添加了滾動條按鈕,現在我們又要用到它。點擊這個按鈕,然後在A21單元格拖出一個滾動條。


 步驟08 設置年採購量滾動條

  在剛剛添加的滾動條上單擊滑鼠右鍵,在彈出的對話框中對滾動條進行如圖中所示的設置。與這個滾動條相關聯的單元格是B19,設置方法是單擊文本框後面的編輯按鈕,再用滑鼠選中B19單元格。單擊「確定」完成設置。


 步驟09 添加並設置採購成本滾動條

  使用與上兩步相機的方法在D21單元格添加滾動條,並對其進行設置,設置內容如圖所示。


 步驟10 添加並設置單位存儲成本滾動條

  同理,在H21單元格添加滾動條,並對其進行設置,設置內容如圖所示。


 步驟11 完成設置

  通過以上幾個步驟的設置,我們已經得到一個具有數據調節功能的採購成本EXCEL分析表了。(END)


採購學習平台

讓採購成為一種樂趣

* (有好的建議隨時評論找我嘻嘻嘻)

文章來源: https://twgreatdaily.com/zh-cn/ZbXBmW8BxtCz63RL4J3f.html