Công Thức Tính Lợi Nhuận Trong Excel Chi Tiết Và đầy đủ Nhất - Unica

Có thể bạn quan tâm

Học không giới hạn 700+ khóa học chỉ với 199K / tháng Xem ngay Đăng nhập Đăng ký Kích hoạt khóa học Hội viên Doanh nghiệp

Danh mục đào tạo

Kinh doanh & Khởi nghiệp Marketing & Bán hàng AI & Công nghệ Thiết kế & Sáng tạo Video & Nhiếp ảnh Ngoại Ngữ Tin Học Văn Phòng Kỹ năng mềm Sức khỏe & Làm đẹp Tài Chính & Đầu tư Phong Cách Sống Sách hay nên đọc Tải app

Hỗ trợ

Hotline: 090 488 6095 Email: [email protected] Close menu Blog Tin Học Văn Phòng Công thức tính lợi nhuận trong Excel chi tiết và đầy đủ nhất

Bởi Nguyễn Thành Đông

31/03/2025 27927 Mục lục

Excel là công cụ hỗ trợ không giới hạn công việc đối với người dùng, đặc biệt là trong vấn đề tài chính. Đối với doanh nghiệp, cơ sở kinh doanh họ thường dùng excel để phân tích biến động doanh thu, lợi nhuận. Trong bài viết sau, Unica sẽ hướng dẫn bạn công thức tính lợi nhuận trong Excel chi tiết và đầy đủ nhất, cùng tham khảo để biết cách tính nhé.

Ví dụ công thức tính lợi nhuận trong Excel

Đố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đ

excel

Phân tích yêu cầu

Với ví dụ chia sẻ ở phần trên, chắc chắn phần nào bạn cũng đã hiểu công thức tính lợi nhuận trong excel như thế nào. Tuy nhiên, để biết cụ thể cách tính lãi lỗ trong excel thì bạn cần phân tích yêu cầu. Cụ thể vấn đề này như sau:

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 tính doanh thu chi phí lợi nhuận bằng excel trong vòng 6 tháng:

Tạo bảng theo dõi các yếu tố

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:

Lập bảng tính số lượng phụ thuộc theo giá bán

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 bảng tính lợi nhuận bằng excel và theo dõi.

Xác định yêu cầu mục tiêu

Tạo công thức tính lợi nhuận trong Excel theo mức giá bán

Phần tiếp theo của bài viết, chúng tôi sẽ hướng dẫn bạn công thức tính doanh thu trong excel, đây là doanh thu lợi nhuận thu về theo mức giá biết. Cụ thể công thức tính lợi nhuận trong excel này như sau:

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 chính là cách tính phần trăm lợi nhuận trong excel.

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 đấy là cách tính doanh thu trong excel nhanh và đơn giản.

Tạo bảng số lượng theo mức giá bán 110.000đ và 130.000đ

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:

Làm tròn các giá bán 110.000đ và 130.000đ

>>> Tất cả đều có trong cuốn sách "HƯỚNG DẪN THỰC HÀNH EXCEL TỪ CƠ BẢN ĐẾN NÂNG CAO"

ĐĂNG KÝ MUA NGAY

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 thủ thuật Excel này là:

Xác định chi phí cho các tháng

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)

Xác định doanh thu

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.

Công thức tính doanh thu

Tính lợi nhuận

Lợi nhuận = Doanh thu - Chi phí

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á:

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

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.

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

Sau khi đã biết cách lập bảng tính lợi nhuận trong excel dựa trên giá biến thì bạn cũng cần phải biết cách lập bảng doanh thu chi phí lợi nhuận dựa theo biến động giá. Đối với cách tính doanh thu để quản lý bán hàng trong excel dựa theo biến động giá bạn thực hiện như sau:

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ỳ vấn đề nào, việc đầu tiên cũng là công đoạn tóm tắt vấn đề và việc tính doanh thu chi phí lợi nhuận trong excel cũng vậy. Đầu tiên bạn cần phải xác định được một số thông tin cơ bản bao gồm: doanh thu, chi phí, lợi nhuận, số lượng bán, mục tiêu kinh doanh. Mục đích để tạo được bảng kết quả cuối cùng.

Ví dụ cụ thể:

Đầu tiên lập bảng theo dõi doanh thu - chi phí - lợi nhuận từng tháng. Ở ví dụ này chúng tôi sẽ lập chỉ tiêu doanh thu, chi phí, lợi nhuận trong 6 tháng. Bằng cách cộng tổng các số liệu này bạn sẽ xác định được mức lợi nhuận trên giá bán cuối cùng tại bảng tính lợi nhuận bằng excel

Lập bảng theo dõi doanh thu - chi phí- lợi nhuận

Tiếp theo bạn lập bảng số lượng theo giá bán. Ở trong ví dụ này chúng tôi đã quy ước 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. Với quy ước này bạn 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:

Bảng số lượng theo các mức giá

Cuối cùng bạn xác định mục tiêu kinh doanh, tức là xác định mức giá bán làm sao để thu lời cao nhất. Những dự kiện ta đã biết bao gồm:

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

Từ 2 dữ liệu có sẵn đó, bạn xác định được một điều rằng: Khi doanh thu thay đổi thì lợi nhuận sẽ thay đổi. Vì vậy việc xác định mức doanh thu để thu lời cao nhất là vô cùng quan trọng. Bạn bắt buộc phải xác định được mức giá bán phù hợp nhất để tối ưu hoá doanh thu.

Từ những dữ liệu đã phân tích ở trên bạn sẽ có một bảng kết quả sau:

Bảng xác nhận lợi nhuận được thu theo các mức giá bán

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 mức giá bán

Ở mức giá bán 200.000 đồng, chúng tôi sẽ mặc định tỷ lệ bán ra đạt 100%. Khi hàng hoá bán đạt hết thì tiếp theo bạn cần phải dùng hàm để tính toán số lượng hàng hoá bán ra.

Ví dụ cụ thể như sau: Tại ô B8, bạn điền công thức B9*120%.

Lý do vì: Ở mức giá 200.000 đồng mặc định tỷ lệ bán đạt 100% thì ở mức giá 180.000 doanh nghiệp chắc chắn phải bán ra 120% số lượng hàng hoá. Vì vậy nên ta nhập công thức trên.

Nhập công thức vào bảng để tính lợi nhuận

Tương tự bạn tính toán hết các dữ liệu cho các ô khác.

Kết quả thu được cho các ô như sau

Ngoài công thức trên, bạn cũng 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. Cụ thể 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ả cuối cùng.

Công thức tinh trong bảng Doanh thu - Chi phí - Lợi nhuận

Công thức tính chi phí: Ta biết chi phí tháng sau sẽ tăng nhẹ 5% so với chi phí tháng trước. Lúc này, bạn áp dụng các công thức tính toán tong 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 cho tới tháng 6

Bảng kết quả cuối cùng hiển thị

Công thức tính doanh thu: Doanh thu sẽ phụ thuộc vào giá bán. Vì vậy bạn cần phải tạo tham số là giá bán để tính toán doanh thu.

Đặt vị trí đặt danh sách giá bán là G1. Tại ô G1 bạn 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á".

Bảng số lượng bán theo mức giá

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

Chú ý: Số lượng hàng hóa bán ra trong tháng bắt buộc phải đượ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ả, bạn có thể kết hợp thêm cả với 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 trong Excel, bạn nhập vào đó công thức sau:

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

Công tính tính lợi nhuận trong bảng excel

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

Công thức tính lợi nhuận trên giá bán: Lợi nhuận = Doanh thu - Chi phí

Sau khi đã có hết các kết quả về doanh thu và chi phí các tháng, bạn điền công thức trừ Excel vào ô B5: B5=B3-B4. Kết quả nhận được như sau:

Bảng lợi nhuận trên giá bán

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

Trường hợp thay đổi mức giá bán thì bạn sẽ phải xác nhận lại lợi nhuận. Để xác định lợi nhuận khi thay đổi mức giá bạn sử dụng tính năng Data Validation. Khi sử dụng tính năng này, mỗi lần thay đổi dữ liệu trong danh sách, excel sẽ tự động cập nhật các thay đổi.

Công thức tính phần trăm lợi nhuận trong Excel

Công thức tính phần trăm lợi nhuận trong excel như sau:

Đối với phần trăm lợi nhuận gộp: Phần trăm lợi nhuận gộp là tỉ suất lợi nhuận sau khi trừ giá vốn của hàng hoá. Cách tính phần trăm lợi nhuận gộp như sau: Tỷ lệ % lợi nhuận gộp = Lợi nhuận gộp / Doanh thu.
Đối với phần trăm lợi nhuận trước thuế/ sau thuế: Phần trăm lợi nhuận này được tính như sau: Tỷ lệ % lợi nhuận trước thuế/ sau thuế = Lợi nhuận trước thuế hoặc sau thuế / Doanh thu

Tổng kết

Công thức tính lợi nhuận trong Excel tuy có hơi phức tạp nhưng nếu như bạn biết 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 thì chắc chắn bạn sẽ không thấy khó nữa, có thể đưa ra cho mình những đáp án chính xác nhất.

>>> Xem thêm:

Khoá học Excel Online giá rẻ chỉ từ 299k
4 Cách gộp họ và tên trong excel siêu nhanh
Cách dùng hàm count trong excel kèm ví dụ

Tags: Excel Trở thành hội viên

Tăng năng suất làm việc lên đến 50% nhờ các hàm và công thức Excel thông minh.

Đăng ký học thử để trải nghiệm ngay hôm nay! course_relate

Trở thành Siêu sao Excel nơi công sở

899.000đ 1.000.000đ Đăng ký Học thử Like 0 Share Tweet Share 0 1 2 3 4 5 0/5 - (1 bình chọn)

Bài liên quan

Phím tắt chuyển sheet trong excel và các cách chuyển sheet nhanh

Cách tính thời gian trong Excel siêu đơn giản, nhanh chóng

Bật mí công thức tính số tháng trong Excel siêu đơn giản

Cách in trang chẵn lẻ trong Excel thao tác nhanh chóng

Hướng dẫn cách tạo combobox trong Excel chi tiết, đơn giản nhất

Cách cài đặt font chữ mặc định cho Excel Khóa học nổi bật

Sách Data & AI Thực Chiến với Excel và Power BI

Nguyễn Hữu Đạt 219.000đ 268.000đ

Sách hướng dẫn thực hành Excel từ cơ bản đến nâng cao

Đội ngũ Unica 198.000đ 300.000đ

Sách hướng dẫn thực hành Google Sheets từ cơ bản đến nâng cao

Tô Văn Thuật 168.000đ 268.000đ Chủ đề được quan tâm Phong Cách Sống Marketing & Bán hàng Kinh doanh & Khởi nghiệp Thiết kế & Sáng tạo Tin Học Văn Phòng Kỹ năng mềm Sức khỏe & Làm đẹp Bài viết phổ biến