LẬP BẢNG THỐNG KÊ DOANH THU TRONG EXCEL

Đố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 quansulienminh.vn tìm hiểu trong bài viết ngày hôm nay nhé.

Bạn đang xem: Lập bảng thống kê doanh thu trong excel

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ẩmTháng 2: 1.513 sản phẩmTháng 3: 1.352 sản phẩmTháng 4: 1.684 sản phẩmTháng 5: 1.484sản phẩmThá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.

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.

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 trongbả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.

Xem thêm: Nên Mua Vàng Để Đeo Vàng Ta Có Bị Hao Không ? Đeo Vàng Ta Có Bị Hao Mòn Khô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ậnPhươ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 đặtdanh sách chọn giá bánlà 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.

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.

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 quansulienminh.vn 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ềExceldướ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ế

Leave a Reply

Your email address will not be published. Required fields are marked *