Công thức tính lợi nhuận trong Excel và cách lập bảng tính lợi nhuận

Đối với bài viết này, hướng dẫn cho các bạn chưa biết theo ví dụ để các bạn có thể hình dung dễ dàng và dễ hiểu hơn, với ví dụ như sau.
Ví dụ cho một cơ sở sản xuất và buôn bán, giá bán 1 sản phẩm là 100.000đ/sp với các kế hoạch bán theo tháng:
– Tháng 1: 1.236 sp
– Tháng 2: 1.513 sp
– Tháng 3: 1.352 sp
– Tháng 4: 1.684 sp
– Tháng 5: 1.484 sp
– Tháng 6: 1.675 sp
Đầu tư cho hoạt động tháng đầu tiên là 100 triệu, mỗi tháng sau tăng 5% với tháng trước.
Yêu cầu cho tính toán đưa ra: 
Với biến động số lượng bán theo mức giá:
+ 80.000đ số lượng bán đạt 120% so với giá 100.000đ
+ 110.000đ số lượng bán đạt 80% so với giá 100.000đ
+ 130.000đ số lượng bán đạt 70% so với giá 100.000đ

1.1. Tạo bảng theo dõi các yếu tố
Với đề bài đưa ra trong 6 tháng vậy nên chúng ta cần phải tính toán xác định được chỉ tiêu các tháng, sau đó tổng lại các tháng mới tính được lợi nhuận là bao nhiêu.
Lập bảng theo dõi 6 tháng:

1.2. Lập bảng tính số lượng phụ thuộc theo giá bán
Theo yêu cầu đề bài lập bảng:

1.3. Xác định yêu cầu mục tiêu
Yêu cầu bài toán là xác định được mức giá bán thu được lợi nhuận cao nhất, từ đây lập bảng theo dõi kết quả:

2.1. Tạo bảng số lượng theo mức giá bán Với giá bán 100.000đ có tỷ lệ bán đạt 100%.
Với giá bán 80.000đ có tỷ lệ bán đạt 120% so với 100.000đ
Vậy: B8=B9*120%
Công thức này áp dụng tương tự với các tháng sau.
Tương tự với giá bán 110.000đ và 130.000đ áp dụng công thức và được kết quả thu được:

Dùng hàm Rounddown để làm tròn các ô chứa dấu thập phân áp dụng công thức cho ô B8:
=ROUNDDOWN(B9*120%;0) và sao chép cho các ô tháng còn lại.
Tương tự cho làm tròn các giá bán 110.000đ và 130.000đ thu được bảng như sau:

2.2. Xác định chi phí cho các tháng.
Vì đề bài có nói, chi phí tháng sau tăng 5% so với tháng trước vậy nên:
– Tháng 1: B3 = 100.000.000đ
– Tháng 2: C3 = B3*5%+B3=B3*(1+5%)
– Tháng 3: D3 = C3*(1+5%)
Và tương tự với các tháng sau.
Kết quả thu được khi áp dụng công thức:

2.3. Xác định doanh thu
Doanh thu phụ thuộc theo giá bán của từng mức, vì thế chúng ta cần có tham số là giá bán để tính.
Chọn H1 là vị trí đặt danh sách chọn giá bán. Sử dụng Data Validation/List. Với danh sách chọn là mức giá bán (A8: A11)

2.4. Công thức tính doanh thu
Doanh thu = Giá bán * Số lượng
Số lượng bán của từng tháng tham chiếu tới bảng đã tính ở bước 1 bằng cách sử dụng hàm Vlookup.

2.5. Tính lợi nhuận
Lợi nhuận = Doanh thu – Chi phí

2.6. Xác định lợi nhuận khi thay từng mức giá bán
Thay đổi lần lượt từng mức giá:

Vậy từ đây có thể thấy nếu bán với mức giá 100.000đ thì thu được lợi nhuận cao nhất.
Tuy có hơi phức tạp trong cách tính lợi nhuận trên, nhưng trước khi tính lợi nhuận thì bạn cần phân tích đề bài yêu cầu và bắt đầu đi vào xây dựng các công thức một cách chuẩn chỉnh và bạn sẽ có thể đưa ra cho mình những đáp án chính xác nhất.

Nếu như bạn muốn nhiều hơn các kỹ năng tính toán kế toán doanh nghiệp, khóa học Kế toán trên excel từ a -z của giảng viên chuyên gia kế toán Excel Phạm Văn Học, khóa học giúp bạn ” có đủ kiến thức, kỹ năng để làm kế toán tổng hợp cho các doanh nghiệp, tự xây dựng file kế toán bằng excel phù hợp với từng doanh nghiệp và hơn nữa hướng dẫn cho bạn những thủ thuật tính toán nhanh chóng vô cũng chính xác”.

Khóa học nổi bật

• 

  THÀNH THẠO Kế toán thực tế trên phần mềm MISA chỉ sau 24 ngày – Học xong làm ngay

  Nguyễn Lê Hoàng

  599,000đ900,000đ
• 

  THÀNH THẠO Kế toán tổng hợp trên Excel sau 30 ngày – Học xong LÀM NGAY

  Nguyễn Lê Hoàng

  499,000đ800,000đ