Thêm tốc độ tăng trưởng vào biểu đồ Excel

xin chào các bạn. Chúng ta sẽ học cách thêm dòng CAGR trong thanh hoặc Dòng trong biểu đồ đơn trong Excel. Tôi đã đính kèm sổ làm việc để tải xuống và sử dụng lại

Tốc độ tăng trưởng kép hàng năm (CAGR) là tỷ suất lợi nhuận cần thiết để một khoản đầu tư tăng từ số dư ban đầu lên số dư cuối kỳ, giả sử lợi nhuận được tái đầu tư vào cuối mỗi năm trong vòng đời của khoản đầu tư. (tham khảo. đầu tư)

CAGR = (Giá trị tương lai/Giá trị hiện tại)^(1/n)−1
n- số năm

Giả sử bạn muốn hiển thị các giá trị hiện tại và CAGR trong một biểu đồ trong excel. Hướng dẫn excel nhanh chóng và dễ dàng ngay cả khi bạn chưa quen với biểu đồ Excel
Đến cuối hướng dẫn, chúng ta sẽ có một cái gì đó giống như hình ảnh bên dưới

Dữ liệu

Tôi đã có dữ liệu ở định dạng sau;

Chúng tôi sẽ tính toán CAGR và các giá trị cần thiết để vẽ đường CAGR

CAGR = ((1910/1437)^(1/9))-1
= 0.03

Bây giờ vì chúng tôi muốn vẽ một đường có độ dốc không đổi (lên hoặc xuống dựa trên giá trị CAGR), chúng tôi cần có các giá trị y (biểu đồ CAGR trong hình bên dưới) cho đường CAGR. Tham số là giá trị x cho dòng CAGR

Slope = CAGR/number of data points
= 0.03/10
= 0.003
CAGR Plot = Slope * Parameter

Tạo biểu đồ

Khi dữ liệu ở dạng yêu cầu. Chọn phạm vi dữ liệu và chèn biểu đồ thanh. , bạn sẽ có một cái gì đó giống như biểu đồ bên dưới

Bây giờ, nhấp chuột phải vào khu vực biểu đồ và nhấp vào 'Chọn dữ liệu'. Bạn sẽ có một hộp thoại như hình bên dưới. sau đó
1. Xóa Năm và Thông số khỏi hộp sê-ri bên trái
2. Chỉnh sửa phạm vi nhãn Trục thành phạm vi Năm

Bước này sẽ loại bỏ các cột không cần thiết

Thêm dòng CAGR

Bây giờ, nhấp chuột phải vào bất kỳ thanh nào và nhấp vào 'Chọn loại biểu đồ chuỗi'
Khi bạn đã mở hộp thoại, hãy thay đổi loại biểu đồ của biểu đồ CAGR thành đường thẳng và đặt nó làm trục phụ và thế là xong

Sau một số thay đổi định dạng cơ bản, bạn sẽ có biểu đồ cần thiết. Đó là về việc thêm dòng CAGR trong biểu đồ Excel

Tải sách bài tập tại đây

Tiếp tục truy cập Analytics Tuts để biết thêm hướng dẫn

Cảm ơn vì đã đọc. Nhận xét các đề xuất và truy vấn của bạn

được gắn thẻ với biểu đồ thanh trong excel, CAGR, Biểu đồ trong Excel, Trực quan hóa dữ liệu, Data Viz, Excel, Thủ thuật Excel, Thủ thuật Excel

Chúng ta sẽ xem xét một số phương pháp khác để tính toán mức tăng trưởng, bao gồm công thức được viết thủ công, phương pháp lập biểu đồ và một phương pháp sử dụng Goal Seek

Cuối cùng, chúng ta sẽ xem xét các chuỗi bắt đầu bằng số âm. Về mặt toán học, tăng trưởng theo cấp số nhân được tính từ một chuỗi như vậy không có ý nghĩa gì, vậy chúng ta phải làm gì?

Phương pháp công thức

Điều mà hầu hết chúng ta muốn từ công thức Tăng trưởng là một con số đơn giản đại diện cho
tốc độ tăng trưởng theo thời gian của một dãy số. Tỷ lệ tăng trưởng kép hàng năm (CAGR) là một ví dụ điển hình

Công thức tính CAGR không khó. Để tính toán tăng trưởng từ một thời điểm bắt đầu và một thời điểm kết thúc, điều đó là đủ. Nói cách khác, nếu chúng tôi có giá trị doanh thu trong Năm 1 và số liệu doanh thu cho Năm 10 và chúng tôi không quan tâm đến khoảng thời gian giữa các năm, chúng tôi sẽ thiết lập bảng tính hiển thị bên dưới, với điều kiện công thức là

=((Giá trị cuối/Giá trị bắt đầu)^(1/(Giai đoạn – 1)) -1Năm 1110. 06Năm 10260. 83=((B3/B2)^(1/9))-1 ==>10. 06%

Mặc dù chúng tôi đang theo đuổi một con số duy nhất, nhưng phương trình và kết quả khá trần trụi ở trên có thể khiến bạn băn khoăn không biết mình đã làm đúng hay chưa. Câu trả lời là một chuyện, nhưng đảm bảo lại là chuyện khác

Chúng ta nên thiết lập một bảng tính để sử dụng số liệu tăng trưởng trong cột Dự đoán và hiển thị tất cả số liệu của các năm trong cột Thực tế với sự khác biệt giữa hai số liệu. Bạn có thể tạo của riêng mình từ đồ họa bên dưới hoặc tải xuống tệp cuối cùng Tính toán tăng trưởng. xlsx

Nếu bạn tạo một trang tính, hãy đảm bảo đặt tên cho các phạm vi Số tiền bắt đầu và Mức tăng trưởng

Số tiền ban đầu 100 Tốc độ tăng trưởng 10. 06%PeriodPredictedActualVariance0=Số tiền bắt đầu100=C5-B51=B5*(1+Tăng trưởng)105=C6-B62=B5*(1+Tăng trưởng)110=C7-B73=B5*(1+Tăng trưởng)140=C8-B8

Từ các cột số được tính toán, chúng ta có thể thấy mức tăng trưởng dự đoán của chúng ta khác xa bao nhiêu so với những con số thực tế mà chúng ta đã bắt đầu. Tất nhiên, sẽ dễ dàng hơn để xem bằng biểu đồ

Phương pháp biểu đồ

Hãy tạo một biểu đồ chỉ từ các khoảng thời gian ban đầu và các giá trị thực tế của chúng ta và để yên các giá trị được dự đoán trong giây lát. Hóa ra, chúng ta có thể nhận được giá trị tăng trưởng từ chính quá trình lập biểu đồ

Đặt dữ liệu của bạn theo định dạng bạn nhìn thấy ở trên và tạo biểu đồ phân tán XY – không phải biểu đồ đường chuẩn. Sẽ có sự khác biệt nếu khoảng thời gian của bạn khác với 1, 2, 3 vì biểu đồ đường chuẩn coi mỗi điểm là 1, 2, 3

Biểu đồ phân tán thực sự đọc các giá trị trong cột A và coi chúng không phải là số thứ tự mà là giá trị thực. Nếu bạn bỏ qua các khoảng thời gian vì dữ liệu bị thiếu hoặc nếu các khoảng thời gian của bạn bắt đầu ở một số khác 1, chắc chắn bạn sẽ thấy sự khác biệt lớn khi thêm đường xu hướng

Sau khi bạn đã tạo biểu đồ phân tán XY như trên, nhấp chuột phải vào chuỗi dữ liệu và bạn sẽ thấy menu bên trên. Nhấp vào Thêm đường xu hướng…

Trong hộp thoại Thêm đường xu hướng…, hãy chỉ ra Hàm mũ là loại đường cong để
phù hợp với dữ liệu thực tế của bạn. Bạn phải nhấp vào hộp gần cuối hộp thoại để Hiển thị phương trình trên biểu đồ như hình bên dưới.

Khi biểu đồ của bạn được cập nhật, biểu đồ sẽ có phương trình dạng y=b * e g*x trong đó g là tốc độ tăng trưởng. Ở đây ta thấy hệ số là 0. 0984 hoặc 9. số 8%. Lưu ý rằng công thức CAGR cho chúng ta tốc độ tăng trưởng là 10. 1%. Cả hai phần lớn khác nhau vì CAGR chỉ sử dụng giai đoạn bắt đầu và kết thúc trong các tính toán của nó, trong đó việc điều chỉnh đường cong sử dụng tất cả dữ liệu.

Phương pháp tìm kiếm mục tiêu

Một lần nữa, chúng tôi chắc chắn có thể tạo các cột dự đoán, thực tế và phương sai để xem đường cong của chúng tôi phù hợp với dữ liệu như thế nào. Trên thực tế, chúng tôi có thể tổng hợp các điểm khác biệt và sử dụng Tìm kiếm Mục tiêu để cố gắng tìm hiểu xem liệu có tồn tại tốc độ tăng trưởng tốt hơn hay không

Vấn đề chỉ đơn thuần là tổng hợp sự khác biệt là một số giá trị dự đoán sẽ
lớn hơn và một số nhỏ hơn giá trị thực. Ngay cả khi sự khác biệt là rất
lớn, một số lượng bằng nhau của các giá trị tích cực và tiêu cực có thể khiến chúng ta tin rằng
chúng tôi có một sự phù hợp tốt khi chúng tôi không

Hãy bình phương các phương sai và sau đó tính tổng chúng. Bình phương luôn dương và
một hình vuông sẽ phóng đại những khác biệt lớn và bỏ qua những khác biệt nhỏ hơn. bạn đã
có lẽ đã nghe nói về phương pháp khớp đường cong này thành bình phương nhỏ nhất

Khi bảng tính được thiết lập (bên dưới), chúng ta sẽ chuyển đến tab Dữ liệu trên dải băng,
Nhấp vào Phân tích What-If > Goal Seek và yêu cầu Excel cố gắng lấy tổng của
ô vuông trong E18 thành 0 bằng cách thay đổi Tốc độ tăng trưởng trong C2. Tất nhiên, chúng tôi sẽ không bao giờ
thực sự bằng 0, nhưng Excel sẽ tiếp tục thử các giá trị khác nhau - hàng nghìn
chúng – cho đến khi nó gần bằng 0 nhất có thể

Goal Seek hơi giống với trò chơi Warmer, Colder của trẻ em, trong đó một bên liên tục nói với bên kia nếu họ đang tiến gần hơn đến đối tượng bí mật (ấm hơn. ) hoặc xa hơn (lạnh hơn. ). Excel cứ tiếp tục cố gắng ngày càng ấm hơn cho đến khi nó không cố gắng ấm hơn nữa

Ở đây, gần nhất chúng ta có thể đưa bình phương của mình về 0 là khi tốc độ tăng trưởng là 10. 06%

Bạn có thể thấy rằng các phương pháp số khác nhau cho kết quả hơi khác nhau. Bạn có thể muốn thử cả ba trên dữ liệu của mình để có được cảm nhận về tốc độ tăng trưởng theo thời gian gần đúng nhất

Xu hướng bắt đầu với giá trị âm

Nếu bạn đang đọc phần này, bạn đã biết rằng việc tính toán tăng trưởng theo cấp số nhân từ một chuỗi bắt đầu bằng các số không dương (số 0 hoặc số âm) là không thể. Sử dụng các phương pháp trên, chúng ta có thể thấy rằng tất cả đều thất bại

Bạn có một vài lựa chọn

Thêm một vô hướng vào các giá trị của bạn

Thật không may, sự lựa chọn của vô hướng xác định sự tăng trưởng. Nói một cách rõ ràng, nếu bạn thêm một số vô hướng nhỏ – chỉ là một số đủ lớn để mang các giá trị âm về dương – tốc độ tăng trưởng của bạn sẽ rất lớn

Nếu bạn thêm một đại lượng LỚN - thêm một triệu vào mỗi khoảng thời gian được hiển thị ở trên - tốc độ tăng trưởng của bạn sẽ gần bằng không

Phương pháp này sẽ không mang lại kết quả có ý nghĩa

Sử dụng ước lượng đa thức hoặc tuyến tính

Ước tính đa thức và tuyến tính sẽ có thể tính toán khoảng thời gian thay đổi ước tính theo thời gian, nhưng những điều này thường không phải là điều chúng tôi muốn nói khi nói về tốc độ tăng trưởng. Các phương trình đưa ra sẽ hữu ích trong việc dự đoán các giá trị trong một vài giai đoạn trong tương lai và điều đó có thể đủ tốt cho mục đích của bạn

Tìm đến gốc của các số

Bộ truyện bạn đang phân tích có phải là một bộ duy nhất không? . Tuy nhiên, nếu bạn chia lợi nhuận đó thành hai chuỗi riêng biệt – doanh thu và chi phí – bạn sẽ thấy rằng bạn có thể biểu thị cả hai dưới dạng số dương. Sử dụng các phương pháp trên, phân tích riêng biệt hai (hoặc nhiều) chuỗi và sau đó kết hợp lại chúng