ชุดข้อมูลนี้ประกอบด้วยปริมาณการขายในหน่วย ราคาขายต่อหน่วย ส่วนประกอบของต้นทุนคงที่ และส่วนประกอบของต้นทุนผันแปร
ขั้นตอนที่ 1 – คำนวณองค์ประกอบต้นทุนที่แตกต่างกัน
ขั้นตอน:
- เลือก C7 และป้อนข้อมูลต่อไปนี้
- ดับเบิลคลิกที่ ฟังก์ชัน SUM เพื่อเลือกหรือกด TAB .
- เลือก C8:C12 (ต้นทุนคงที่)
- ใช้สูตรนี้ใน C7 :
- กด ENTER
- ใช้สูตรนี้ใน C8 เพื่อหาค่าคอสของตัวแปรรวม
คุณสามารถค้นหาต้นทุนผันแปรเฉลี่ย (ค่าเฉลี่ยของต้นทุนผันแปรสำหรับแต่ละหน่วยของผลิตภัณฑ์) ได้โดยการหารต้นทุนผันแปรทั้งหมดด้วยหน่วยการผลิตทั้งหมด:
- ใช้สูตร
อ่านเพิ่มเติม: วิธีการคำนวณการวิเคราะห์จุดคุ้มทุนด้วยสูตรใน Excel
ขั้นตอนที่ 2 – คำนวณจุดคุ้มทุนของการขาย
ขั้นตอน:
- เลือก C10 และป้อนสูตรด้านล่าง
- กด ENTER .
- ไปที่ C11 และป้อนสูตรต่อไปนี้
- กด ENTER .
หลังจากขาย 6071 หน่วยบริษัทจะถึงจุดคุ้มทุน ยอดขายจะเท่ากับ $30,357 .
อ่านเพิ่มเติม: วิธีการคำนวณยอดขายถึงจุดคุ้มทุนด้วยสูตรใน Excel
ขั้นตอนที่ 3 – สร้างตารางต้นทุน รายได้ และกำไร
ขั้นตอน:
- สร้างโครงร่างพื้นฐานของตารางใน B4:G14 .
- ป้อนหน่วยโดยเรียงลำดับจากน้อยไปหามากใน หน่วย คอลัมน์
- ไปที่ C5 และป้อนสูตรต่อไปนี้
- กด ENTER .
- ค้นหา Fill Handle ที่มุมขวาล่างของ C5 :บวก (+ ) ลงชื่อ
- ดับเบิลคลิก
เซลล์อื่นๆ จะถูกเติมโดยอัตโนมัติ
- เลือก D5 และใช้สูตรด้านล่าง
จำนวน หน่วย คูณด้วย ต้นทุนผันแปรเฉลี่ย . การอ้างอิงเซลล์แบบสัมบูรณ์ ถูกนำมาใช้
- บวกต้นทุนผันแปรเข้ากับต้นทุนคงที่เพื่อให้ได้ ต้นทุนรวม ใน E5 .
- หากต้องการค้นหารายได้ ให้คูณหน่วยใน B5 ตามราคาขายต่อหน่วยใน C5 ในแผ่นงานคุ้มทุน
- ไปที่ F5 และป้อนสูตรด้านล่าง
- กด ENTER .
- ไปที่ G5 และใช้สูตรต่อไปนี้
- กด ENTER .
ขั้นตอนที่ 4 – แทรกแผนภูมิจุดคุ้มทุน
ขั้นตอน:
- เลือกตารางที่ไม่มี กำไร คอลัมน์
- ใน แทรก คลิกแทรกกระจาย (X, Y) หรือแผนภูมิฟอง
- เลือก กระจายด้วยเส้นเรียบและเครื่องหมาย .
แผนภูมิจะปรากฏขึ้น
- คลิกขวาที่เครื่องหมายบนบรรทัดของชุดข้อมูลใดๆ
ในเมนูบริบท:.
- คลิก จัดรูปแบบชุดข้อมูล… .
ในรูปแบบชุดข้อมูล :
- คลิก เติมและบรรทัด .
- เลือก เครื่องหมาย .
- ขยายตัวเลือกเครื่องหมายและเลือก ไม่มี .
- ขยาย เติม และเลือก ไม่มีการเติม .
ทำตามขั้นตอนเดียวกันสำหรับซีรี่ส์อื่น
เพื่อแยกความแตกต่างระหว่างต้นทุนคงที่และต้นทุนผันแปร
- คลิกขวาที่บรรทัดต้นทุนคงที่
- เลือก จัดรูปแบบชุดข้อมูล… .
ในจัดรูปแบบชุดข้อมูล :
- คลิก เติมและบรรทัด .
- ใน บรรทัด ให้ตั้งค่า ประเภทเส้นประ เป็น จุดประยาว .
นี่คือผลลัพธ์
ขั้นตอนที่ 5 – กำหนดจุดคุ้มทุนในแผนภูมิ
ขั้นตอน:
- คลิกขวาภายในพื้นที่ลงจุด
- เลือก เลือกข้อมูล... .
- คลิก เพิ่ม ในกล่องโต้ตอบเลือกแหล่งข้อมูล
ใน แก้ไขซีรีส์ :
- ป้อน BEP ในชื่อซีรี่ส์ .
- ไปที่ คุ้มทุน แผ่นงานและให้ข้อมูลอ้างอิงของ C10 และ C11 ในค่าซีรีส์ X และ ค่าซีรี่ส์ Y .
- คลิก ตกลง .
- คลิก ตกลง .
- เปิด จัดรูปแบบชุดข้อมูล สำหรับซีรีส์ใหม่
- ไปที่ ตัวเลือกมาร์กเกอร์ .
- เลือกในตัว และเลือกเครื่องหมายวงกลมใน ประเภท .
- ตั้งค่าเครื่องหมาย ขนาด ถึง 8 .
นี่คือผลลัพธ์
ขั้นตอนที่ 6 – ใช้เส้นคุ้มทุน
ขั้นตอน:
- คลิก องค์ประกอบแผนภูมิ รูปทรงบวก ไอคอน.
- คลิกหัวลูกศรทางด้านขวาของแถบข้อผิดพลาด
- เลือก ตัวเลือกเพิ่มเติม… .
ในแถบข้อผิดพลาดของรูปแบบ บานหน้าต่างงาน:
- เลือก ตัวเลือกแถบข้อผิดพลาด .
- เลือก ลบ ในทิศทาง และ ไม่มีขีดจำกัด ในรูปแบบสิ้นสุด .
- ใน จำนวนข้อผิดพลาด เลือกเปอร์เซ็นต์ และตั้งค่าเป็น 100% .
- คลิก ลูกศรแบบเลื่อนลงใน ตัวเลือกแถบข้อผิดพลาด .
- เลือก ซีรี่ส์ “BEP” Y แถบข้อผิดพลาด และทำตามขั้นตอนเดียวกันสำหรับ แถบข้อผิดพลาดแนวตั้ง .
นี่คือผลลัพธ์
การคำนวณราคาขายที่ BEP (จุดคุ้มทุน) โดยใช้เครื่องมือค้นหาเป้าหมายใน Excel
Tขั้นตอน:
- เพื่อคำนวณ รายได้ ให้ป้อนสูตรต่อไปนี้ใน C9 .
- ใช้สูตรนี้เพื่อกำหนด กำไร ใน C10 .
จุดคุ้มทุนคือเงื่อนไขของบริษัทหรือธุรกิจก่อนที่จะเริ่มได้รับผลกำไร
- ไปที่ข้อมูล แท็บ
- คลิก What-If-Analysis ใน การคาดการณ์ .
- เลือก แสวงหาเป้าหมาย .
- ในแสวงหาเป้าหมาย กล่องโต้ตอบ ป้อนข้อมูลอ้างอิงตามที่แสดงด้านล่าง แล้วคลิก ตกลง .
C10 ถูกตั้งค่าเป็น 0 โดยการเปลี่ยนค่าของ C5 .
ผลลัพธ์คือ:3.90 สำหรับ ราคาขายต่อหน่วย .
ส่วนฝึกซ้อม
ฝึกฝน ที่นี่
ดาวน์โหลดแบบฝึกหัด
ดาวน์โหลดสมุดงาน Excel ต่อไปนี้
บทความที่เกี่ยวข้อง
- วิธีคำนวณจุดคุ้มทุนใน Excel
- วิธีการวิเคราะห์จุดคุ้มทุนหลายผลิตภัณฑ์ใน Excel
- วิธีการวิเคราะห์จุดคุ้มทุนด้วย Goal Seek ใน Excel
<<กลับไปเพื่อคุ้มทุนการวิเคราะห์ Excel | Excel สำหรับการเงิน <แข็งแกร่ง>| เรียนรู้ Excel รับแบบฝึกหัด Excel ขั้นสูงพร้อมโซลูชันฟรี!
