ลำดับความคิดในการใช้ Excel สร้างงาน

StepbyStep

เมื่อได้รับมอบหมายงาน อย่ารีบร้อนเปิดคอมแล้วใช้ Excel เลยล่ะ ต้องคิด คิด คิด ให้รอบคอบก่อน

  1. ใครจะเป็นผู้ใช้แฟ้ม มีความสามารถในการใช้ Excel มากน้อยแค่ไหน
  2. จะใช้แฟ้มเพียงครั้งเดียวหรือต้องนำกลับมาใช้ซ้ำแล้วซ้ำอีก
  3. เร่งด่วนขนาดไหน
  4. รายงานที่ต้องการเป็นเรื่องอะไร มีหน้าตาอย่างไร แสดงข้อมูลอะไรบ้าง
  5. โครงสร้างตารางรายงานตายตัวแล้วหรือยัง
  6. ต้องหาข้อมูลอะไรบ้างมาใช้เพื่อสร้างรายงานนั้น
  7. ข้อมูลนั้นมีอยู่แล้ว ลิงก์มาใช้เลยได้ไหม หรือต้องทำขึ้นมาใหม่ ปรับแก้ใหม่ก่อน
  8. จำนวนรายการคงที่หรือมีรายการใหม่เพิ่มขึ้นอีกในอนาคต
  9. จำนวนข้อมูลมากน้อยแค่ไหน มีรายการซ้ำหรือไม่

จากนั้นจึงคิดถึงวิธีสร้างงาน

  1. จะแยกชีทหรือแยกแฟ้มดีกว่ากัน
  2. การคำนวณเรื่องใดบ้างที่นำไปใช้ต่อที่อื่นอีกหลายแห่ง
  3. จะเลือกใช้สูตรหรือใช้คำสั่งบนเมนูเพื่อหาผลลัพธ์ดีกว่ากัน
  4. มีตัวแปรอะไรบ้างที่ต้องเปลี่ยนไปเปลี่ยนมา
  5. ตัวแปรกระจายอยู่หลายที่ หรือเป็นตารางที่ติดกัน
  6. จะสร้างสูตรลัดสูตรเดียว หรือจะใช้หลายสูตรมาคำนวณแยกทีละขั้น
  7. ต้องเตรียมข้อมูลเพื่อใช้สร้างกราฟอะไรบ้าง
  8. จำเป็นไหมที่จะต้องใช้ VBA

FIFO Costing with Excel Data Table

 
FIFO10
 

หนึ่งในผลงานคิดค้นของผม มาดูเวลาเพื่อนชาวต่างชาติเขาขอลอกไปใช้ เขาให้เกียรติผมมากครับ

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

On a side note, there are examples where FIFO could be achieved through DATA Tables (non-VBA method) and Custom Functions method. As per listed below for reference as it may be more applicable for you depending on your unique set of circumstances / requirements.

By Somkiat Foongkiat: A very creative use of DATA TABLES (non- VBA method) ~ explanation with sample file could be found over at his site http://pakaccountants.com/fifo-costing-inventory-excel-data-tables/

คำอธิบายภาษาไทย

https://drive.google.com/file/d/1fUQ52bI7EKaxRYV7ipdPk6oWnK_d2N1i/view?usp=sharing

Download แฟ้มตัวอย่าง

https://drive.google.com/file/d/1zSmU3O92TtrHuwXeJcDWzYR-GcFPKvMo/view?usp=sharing

https://drive.google.com/file/d/1VTqoUnAhOabDSIosHHmqObg8_ndk1xiA/view?usp=sharing

https://drive.google.com/file/d/1R2AmUxkGEgH-E9UCxwm15cex35RTdHk_/view?usp=sharing

คำอธิบายภาษาอังกฤษ

https://pakaccountants.com/fifo-costing-inventory-excel-data-tables/

By Hasaan Fazal - February 19, 2016

เขาอีเมลมาขออนุญาตนำตัวอย่างไปใช้ และทำคำอธิบายให้อย่างดีมากครับ

วิธีสร้างใบเสนอราคา Invoice Quotation

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

ก่อนจะรีบร้อนกรอกอะไรลงไปในตาราง ต้องตั้งหลักไว้ว่า “สิ่งใดที่ทำลงไปในตารางจะทำไว้เพียงครั้งแรกครั้งเดียวเท่านั้น ห้ามกรอกข้อมูลซ้ำอย่างเด็ดขาด” โดยเฉพาะข้อมูลที่เป็นชื่อ เช่น ชื่อลูกค้า ชื่อสินค้า ชื่อพนักงานขาย หรือแม้แต่ชื่อที่อยู่ของบริษัทตัวเองที่พิมพ์ไว้บนหัวรายงาน เมื่อใดที่ต้องการนำชื่อเหล่านี้มาใช้แสดงในตาราง อย่าพิมพ์ซ้ำอีก แต่ให้สร้างสูตรลิงก์ดึงข้อมูลมาใช้ ซึ่งหนีไม่พ้นสูตร IF Choose And Or VLookup Match Index หรือใช้คำสั่ง Data > Validation โดยอาศัยการตั้งชื่อ Range Name ซึ่งจะทำให้สามารถดึงข้อมูลที่กรอกไว้มาใช้ข้ามชีทหรือข้ามแฟ้มได้สะดวกขึ้นมาก

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

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

ส่วนตัวข้อมูลที่ต้องนำมาใช้ซ้ำแล้วซ้ำอีกให้บันทึกไว้ในตารางฐานข้อมูล หรือตาราง Input ซึ่งแยกออกมาเป็นหลายตารางตามประเภทของข้อมูล เช่น

  • ตารางข้อมูลสินค้า ประกอบด้วย รหัสสินค้า ชื่อสินค้า หน่วยที่ใช้นับ รายละเอียดของสินค้า ราคาต้นทุน ราคาขาย
  • ตารางข้อมูลลูกค้า ประกอบด้วย รหัสลูกค้า ชื่อนามสกุล ที่อยู่ หมายเลขโทรศัพท์
  • ตารางข้อมูลพนักงานขาย ประกอบด้วย รหัสพนักงาน ชื่อนามสกุล
  • ตารางข้อมูลประเภทการส่งสินค้า ประกอบด้วย รหัสผู้ส่งสินค้า ชื่อผู้ส่ง วิธีการส่งสินค้า ระยะเวลาที่ใช้ในการส่งสินค้า
  • ตารางบันทึกการขายรายวัน ประกอบด้วย เลขที่ Invoice หรือเลขที่ใบเสร็จ วันที่และเวลาที่ขาย รหัสลูกค้า วันที่และเวลาที่จะส่งสินค้า
  • ตารางรายละเอียดของสินค้าที่ขายใน Invoice แต่ละใบ ประกอบด้วย เลขที่ Invoice รหัสสินค้า จำนวนสินค้า

สูตรที่ยากหน่อยในการดึงข้อมูลว่าเลขที่ Invoice แต่ละใบประกอบด้วยการขายสินค้าอะไรบ้าง ต้องพึงพาสูตรที่เรียกว่า สูตร Multiple Match แนะนำให้ Download นำตัวอย่างนี้ไปแกะกัน

https://drive.google.com/file/d/199CdxnHkN1A84bmI-ZnYoZnKL1zTBeqm/view?usp=sharing

Northwind

ตัวอย่างนี้เป็นตัวอย่างในโปรแกรม Microsoft Access ซึ่งผมลอกนำมาแสดงให้ดูว่า หากจะนำ Excel มาประยุกต์ใช้ก็ทำได้เช่นกัน ใช้เป็นตัวอย่างประกอบหลักสูตรหันมาใช้ Excel จัดการฐานข้อมูลแทน Access กันดีกว่า

วิธีประยุกต์ใช้ Excel ในการศึกษาความเป็นไปได้

ในช่วงกว่ายี่สิบปีที่ผมเป็นวิทยากรสอน Excel มีผู้สนใจอยากจะให้ผมสอนหลักสูตรวิธีศึกษาความเป็นไปได้หรือที่เรียกติดปากกันว่า “ทำฟีส” อยู่เสมอ ซึ่งแทบทุกครั้งผมต้องปฏิเสธกลับไป มีอยู่ครั้งเดียวเท่านั้นที่ผมรับสอนการทำฟีส ครั้งนั้นตั้งใจจะเปิดหลักสูตรนี้ที่สมาคมส่งเสริมเทคโนโลยี (ไทย-ญี่ปุ่น) แต่พบว่าพอจะถึงวันอบรมมีผู้สนใจสมัครแค่ไม่กี่คน ทางสมาคมจึงปิดหลักสูตรนี้ไป ส่วนผู้ที่สนใจก็ขอร้องให้ผมเปิดสอนส่วนตัวให้ที่บ้าน เป็นบทเรียนสำคัญที่ทำให้ผมรู้ว่าไม่ควรรับสอนหลักสูตรทำฟีสนี้เด็ดขาด ทำไมน่ะหรือครับ เพราะพื้นฐานของผู้เข้าอบรมต้องมีพร้อมที่จะรับความรู้ด้วย

การหาอัตราผลตอบแทนที่เราเรียกกันว่า IRR (Internal Rate of Return) กับ NPV (Net Present Value) นั้นไม่ใช่เรื่องยากเพราะ Excel มีสูตรสำเร็จรูปเตรียมพร้อมให้ใช้งานได้ทันที ตัวผมก็เรียนปริญญาโท MBA Finance จาก University of Wisconsin โดยเน้นด้าน Capital Budgeting ถ้าใช้ความรู้จากที่เรียนมานำมาสอน Excel ทำฟีสก็ทำได้อยู่แล้ว แต่ในการทำฟีสเพื่อตัดสินใจลงทุนของจริงนั้นไม่ได้ง่ายอย่างที่สอนในห้องเรียนหรือตามที่แนะนำกัน

Feas01

วิธีการคำนวณอัตราผลตอบแทนที่แนะนำให้ใช้กันทั่วไป ตามภาพนี้จะใช้ตัวเลขกระแสเงินสดสุทธิในบรรทัดสุดท้ายที่ตีกรอบไว้ไปใช้ในการคำนวณหา IRR กับ NPV โดยคำนวณหาตัวเลขมาจากยอดกำไรสุทธิ บวกกลับด้วยค่าเสื่อมราคา แล้วปรับต่อด้วยยอดรายการค้างรับค้างจ่าย (ตามภาพนี้สมมติว่าไม่มีรายการค้างรับค้างจ่ายเสียอีก) เพื่อให้เป็นตัวเงินสดคงเหลือในแต่ละปี จากนั้นจึงนำไปคำนวณร่วมกับเงินลงทุน ซึ่งจากสายตาก็ตัดสินใจได้ไม่ยากแล้วเพราะจากเงินลงทุน 2.4 ล้านบาทสามารถสร้างกระแสเงินสดสุทธิในปีต่อมามากมายหลายเท่าตัว กลายเป็นภาพลวงตาที่ชัดเจนว่าเมื่อนำตัวเลขไปหา IRR หรือ NPV on Project ต้องได้อัตราผลตอบแทนที่พุ่งทะลุกราฟอย่างแน่นอน

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

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

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

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

นี่คือสาเหตุที่ผมไม่อยากสอนการทำฟีส แต่หากใครมีฟีสที่ทำไว้แล้วและต้องการปรึกษาเพื่อปรับปรุงให้สมจริงสมจังมากขึ้น ผมก็ยินดีให้คำปรึกษาครับ หรืออยากดูแฟ้มที่ผมเคยสร้างไว้สมัยที่ทำงานในธุรกิจอสังหาริมทรัพย์ เชิญ Download ที่ลิงก์
https://www.excelexperttraining.com/download/FS-Model.zip

วิธีคำนวณต้นทุนขายแบบ FIFO

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

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

Download คำธิบาย

https://drive.google.com/file/d/1fUQ52bI7EKaxRYV7ipdPk6oWnK_d2N1i/view?usp=sharing

อาจารย์ Hasaan Fazal ชาวปากีสถาน ได้นำวิธีการนี้ทำวิดีโออธิบายไว้ที่

http://pakaccountants.com/fifo-costing-inventory-excel-data-tables/

© Copyright 1999

สงวนลิขสิทธิ์ตามกฎหมาย

ห้ามนำข้อความหรือส่วนหนึ่งส่วนใดของบทความหรือวิดีโอหรือรูปภาพไปใช้เพื่อการค้าขาย หรือเพื่อประโยชน์ส่วนตัว

อนญาตให้นำไปใช้เพื่อสาธารณประโยชน์โดยขอให้ระบุที่มาและชื่อผู้เขียนกำกับไว้ด้วยเสมอ

ลิงก์เว็บ Excel Expert Training

เว็บสำหรับ เรียนออนไลน์

เว็บสำหรับ เรียนแบบกลุ่ม-ส่วนตัว

ติดตามข่าวสารได้จาก facebook

ถามปัญหาได้ที่ กลุ่มคนรัก Excel

และไลน์กลุ่ม Excel Expert Group

ที่อยู่และการติดต่อ

สมเกียรติ ฟุ้งเกียรติ 7/1 รามคำแหง ซอย 35 หัวหมาก บางกะปิ กทม 10240 โทร 097-140-5555, 02-718-9331

Excel@ExcelExpertTraining.com

sfk234x234