Bộ dữ liệu này bao gồm Số lượng bán hàng theo đơn vị, Giá bán trên đơn vị, các thành phần của Chi phí cố định, và các thành phần của Chi phí biến đổi.
Bước 1 – Tính các thành phần chi phí khác nhau
Các bước:
- Chọn C7 và nhập như sau.
- Nhấp đúp vào hàm SUM để chọn nó hoặc nhấn TAB.
- Chọn C8:C12 (chi phí cố định).
- Sử dụng công thức này trong C7:
- Nhấn ENTER.
- Sử dụng công thức này trong C8 để tìm Tổng chi phí biến đổi.
Bạn có thể tìm Chi phí biến đổi Trung bình (trung bình chi phí biến đổi cho mỗi đơn vị sản phẩm) bằng cách chia tổng chi phí biến đổi cho tổng đơn vị sản xuất:
- Sử dụng công thức.
Xem thêm: Cách tính phân tích Break Even với công thức trong Excel
Bước 2 – Tính điểm Break-Even của Doanh số
Các bước:
- Chọn C10 và nhập công thức dưới đây.
- Nhấn ENTER.
- Đi đến C11 và nhập công thức sau.
- Nhấn ENTER.
Sau khi bán 6071 đơn vị, công ty sẽ đạt điểm break-even. Số tiền doanh số sẽ là $30,357.
Xem thêm: Cách tính Doanh số Break-Even với công thức trong Excel
Bước 3 – Tạo bảng Chi phí, Doanh thu và Lợi nhuận
Các bước:
- Tạo khung cơ bản của bảng trong B4:G14.
- Nhập đơn vị theo thứ tự tăng dần trong cột Unit.
- Đi đến C5 và nhập công thức sau.
- Nhấn ENTER.
- Tìm Fill Handle ở góc dưới bên phải của C5: dấu cộng (+).
- Nhấp đúp vào nó.
Các ô khác được tự động điền.
- Chọn D5 và sử dụng công thức dưới đây.
Số Đơn vị được nhân với Chi phí biến đổi Trung bình. Một tham chiếu ô tuyệt đối được sử dụng.
- Thêm chi phí biến đổi vào chi phí cố định để lấy Tổng Chi phí trong E5.
- Để tìm Doanh thu, nhân Đơn vị trong B5 với Giá bán trên đơn vị trong C5 trong trang tính Break Even.
- Đi đến F5 và nhập công thức dưới đây.
- Nhấn ENTER.
- Đi đến G5 và sử dụng công thức sau.
- Nhấn ENTER.
Bước 4 – Chèn biểu đồ Break-Even
Các bước:
- Chọn bảng không có cột Profit.
- Trong Insert, nhấp Insert Scatter (X, Y) or Bubble Chart.
- Chọn Scatter with Smooth Lines and Markers.
Biểu đồ sẽ được hiển thị.
- Nhấp chuột phải vào dấu đánh dấu trên đường線 của bất kỳ series nào.
Trong menu ngữ cảnh:.
- Nhấp Format Data Series… .
Trong Format Data Series :
- Nhấp Fill & Line.
- Chọn Marker.
- Mở rộng tùy chọn Marker và chọn None.
- Mở rộng phần Fill và chọn No fill.
Làm theo cùng quy trình cho series khác.
Để phân biệt đường線 của chi phí cố định và biến đổi.
- Nhấp chuột phải vào đường線 chi phí cố định.
- Chọn Format Data Series….
Trong Format Data Series:
- Nhấp Fill & Line.
- Trong Line, đặt Dash type là Long Dash Dot.
Đây là đầu ra.
Bước 5 – Xác định điểm Break-Even trên biểu đồ
Các bước:
- Nhấp chuột phải bên trong khu vực vẽ.
- Chọn Select Data… .
- Nhấp Add trong hộp thoại Select Data Source.
Trong Edit Series:
- Nhập BEP trong Series name.
- Đi đến trang tính Break Even và đưa ra tham chiếu của C10 và C11 trong Series X values và Series Y values.
- Nhấp OK.
- Nhấp OK.
- Mở Format Data Series cho series mới.
- Đi đến Marker options.
- Chọn Built-in và chọn dấu tròn trong Type.
- Đặt Size của marker thành 8.
Đây là đầu ra.
Bước 6 – Áp dụng đường Break-Even
Các bước:
- Nhấp vào biểu tượng Chart Element hình dấu cộng.
- Nhấp vào mũi tên ở bên phải của Error Bars.
- Chọn More Options….
Trong ngăn Format Error Bars:
- Chọn Error Bar Options.
- Chọn Minus trong Direction và No Cap trong End Style.
- Trong Error Amount, chọn Percentage và đặt thành 100%.
- Nhấp vào mũi tên thả xuống trong Error Bar Options.
- Chọn Series “BEP” Y Error Bars và làm theo cùng quy trình cho Vertical Error Bar.
Đây là đầu ra.
Tính Giá bán tại BEP (Điểm Break-Even) Sử dụng công cụ Goal Seek trong Excel
TCác bước:
- Để tính Doanh thu, nhập công thức sau vào C9.
- Sử dụng công thức này để xác định Lợi nhuận trong C10.
Điểm break-even là điều kiện của một công ty hoặc doanh nghiệp trước khi nó bắt đầu có lợi nhuận.
- Đi đến tab Data.
- Nhấp What-If-Analysis trong Forecast.
- Chọn Goal Seek.
- Trong hộp thoại Goal Seek, nhập tham chiếu như hình dưới đây, và nhấp OK.
C10 được đặt là 0 bằng cách thay đổi giá trị của C5.
Đầu ra là: 3.90 cho Giá bán trên đơn vị.
Phần thực hành
Thực hành tại đây.
Tải xuống Workbook thực hành
Tải xuống workbook Excel sau.
Các bài viết liên quan
- Cách tính điểm Break-Even trong Excel
- Cách làm phân tích Break-Even đa sản phẩm trong Excel
- Cách làm phân tích Break-Even với Goal Seek trong Excel
<< Quay lại Phân tích Break Even Excel | Excel cho Tài chính | Học Excel
Nhận miễn phí Bài tập Excel nâng cao với lời giải!