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

Đố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é.

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.

Xem thêm: Hướng dẫn trình bày bảng tính Excel đẹp và chuyên nghiệp 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.

Nếu bạn là kế toán viên, nhà tài chính, hay nhà quản lý, thường xuyên phải tính lợi nhuận và quản lý công việc kinh doanh trên Excel, bạn có thể tham khảo ngay khóa học sau của Gitiho giúp bạn tận dụng tối đa lợi ích của Excel, làm việc hiệu quả, cũng như tiết kiệm được nhiều thời gian:

EXG08 - Excel cho Tài chính, Kế toán và Phân tích tài chính

G-LEARNING

499,000^đ 899,000^đ

Đăng ký Học thử

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. 

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:

Xem thêm: Chia sẻ file mẫu bảng tính lương Excel theo thông tư 133

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:

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:

Xem thêm: Hướng dẫn cách hạch toán doanh thu bán hàng chi tiết cho kế toán

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: Cách sắp xếp sheet (bảng tính) trong file Excel theo thứ tự A đến Z hoặc ngược lại

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. 

Mong rằ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 tin học văn phòng của Gitiho và đăng ký khóa học Excel online với chúng mình nhé.

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