การวิเคราะห์จุดคุ้มทุนเป็นเครื่องมือสำหรับการประเมินศักยภาพในการทำกำไรของรูปแบบธุรกิจและสำหรับการประเมินกลยุทธ์การกำหนดราคาต่างๆ คุณสามารถรวบรวมต้นทุนคงที่ ต้นทุนผันแปร และตัวเลือกการกำหนดราคาใน Excel ได้อย่างง่ายดายเพื่อกำหนดจุดคุ้มทุนสำหรับผลิตภัณฑ์ของคุณ นี่คือจำนวนหน่วยที่คุณต้องขายในราคาที่คุณกำหนดเพื่อที่จะคุ้มทุน
ขั้นตอน
ส่วนที่ 1 จาก 5: การสร้างตารางต้นทุนผันแปรของคุณ
ขั้นตอนที่ 1 เปิด Excel และสร้างสมุดงานเปล่าใหม่
คุณจะต้องสร้างแผ่นงานหลายแผ่นในสมุดงานนี้เพื่อจัดการติดตามค่าใช้จ่ายทั้งหมดของคุณ
ขั้นตอนที่ 2 คลิกปุ่ม "+" ถัดจาก "Sheet1" ที่ด้านล่างของหน้าจอ
นี้จะสร้างแผ่นงานเปล่าใหม่
ขั้นตอนที่ 3 เปลี่ยนชื่อแผ่นงานใหม่เป็น "VariableCosts
" แผ่นงานนี้จะมีตารางที่ติดตามต้นทุนผันแปรทั้งหมดของผลิตภัณฑ์ของคุณ เช่น ค่าขนส่ง ค่าคอมมิชชัน และค่าใช้จ่ายอื่นๆ
ขั้นตอนที่ 4 สร้างป้ายกำกับส่วนหัวสำหรับแผ่นงานใหม่
ในการสร้างตารางต้นทุนผันแปรพื้นฐาน ให้ป้อน "คำอธิบาย" ลงใน A1 และ "จำนวนเงิน" ลงใน B1
ขั้นตอนที่ 5 ป้อนชื่อต้นทุนผันแปรของธุรกิจของคุณในคอลัมน์ A
ใต้ส่วนหัว "คำอธิบาย" ให้ป้อนประเภทของต้นทุนผันแปรที่คุณจะต้องใช้สำหรับผลิตภัณฑ์ของคุณ
ขั้นตอนที่ 6 ปล่อยให้คอลัมน์ B ("Amount") ว่างไว้ก่อน
คุณจะต้องกรอกค่าใช้จ่ายจริงในภายหลังในกระบวนการนี้
ขั้นตอนที่ 7 สร้างตารางจากข้อมูลที่คุณป้อน
การเปลี่ยนข้อมูลเป็นตารางจะทำให้ง่ายต่อการใส่สูตรในภายหลัง:
- เลือกข้อมูลทั้งหมด รวมทั้งแถวส่วนหัวและจำนวนที่ว่าง โดยคลิกและลากเมาส์ไปที่เซลล์ทั้งหมด
- คลิกปุ่ม "จัดรูปแบบเป็นตาราง" คุณจะพบสิ่งนี้ในแท็บหน้าแรก หากคุณกำลังใช้ Excel for Mac ให้คลิกแท็บ Tables คลิกปุ่ม "New" จากนั้นเลือก "Insert Table with Headers"
- ทำเครื่องหมายที่ช่อง "ตารางของฉันมีส่วนหัว" การทำเช่นนี้จะรักษาป้ายกำกับในแถวแรกเป็นป้ายกำกับส่วนหัว
- คลิกฟิลด์ "ชื่อตาราง" ที่มุมบนขวาและตั้งชื่อเป็น "VariableCosts"
ส่วนที่ 2 จาก 5: การสร้างตารางต้นทุนคงที่
ขั้นตอนที่ 1 คลิกปุ่ม "+" ถัดจาก "VariableCosts" ที่ด้านล่างของหน้าจอ
นี้จะสร้างแผ่นงานเปล่าอีก
ขั้นตอนที่ 2 เปลี่ยนชื่อแผ่นงานใหม่เป็น "FixedCosts
" เอกสารนี้จะระบุต้นทุนคงที่ทั้งหมดสำหรับผลิตภัณฑ์ของคุณ เช่น ค่าเช่า ค่าประกัน และค่าใช้จ่ายอื่นๆ ที่ไม่เปลี่ยนแปลง
ขั้นตอนที่ 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: Gross Margin - นี่คือจำนวนเงินทั้งหมดที่คุณทำสำหรับหน่วยทั้งหมดที่ขายหลังต้นทุน
- 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 ป้อนสูตรต้นทุนคงที่
ซึ่งจะคำนวณต้นทุนคงที่ทั้งหมดสำหรับผลิตภัณฑ์ของคุณ คลิก C6 และป้อนสูตรต่อไปนี้:
=SUM(ต้นทุนคงที่)
ขั้นตอนที่ 3 ป้อนสูตรมาร์จิ้นหน่วย
สิ่งนี้จะคำนวณมาร์จิ้นที่คุณทำหลังจากพิจารณาต้นทุนผันแปรแล้ว คลิก C8 และป้อนสูตรต่อไปนี้:
=Price_Per_Unit-SUM(ต้นทุนผันแปร)
ขั้นตอนที่ 4 ป้อนสูตรอัตรากำไรขั้นต้น
ซึ่งจะกำหนดจำนวนเงินทั้งหมดที่คุณทำสำหรับหน่วยทั้งหมดที่คุณขายหลังจากต้นทุนผันแปร คลิก C9 และป้อนสูตรต่อไปนี้:
=Unit_Margin*Unit_Sold
ขั้นตอนที่ 5. ป้อนสูตร BEP
สิ่งนี้ใช้ต้นทุนคงที่ของคุณและเปรียบเทียบกับมาร์จิ้นของคุณ แจ้งให้คุณทราบจำนวนหน่วยที่คุณต้องขายเพื่อให้คุ้มทุน คลิก C11 และป้อนสูตรต่อไปนี้:
=IFERROR(Fixed_Costs/Unit_Margin, 0)
ตอนที่ 5 จาก 5: การหาจุดคุ้มทุน
ขั้นตอนที่ 1 ป้อนต้นทุนผันแปรของธุรกิจของคุณ
กลับไปที่ตาราง VariableCosts และกรอกค่าใช้จ่ายทั้งหมดที่เกี่ยวข้องกับผลิตภัณฑ์ของคุณ ยิ่งคุณอยู่ที่นี่มากเท่าไหร่ การคำนวณ BEP ของคุณก็จะยิ่งแม่นยำมากขึ้นเท่านั้น
ต้นทุนแต่ละรายการในตาราง VariableCosts ควรเป็นต่อหน่วยที่ขาย
ขั้นตอนที่ 2 ป้อนต้นทุนคงที่ของธุรกิจของคุณ
ป้อนค่าใช้จ่ายเหล่านี้ลงในตารางต้นทุนคงที่ของคุณ นี่คือค่าใช้จ่ายในการดำเนินธุรกิจของคุณ และควรตั้งค่าทั้งหมดเป็นช่วงเวลาเดียวกัน (เช่น ค่าใช้จ่ายรายเดือน)
ขั้นตอนที่ 3 ป้อนราคาต่อหน่วย
ในแผ่นงาน BEP ให้ป้อนราคาประเมินเริ่มต้นต่อหน่วย คุณจะสามารถปรับค่านี้ได้เมื่อคุณทำการคำนวณ
ขั้นตอนที่ 4 ป้อนจำนวนหน่วยที่คุณต้องการขาย
นี่คือจำนวนหน่วยที่คุณต้องการขายในกรอบเวลาเดียวกับต้นทุนคงที่ของคุณ ตัวอย่างเช่น หากค่าใช้จ่ายคงที่ของคุณรวมค่าเช่ารายเดือนและการประกัน หน่วยที่ขายจะเป็นจำนวนหน่วยที่ขายในกรอบเวลาเดียวกันนั้น
ขั้นตอนที่ 5. อ่านผลลัพธ์ "หน่วย"
เซลล์เอาต์พุตหน่วย (C11) จะแสดงจำนวนหน่วยที่คุณต้องขายในกรอบเวลาเพื่อให้คุ้มทุน ตัวเลขนี้จะเปลี่ยนแปลงขึ้นอยู่กับราคาต่อหน่วยเช่นเดียวกับตารางต้นทุนผันแปรและต้นทุนคงที่ของคุณ
ขั้นตอนที่ 6 ทำการปรับเปลี่ยนราคาและต้นทุน
การเปลี่ยนราคาต่อหน่วยจะเปลี่ยนจำนวนหน่วยที่คุณต้องการเพื่อให้คุ้มทุน ลองเปลี่ยนราคาและดูว่าเกิดอะไรขึ้นกับค่า BEP ของคุณ
