Nhã Linh
Nhã Linh PRO
Thảo luận 0 thảo luận
Vỗ tay 0 vỗ tay
Lượt xem 7697 lượt xem

Hướng dẫn lập bảng tính lợi nhuận bằng Excel dựa theo biến động giá

Apr 19 2021

Đối với doanh nghiệp, việc giá bán thay đổi sẽ ảnh hưởng trực tiếp đến số lượng hàng hóa bán ra, dẫn đến sự biến động trong doanh thu và lợi nhuận. Trong các trường hợp như vậy, chúng ta cần lập bảng tính lợi nhuận bằng Excel như thế nào? Hãy cùng Gitiho tìm hiểu trong bài viết ngày hôm nay nhé.

Tuyệt đỉnh Excel - Trở thành bậc thầy Excel trong 16 giờ

Tình huống lập bảng tính lợi nhuận bằng Excel dựa theo giá bán

Mức giá ổn định trong bảng tính lợi nhuận bằng Excel

Chúng ta có một tình huống tính toán lợi nhuận trên giá bán như sau: Bộ phận Marketing của doanh nghiệp sau quá trình tìm hiểu và nghiên cứu thị trường đã đưa ra các con số cụ thể với mức giá bán 200.000VNĐ/sản phẩm thì doanh thu của doanh nghiệp sẽ rơi vào khoảng:

  • Tháng 1: 1.236 sản phẩm
  • Tháng 2: 1.513 sản phẩm
  • Tháng 3: 1.352 sản phẩm
  • Tháng 4: 1.684 sản phẩm
  • Tháng 5: 1.484 sản phẩm
  • Tháng 6: 1.675 sản phẩm

Trong khi đó, chi phí hoạt động của tháng 1 ghi nhận trong sổ kế toán là 200 triệu VNĐ, trung bình mỗi tháng chi phí sẽ tăng 5% so với tháng trước. Dựa vào những yếu tố trên, ta hoàn toàn có thể lập bảng tính lợi nhuận bằng Excel để kê khai lợi nhuận trên giá bán của doanh nghiệp

Mức giá biến động trong bảng tính lợi nhuận bằng Excel

Tuy nhiên, doanh nghiệp đã nhận thấy sự biến động trong số lượng hàng hóa bán ra dựa trên giá bán như sau:

  • Mức giá 180.000VNĐ thì số lượng bán đạt 120% so với mức giá 200.000đ
  • Mức giá 220.000đ thì số lượng bán đạt 80% so với mức giá 200.000đ
  • Mức giá 240.000đ thì số lượng bán đạt 70% so với mức giá 200.000đ

Như vậy, câu hỏi đặt ra cho doanh nghiệp là: Tìm mức giá bán thu được lợi nhuận cao nhất.

Cách lập bảng tính lợi nhuận bằng Excel dựa theo biến động giá

Trình bày tóm tắt tình huống trên bảng tính lợi nhuận bằng Excel

Để giải quyết bất kì một bài toán nào, chúng ta cũng cần bắt đầu bằng bước tóm tắt đề bài. Vì vậy, hãy cùng nhập các thông tin cần thiết vào trang tính Excel. 


Lập bảng theo dõi Doanh thu - Chi phí - Lợi nhuận theo tháng



Chúng ta có 6 tháng trong bảng, yêu cầu phải xác định các chỉ tiêu doanh thu, chi phí, lợi nhuận của doanh nghiệp trong tất cả 6 tháng. Bằng cách cộng tổng các số liệu đó, ta sẽ xác định được mức lợi nhuận trên giá bán cuối cùng trên bảng tính lợi nhuận bằng Excel.


Lập bảng tính số lượng theo giá bán


Ở phần trên, chúng ta đã biết số lượng hàng bán ra theo từng tháng dựa trên mức giá 200.000VNĐ/sản phẩm. Như vậy, chúng ta có thể lập bảng doanh thu làm cơ sở tính lợi nhuận trên giá bán như sau:



Xác định mục tiêu kinh doanh

Yêu cầu quan trọng nhất của bảng tính lợi nhuận bằng Excel là xác định mức giá bán thu lời cao nhất. Dưới đây là những dữ kiện ta đã biết:

  • Tại mỗi mức giá, doanh thu khác nhau.
  • Mức giá không làm ảnh hưởng đến chi phí sản xuất và duy trì hoạt động doanh nghiệp.

Từ 2 dữ kiện trên, ta hiểu rằng lợi nhuận trên giá bán sẽ thay đổi khi doanh thu thay đổi. Dựa vào đó, chúng ta sẽ tìm được giá bán phù hợp nhất để tối đa hóa doanh thu.


Ta lập bảng theo dõi kết quả như sau:

Vậy là ta đã hoàn thành việc tạo các bảng tính để lập bảng tính lợi nhuận bằng Excel. Bây giờ, chúng ta sẽ tiến hành áp dụng các công thức hàm Excel để tính ra kết quả bài toán ngày hôm nay.

Xem thêm: Hướng dẫn nguyên tắc kế toán các khoản doanh thu theo thông tư 200

Các công thức hàm để lập bảng tính lợi nhuận trong Excel

Công thức tính trong bảng Số lượng bán theo các mức giá bán

Tại mức giá ban đầu 200.000VNĐ, ta mặc định tỉ lệ hàng hóa bán ra đạt 100%. Dựa vào đó, chúng ta có thể sử dụng các hàm Excel cơ bản để tính toán số lượng hàng hóa bán ra tại các mức giá khác trong bảng tính lợi nhuận bằng Excel.

Xem thêm: Hướng dẫn cách viết các hàm trong Excel chi tiết dễ hiểu nhất

Tại ô B8, ta điền công thức: =B9*120%

Lí do là bởi vì tại mức giá 180.000VNĐ, doanh nghiệp có thể bán ra 120% số lượng hàng hóa so với mức giá 200.000VNĐ.


Tương tự, ta nhập các công thức nhân trong Excel để dễ dàng tính toán các dữ liệu ô khác trong bảng.


Kết quả thu được tại bảng tính lợi nhuận trong Excel như sau:



Lưu ý: Kết quả Excel trả về có thể xuất hiện các số thập phân. Tuy nhiên, do số lượng sản phẩm phải là số nguyên nên chúng ta cần thực hiện làm tròn xuống cho tất cả giá trị lợi nhuận trên giá bán trong bảng.


Ví dụ tại cột Tháng 1, chúng ta có thể sử dụng hàm ROUNDDOWN với các ô B8, B10, B11 để làm tròn các con số tại bảng tính lợi nhuận trong Excel như sau:

  • B8=ROUNDDOWN(B9*120%,0)
  • B10=ROUNDDOWN(B9*80%,0)
  • B11=ROUNDDOWN(B9*70%,0)

Sau đó chúng ta áp dụng công thức với các cột còn lại để ra được kết quả như hình dưới đây:



Phương pháp tính trong bảng Doanh thu - Chi phí - Lợi nhuận

Phương pháp tính Chi phí

Ta đã biết chi phí tháng sau tăng nhẹ 5% so với tháng trước. Do vậy, ta sử dụng các công thức tính toán trong Excel như sau:

  • Chi phí tháng 1: B3=200.000.000đ
  • Chi phí tháng 2: C3=B3*5%+B3=B3*(1+5%)
  • Chi phí tháng 3: D3=C3*(1+5%)
  • Cứ như vậy tăng dần tới tháng 6

Do đó kết quả thu được của bảng tính lợi nhuận bằng Excel hiển thị như hình dưới đây:

Xem thêm: Hướng dẫn cách vẽ biểu đồ tìm điểm chi phí sản xuất tối ưu trên Excel

Phương pháp tính Doanh thu

Doanh thu của doanh nghiệp phụ thuộc vào sự biến động trong giá bán. Do đó chúng ta cần có 1 tham số là Giá bán để tính doanh thu.

Coi vị trí cần đặt danh sách chọn giá bán là G1. Tại ô G1 chúng ta sử dụng Data Validation để tạo danh sách các giá trị có thể điền. Trong đó danh sách chọn là các mức giá bán tại bảng Số lượng bán theo các mức giá.

Sau khi đã có danh sách các mức giá tại ô G1, ta có công thức tính doanh thu mỗi tháng: Doanh thu = Giá bán * Số lượng bán.

Số lượng hàng hóa bán ra trong tháng cần được tham chiếu tới bảng Số lượng bán theo các mức giá bằng cách sử dụng hàm VLOOKUP trong Excel. Với yêu cầu tìm kiếm dữ liệu theo nhiều cột kết quả, ta có thể kết hợp thêm hàm MATCH để làm công thức hàm VLOOKUP linh động hơn. Tại ô B3 của bảng tính lợi nhuận bằng Excel, ta nhập công thức sau:

=$G$1*VLOOKUP($G$1,$A$8:$G$12,MATCH(B2,$A$8:$G$8,0),0)


Tương tự, ta áp dụng công thức để tính doanh thu cho các tháng sau. 

Xem thêm: Hướng dẫn cách sử dụng hàm vlookup kết hợp hàm Match khi tìm theo nhiều cột chứa kết quả

Phương pháp tính Lợi nhuận trên giá bán

Lợi nhuận trên giá bán có thể được xác định một cách dễ dàng bằng công thức: Lợi nhuận = Doanh thu – chi phí

Sau khi đã có tất cả các kết quả về doanh thu và chi phí các tháng, chúng ta có thể dễ dàng hoàn thành bảng tính lợi nhuận bằng Excel cho mức giá 180.000VNĐ bằng cách điền công thức trừ Excel vào ô B5: B5=B3-B4.

Như vậy, kết quả cuối cùng về lợi nhuận trên giá bán 180.000VNĐ chúng ta thu được như sau:

Xác định lợi nhuận khi thay đổi mức giá bán

Như vậy, chúng ta đã hoàn thành công việc xác định lợi nhuận cho mức giá 180.000VNĐ. Với tính năng Data Validation, mỗi lần chúng ta thay đổi dữ liệu trong danh sách, Excel sẽ tự động cập nhật kết quả các hàm sử dụng giá trị thay đổi đó. 

Sau khi chúng ta tính tổng lợi nhuận trên giá bán sau 6 tháng, húng ta có thể điền lợi nhuận thu được theo các mức giá bán như sau:

Theo như trong bảng, với mức giá 180.000VNĐ, doanh nghiệp sẽ thu về lợi nhuận cao nhất. Chúng ta đã hoàn thành bảng tính lợi nhuận bằng Excel rồi.

Xem thêm: Hướng dẫn tự động xác định kết quả tìm mức giá bán tối ưu bằng cách sử dụng VBA

Tổng kết

Chúng ta vừa tìm hiểu cách lập bảng tính lợi nhuận bằng Excel giúp doanh nghiệp tính toán lợi nhuận trên giá bán, qua đó đưa ra các quyết định quan trọng cho công việc kinh doanh hiện tại và tương lai. Hi vọng bài viết trên đã đem lại những kiến thức bổ ích cho các bạn kế toán tài chính. Nếu các bạn muốn tham khảo thêm các loại tài liệu Excel khác, hãy tìm đọc những bài viết trên blog Gitiho và đăng kí khóa học Tuyệt đỉnh Excel với chúng mình nhé.

Chúc các bạn thành công!

Tham khảo thêm các bài viết về Excel dưới đây:

Hướng dẫn cách dùng hàm index trong Excel chi tiết nhất

Hướng dẫn cách copy chỉ những giá trị hiển thị sau khi lọc trong Excel

Hướng dẫn cách sắp xếp ngày trong Excel

Hướng dẫn cách dùng hàm VLOOKUP qua các ví dụ từ cơ bản đến nâng cao

Hướng dẫn cách sử dụng hàm IF với nhiều điều kiện: AND, OR, hàm IF lồng nhau và hơn thế


Cùng tham gia cộng đồng hỏi đáp về chủ đề Excel Cơ Bản

Thảo luận 0 câu trả lời
Lượt xem 7697 lượt xem
Vỗ tay vỗ tay

0 Bình luận

@ 2020 - Bản quyền của Công ty cổ phần công nghệ giáo dục Gitiho Việt Nam
Giấy chứng nhận Đăng ký doanh nghiệp số: 0109077145, cấp bởi Sở kế hoạch và đầu tư TP. Hà Nội