วิธีคำนวณการวิเคราะห์จุดคุ้มทุนใน Excel

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

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

การวิเคราะห์จุดคุ้มทุนคืออะไร

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

เช่น ต้นทุนรวม=ต้นทุนคงที่ + ต้นทุนเบ็ดเตล็ด + ต้นทุนผันแปร * หน่วยของสินค้าที่ขาย

หลังจากที่บริษัทเริ่มขายสินค้าแล้ว มูลค่าการขายจะถูกกำหนดโดยผลิตภัณฑ์ของหน่วยที่ขายและราคาขายของรายการต่อหน่วย

เช่น มูลค่าการขาย=ราคาขายของรายการ * หน่วยที่ขาย

บริษัทจำเป็นต้องสร้างรายได้มากกว่าต้นทุนทั้งหมดเพื่อทำกำไร ในการวิเคราะห์จุดคุ้มทุน คุณต้องคำนวณ Contribution Margin ก่อนคำนวณจุดคุ้มทุน กำไรจากการสนับสนุนคำนวณเพื่อดูว่ามีการสร้างรายได้มากกว่าต้นทุนรวมของการผลิตผลิตภัณฑ์มากน้อยเพียงใด ในการคำนวณส่วนต่าง คุณต้องลบมูลค่าการขายออกจากต้นทุนรวมที่เกิด

เช่น ส่วนต่างส่วนเพิ่ม=ต้นทุนรวม-มูลค่าการขาย

จุด ที่คุณขายหน่วยให้เพียงพอเพื่อคืนการลงทุนของคุณเรียกว่าจุดคุ้มทุน (BEP) สิ่งใดก็ตามที่เกิดขึ้นหลังจากจุดคุ้มทุนถือเป็นผลกำไร ดังนั้นจึงเป็นที่เข้าใจได้ว่าทำไมการเข้าถึงสิ่งนี้จึงเป็นสิ่งสำคัญสำหรับทุกบริษัท ในการคำนวณ BEP คุณต้องหารต้นทุนคงที่ด้วย Contribution Margin

เช่น BEP=Fixed Cost/Contribution Margin

How to Calculate Break-แม้แต่การวิเคราะห์ใน Excel

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

ในทำนองเดียวกัน เมื่อใช้ Excel คุณสามารถคำนวณจำนวนหน่วยทั้งหมดที่คุณต้องขายเพื่อให้ถึงจุดคุ้มทุน ซึ่งจะช่วยสร้างเป้าหมายให้กับบริษัทของคุณในแง่ของยอดขาย

ในส่วนนี้ เราได้กล่าวถึงขั้นตอนในการคำนวณจุดคุ้มทุนและคำนวณหน่วยเป้าหมายที่จะขาย ทั้งหมดนี้ใช้โปรแกรม Excel

ใช้สูตร

ขั้นตอนแรกในการวิเคราะห์จุดคุ้มทุนคือการคำนวณจุดคุ้มทุน เพื่อช่วยให้คุณเข้าใจแนวคิดได้ดีขึ้น เรามีตัวอย่างที่คุณสามารถอ้างอิงได้

Yasha ดำเนินธุรกิจเกี่ยวกับเทียน เธอแบกรับ $4 ต่อหน่วยเทียนและมีราคาคงที่ที่ $5,000 เธอตั้งเป้าที่จะขายเทียนแต่ละอันในราคา $15 ตอนนี้ เราจะคำนวณจำนวนหน่วยที่เธอต้องการขายเพื่อให้ถึงจุดคุ้มทุน:

ขั้นตอนที่ 1: คำนวณต้นทุนรวม

ขั้นตอนแรกคือการคำนวณต้นทุนทั้งหมด โดย Yasha ในการสร้างเทียนของเธอ ในการคำนวณต้นทุนรวม ให้ป้อนสูตรในรูปแบบต่อไปนี้:

ต้นทุนรวม =ต้นทุนคงที่รวม + (ต้นทุนต่อหน่วยผันแปร * หน่วยขายแล้ว)

ขั้นตอนที่ 2: คำนวณรายได้

หลังจากคำนวณต้นทุนรวมของการผลิตผลิตภัณฑ์แล้ว คุณต้องกำหนดรายได้ที่สร้างขึ้น ในการคำนวณมูลค่าการขาย ให้ป้อนสูตรในรูปแบบต่อไปนี้:

รายได้= หน่วยที่ขาย * ราคาขายต่อหน่วย

ขั้นตอนที่ 3: คำนวณ Contribution Margin

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

ส่วนต่างส่วนต่าง =ราคาขาย – ต้นทุนผันแปร

ขั้นตอนที่ 4: คำนวณจุดคุ้มทุน (BEP)

หลังจากที่คุณคำนวณรายการดังกล่าวแล้ว คุณสามารถดำเนินการคำนวณจุดคุ้มทุนของผลิตภัณฑ์ของคุณได้ในที่สุด ป้อนสูตรในรูปแบบต่อไปนี้:

จุดคุ้มทุน (BEP) = ต้นทุนคงที่ / Contribution Margin

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

ใช้เครื่องมือ Go Seek

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

Excel มีคุณลักษณะพิเศษ การวิเคราะห์แบบ What-if ที่ทำให้กระบวนการนี้ง่ายยิ่งขึ้น แทนที่จะคำนวณหน่วยที่คุณจะต้องขายด้วยตนเองเพื่อให้ถึงจุดคุ้มทุน คุณสามารถใช้เครื่องมือ Go Seek เพื่อทำงานให้คุณ

ในส่วนนี้ เราจะใช้ตัวอย่างเดียวกันกับที่เราใช้ข้างต้นในการคำนวณหน่วยเป้าหมายที่จะขายเพื่อให้ตรงตามจุดคุ้มทุนใน Excel

เปิดสเปรดชีตของคุณไปที่แท็บ ข้อมูล จาก แถบเมนู
วางเมนูลงสำหรับ การวิเคราะห์แบบ What-If และเลือกเป้าหมายการแสวงหา.
ในหน้าต่าง Goal Seek ป้อนรายละเอียดต่อไปนี้:ตั้งค่าเซลล์: อ้างถึงเซลล์ที่มีจุดคุ้มทุนของคุณเป็นมูลค่า: เปลี่ยนค่าเป็น 0.โดยการเปลี่ยนเซลล์: อ้างถึงเซลล์ที่ขายได้
คลิก ตกลง

วิธีการทำงานของเครื่องมือ Go Seek คือการนำข้อมูลจากเซลล์ที่มีจุดคุ้มทุนและตั้งค่าเป็น 0 โดยการเปลี่ยนมูลค่าของหน่วยที่ขาย ดังที่เราได้กำหนดไว้แล้วว่าเมื่อบริษัทของคุณพบกับจุดคุ้มทุน จุดคุ้มทุนจะเป็นศูนย์ ค่าที่อัปเดตใน หน่วยที่ขาย คือจำนวนหน่วยที่คุณต้องขายเพื่อให้ถึงจุดคุ้มทุน

จะแสดงการวิเคราะห์จุดคุ้มทุนในแผนภูมิได้อย่างไร

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

ขั้นตอนที่ 1: คำนวณกำไรสุทธิ

คุณต้องป้อนกำไรสุทธิเพื่อสร้างไดอะแกรมของคุณ กำไรสุทธิหมายถึงจำนวนกำไรที่เกิดจากการขายหน่วย ป้อนสูตรในรูปแบบต่อไปนี้เพื่อคำนวณกำไรสุทธิ:

กำไรสุทธิ = รายรับ – ต้นทุนผันแปรรวม – ต้นทุนคงที่ทั้งหมด

ขั้นตอนที่ 2: เติมค่า

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

ขั้นตอนที่ 3: สร้างแผนภูมิ

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

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

หากคุณต้องการจัดรูปแบบ เพิ่มเติมลักษณะแผนภูมิของคุณ ไปที่ การออกแบบแผนภูมิ จาก แถบเมนูและใช้การจัดรูปแบบตามที่คุณต้องการ