MS Excel'de hareketli ortalama ve üstel yumuşatma. Excel'de menkul kıymetler piyasasındaki hisse fiyatını tahmin etme

Hareketli ortalamanın hesaplanması, her şeyden önce, zaman aralıklarında ölçüm dalgalanmalarını yumuşatmaya dayalı dinamik bir serinin geliştirilmesindeki eğilimlerin tanımlanmasını ve analizini basitleştirmeyi mümkün kılan bir yöntemdir. Bu dalgalanmalar, genellikle bireysel hesaplama ve ölçüm tekniklerinin yan etkisi olan veya farklı zaman koşullarının sonucu olan rastgele hatalardan kaynaklanabilir.

Hareketli Ortalama aracına, Araçlar menüsündeki Veri Analizi komut iletişim kutusundan erişilebilir.

Hareketli ortalama aracını kullanarak Tablo 1.1'deki (Tablo 3.1) ekonomik performansı tahmin ediyorum.

Masa3 .1 ― İncelenen dinamik serilerin göstergelerinin davranışındaki eğilimin hareketli ortalama yöntemi kullanılarak değerlendirilmesi

Not - Kaynak: .

Tablodaki verilere dayanarak hareketli bir ortalama grafiği oluşturuyorum.

Şekil 3.1 - Hareketli Ortalama

Not - Kaynak: .

Zincir büyüme oranlarının ve hareketli ortalamanın genel dinamikleri, hareketli ortalama göstergesinin artma, sonra azalma ve sonra tekrar artma eğiliminde olduğu görülebildiği grafikte gösterilmektedir, yani. her ay ticaret hacmi sürekli değişiyor.

Hareketli ortalama hesaplaması, kısa vadeli ekonomik performansı tahmin etmenin hızlı ve kolay bir yoludur. Bazı durumlarda, uzun vadeli gözlemlere dayalı diğer yöntemlerden bile daha etkili görünüyor, çünkü gerekirse, incelenen göstergenin dinamik aralığını yalnızca en son trendi yansıtacak kadar çok üyeye indirgemeye izin veriyor. gelişmesinde. Böylece tahmin önceki aykırı değerler, kırılmalar ve diğer şeyler nedeniyle bozulmayacak ve tahmin edilen göstergenin kısa vadedeki olası değerini çok daha doğru bir şekilde yansıtacaktır.

1. Excel kullanarak doğrusal tahminler oluşturma

Dışsal değişkenlerin işlevsel bağımlılıklarının türüne göre, trend modelleri doğrusal ve doğrusal olmayabilir. Ekonomik süreçlerin karmaşıklığı ve ekonomik sistemlerin açıklık özelliği, çoğu durumda ekonomik göstergelerin gelişiminin doğrusal olmayan doğasını belirler. Bununla birlikte, doğrusal modellerin oluşturulması hem teknik hem de matematiksel açıdan çok daha az zaman alan bir prosedürdür. Bu nedenle, pratikte, doğrusal olmayan süreçlerin kısmi dönüşümüne genellikle izin verilir (verilerin ön grafiksel analizinin buna izin vermesi koşuluyla) ve incelenen göstergenin davranışını modellemek, doğrusal bir denklemi derlemeye ve değerlendirmeye indirgenir. dinamikler.

1. Trend Modeli Oluşturmak İçin Doğrusal Kullanma

DOT çalışma sayfası işlevi, gözlemlerin sonuçları ile sabitlenme zamanları arasındaki doğrusal ilişkinin doğasını belirlemeye ve ona orijinal verilere en iyi yaklaşan matematiksel bir açıklama vermeye yardımcı olur. Bir model oluşturmak için, y=mx+b biçiminde bir denklem kullanır; burada y, incelenen göstergedir; x=t zaman trendidir; b, m, y-kesişini ve eğimini karakterize eden denklem parametreleridir. sırasıyla trend çizgisi. DOT modelinin parametrelerinin hesaplanması en küçük kareler yöntemine dayanmaktadır.

DOT işlevini, Standart araç çubuğunda bulunan İşlev Sihirbazı iletişim kutusundan (İstatistik kategorisi) çağırabilirsiniz.

Tablo 3.2 - DOT işlevini kullanarak bir doğrusal trend modelinin hesaplanması ve değerlendirilmesi

Menüden seçin Hizmet paragraf Veri analizi, ana elemanı alan olan aynı ada sahip bir pencere görünecektir. Analiz araçları. Bu alan, Microsoft Excel'de uygulanan istatistiksel veri işleme yöntemlerinin bir listesini sağlar. Listelenen yöntemlerin her biri, fare işaretçisi ile ilgili yöntemi seçmeniz ve Tamam düğmesini tıklamanız gereken etkinleştirmek için ayrı bir çalışma modu olarak uygulanır. Çağrılan modun iletişim kutusu göründükten sonra çalışmaya başlayabilirsiniz.

Çalışma modu " hareketli ortalama» basit hareketli ortalama yöntemine dayalı ampirik bir dinamik serinin seviyelerini yumuşatmak için kullanılır.

Çalışma modu " Üstel Düzeltme» basit üstel yumuşatma yöntemine dayalı ampirik dinamik serilerin seviyelerini yumuşatmak için kullanılır.

Bu modların iletişim kutularında (Şekil 2 ve 3), aşağıdaki parametreler ayarlanır:

2. Onay kutusu Etiketler– giriş aralığındaki ilk satır (sütun) başlıklar içeriyorsa aktif durum ayarlanır. Başlık yoksa, onay kutusu devre dışı bırakılmalıdır. Bu durumda, çıkış aralığı verileri için standart adlar otomatik olarak oluşturulacaktır.

3. Aralık(yalnızca Hareketli Ortalama iletişim kutusunda) – yumuşatma penceresinin boyutunu girin R. Varsayılan p=3.

Şekil 2 - Hareketli Ortalama İletişim Kutusu

4. çürüme faktörü(yalnızca Üstel düzeltme iletişim kutusunda) – üstel düzeltme katsayısının değerini girin P. Varsayılan, p=0.3.

5. Çıktı Aralığı / Yeni Çalışma Sayfası / Yeni Çalışma Kitabı– Çıkış aralığı konumunda, çıkış aralığının sol üst hücresine bir referans girmenin gerekli olduğu alan etkinleştirilir. Çıkış aralığının boyutu otomatik olarak belirlenecek ve çıkış aralığının kaynak verilerle çakışması durumunda ekranda bir mesaj görüntülenecektir. Yeni çalışma sayfası konumunda, hücreden başlayarak yeni bir sayfa açılır. A1 analiz sonuçları girilir. Karşılık gelen anahtar konumunun karşısındaki alanda bir ad belirlemeniz gerekiyorsa. Yeni çalışma kitabı konumunda, ilk sayfasında hücreden başlayarak yeni bir çalışma kitabı açılır. A1 analiz sonuçları girilir.

6. Grafik çıktısı– dinamik serinin gerçek ve teorik seviyelerinin grafiklerinin çalışma sayfasında otomatik olarak oluşturulması için aktif duruma ayarlanır.

7. standart hatalar– çıkış aralığında standart hatalar içeren bir sütunun dahil edilmesi gerekiyorsa aktif duruma ayarlanır.

Şekil 3 - Üstel Düzeltme İletişim Kutusu

örnek 1

Kentin tüketici işbirliği mağazaları tarafından tarım ürünlerinin satışına (milyon ruble) ilişkin veriler, bir Microsoft Excel çalışma sayfasında oluşturulan bir tabloda verilmektedir (Şekil 4). Belirtilen dönemde (2009 - 2012), bu ekonomik sürecin gelişimindeki ana eğilimin belirlenmesi gerekmektedir.

Şekil 4 - Başlangıç ​​verileri

Sorunu çözmek için çalışma modunu kullanıyoruz " hareketli ortalama". Aynı adlı iletişim kutusunda ayarlanan parametrelerin değerleri Şekil 5'te, bu modda hesaplanan göstergeler Şekil 6'da ve çizilen grafikler Şekil 7'de gösterilmektedir.

Şekil 5 - İletişim kutusunun doldurulması

Şekil 6 - Analiz sonuçları

Şekil 7 - Hareketli Ortalama

Sütun D (Şekil 5), düzleştirilmiş seviyelerin değerlerini hesaplar. Örneğin, birinci düzleştirilmiş düzeyin değeri D5 hücresinde =ORTALAMA(C2:C5) formülü kullanılarak hesaplanır, ikinci düzleştirilmiş düzeyin değeri D6 hücresinde =ORTALAMA(C5:C8) formülü kullanılarak hesaplanır, vb. .

Sütun E, =KAREKÖK(SUMQDIFF (gerçek değer bloğu; tahmini değer bloğu) / yumuşatma pencere boyutu) formülünü kullanarak standart hataları hesaplar.

Örneğin, E10 hücresindeki değer =KAREKÖK(SUMQDIFF(C7:C10,O7:B10)/4) formülü kullanılarak hesaplanır.

Ancak, yukarıda belirtildiği gibi, yumuşatma penceresinin boyutu bir çift sayıysa ( p=2m), o zaman hesaplanan ortalama değer herhangi bir t zamanı ile ilişkilendirilemez, bu nedenle merkezleme prosedürü uygulanmalıdır.

Söz konusu örnek için p=4, bu nedenle merkezleme prosedürü gereklidir. Böylece ilk düzleştirilmiş düzey (265,25) II. ve III. çeyrekler arasında kaydedilmiş olur. 2009 vb. Merkezleme prosedürünü uygulayarak (bunun için ORTALAMA işlevini kullanıyoruz), merkezleme ile düzleştirilmiş seviyeler elde ederiz. III kV için. 2009, birinci ve ikinci düzleştirilmiş seviyeler arasındaki medyan değer belirlenir: (265,25 + 283,25)/2 = 274,25; IV çeyrek için. 2009, ikinci ve üçüncü düzleştirilmiş düzeyler ortalanır: (283,25 + 292,00)/2 = 287,6, vb. Hesaplanan değerler Tablo 1'de sunulmaktadır. Düzeltilmiş hareketli ortalama grafiği Şekil 8'de gösterilmektedir.

Tablo 1 - Düzleştirilmiş ürün satış seviyelerinin dinamikleri

Yıl	Çeyrek	Satış hacmi, milyon ruble	Merkezleme ile pürüzsüz seviyeler
		274,25
		287,63
			297,00
		307,50
		334,63
		374,13
			402,88
		421,00
		429,00
		430,75
			435,38
		446,63

Şekil 8 - Düzeltilmiş hareketli ortalama grafiği

Örnek 2

Ele alınan problem, basit üstel düzeltme yöntemi kullanılarak da çözülebilir. Bunu yapmak için "Üstel Düzleştirme" çalışma modunu kullanmalısınız. Aynı adlı iletişim kutusunda ayarlanan parametrelerin değerleri Şekil 9'da, bu modda hesaplanan göstergeler Şekil 10'da ve çizilen grafikler Şekil 11'de gösterilmektedir.

Şekil 9 - "Üstel Yumuşatma" iletişim kutusunun doldurulması

Şekil 10 - Analiz sonuçları

Şekil 11 - Üstel Düzeltme

Sütun D'de (Şekil 10), düzleştirilmiş seviyelerin değerleri yineleme ilişkilerine göre hesaplanır.

Sütun E, =KAREKÖK(SUMQDIFF (gerçek değerler bloğu; tahmin edilen değerler bloğu) / 3) formülünü kullanarak standart hataları hesaplar. Kolayca görebileceğiniz gibi (Şekil 8 ve 11'i karşılaştırın), basit üstel yumuşatma yöntemini kullanırken, basit hareketli ortalama yönteminin aksine, küçük dalgalar korunur.

işin amacı : Analiz çalışma sayfası özelliği ile frekans analizi problemlerini çözme becerisi kazanın MS Excel.

Kısa teori

Ekonomik göstergeleri analiz ederken, genellikle göstergelerin belirli değer aralıklarında ne sıklıkta ortaya çıktığı sorusu ortaya çıkar.

İşlev FREKANS analizi çalışma sayfası Excel istatistiksel fonksiyonlar kategorisine aittir ve frekans dağılımını dikey bir dizi olarak döndürür. Belirli bir değer kümesi ve belirli bir cep (aralık) kümesi için, frekans dağılımı her aralığa kaç değer düştüğünü sayar.

Veri dizisi, tek boyutlu veya iki boyutlu bir dizi olabilir (örneğin, A4:D15).

Sözdizimi: FREQUENCY(veri_dizisi; cep_dizisi)

Frekans analizi için komutu kullanabilirsiniz.Hizmet/Veri Analizi.Veri analizi eklentilerden biridir mükemmel . Bu komut menüde yoksa, komutu çalıştırmalısınız.Hizmet / Eklentilerve pencerede ilgili kutuyu işaretleyin eklentiler

1. Egzersiz

işlevi kullanma Sıklık bir dizi sipariş miktarı seçmek için () verilen değer aralıklarında kaç değerin düştüğünü sayın aralığına girin. Örneğin, 0 - 1000, 1001 - 1500, 1501 - 2000, 2001 - 2500, 2500'ün üzerinde.

prosedür:

1. çalışma sayfasında Excel Eylül ayı için şirketin 20 şubesindeki sipariş hacmine ilişkin verileri, bir kısmı şekilde gösterilen bir tablo şeklinde girin.

	Şube numarası	Eylül
		1230		1000
				1500
		….		2000
				2500

1. Serbest hücre aralığında (sütun), aralıkların üst sınırlarını girin (Örneğin, D 2=1000, D 3=1500, D 4=2000, D 5=2500).
2. Boşluk sütununa bitişik sütunun hücre bloğunu seçin ( E2:E 21). Aralığın alt sınırını aşan değerlerin sayısını saymak için, aralık aralığından bir hücre fazla olacak şekilde bir aralık tahsis edilir.
3. E aralığı 2'ye: E 6 formülü girin ( =FREKANS( E2: E15; J2:J6)).

Bunun için işlev sihirbazını kullanın (Ekle/İşlev). "İstatistik" kategorisinde, listeden "Frekans" işlevini seçin. FREKANS işlevinin iletişim kutusunda, örnek dizi ve aralık dizisi alanlarını doldurun (Şekil 1).Diyalog penceresinden çıkmadantuş kombinasyonuna basın< Ctrl / Shift / Enter > dizi öğelerini hesaplamak için.

Şekil 1 Bir işlev iletişim kutusunu doldurma örneği Sıklık.

1. Sonuçlara göre bir grafik oluşturun.
2. Dosya 'yı kaydet.

Görev 2

Çeşitli yaş kategorilerindeki insanların boyları hakkında istatistikler içeren bir çalışma sayfasında iki boyutlu bir dizi oluşturun. FREQUENCY işlevini ve Veri Analizini (menü öğesi) kullanarak sonuçların bir frekans analizini gerçekleştirin.Veri Analizi/ Histogram).

Alandaki iletişim kutusundaki veri analizi aracını kullanırkengiriş aralığıalanda histogramın oluşturulduğu ilk aralığı girinCep aralığı- aralıkların üst sınırlarının değerlerini içeren bir aralık. Histogram, yeni veya geçerli çalışma sayfasında oluşturulur.

Bölüm 2

MS Excel'de tahmin problemlerini çözme. hareketli ortalama yöntemi

işin amacı : İstatistiksel yazılım paketi MS Excel kullanarak bir işletmenin ekonomik faaliyetini tahmin etme becerisini kazanmak.

Kısa teori

Tahmin, bir sistemin gelecekteki olası durumlarının bilimsel olarak doğrulanmış bir açıklamasıdır. Herhangi bir kuruluş, kısa vadeli ve uzun vadeli planlar geliştirirken, üretim hacmi, satışlar, üretim maliyetleri vb. Gibi ekonomik faaliyetin en önemli göstergelerinin değerini tahmin etmek zorunda kalır. Şu anda, tahmin problemlerini çözmek için, yazılımı istatistiksel yazılım paketleri içeren modern bilgi teknolojileri kullanılmaktadır.

Ortamdaki tahmin problemlerini çözmek için MS Excel, Analiz Paketi tarafından kullanılır , analiz araçlarını içerir. Veri analizi için bir araç seçerek ve gerekli parametreleri ayarlayarak, karmaşık istatistiksel sorunları grafik yorumlamayla birlikte hızla çözebilirsiniz.

Ekonomik göstergelerin zaman serilerinin ön analizi, trend tanımını ihlal eden seri seviyelerinin anormal değerlerinin tanımlanmasını içerir. Göstergelerin anormal değerlerini ortadan kaldırmak için bir zaman serisi yumuşatma prosedürü uygulanır. Bu durumda serinin trendini belirlemek için gerçek değerler hesaplananlarla değiştirilir.

Bir tahmin yöntemi seçilirken, zaman serisinin rastgele değişkenindeki değişimin doğası dikkate alınır. Ortalama değerlerin değişimi önemsiz ise ve zaman serisinin tüm gözlemleri tahmin için aynı öneme sahipse hareketli ortalama yöntemi kullanılır. Hareketli ortalamalar, zaman serilerindeki rastgele ve periyodik dalgalanmaları yumuşatmanıza (filtrelemenize) olanak tanır. Basit hareketli ortalama yumuşatma, en yaygın yumuşatma prosedürüdür.

Analiz aracında Excel hareketli ortalamatahmin edilen değerin hesaplanmasında yer alan değerlerin sayısı parametre tarafından verilir. Aralık . Yumuşatma aralığının değeri ne kadar büyük seçilirse, serinin değerlerindeki küçük dalgalanmaları o kadar yumuşatmak gerekir. Yöntembasit hareketli ortalamadoğrusal gelişim trendi olan zaman serilerinde iyi sonuçlar verir.

Tahmin için gözlemlerin en son sonuçları en önemliyse, üstel düzeltme yöntemi kullanılır. Üstel yumuşatma yönteminde, her bir değer, veriler "eskidikçe" azalan değişken bir ağırlıkla tahmin edilen değerlerin oluşumuna katılır. Analiz aracında ms excel" Üstel Düzeltme»ağırlık faktörü veya yumuşatma parametresi, parametre tarafından belirlenirçürüme faktörü. Genellikle ekonomik problemlerdeki zaman serileri için yumuşatma parametresinin değeri 0,1 ila 0,3 aralığında ayarlanır. Prosedürde ilk hesaplanan değerÜstel Düzeltme MS Excel Analiz Paketi serinin ilk üyesinin seviyesine eşit alınır. Yöntem, serinin ilk değerleri için başlangıç ​​ve hesaplanan veriler arasında iyi bir uyum sağlar. Son hesaplanan değerler, karşılık gelen başlangıç ​​değerlerinden önemli ölçüde farklıysa, yumuşatma parametresinin değerinin değiştirilmesi önerilir. Serinin hesaplanan değerleri ile birlikte Analysis paketinin görüntülemenize izin verdiği standart hatalar ve grafikten yola çıkarak tutarsızlık değerlerini tahmin edebilirsiniz.

İletişim hizmetlerinin sağlanmasıyla uğraşan bir işletmenin performans göstergelerini tahmin etme olasılıklarını ele alalım.

Laboratuvar çalışması için ödev (bölüm 2)

1. Egzersiz : İşletmenin ürün (hizmet) hacminin değerinin tahmini değerini hareketli ortalama yöntemini kullanarak hesaplayın.

Görev sırası:

İşletme tarafından son 10 yılda sağlanan milyon ruble cinsinden hizmetlerin hacmine ilişkin verileri içeren çalışma sayfasında bir sütun oluşturalım. Hareketli bir ortalama kullanarak göstergenin eğilimini belirleyelim. Hareketli ortalamanın üç yıllık bir dönemini seçelim, çünkü daha kısa bir dönemde hareketli ortalama eğilimi yansıtmayabilir, ancak daha uzun bir dönemde eğilimi yumuşatır.

Hesaplamalar için, formülün doğrudan girilmesi yöntemini kullanıyoruz. Örneğimiz için gerçekleştirilen hizmet hacminin üç yıllık hareketli ortalamasını almak için hücreye girin B Hesaplamak için 5 formül =ORTALAMA( A2:Bir 4). Formülü aralığa kopyala B6: B11.

Şekil 1 Basit hareketli ortalama hesaplaması

Sonuçları, ilk verilerdeki ve hareketli ortalamadaki değişikliklerin dinamiklerini gösteren bir grafikle gösterelim.

Şekil 2 Basit hareketli ortalama hizmet hacmi trend tablosu

Başka bir çözüm, hareketli tamsayıyı belirlemek için kullanmaktır. Analiz paketi . Analiz Paketi bir eklentidir Excel (menü öğesini seçinHizmet / Eklentilerve kutuyu işaretleyin Analiz paketi).

prosedür

1. Çalıştır komutuHizmet/Veri Analizive analiz araçları listesinden seçim yapınhareketli ortalama.
2. İletişim kutusunda, hareketli ortalamayı hesaplamak için parametreleri belirtin:

• Giriş aralığı olarak, hizmet hacmiyle ilgili verileri içeren bir hücre bloğu seçin.
• Aralık Belirtin- 3 (varsayılan 3'tür), çalışma sayfasının herhangi bir hücresinin çıkış aralığı olarak (sonuçların görüntülenmesi gereken çalışma sayfasının hücresine tıklamanız yeterlidir);

mükemmel hareketli ortalamayı hesaplamak için formüle değer girme işini yapacak. Yetersiz veri nedeniyle, ilk gözlem sonuçları için ortalama hesaplanırken, çıkış aralığının ilk hücrelerinde #N/A hata değeri görüntülenecektir. Lütfen serinin ilk elde edilen değerinin üçüncü değil, dördüncü dönem için bir tahmin olduğunu unutmayın. Bu nedenle, çıktı için belirtilen hücre gözlem sütununun başlangıcına karşılık geliyorsa, hesaplanan değerler sütunu bir hücre aşağı taşınmalıdır. Bu eylem, tahminleri tam olarak hesaplandıkları dönemlere ekler.

Kullanılan hesaplama formüllerini ve elde edilen sonuçları analiz edin.

Beş yıllık basit hareketli ortalamaları da aynı şekilde hesaplayın. İki hesaplama seçeneği için yumuşatma sonuçlarını karşılaştırın.

Görev 2: Üstel düzeltme yöntemini kullanarak işletmenin ürün (hizmet) hacminin değerinin tahmin edilen değerini hesaplayın.

prosedür:

1. MS Excel sayfasında şirketin son 10 yıldaki çalışan sayısına ilişkin verileri içeren bir liste oluşturun. Verileri gelişigüzel, ancak bir trend izlenebilecek şekilde girin.
2. 0,1 ve ardından 0,3 yumuşatma parametreleriyle üstel ortalamayı kullanarak bir zaman serisi yumuşatma gerçekleştirin. Hesaplamaların sonuçlarına göre bir grafik oluşturun ve ortaya çıkan zaman serilerinden hangisinin daha düzgün olduğunu belirleyin.

takımı kullanHizmet/Veri Analizive analiz araçları listesinden seçim yapınÜstel yumuşatma.Hareketli ortalamayı hesaplamak için parametreleri belirtin:

• Giriş aralığı olarak, nüfus verilerini içeren bir hücre bloğu seçin.
• Belirtin çürüme faktörü. Çalışma sayfasının herhangi bir hücresinde çıktı aralığı olarak.
• Çizim çıktısını ve standart hataları belirtin.

1. Ortaya çıkan grafiklere trend çizgileri ekleyin. Bunu yapmak için grafik çizgisini seçin (sadece grafik çizgisine sağ tıklayın) ve içerik menüsünden öğeyi seçinTrend çizgisi ekle. İletişim kutusunda, verileriniz için en uygun trend tipini seçin (örneğin, doğrusal filtreleme) ve grafikte uydurma eğrisi denklemini görüntülemek için kutuyu işaretleyin.
2. Sonuçları kontrol edin ve kaydedin.

Bölüm 3

Çalışma Sayfası ve Dolgu İşareti İşlevleriyle Tahmin Sorunlarını Çözme

Kısa teori

Ekonomik tahminlerde çeşitli büyüme modelleri kullanılmaktadır. Büyüme eğrisi, belirli bir zaman serisine yaklaşan belirli bir fonksiyondur. Büyüme eğrileri kullanılarak tahmin geliştirilirken, şekli zaman serisinin dinamiklerine uygun eğriler seçilir, parametreleri değerlendirilir, seçilen eğrilerin tahmin sürecine uygunluğu kontrol edilir ve bir nokta veya aralık tahmini yapılır. hesaplandı.

Birkaç eğri uydurma yöntemi vardır. En basitlerinden biri görsel yöntemdir. Gelişim trendi (eğilim) grafikte yeterince görünmüyorsa, yukarıda açıklandığı gibi seri düzleştirilir ve ardından yeni seriye karşılık gelen eğri seçilir. Bu durumda bilgisayar sistemlerinin modern yazılım araçları da kullanılmaktadır. İÇİNDE Excel belirli bir süre için tahmin değerlerini hesaplamanıza izin veren yerleşik özel işlevler.

Excel, doğrusal ekstrapolasyon gerçekleştirir, örn. verilen bir dizi noktadan geçen en uygun düz çizgiyi hesaplar. Görev, grafik üzerinde bir dizi nokta çizmek ve ardından fonksiyonun gelişiminin en az hatayla izlenebileceği bir çizgi seçmektir. Bu çizgiye TREND çizgisi denir. Kullanıcı, trend analizi ve kısa vadeli tahmin için hesaplama sonucunu kullanabilir.

Excel, çeşitli türlerde eğilim çizgilerini otomatik olarak doğrudan grafik üzerinde çizebilir. Hesaplamalar iki şekilde yapılabilir:

• Doldurma tutamacını kullanma
• Çalışma Sayfası İşlevlerini Kullanma

ilk yol

Doğrusal yaklaşım

• Doldurma tutamacını sol fare tuşuyla sürükleyin, böylece tahmin edilen değerleri hesaplamak istediğiniz hücreler de seçilir. Bu şekilde hesaplanan değerler doğrusal bir tahmine karşılık gelir.

Üstel Yaklaşım

• Gözlem sonuçları olan hücreleri seçin.
• Tahmini değerlerini hesaplamak istediğiniz hücrelerin de seçilmesi için doldurma tutamacını farenin sağ tuşuyla sürükleyin.
• Görünen bağlam menüsünde "Üstel Yaklaşım" komutunu seçin.

ikinci yol

MS Excel'de yerleşik istatistiksel çalışma sayfası işlevleri.

AKIM() - doğrusal en küçük kareler yaklaşımına göre değerleri döndürür.

YÜKSEKLİK() - üstel eğilime göre değerler döndürür.

Bu işlevleri kullanmak, regresyon analizini hesaplamanın başka bir yoludur.

Biçim

TREND(ret_y_value, eski_x_değeri, yeni_x_değeri, sabit)

BÜYÜME işlevi, üstel bir eğilime göre değerler döndürür.

Laboratuvar çalışması için ödev (bölüm 3)

1. Egzersiz:

Doldurma tutamacını kullanarak bir yıl ve sonraki üç dönem için (2011'e kadar) doğrusal ve üstel bir tahmin hesaplayın.

Görev 2:

TREND ve BÜYÜME çalışma sayfası işlevlerini kullanarak bir yıl ve sonraki üç dönem için doğrusal ve üstel bir tahmin hesaplayın. Aralık tahminini hesaplamak için, işlev iletişim kutusunun parametrelerini doldurduktan sonra ve ondan çıkmadan Ctrl / Shift / Enter tuş bileşimine basın.

Çalışma sayfasının formül çubuğunda dizi öğelerini hesaplamak için bir formül görünmelidir, örneğin,

( = TREND (B 3: G 3; B 2: G 2; B 2: H 2))

Hangi modelin en doğru olduğunu belirleyin.

Birinci ve ikinci görevler için grafikler ve eğilim çizgileri çizin.

Diğer tüm faaliyetlerde olduğu gibi iş hayatında da kişi bundan sonra ne olacağını bilmek ister. Geleceği %100 doğrulukla tahmin edebilen o şanslı kişinin servetini hayal etmek bile zor. Ancak, ne yazık ki (veya neyse ki), öngörü armağanı son derece nadirdir. AMA ... girişimci, en azından genel anlamda gelecekteki iş durumunu hayal etmeye çalışmakla yükümlüdür.

İlk başta, aynı anda birkaç basit ve kullanışlı teknik hakkında bir gönderide yazmak istedim, ancak gönderi çok büyük olmaya başladı. Ve böylece tahmin konusuna ayrılmış birkaç gönderi olacak. Bu yazıda, Excel özelliklerini kullanan en basit tahmin yöntemlerinden biri olan hareketli ortalama yöntemini açıklayacağız.

Çoğu zaman pazarlama araştırması uygulamasında aşağıdaki değerler tahmin edilir:

• satış hacimleri
• Pazar büyüklüğü ve kapasitesi
• üretim hacimleri
• Hacimleri içe aktar
• Fiyat dinamikleri
• Ve benzeri.

Bu yazıda ele aldığımız tahmin için, aşağıdaki basit algoritmaya bağlı kalmanızı tavsiye ederim:

1. Sorunla ilgili ikincil bilgilerin toplanması(tercihen hem nicel hem de nitel). Örneğin, pazarınızın büyüklüğünü tahmin ediyorsanız, pazarla ilgili istatistiksel bilgiler (üretim hacimleri, ithalat, fiyat dinamikleri, satış hacimleri vb.) ve trendler, sorunlar veya pazar fırsatları toplamanız gerekir. Satış hacmini tahmin ediyorsanız, dönem için satış verilerine ihtiyacınız vardır. Tahmin için ne kadar çok geçmiş veriye bakarsanız o kadar iyidir. Tahmini, tahmin edilen fenomeni etkileyen faktörlerin bir analizi ile desteklemek arzu edilir (SWOT, PEST analizi veya başka herhangi birini kullanabilirsiniz). Bu, geliştirme mantığını anlamanıza izin verecek ve bu şekilde belirli bir trend modelinin akla yatkınlığını kontrol edebileceksiniz.

2. Daha da arzu edilir nicel verileri kontrol et. Bunu yapmak için, aynı göstergelerin değerlerini karşılaştırmanız gerekir, ancak farklı kaynaklardan elde edilir. Her şey birleşirse, verileri Excel'e "sürebilirsiniz". Veriler ayrıca aşağıdaki gereksinimleri de karşılamalıdır:

• Taban çizgisi, en erkenden en sonuncuya kadar gözlemlerin sonuçlarını içerir.
• Tüm temel zaman periyotları aynı uzunluğa sahiptir. Örneğin bir güne ait verileri üç günlük ortalamalarla karıştırmamalısınız.
• Gözlemler, her zaman periyodunun aynı anında sabitlenir. Örneğin, trafik aynı anda ölçülmelidir.
• Veri atlamaya izin verilmez. Tahmin yaparken gözlemlerin bir sonucunu bile atlamak istenmez, bu nedenle gözlemleriniz kısa bir süre için sonuç vermezse, bunları en azından yaklaşık verilerle doldurmaya çalışın.

3. Verileri kontrol ettikten sonra şunları yapabilirsiniz: çeşitli tahmin tekniklerini uygulamak. En basit yöntemle başlamak istiyorum - HAREKETLİ ORTALAMA YÖNTEMİ

HAREKETLİ ORTALAMA YÖNTEMİ

Hareketli ortalama yönteminin kullanımı kolaydır, ancak doğru bir tahmin yapmak için çok basittir. Bu yöntemi kullanırken, herhangi bir dönemin tahmini, zaman serisinin önceki birkaç gözleminin ortalamasını almaktan başka bir şey değildir. Örneğin, üç aylık bir hareketli ortalama seçerseniz, Mayıs ayı tahmini Şubat, Mart ve Nisan aylarının ortalaması olacaktır. Tahmin yöntemi olarak dört aylık hareketli ortalamayı seçerek Mayıs göstergesini Ocak, Şubat, Mart ve Nisan aylarının ortalaması olarak değerlendirebilirsiniz.

Genel olarak, bir hareketli ortalama tahmini, gözlem dönemini hemen takip eden dönem için bir tahmin olarak kabul edilir. Aynı zamanda, böyle bir tahmin, incelenen fenomen sırayla geliştiğinde geçerlidir, yani. belirli eğilimler vardır ve değer eğrisi grafikte deli gibi zıplamaz.

Hareketli ortalamaya kaç tane gözlemin dahil edilmesinin istendiğini belirlemek için, önceki deneyimlerden ve veri seti hakkında mevcut bilgilerden yola çıkılmalıdır. Hareketli ortalamanın en son birkaç gözleme artan tepkisi ile bu ortalamanın büyük değişkenliği arasında bir denge kurmak gerekir.

Peki bunu nasıl yapıyorsunmükemmel

1. Diyelim ki son 29 aylık aylık satış hacminiz var. Ve 30. ayda ne kadar satış olacağını belirlemek istiyorsunuz. Ancak, dürüst olmak gerekirse, tahmin değerleri hesaplanırken 30 geçmiş değerle çalışmak hiç de gerekli değildir, çünkü bu yöntem ortalamayı hesaplamak için yalnızca son birkaç ayı kullanacaktır. Bu nedenle, hesaplama için sadece birkaç ay yeterlidir.

2. Bu tabloyu Excel'in anlayacağı şekle getiriyoruz yani. böylece tüm değerler aynı satırda olsun.

3. Ardından, önceki üç (seçtiğiniz gibi dört, beş?) değerlerin ortalamasını hesaplamak için formülü giriyoruz (bkz.). Hesaplama için son 3 değeri kullanmak en uygunudur, çünkü daha fazla dikkate alınırsa, verilerin aşırı ortalaması alınır; daha az ise, doğru olmaz.

4. Tahmin ayı olan 30'a kadar sonraki tüm değerler için otomatik tamamlama işlevini kullanma. Böylece fonksiyon Haziran 2010 tahminini hesaplayacaktır. Tahmin değerlerine göre Haziran ayında satışlar yaklaşık 408 adet olacaktır. Ancak, örneğimizde olduğu gibi, düşüş eğilimi sabitse, ortalama tahminin hesaplanmasının biraz fazla tahmin edileceğini veya olduğu gibi gerçek değerlerin "gerinde kalacağını" unutmayın.

En basit tahmin yöntemlerinden biri olan hareketli ortalama yöntemini ele aldık. Sonraki gönderilerde, diğer, daha doğru ve karmaşık tekniklere bakacağız. Umarım yazım size yardımcı olur.