การคำนวณต้นทุนขาย (Cost of Goods Sold) เป็นงานหนึ่งที่หลีกเลี่ยงไม่ได้ในงานธุรกิจ ซึ่งที่ผ่านมาแม้สามารถใช้ Excel คำนวณหาต้นทุนได้ก็ตามก็ยังคำนวณได้ยาก มีหลายขั้นตอนยิ่งมีวัตถุดิบมากมายหลายประเภท ยิ่งทำให้ผู้ทำงานเกิดความเครียด เพราะต้องใช้ Excel สร้างสูตรคำนวณหาต้นทุนทั้งยอดรับยอดจ่ายแต่ละยอดด้วยตัวเอง หรือถ้าใช้สูตรที่สร้างขึ้นด้วย VBA ก็ยากเกินกว่าที่คนทั่วไปจะใช้เป็นบางคนถึงกับยอมแพ้หันไปใช้เครื่องคิดเลขคำนวณหาต้นทุนแทนเสียด้วยซ้ำ

บทความนี้จะนำเสนอวิธีการคำนวณหาต้นทุนขายแบบ FIFO โดยใช้คำสั่ง Data Table ซึ่งคำสั่งนี้มีอยู่ใน Excel ตั้งแต่รุ่นแรก แต่น้อยคนนักจะใช้เป็นขนาดผู้เขียนซึ่งคิดว่าตัวเองเข้าใจ Data Table เป็นอย่างดียังนึกไม่ถึงว่าจะนำ Data Table มาใช้คำนวณหาต้นทุนขายแบบ FIFO ได้ด้วย โดยแบ่งเนื้อหาคำอธิบายออกเป็นหลายตอน ดังนี้

1. แนวทางการออกแบบตารางคำนวณต้นทุนขายแบบ FIFO
2. วิธีใช้ Data Table กับงานสินค้าคงคลังโดยทั่วไป
3. วิธีใช้ Data Table คำนวณหาต้นทุนขายแบบ FIFO
4. วิธีคำนวณต้นทุน กรณีบันทึกสินค้าหลายประเภทที่ซื้อไว้ในตารางเดียวกัน
5. วิธีคำนวณต้นทุน กรณีบันทึกสินค้าหลายประเภทที่ซื้อและขายไว้ในตารางเดียวกัน

คุณควรมีพื้นความรู้ทราบวิธีใช้เครื่องหมาย $ควบคุมตำแหน่งอ้างอิงและการใช้ชื่อ Range Name และต้องเข้าใจมาก่อนว่าวิธีการคำนวณต้นทุนขายแบบ FIFO ด้วยเครื่องคิดเลขทำกันอย่างไร

Download แฟ้มตัวอย่างได้จาก http://www.excelexperttraining.com/forums/showthread.php?t=3345

แนวทางการออกแบบตาราง
คำนวณต้นทุนขายแบบ FIFO
ในภาพนี้เป็นตารางข้อมูลการรับสินค้าตามลำดับก่อนหลังกล่าวคือ ครั้งแรกรับสินค้าจำนวน 50,000 หน่วยในราคาต้นทุน 10 บาทต่อหน่วย ครั้งที่สองรับสินค้าจำนวน 10,000 หน่วยในราคาต้นทุน 15 บาทต่อหน่วยครั้งที่สามรับสินค้าจำนวน 70,000 หน่วยในราคาต้นทุน 10 บาทต่อหน่วย ครั้งที่สี่รับสินค้าจำนวน 100,000 หน่วยในราคาต้นทุน 5 บาทต่อหน่วย หากขายสินค้าจำนวน 100,000 หน่วย จะสร้างสูตรคำนวณหาต้นทุนขายอย่างไร
ถ้าคำนวณด้วยเครื่องคิดเลข สินค้าที่ขายจำนวน 100,000 หน่วย คิดเป็นต้นทุนขาย 1,050,000 บาทซึ่งคำนวณได้จากสูตร

=(50000*10)+(10000*15)+(40000*10)
=500000+150000+400000

แต่ถ้าขายรวมเป็นจำนวน 150,000 หน่วย คิดเป็นต้นทุนขาย 1,450,000 บาทซึ่งคำนวณได้จากสูตร
=(50000*10)+(10000*15)+(70000*10)+(20000*5)
=500000+150000+700000+100000

หลายคนเมื่อหันมาใช้ Excel คำนวณหาต้นทุนขายมักใช้วิธีสร้างสูตรคำนวณเลียนแบบวิธีคำนวณด้วยเครื่องคิดเลขโดยพยายามหาสูตรลัดเพียงสูตรเดียวสร้างลงไปในเซลล์เดียวเพื่อลัดหายอดต้นทุนขายทำให้ต้องนำสูตรมาคำนวณซ้อนกันหลายชั้นจนกลายเป็นยาว ยากต่อการแกะหรือแก้ไขอีกทั้งมีจุดอ่อนที่สำคัญคือไม่สามารถนำสูตรนั้นมาใช้คำนวณหาต้นทุนขายเมื่อยอดจำนวนสินค้าที่ขายเปลี่ยนต่างไปจากเดิมหรือหากคิดสูตรออกมาได้ก็จะกลายเป็นสูตรที่ยาวเหยียดมากกว่าเดิมเข้าไปอีกซึ่งจวนจนทุกวันนี้ผมก็ยังไม่สามารถคิดสูตรลัดสูตรเดียวเพื่อใช้คำนวณหาต้นทุนขายในเซลล์เดียวได้แต่อย่างไรและเชื่อว่าไม่มีใครคิดสูตรเดียวออกมาได้หรอกเว้นแต่จะสร้างด้วยรหัส VBA เท่านั้น

แทนที่จะคิดหาสูตรลัด การออกแบบตารางคำนวณให้ดีจะช่วยคำนวณต้นทุนขายของแต่ละยอดสินค้าที่รับเข้ามาจะง่ายกว่าทั้งในแง่สะดวกต่อการทำความเข้าใจและการตรวจสอบความถูกต้องของการคำนวณ


จากภาพนี้ ตารางด้านซ้ายตั้งแต่เซลล์ D6:E11บันทึกจำนวนและต้นทุนต่อหน่วยของสินค้าที่รับเข้ามา เซลล์ G2 ตั้งชื่อว่า TotalUnit ใช้บันทึกจำนวนรวมของสินค้าที่ขายออกไปทั้งหมดซึ่งสมมติว่าขายออกไป 100,000 หน่วย แล้วยอดนี้จะถูกกระจายออกไปในเซลล์ G6:G11 เพื่อตัดยอดซื้อแบบ FIFO เป็นจำนวนเท่ากับ 50,000 หน่วย 10,000 หน่วย และ 40,000 หน่วยตามลำดับ เมื่อนำมาคูณกับต้นทุนต่อหน่วยจากเซลล์ E6:E11 ทำให้คำนวณหาต้นทุนขายแบบ FIFO แสดงไว้ในเซลล์ H6:H11 รวมเป็นผลลัพธ์ต้นทุนขายที่ต้องการแสดงไว้ในเซลล์ H12

สูตรที่ใช้กระจายยอดซื้อในเซลล์ G6:G11 ถือเป็นหัวใจของการคำนวณทั้งหมด โดยสร้างสูตร =MIN(D6,TotalUnit-SUM($G$5:G5)) ลงไปในเซลล์ G6 แล้ว copy ลงไปจนถึงเซลล์ G11

สูตร =MIN(D6,TotalUnit-SUM($G$5:G5)) ใช้คำนวณยอดจำนวนหน่วยขายที่สามารถตัดออกจากยอดจำนวนซื้อโดยตัดได้สูงสุดไม่เกินจำนวนซื้อ

· D6 เป็นจำนวนหน่วยที่ซื้อเข้ามา
· TotalUnit-SUM($G$5:G5) เป็นจำนวนหน่วยที่ขายทั้งหมดลบด้วยรวมจำนวนหน่วยขายสะสมที่ตัดออกไปแล้วในงวดก่อนหน้าสังเกตว่า $G$5 เป็นตำแหน่งอ้างอิงแบบคงที่ ส่วน G5 เป็นตำแหน่งอ้างอิงที่จะขยับตำแหน่งไปตามแต่ว่าจะ copy สูตรนี้ไปวางไว้ที่เซลล์ใด ทำให้ช่วงของตำแหน่งอ้างอิงขยายตัวไปเรื่อยๆจากช่วง$G$5:G5 > $G$5:G6 > $G$5:G7 > $G$5:G8 > $G$5:G9 > $G$5:G10
· ขั้นตอนการสร้างสูตร SUM($G$5:G5) เมื่อพิมพ์ถึงคำว่า sum( แล้วให้คลิกที่เซลล์ G5จากนั้นให้กดปุ่มจุดทศนิยม . จะทำให้สูตรปรับตัวเองเป็นช่วงเซลล์G5:G5 ให้ทันที แล้วให้พิมพ์เครื่องหมายวงเล็บปิดแล้วคลิกลงไปที่คำว่าG5 ตัวแรกในสูตรแล้วกดปุ่ม F4 ครั้งหนึ่งทำให้G5 เปลี่ยนไปเป็น $G$5 แล้วจึงกดปุ่ม Enterเพื่อรับสูตรลง

เมื่อทดลองเปลี่ยนจำนวนหน่วยที่ขายรวมในเซลล์ G2 จากยอด 30,000 > 70,000 > 100,000 > 150,000 หน่วย จะเห็นการกระจายยอดรวมจำนวนหน่วยที่ขายออกไปตามภาพต่อไปนี้


โปรดสังเกตว่าตัวเลขที่ใช้บันทึกลงไปในเซลล์ G2 นั้นเป็นตัวเลขจำนวนหน่วยรวมสะสมของสินค้าที่ขายไปแล้วทั้งหมดมิใช่ตัวเลขจำนวนหน่วยที่ขายในแต่ละครั้ง การคำนวณข้างต้นจึงยังเป็นภาระของผู้สร้างงานต้องคำนวณเองเพื่อหาต้นทุนของสินค้าที่ขาย 30,000 หน่วยแรก และ 40,000หน่วยถัดไป และ 30,000 หน่วยถัดไป และ 50,000 หน่วยถัดไป โดยคำนวณจากส่วนต่างของยอดต้นทุนขายรวมแต่ละครั้ง เช่น นำยอดต้นทุนรวม 750,000 บาทลบด้วย 300,000 บาท คงเหลือเป็นต้นทุน 450,000 บาทของสินค้าจำนวน 40,000 หน่วยเป็นต้น ซึ่งการคำนวณเหล่านี้หากใช้ Data Table ช่วยจะลัดหาคำตอบได้ทันที

Links for FIFO Costing with Excel Data Table

Part I :
http://social.technet.microsoft.com/wiki/contents/articles/14006.fifo-costing-with-excel-data-table-part-i-th-th.aspx

Part II :
http://social.technet.microsoft.com/wiki/contents/articles/14020.fifo-costing-with-excel-data-table-part-ii-th-th.aspx

Part III :
http://social.technet.microsoft.com/wiki/contents/articles/14039.fifo-costing-with-excel-data-table-part-iii-th-th.aspx

Part IV :
http://social.technet.microsoft.com/wiki/contents/articles/14040.fifo-costing-with-excel-data-table-part-iv-th-th.aspx