Bảng doanh thu chi phí lợi nhuận
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.
Bạn đang xem: Bảng doanh thu chi phí lợi nhuận
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:
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ánTạ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.
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.
Xem thêm: Tổng Quan Về Quản Trị Rủi Ro Tín Dụng Là Gì ? Rủi Ro Tín Dụng Là Gì
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:
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 6Do đó 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:
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ánLợ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: Cách Lấy Lại Tên Đăng Nhập Vietinbank Ipay Là Gì ? Số Giấy Tờ Cá Nhân Là Gì?
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 vietradeportal.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ế
