Sử dụng Excel tìm điểm hoà vốn

Excel và các phần mềm bảng tính khác như Open Office Calc... có chức năng phân tích Goal Seek cho phép tìm mục tiêu. Hướng dẫn ngắn, đơn giản sau giúp chúng ta khai thác chức năng này để giải các bài toán tìm điểm hoà vốn trong môn kế toán quản trị (bằng Excel 2007).

Xét bài sau:
Công ty X, sản xuất một loại sản phẩm A có tài liệu kế hoạch dự kiến như sau:
- Sản lượng tiêu thụ trong năm: 1000sp
- Đơn giá bán: 100đ/sp
- Biến phí: 60đ/sp
- Tổng định phí: 30.000đ
Yêu cầu:
- Lập báo cáo KQKD theo số dư đảm phí
- Xác định sản lượng hoà vốn(Qhv), doanh thu hoà vốn(DThv)
- Vẽ đồ thị doanh thu, chi phí, lợi nhuận


Hướng dẫn:
1. Xác định điểm hoà vốn
Bước 1. Tạo bảng tính cho báo cáo số dư đảm phí theo mẫu:





Bước 2. Sử dụng chức năng Goal Seek để tìm sản lượng hoà vốn.
Sản lượng hoà vốn: Sản lượng mà tại mức đó, lợi nhuận bằng 0 hay tổng doanh thu = tổng chi phí.
Ta cần tìm giá trị của sản lượng (Ô B4) sao cho lợi nhuận ( Ô C9 = 0). Thực hiện như sau:
- Chọn menu Data -> What If Analysis -> Goal seek (Excel 2003:  Tools - > Goal Seek)



- Thiết lập thông số cho chức năng như sau.




Ở cửa sổ Goal seek, các tham số
- Set Cell:  Ô mục tiêu
- To value: Giá trị muốn mục tiêu đạt tới
- By changing cell: Ô chứa biến số
Chức năng này được Excel thực hiện bằng cách thay đổi giá trị của ô biến số (B4) , để tìm được giá trị của biến sao cho ô mục tiêu (C9) đạt được giá trị muốn có (bằng 0).
[Lưu ý: để chức chức năng tìm mục tiêu chạy được, ô chứa giá trị mục tiêu phải là công thức có phụ thuộc vào ô biến số]

Bấm OK để xác nhận, kết quả được thể hiện:
 Lợi nhuận, ô C9 = 0
 Sản lượng, ô B4 = 750 là sản lượng hoà vốn, sản lượng làm cho lợi nhuận bằng 0
 Tại đó, doanh thu (C5) 75.000 là doanh thu hoà vốn.

(*) Chúng ta cũng có thể sử dụng chức năng này để tìm mức sản lượng sao cho đạt được lợi nhuận mục tiêu. Chẳng hạn, muốn tìm mức sản lượng để đạt được lợi nhuận 15.000đ, các tham số cho chức năng này như sau

kết quả



2. Vẽ đồ thị doanh thu, chi phí
Bước 1. Lập một bảng dữ liệu mẫu
Bạn hãy tạo bảng dữ liệu mẫu dạng như sau, chỉ cần hơn 3 dòng là được. Trong đó
+ 1 dòng ứng với sản lượng bằng 0 hay  bằng 1.
+ 1 dòng ứng với sản lượng hoà vốn đã tìm được ở câu trên
+ 1 dòng khác với sản lượng cao hơn sản lượng hoà vốn, lớn hay nhỏ tuỳ ý muốn của bạn, sao cho đồ thị dễ nhìn



Bạn hãy sử dụng các công thức để điền cho cột doanh thu, chi phí theo sản lượng, ở hình trên các công thức:
Doanh thu =  Sản lượng * đơn giá,  [H4] = G4*$B$5
Chi phí = Sản lượng * biến phí đơn vị + Định phí, [I4] = G4*$B$6 + $C$8

Bước 2. Vẽ đồ thị
- Chọn bảng dữ liệu, rồi chạy lệnh Insert -> Scatter(Đồ thị phân tán) -> và chọn một trong kiểu đồ thị nét liên mềm như hình dưới đây.
(Excel 2003: Chọn menu Insert -> Chart -> Chọn kiểu XY Scatter)


Đồ thị được vẽ như sau



Để hiện giá trị các điểm trên đồ thị, hãy nhấp chuột phải vào mỗi đường, chọn Add Data Labels


3. Bổ sung đường lợi nhuận lên đồ thị
- Thêm cột lợi nhuận vào bảng dữ liệu


- Bổ sung đường lợi nhuận lên đồ thị:
+ Nhấp chuột phải vào đồ thị, chọn Select Data





+ Cửa sổ hiện lên, như ta thấy chỉ có 2 đường(dải, loạt dữ liệu) Doanh thu và Chi phí



Chọn lệnh Add để thêm đồ thị và chọn dữ liệu cho đồ thị mới như sau



Bấm OK để xác nhận, kết quả ta có 3 đồ thị doanh thu, chi phí, lợi nhuận kết hợp trên hệ toạ độ như dưới đây.



Trên đồ thị, thẳng cột, vị trí đường doanh thu cắt đường chi phí trùng với điểm đường lợi nhuận cắt trục hoành (sản lượng), tại đó lợi nhuận bằng 0.



Nguyễn Ngọc Hiến



Xin bạn cho biết ý kiến về bài viết này

• Rất hữu ích
• Hữu ích
• Không hữu ích

View Results
Create a Blog Poll

låna pengar nu