Hãy tưởng tượng bạn điều hành một công ty nội thất văn phòng. Bạn muốn ngừng đặt thêm hai thương hiệu đồ nội thất – Relaxer (một loại ghế) và Boca Top (một loại bàn). Bạn hiện có 20,000 ghế Relaxer và 5,000 chiếc ghế Boca Top, chúng có giá trị tương ứng $200,000 và $100,000. Khi bán, công ty sẽ thu được lợi nhuận gộp là $100,000 và $25,000. Bạn hy vọng sẽ bán được chúng trong 2 hoặc 3 năm và dự báo doanh số bán hàng như dưới đây.
Do đó bạn cần thực hiện việc phân tích và chuẩn bị một mô hình cho các dòng tiền diễn ra trong tương lai. Điều này có hoàn toàn dễ dàng khi bạn dùng các kỹ thuật phân tích khác nhau trong Excel.
1. Thiết lập vấn đề
Hầu hết các vấn đề trường hợp kinh doanh sẽ có ba loại tham số sau:
- Fixed Inputs (Đầu vào cố định) – ví dụ: dữ liệu kho ghế & bàn, giá trị sổ sách của các mặt hàng này.
- Variables (Biến) – Số lượng ghế và bàn được bán mỗi tháng (hoặc năm), kỳ vọng lợi nhuận.
- Assumptions (Giả định) – Có thể bán hết tất cả các mặt hàng (không xóa sổ), lợi nhuận trên mỗi chiếc và giá trị sổ sách trên mỗi chiếc không thay đổi theo thời gian
Tất nhiên, ba loại này có thể chồng chéo lên nhau. Sử dụng kinh nghiệm và kiến thức ngành của bạn để xác định các dữ liệu thuộc về loại tham số nào.
2. Tại sao phải bận tâm điều này, tại sao tất cả dữ liệu nhập vào không thể là một biến?
Bạn có thể coi mọi thứ là một biến trong mô hình của mình. Điều này sẽ mang lại sự linh hoạt tối đa, nhưng đi kèm với rất nhiều chi phí. Mô hình của bạn trở nên phức tạp và có thể mất nhiều thời gian để phát triển. Nó có thể cần quá nhiều kĩ năng, vì vậy hãy xác định một vài hằng số (constants – đầu vào cố định).
Khi mô hình của bạn ở trong Excel, tất cả các ô dữ liệu đều có thể được chỉnh sửa. Vì vậy, về mặt kỹ thuật tất cả đều là các biến.
3. Xác định đầu ra
Bước tiếp theo là xác định đầu ra. Trong trường hợp này, chúng ta có thể tính toán ba loại đầu ra.
- Số lượng bàn ghế được bán theo tháng
- Doanh thu theo tháng
- Lợi nhuận theo tháng
Chúng ta có thể thêm một đầu ra tùy chọn – trực quan hóa các kết quả (visualization of the results).
4. Làm thế nào để đi từ đầu vào đến đầu ra?
Đây là nơi chúng ta tìm ra các quy tắc kinh doanh và logic tính toán để đi đến đầu ra thông qua dữ liệu đầu vào.
Hãy xác định công thức cho mỗi giá trị đầu ra:
- Số lượng bán ra mỗi tháng = Số lượng bán được trong năm/12
- Doanh thu mỗi tháng = số lượng bán mỗi tháng * giá trị sổ sách trên mỗi chiếc.
- Lợi nhuận mỗi tháng = đơn vị mỗi tháng * lợi nhuận trên mỗi chiếc
Mô hình ban đầu
Thiết lập khu vực dữ liệu đầu vào như dưới đây. Các ô màu cam chứa đầu vào của người dùng. Những màu xám có giá trị tính toán.
Lưu ý: Nếu công việc kinh doanh của bạn phức tạp, bạn cần thiết lập các bảng tính chuyên dụng cho từng loại đầu vào (fixed, variable và assumption). Điều này sẽ cho phép bạn sử dụng các kết hợp khác nhau và kiểm soát đầu ra theo cách tốt hơn.
1. Tính toán đầu ra
Phần khó khăn là tìm ra số lượng ghế & bàn được bán mỗi tháng. Khi chúng ta có những con số này, việc tính toán doanh thu và lợi nhuận mỗi tháng sẽ trở nên dễ dàng.
Hãy để đầu ra trong 60 tháng. Mặc dù ban đầu bạn ước tính rằng tất cả hàng trong kho sẽ được bán trong 3 năm đầu tiên, nhưng điều này cho phép bạn theo dõi dòng tiền trong 5 năm, nếu có sự thay đổi trong đầu vào.
Số tháng bán hàng sẽ nằm trong cột G, từ G6 đến G65. Cụ thể như sau:
- Số tháng trong G6
- Số lượng ghế hàng năm trong phạm vi C$19: C$23.
Chúng ta có thể tính toán số lượng ghế được bán trong tháng đó bằng cách sử dụng công thức dưới đây (gọi là công thức 1) Số lượng ghế bán ra mỗi tháng (ô H6)=INDEX(C$19: C$23, INT(($G6-1)/12)+1)/1
2. Công thức trên hoạt động
Đơn giản, chúng tôi chọn số lượng bán ra hàng năm để biểu thị cho tháng bằng công thức INDEX. Để tính năm từ tháng (G6), chúng tôi sử dụng số học đơn giản: INT(($G6-1)/12)+1. Khi số lượng bán ra hàng năm được chọn, chúng tôi chỉ cần chia nó cho 12 để có được số lượng hàng tháng (tức là số chiếc được bán mỗi tháng).
Lưu ý rẳng công thức trên được thiết lập theo kiểu tham chiếu hỗn hợp, điều này sẽ giúp bạn kéo và sử dụng lại cùng một công thức để tính toán số lượng trên bảng. Các số liệu tính toán sẽ đi vào cột H&I.
3. Tính toán doanh thu, lợi nhuận và tổng lợi nhuận
Bây giờ chúng ta biết số lượng bán ra mỗi tháng, tính toán ba đầu ra còn lại là điều dễ dàng.
1. Doanh thu của ghế Relaxer (ô J6)=H6*C$8 (Lưu ý: C$8 có giá trị sổ sách trên mỗi ghế)
2. Lợi nhuận của ghế (ô L6)=H6*C$11
3. Tổng lợi nhuận (ô N6)=L6+M6
Tham khảo bảng dữ liệu bên dưới để xem kết quả mẫu đi cùng với số công thức.
4. Thêm tình huống vào mô hình này
Mô hình ban đầu của chúng tôi là một công thức đơn giản được cho bởi bảng kết quả. Nhưng nếu bạn muốn thấy dòng lợi nhuận theo các kịch bản khác nhau thì sao? Có thể là dự báo hàng năm ban đầu của bộ phận tiếp thị quá lạc quan và bạn muốn xem điều gì sẽ xảy ra nếu chúng ta bán ít ghế hơn trong năm đầu tiên.
Giả sử chúng tôi có 10 kịch bản như vậy và với mỗi kịch bản, bạn muốn xác định các đầu vào bên dưới:
- Lợi nhuận trên mỗi chiếc bán ra
- Phân tích số lượng bán ra hàng năm trong 5 năm
Điều này có nghĩa, chúng tôi có tổng cộng 12 dữ liệu đầu vào cho mỗi kịch bản (6 cho bàn Boca Top và 6 cho Relaxer)
5. Thiết lập kịch bản cho bảng dữ liệu trong bảng tính
Bây giờ chúng ta có các kịch bản để xác định một số dữ liệu đầu vào, hãy nhập số kịch bản vào phần dữ liệu đầu vào, như được hiển thị bên dưới.
- Sử dụng công thức cho ô C26=MATCH(C25,$P$6:$P$16,0)
- Sau đó tính toán cho các giá trị đầu vào trong vùng màu cam bằng cách áp dụng các công thức sau:
- Lợi nhuận mỗi chiếc (Relaxer)=INDEX($Q$6:$Q$16,$C$26), Q6:Q16 chứa lợi nhuận mỗi chiếc cho từng kịch bản.
- Số lượng hàng năm (Relaxer)=INDEX($R$6:$V$16, $C$26,ROWS($B$13:$B13))
6. Tính tổng lợi nhuận cho mỗi kịch bản
Sử dụng bảng dữ liệu, chúng ta có thể nhanh chóng tính toán tổng lợi nhuận hàng tháng cho mỗi kịch bản 10 + 1.
Thiết lập một bảng trống như dưới đây.
Đảm bảo cột đầu tiên đề cập đến tổng lợi nhuận hàng tháng được tính trong cột N(N6:N65) trong mô hình ban đầu của chúng tôi. Khi bảng trống được thiết lập, sử dụng các bước bên dưới để tính lợi nhuận theo từng kịch bản.
- Chọn toàn bộ bảng bao gồm cột đầu tiên (tham chiếu một) và các tiêu đề.
- Chuyển đến Data > What if analysis > Data tables
- Chọn ô Row input cell và trỏ đến tên kịch bản trong vùng nhập (chẳng hạn ô C25 trong mô hình của chúng tôi).
- Nhấp vào OK.
Excel tính toán lợi nhuận cho mỗi trong số 11 kịch bản cho tất cả 60 tháng (tổng cộng 660 giá trị được tính)
7. Làm thế nào để số lượng lớn các con số tính được trở nên có ý nghĩa?
Mô hình được xây dựng dựa trên từng kịch bản là tốt, tuy nhiên nó cũng tạo ra một thách thức mới. Làm thế nào để khiến các dữ liệu này trở nên có ý nghĩa? Đơn giản, đó là tạo một biểu đồ. Có nhiều cách để minh họa dữ liệu đã tính được này. Dưới đây là một ví dụ:
Chúng tôi đã minh họa chỉ cho 5 kịch bản đầu tiên (Original + 4 bản nữa). Bạn có thể thay đổi điều này tùy thuộc vào những gì mỗi kịch bản đại diện.
Mô hình 2 – Điều gì sẽ xảy ra nếu số lượng bán ra không giống nhau mỗi tháng?
Bây giờ chúng ta đều biết rằng không có doanh nghiệp nào bán có cùng một số lượng hàng bán ra mỗi tháng. Bạn sẽ có một vài tháng cao và vài tháng thấp. Vậy làm thế nào để thêm các biến thể hàng tháng cho mô hình?
Hãy nói rằng bạn có % chia tách hàng tháng cho Relaxer và Boca Top được xác định trong phạm vi như được hiển thị bên dưới:
Chúng tôi có thể nhập thông tin mới này vào mô hình của mình bằng cách thay đổi công thức 1 (Số lượng bán ra mỗi tháng). Mọi thứ khác sẽ hoạt động tốt khi công thức 1 được cố định.
Đây là công thức 1 mới (số lượng bán ra mỗi tháng). Tìm hiểu xem nó hoạt động thế nào là bài tập về nhà của bạn.
=INDEX(C$19:C$23,INT(($G6-1)/12)+1)* INDEX(C$30:C$41,MOD($ G6-1,12)+1)
Chú ý:
- C$19: C$23 chứa số lượng hàng năm của Relaxer
- G6 chứa số tháng
- C$30:C$41 đã chia % hàng tháng cho Relaxer
Khi bạn thay đổi công thức 1, bạn có thể thấy nó ảnh hưởng đến dòng tiền (doanh thu & lợi nhuận mỗi tháng) trong 5 năm.
Mô hình 3 – Điều gì sẽ xảy ra nếu chúng ta không biết có thể bán được bao nhiêu mỗi năm?
Việc đặt mua một số lượng hàng trước khi bạn biết chính xác bạn sẽ bán được bao nhiêu mỗi năm gần như là điều không thể. Nhưng toàn bộ mô hình của chúng tôi phụ thuộc vào đầu vào này. Điều gì sẽ xảy ra nếu chúng ta không biết số lượng bán ra hàng năm?
Có thể chúng ta có thể giả định số lượng tháng đầu tiên & các biến thể hàng tháng (như được định nghĩa trong Mô hình 2 ở trên) và tìm ra số lượng hàng bán hàng năm. Vì số lượng tháng đầu tiên là một biến số, chúng ta có thể thay đổi nó để xem loại dòng tiền nào sẽ được tạo ra. Nó sẽ trông như sau:
1. Thiết lập mô hình dự báo dựa trên tháng bắt đầu
Giả sử chúng ta biết khối lượng tháng đầu tiên cho Relaxer & Boca Top – Ô C43 & D43 tương ứng.
Chúng ta có thể tính toán dự báo theo một số cách:
2. Tính toán số lượng bán ra hàng năm
Bằng cách nhân Q21 với tỷ lệ phần trăm tháng đầu tiên (như được xác định trong mô hình 2). Chúng ta có thể tính toán số lượng bán ra theo các tháng liên tiếp bằng cách tăng/giảm khối lượng tháng đầu tiên theo % thay đổi hàng tháng (điều này đòi hỏi dữ liệu đầu vào mới).
3. Mô phỏng số lượng bán hàng từng tháng
Bằng cách thay đổi ngẫu nhiên giá trị của tháng đầu tiên trong khi theo một số mẫu phân chia hàng tháng. Bước 2&3 yêu cầu dữ liệu đầu vào hoặc bảng dữ liệu mới được thiết lập. Cách thực hiện điều này đã được hướng dẫn chi tiết ở các phần trên, nên sẽ không có gì khó khăn đối với bạn.
Chúng tôi tính toán số lượng bán hàng mỗi năm bằng cách sử dụng các công thức đơn giản như thế này:
- Năm 1 (ô C19) = MIN(C43 /C28, C$6) (C43 có số lượng bán hàng cho tháng đầu tiên và C28 có % đầu tiên %)
- Năm 2: = MIN(C$19, C$6-SUM(C$19:C19)
Lưu ý: C6 là tổng số ghế Relaxer có trong kho. Chúng ta chỉ có thể bán nếu trong kho còn hàng. Nếu số lượng bán ra của tháng đầu tiên cao, thì chúng ta có thể sẽ bán hết hàng trong kho nhanh chóng.
Khi số lượng hàng bán ra này được tính toán, chúng ta chỉ cần trực quan hóa kết quả (cột lợi nhuận hàng tháng L & M) trong biểu đồ đường.
Nếu bạn liên kết đầu vào C43 & D43 với hai thanh cuộn riêng biệt, bạn có thể tương tác với chúng trong biểu đồ và nhanh chóng phân tích kết quả.
good job