Excel được sử dụng rất nhiều trong tài chính, quản trị với khả năng phân tích dữ liệu giúp đưa ra quyết định. Một trong những bài toán thường gặp đó là phân tích biến động doanh thu, lợi nhuận theo biến động giá bán. Việc biến động giá bán sẽ ảnh hưởng tới số lượng bán, từ đó ảnh hưởng tới doanh thu, lợi nhuận thế nào?. Trong bài viết này Học Excel Online sẽ giúp bạn nắm được 1 cách cơ bản về việc phân tích này, cách tính lợi nhuận dựa vào biến động giá bán kèm theo số lượng bán trên Excel.

Để dễ hình dung, chúng ta sẽ lấy ví dụ cụ thể như sau:

Đề bài

Bộ phận Marketing khi nghiên cứu thị trường đã đưa ra một bảng kế hoạch như sau:

Ở mức giá giả định ban đầu là 100.000đ/sản phẩm thì số lượng bán như sau:

• 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

Chi phí hoạt động tháng 1 là 100 triệu, mỗi tháng tiếp theo sẽ tăng 5% so với tháng trước.

Yêu cầu: Tìm mức giá bán thu được lợi nhuận lớn nhất, biết biến động số lượng bán theo các mức giá bán như sau:

• Mức giá 80.000đ thì số lượng bán đạt 120% so với mức giá 100.000đ
• Mức giá 110.000đ thì số lượng bán đạt 80% so với mức giá 100.000đ
• Mức giá 130.000đ thì số lượng bán đạt 70% so với mức giá 100.000đ

Cách giải quyết

1. Trình bày đề bài trên Excel

Một trong những nguyên nhân lớn khiến hầu hết chúng ta không làm được yêu cầu công việc chính là việc không biết cách trình bày lại được yêu cầu trên Excel. Việc trình bày một cách khoa học sẽ giúp chúng ta hiểu rõ được mục đích và phương pháp đưa ra được cách giải quyết vấn đề.

Bước 1: Lập bảng theo dõi Doanh thu – Chi phí – Lợi nhuận theo từng tháng.

Bởi chúng ta có 6 tháng, do đó phải xác định các chỉ tiêu theo từng tháng. Cộng tổng các tháng lại để xác định xem trong 6 tháng đó lợi nhuận cuối cùng là bao nhiêu.

Bước 2: Lập bảng tính số lượng bán theo giá bán

Trong đề bài đã cung cấp cho chúng ta đủ thông tin về các mức giá bán, số lượng bán ở 1 mức là 100.000đ và tỷ lệ thay đổi theo các mức giá bán khác. Do đó chúng ta lập bảng tính như sau:

Bước 3: Xác định mục tiêu

Yêu cầu bài toán là xác định mức giá bán thu được lợi nhuận cao nhất. Ở đây chúng ta hiểu là:

• Mỗi mức giá sẽ làm thay đổi doanh thu
• Chi phí không ảnh hưởng bởi mức giá (theo dữ kiện đề bài)
• Do đó lợi nhuận sẽ thay đổi khi doanh thu thay đổi
• Cần tìm ra ở giá bán nào có lợi nhuận cao nhất

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

Sau khi đã xây dựng được cấu trúc các bảng tính chúng ta sẽ thực hiện xây dựng các công thức tính như sau:

2. Xây dựng công thức tính lợi nhuận theo các mức giá bán

Bước 1: Xây dựng công thức tính bảng Số lượng bán theo các mức giá bán

Tại mức giá 100.000đ được quy ước có tỷ lệ số lượng bán đạt 100%, tương ứng với các số liệu đã có ở dòng 9

Mức giá 80.000đ có tỷ lệ số lượng bán đạt 120% so với mức giá 100.000 do đó:

B8=B9*120%

Công thức tại B8 có thể áp dụng sang các tháng khác tới tháng 6 (ô G8)

Tương tự các mức giá khác:

• Mức giá 110.000 ô B10 = B9*80%
• Mức giá 130.000 ô B11=B9*70%

Kết quả thu được:

Lưu ý: Khi tính ra kết quả thì chúng ta có thể thấy phần số lẻ (số có phần thập phân). Nhưng về nguyên tắc sản phẩm không có lẻ, do đó chúng ta phải làm tròn xuống cho tất cả các giá trị tính được.

Sử dụng hàm ROUNDDOWN để làm tròn xuống cho công thức tại các ô như sau:

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

Áp dụng công thức từ B8 tới G8, B10 tới G10, B11 tới G11

Khi đó bảng kết quả sẽ là:

Bước 2: Xác định chi phí các tháng

Chi phí tháng sau tăng 5% so với tháng trước: cách tính như sau:

• Chi phí tháng 1: B3=100.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 sẽ là:

Bước 3: Xác định doanh thu : tạo danh sách chọn các mức giá bán

Doanh thu phụ thuộc vào từng mức 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à H1.

Tại ô H1 sử dụng Data Validation/List. Trong đó danh sách chọn là các mức giá bán (A8:A11)

(Xem thêm: Tạo danh sách chọn Drop-down List với chức năng Data Validation)

Bước 4: Xác định doanh thu : Công thức tính doanh thu

Với mức giá bán được  chọn tại ô H1 ta có doanh thu tương ứng của mỗi tháng là:

Doanh thu = Giá bán * Số lượng bán.

Số lượng bán của tháng sẽ được tham chiếu tới bảng Số lượng bán theo giá bán (bước 1) bằng cách sử dụng hàm Vlookup. Tuy nhiên ở đây chúng ta cần tìm theo nhiều cột kết quả, do đó có thể kết hợp thêm hàm Match để làm công thức vlookup được linh động hơn, không phải xác định thủ công đối số col_index_num trong hàm vlookup.

(Xem thêm: 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ả)

Kết quả thu được như sau:

Bước 5: Xác định lợi nhuận

Lợi nhuận có thể được xác định một cách dễ dàng bằng cách:

Lợi nhuận = Doanh thu – chi phí

Sau khi đã thực hiện tất cả các bước trên chúng ta có kết quả tổng lợi nhuận cho mức giá bán 80.000đ là:

Bước 6: Xác định kết quả khi thay đổi từng mức giá bán

Sau khi đã xác định được lợi nhuận cho 1 mức giá, chúng ta chỉ cần thay đổi giá bán là có thể tự động xác định được lợi nhuận của mức giá đó.

Lần lượt thay đổi các giá bán, chúng ta có kết quả cuối cùng như sau:

Kết luận: Mức giá có lợi nhuận cao nhất là mức giá 100.000đ

Đây là dạng bài toán điển hình trong các mô hình giả định, xây dựng phương án kinh doanh, phương án hoạt động tối ưu dựa trên giả định thay đổi 1 tiêu chí. Qua bài toán này chúng ta đã có thể bước đầu hình thành được tư duy xây dựng mô hình giả định trên Excel một cách cơ bản được rồi.

Tải về file mẫu tại địa chỉ: http://bit.ly/2JQJuy2

Xem thêm: 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

Ngoài ra Học Excel Online xin giới thiệu với các bạn Khóa học Excel từ cơ bản tới nâng cao dành cho người đi làm. Đây là 1 khóa học rất đầy đủ kiến thức và bổ trợ rất tuyệt vời cho bạn trong việc làm quen với Excel, sử dụng các công cụ, các hàm trong Excel để giúp bạn làm việc trên Excel được tốt hơn, hiệu quả hơn. Chi tiết xem tại: