สร้างแผนภูมิวิเคราะห์จุดคุ้มทุนใน Excel: คู่มือทีละขั้นตอน

ชุดข้อมูลนี้รวม ปริมาณยอดขายเป็นหน่วย, ราคาขายต่อหน่วย, องค์ประกอบของต้นทุนคงที่, และองค์ประกอบของต้นทุนแปรผัน

ขั้นตอนที่ 1 – คำนวณองค์ประกอบต้นทุนต่างๆ

ขั้นตอน:

• เลือก C7 ใส่ข้อมูลต่อไปนี้

• ดับเบิลคลิกฟังก์ชัน SUM เพื่อเลือก หรือกด TAB

• เลือก C8:C12 (ต้นทุนคงที่)

• ใช้สูตรนี้ใน C7:

• กด ENTER

• ใช้สูตรนี้ใน C8 หา Total Variable Cost:

หา Average Variable Cost (ค่าเฉลี่ยต้นทุนแปรผันต่อหน่วยผลิตภัณฑ์) โดยการหารต้นทุนแปรผันรวมด้วยหน่วยการผลิตทั้งหมด:

• ใช้สูตร

อ่านเพิ่มเติม: วิธีคำนวณ Break Even Analysis ด้วยสูตรใน Excel

ขั้นตอนที่ 2 – คำนวณจุดคุ้มทุนของยอดขาย

ขั้นตอน:

• เลือก C10 ใส่สูตรด้านล่าง

• กด ENTER

• ไปที่ C11 ใส่สูตรต่อไปนี้

• กด ENTER

หลังขาย 6071 หน่วย บริษัทจะถึงจุดคุ้มทุน ยอดขายจะเป็น $30,357

อ่านเพิ่มเติม: วิธีคำนวณ Break-Even Sales ด้วยสูตรใน Excel

ขั้นตอนที่ 3 – สร้างตารางต้นทุน รายได้ และกำไร

ขั้นตอน:

• สร้างโครงสร้างตารางพื้นฐานใน B4:G14

• ใส่หน่วยตามลำดับเพิ่มขึ้นในคอลัมน์ Unit

• ไปที่ C5 ใส่สูตรต่อไปนี้

• กด ENTER

• หา Fill Handle ที่มุมขวาล่างของ C5: เครื่องหมายบวก (+)
• ดับเบิลคลิก

เซลล์อื่นๆ จะถูกเติมอัตโนมัติ

• เลือก D5 ใช้สูตรด้านล่าง

จำนวน Units คูณกับ Average Variable Cost ใช้ absolute cell reference

• บวกต้นทุนแปรผันกับต้นทุนคงที่ เพื่อได้ Total Cost ใน E5

• หา Revenue คูณ Units ใน B5 ด้วย Unit Selling Price ใน C5 ในแผ่น Break Even
• ไปที่ F5 ใส่สูตรด้านล่าง

• กด ENTER

• ไปที่ G5 ใช้สูตรต่อไปนี้

• กด ENTER

ขั้นตอนที่ 4 – แทรกแผนภูมิจุดคุ้มทุน

ขั้นตอน:

• เลือกตารางโดยไม่รวมคอลัมน์ Profit
• ใน Insert คลิก Insert Scatter (X, Y) or Bubble Chart
• เลือก Scatter with Smooth Lines and Markers

แผนภูมิจะแสดง

• คลิกขวาที่เครื่องหมายบนเส้นของซีรีส์ใดๆ

ในเมนูบริบท:

• คลิก Format Data Series…

ใน Format Data Series:

• คลิก Fill & Line
• เลือก Marker
• ขยายตัวเลือก Marker เลือก None
• ขยายส่วน Fill เลือก No fill

ทำขั้นตอนเดียวกันสำหรับซีรีส์อื่น

เพื่อแยกแยะเส้นของต้นทุนคงที่และแปรผัน

• คลิกขวาที่เส้นต้นทุนคงที่
• เลือก Format Data Series…

ใน Format Data Series:

• คลิก Fill & Line
• ใน Line ตั้ง Dash type เป็น Long Dash Dot

นี่คือผลลัพธ์

ขั้นตอนที่ 5 – กำหนดจุดคุ้มทุนในแผนภูมิ

ขั้นตอน:

• คลิกขวาภายในพื้นที่พล็อต
• เลือก Select Data…

• คลิก Add ในกล่อง Select Data Source

ใน Edit Series:

• ใส่ BEP ใน Series name
• ไปที่แผ่น Break Even ใส่อ้างอิง C10 และ C11 ใน Series X values และ Series Y values
• คลิก OK

• คลิก OK

• เปิด Format Data Series สำหรับซีรีส์ใหม่
• ไปที่ Marker options
• เลือก Built-in เลือกเครื่องหมายวงกลมใน Type
• ตั้งขนาด Size เป็น 8

นี่คือผลลัพธ์

ขั้นตอนที่ 6 – ใช้เส้นจุดคุ้มทุน (Break-Even Line)

ขั้นตอน:

• คลิกไอคอนรูปบวก Chart Element
• คลิกลูกศรขวาของ Error Bars
• เลือก More Options…

ในบานหน้าต่าง Format Error Bars:

• เลือก Error Bar Options
• เลือก Minus ใน Direction และ No Cap ใน End Style
• ใน Error Amount เลือก Percentage ตั้งเป็น 100%

• คลิกลูกศรดรอปดาวน์ใน Error Bar Options
• เลือก Series “BEP” Y Error Bars ทำขั้นตอนเดียวกันสำหรับ Vertical Error Bar

นี่คือผลลัพธ์

การคำนวณราคาขายที่ BEP (จุดคุ้มทุน) โดยใช้ Goal Seek ใน Excel

Tขั้นตอน:

• คำนวณ Revenue ใส่สูตรใน C9

• ใช้สูตรนี้หา Profit ใน C10

จุดคุ้มทุนคือเงื่อนไขของบริษัท/ธุรกิจก่อนเริ่มได้กำไร

• ไปแท็บ Data
• คลิก What-If-Analysis ใน Forecast
• เลือก Goal Seek

• ในกล่อง Goal Seek ใส่อ้างอิงตามภาพ คลิก OK

C10 ตั้งเป็น 0 โดยเปลี่ยนค่า C5 ผลลัพธ์: 3.90 สำหรับ Unit Selling Price

ส่วนฝึกปฏิบัติ

ฝึกฝน ที่นี่

ดาวน์โหลดสมุดงานฝึกปฏิบัติ

ดาวน์โหลดสมุดงาน Excel ด้านล่าง

บทความเกี่ยวข้อง

• วิธีคำนวณ Break-Even Points ใน Excel
• วิธีทำ Multi-Product Break-Even Analysis ใน Excel
• วิธีทำ Break-Even Analysis ด้วย Goal Seek ใน Excel

<< กลับไป Break Even Analysis Excel | Excel For Finance | Learn Excel

รับแบบฝึกหัด Excel ขั้นสูงพร้อมเฉลยฟรี!