การวิเคราะห์จุดคุ้มทุนเป็นเครื่องมือสำหรับประเมินโอกาสในการทำกำไรของรูปแบบธุรกิจและกลยุทธ์การกำหนดราคาต่างๆ คุณสามารถค้นหาต้นทุนคงที่ ต้นทุนผันแปร และตัวเลือกการกำหนดราคาใน Excel เพื่อกำหนดจุดคุ้มทุนของผลิตภัณฑ์ จุดคุ้มทุนคือจำนวนหน่วยที่ต้องขายในราคาที่กำหนดเพื่อให้คุ้มทุน (ครอบคลุมต้นทุนการผลิต)
ขั้นตอน
ส่วนที่ 1 จาก 5: การสร้างตารางต้นทุนผันแปร
ขั้นตอนที่ 1 เปิด Excel และสร้างสมุดงาน
คุณจะต้องมีแผ่นงานในสมุดงานเพื่อติดตามค่าใช้จ่ายทั้งหมดของคุณ
ขั้นตอนที่ 2 คลิกปุ่ม "+" ถัดจาก "Sheet1" ที่ด้านล่างของหน้าจอ
นี้จะเปิดกระดาษการทำงานใหม่
ขั้นตอนที่ 3 ตั้งชื่อแผ่นงานใหม่ว่า "ต้นทุนผันแปร"
แผ่นงานนี้จะมีตารางทั้งหมดที่ตรวจสอบต้นทุนผันแปรทั้งหมด เช่น ค่าขนส่ง ค่าคอมมิชชัน และอื่นๆ
ขั้นตอนที่ 4 สร้างป้ายชื่อสำหรับแผ่นงานใหม่
ในการสร้างตารางต้นทุนผันแปรมาตรฐาน ให้ป้อน "คำอธิบาย" ลงในเซลล์ A1 และ "จำนวนเงิน" ลงในเซลล์ B1
ขั้นตอนที่ 5 ป้อนชื่อต้นทุนผันแปรธุรกิจของคุณในคอลัมน์ A
ภายใต้หัวข้อ "คำอธิบาย" ให้ป้อนประเภทของต้นทุนผันแปรของผลิตภัณฑ์ที่จะจัดการ
ขั้นตอนที่ 6 ปล่อยให้คอลัมน์ B ("จำนวนเงิน") ว่างไว้ก่อน
คอลัมน์นี้จะถูกเติมด้วยต้นทุนที่เกิดขึ้นจริงในระหว่างกระบวนการในภายหลัง
ขั้นตอนที่ 7 สร้างตารางจากข้อมูลที่ป้อน
เปลี่ยนข้อมูลให้เป็นตารางเพื่อให้คุณสามารถใส่ลงในสูตรได้อย่างง่ายดาย:
- เลือกข้อมูลทั้งหมด รวมทั้งแถวชื่อเรื่องและเซลล์ว่าง โดยคลิกและลากเคอร์เซอร์ของเมาส์ไปที่เซลล์ทั้งหมด
- คลิกปุ่ม "จัดรูปแบบเป็นตาราง" คุณจะพบได้ภายใต้ป้ายกำกับหน้าแรก หากคุณกำลังใช้ Excel for Mac ให้คลิกป้ายชื่อตาราง คลิกปุ่ม " ใหม่ " แล้วเลือก " แทรกตารางที่มีส่วนหัว"
- ทำเครื่องหมายที่ช่อง " ตารางของฉันมีส่วนหัว " ดังนั้น ป้ายกำกับแถวแรกจะถูกตั้งค่าเป็นป้ายกำกับชื่อ
- คลิกช่อง "ชื่อตาราง" ที่มุมบนขวาและเปลี่ยนชื่อเป็น "ต้นทุนผันแปร"
ส่วนที่ 2 จาก 5: การสร้างตารางต้นทุนคงที่
ขั้นตอนที่ 1 คลิกปุ่ม "+" ถัดจาก "ต้นทุนผันแปร" ที่ด้านล่างของหน้าจอ
นี้จะเปิดกระดาษการทำงานใหม่
ขั้นตอนที่ 2 เปลี่ยนชื่อแผ่นงานใหม่เป็น "ต้นทุนคงที่"
แผ่นงานนี้จะมีต้นทุนคงที่ทั้งหมดของผลิตภัณฑ์ เช่น ค่าเช่า ค่าประกัน และค่าใช้จ่ายอื่นๆ ที่ไม่เปลี่ยนแปลง
ขั้นตอนที่ 3 สร้างป้ายกำกับชื่อ
เช่นเดียวกับเวิร์กชีตต้นทุนผันแปร สร้างป้ายกำกับ "คำอธิบาย" ในเซลล์ A1 และป้ายกำกับ "จำนวนเงิน" ในเซลล์ B1
ขั้นตอนที่ 4 ป้อนชื่อต้นทุนคงที่ของธุรกิจของคุณในคอลัมน์ A
กรอกข้อมูลในคอลัมน์แรกพร้อมคำอธิบายต้นทุนคงที่ เช่น "เช่า"
ขั้นตอนที่ 5. ปล่อยคอลัมน์ B (“Amount”) ว่างไว้สำหรับตอนนี้
คุณจะต้องกรอกค่าใช้จ่ายเหล่านี้หลังจากกรอกเอกสารทั้งหมดแล้ว
ขั้นตอนที่ 6 สร้างตารางจากข้อมูลที่ป้อน
เลือกทุกสิ่งที่คุณสร้างในเวิร์กชีตนี้ รวมถึงชื่อ:
- คลิกปุ่ม "จัดรูปแบบเป็นตาราง" ในป้ายกำกับหน้าแรก
- ทำเครื่องหมายที่ "ตารางของฉันมีส่วนหัว" เพื่อเปลี่ยนแถวที่ 1 เป็นชื่อตาราง
- คลิกช่อง "ชื่อตาราง" และป้อนชื่อตาราง "ต้นทุนคงที่"
ส่วนที่ 3 จาก 5: การสร้างเวิร์กชีตจุดคุ้มทุน
ขั้นตอนที่ 1. เปลี่ยนชื่อ Sheet1 เป็น "BEP" และเลือก
แผ่นงานนี้จะมีแผนภูมิ BEP (จุดคุ้มทุน) หลักของคุณ คุณไม่จำเป็นต้องเปลี่ยนชื่อเป็น “BEP” แต่จะทำให้งานของคุณง่ายขึ้นถ้าคุณทำ
ขั้นตอนที่ 2 กำหนดเค้าโครงสำหรับแผ่นงานจุดคุ้มทุน
เพื่อประโยชน์ของตัวอย่างนี้ ให้สร้างเวิร์กชีตโดยใช้เค้าโครงต่อไปนี้:
- A1: การขาย - นี่คือป้ายกำกับสำหรับส่วนการขายของเวิร์กชีต
- B2: ราคาต่อหน่วย – เป็นราคาที่เรียกเก็บสำหรับแต่ละรายการขาย
- B3: หน่วยที่ขาย – นี่คือจำนวนหน่วยที่ขายในราคาที่กำหนดในช่วงเวลาที่กำหนด
- A4: ต้นทุน – นี่คือป้ายชื่อสำหรับส่วนต้นทุนของเวิร์กชีต
- B5: ต้นทุนผันแปร – นี่คือต้นทุนผลิตภัณฑ์ที่คุณสามารถควบคุมได้ (ค่าขนส่ง อัตราค่าคอมมิชชัน ฯลฯ)
- B6: ต้นทุนคงที่ – นี่คือต้นทุนผลิตภัณฑ์ที่คุณไม่สามารถควบคุมได้ (ค่าเช่าสิ่งอำนวยความสะดวก ประกันภัย ฯลฯ)
- A7: รายได้ – นี่คือจำนวนเงินที่เกิดจากการขายผลิตภัณฑ์ก่อนที่จะหักค่าใช้จ่าย
- B8: Unit Margin – นี่คือจำนวนเงินที่ทำต่อหน่วยหลังจากหักต้นทุนแล้ว
- B9: อัตรากำไรขั้นต้น – นี่คือจำนวนเงินทั้งหมดที่เกิดจากการขายหน่วยของผลิตภัณฑ์หลังจากหักต้นทุน
- A10: BEP – นี่คือป้ายกำกับสำหรับส่วนจุดคุ้มทุนของเวิร์กชีต
- B11: หน่วย – นี่คือจำนวนหน่วยที่ต้องขายเพื่อให้ครอบคลุมต้นทุนการผลิต
ขั้นตอนที่ 3 เปลี่ยนรูปแบบตัวเลขสำหรับเซลล์เอาต์พุตและอินพุต
คุณต้องเปลี่ยนรูปแบบตัวเลขสำหรับเซลล์ที่ต้องการเพื่อให้ข้อมูลปรากฏอย่างถูกต้อง:
- ไฮไลต์ C2, C5, C6, C8 และ C9 คลิกเมนูแบบเลื่อนลงในส่วน "หมายเลข" ของป้ายกำกับหน้าแรกและเลือก "สกุลเงิน"
- เน้น C3 และ C11 คลิกเมนูแบบเลื่อนลงและเลือก "รูปแบบตัวเลขเพิ่มเติม" เลือก " ตัวเลข " และตั้งค่า " ตำแหน่งทศนิยม " เป็น "0"
ขั้นตอนที่ 4 สร้างช่วงเพื่อใช้ในสูตร
เลือกและสร้างช่วงต่อไปนี้เพื่อให้สูตรทำงานได้ ด้วยวิธีนี้ ตัวแปรสามารถสร้างขึ้นเพื่อป้อนลงในสูตรเพื่อให้คุณสามารถติดตามและอัปเดตค่าของตัวแปรได้อย่างง่ายดาย
- เลือก B2:C3 จากนั้นคลิกป้ายกำกับ "สูตร" คลิก "สร้างจากส่วนที่เลือก" และดำเนินการต่อด้วย "ตกลง"
- เลือก B5:C6 จากนั้นคลิกป้ายกำกับ "สูตร" คลิก "สร้างจากส่วนที่เลือก" และดำเนินการต่อด้วย "ตกลง"
- เลือก B8:C9 จากนั้นคลิกป้ายกำกับ "สูตร" คลิก "สร้างจากส่วนที่เลือก" และดำเนินการต่อด้วย "ตกลง"
- เลือก B11:C11 จากนั้นคลิกป้ายกำกับ "สูตร" คลิก " สร้างจากการเลือก " และดำเนินการต่อด้วย "ตกลง"
ส่วนที่ 4 จาก 5: การป้อนสูตร
ขั้นตอนที่ 1 ป้อนสูตรต้นทุนผันแปร
สูตรนี้จะคำนวณต้นทุนผันแปรทั้งหมดสำหรับสินค้าที่ขาย คลิก C5 และป้อนสูตรต่อไปนี้:
=SUM(ต้นทุนผันแปร)*Units_Sold
ขั้นตอนที่ 2 เสียบต้นทุนคงที่ลงในสูตร
Excel จะคำนวณต้นทุนคงที่ทั้งหมดของผลิตภัณฑ์ของคุณ คลิก C6 และป้อนสูตรต่อไปนี้:
=SUM(ต้นทุนคงที่)
ขั้นตอนที่ 3 ป้อนสูตรมาร์จิ้นหน่วย
สูตรนี้จะคำนวณมาร์จิ้นที่ได้หลังจากรวมต้นทุนผันแปร คลิก C8 และป้อนสูตรต่อไปนี้:
=Price_Per_Unit-SUM(ตัวแปรต้นทุน)
ขั้นตอนที่ 4 ป้อนสูตรอัตรากำไรขั้นต้น
สูตรนี้กำหนดจำนวนกำไรรวมของทุกหน่วยหลังจากหักต้นทุนผันแปร คลิก C9 และป้อนสูตรต่อไปนี้:
=Margin_Unit*Unit_Sold
ขั้นตอนที่ 5. ป้อนสูตร BEP
สูตรนี้ใช้ต้นทุนคงที่และเปรียบเทียบกับมาร์จิ้น เพื่อให้คุณทราบจำนวนหน่วยที่คุณต้องขายเพื่อที่จะคุ้มทุน คลิก C11 และป้อนสูตรต่อไปนี้:
=IFERROR(Fixed_Cost/Unit_Margin, 0)
ตอนที่ 5 จาก 5: การหาจุดคุ้มทุน
ขั้นตอนที่ 1 ป้อนต้นทุนผันแปรของธุรกิจของคุณ
กลับไปที่ตารางต้นทุนผันแปรและกรอกต้นทุนทั้งหมดที่เกี่ยวข้องกับผลิตภัณฑ์ ยิ่งบันทึกค่าใช้จ่ายได้แม่นยำมากเท่าใด การคำนวณก็จะยิ่งแม่นยำมากขึ้นเท่านั้น
ต้นทุนแต่ละรายการในตารางต้นทุนผันแปรต้องเป็นต่อหน่วยที่ขาย
ขั้นตอนที่ 2 ป้อนต้นทุนคงที่ของธุรกิจของคุณ
ป้อนต้นทุนเหล่านี้ลงในตารางต้นทุนคงที่ นี่คือค่าใช้จ่ายในการดำเนินธุรกิจ ซึ่งทั้งหมดกำหนดไว้ในช่วงเวลาเท่ากัน (เช่น ค่าบริการรายเดือน)
ขั้นตอนที่ 3 ป้อนราคาต่อหน่วย
ในเวิร์กชีต BEP ให้ป้อนราคาโดยประมาณต่อหน่วย คุณจะสามารถปรับค่าได้เมื่อการคำนวณดำเนินไป
ขั้นตอนที่ 4 ป้อนจำนวนหน่วยที่คุณต้องการขาย
นี่คือจำนวนการขายที่ต้องการในช่วงเวลาเดียวกับต้นทุนคงที่ ตัวอย่างเช่น หากค่าใช้จ่ายคงที่ของคุณรวมค่าเช่ารายเดือนและการประกัน หน่วยขายคือจำนวนหน่วยที่ขายในช่วงเวลาเดียวกัน
ขั้นตอนที่ 5. อ่านเอาต์พุต "หน่วย"
เซลล์เอาท์พุตหน่วย (C11) จะแสดงจำนวนหน่วยที่จะต้องขายเพื่อให้คุ้มทุน ตัวเลขนี้จะเปลี่ยนแปลงขึ้นอยู่กับราคาต่อหน่วยเช่นเดียวกับตารางต้นทุนผันแปรและต้นทุนคงที่ของคุณ
ขั้นตอนที่ 6 ทำการปรับเปลี่ยนราคาและต้นทุน
การเปลี่ยนแปลงราคาต่อหน่วยจะเป็นการเปลี่ยนจำนวนหน่วยที่ต้องการเพื่อให้คุ้มทุน ลองเปลี่ยนราคาขายของหน่วยและดูผลกระทบต่อค่า BEP
