Docstoc

SPP1

Document Sample
SPP1 Powered By Docstoc
					                            Содержание

                                                                     стр
1. По данным, характеризующим изменение объемов продаж (таблица      3
1), требуется выполнить следующие задания….


2. По данным, характеризующим изменение объема продаж (таблица 5),   11
требуется выполнить следующие задания, используя программу Excel:


Список литературы                                                    14
       По данным, характеризующим изменение объемов продаж (таблица 1),
требуется выполнить следующие задания:
       1. Построить график изменения объема продаж во времени.
       2. Применить метод трехчленной скользящей средней.
       3. Построить систему нормальных уравнений и рассчитать константы
прогнозирующей функции.
       4. Определить наиболее вероятные объемы продаж в 13,14,15 месяцы.
       5. Рассчитать возможные: ошибки прогноза, определяв доверительные
- интервалы для индивидуальных значений объема продаж в 13,14,15 месяцы.
       6. Построить графики скользящей средней и прогнозирующей функции
вида yt = f(t).
       Исходные данные содержатся в таблице 1.
                                     Таблица 1. Исходные данные по вар. 1.
          Месяцы        1 2 3 4 5 6 7 8 9 10 11 12
          Объем, продаж 12 35 42 47 56 48 53 58 46 57 53 48
          (тыс. руб.)

       Вид прогнозирующей функции – степенная.
       Вид сравниваемой прогнозирующей функции – линейная.




                                                                           3
                                                                РЕШЕНИЕ
      1. График изменения объемов продаж.
      График строится путем нанесения точек, соответствующих исходным данным, на
координатное поле и соединения их прямыми отрезками.

                                                  Динамика объема продаж (тыс. руб.)

                                 70


                                 60
        объем продаж, тыс.руб.




                                 50


                                 40


                                 30


                                 20


                                 10


                                  0
                                      0   2             4           6              8        10      12   14
                                                                          месяцы


                                               Рис. 1. График изменения объема продаж
      2. Применим метод трехчленной скользящей средней
      Метод скользящей средней используется для сглаживания эмпирических кривых.
Метод основан на замене фактических показателей их усредненными величинами. В
зависимости от периода усреднения различают скользящие средние, рассчитанные для
нечетного и четного чисел интервалов времени. Количество членов в скользящей средней
определяется количеством усредняемых точек.
      Кроме того, из-за сглаживания происходит выравнивание контура исходной
кривой, что позволяет визуально определить тенденцию изменения показателя, т.е. по
внешнему виду полученной кривой сделать предварительный прогноз.
      Исходя из вышесказанного, значения трехчленных скользящих средних для трех
членов ряда вычисляются по формуле:
                                              y t =( yt-1+ yt+ yt+1)/3,     t = 2, 3,…, ( n - 1),
      где yt-1, yt, yt+1 - выбираются из построенного графика.
      Расчет трехчленной скользящей средней производим в таблице 2.




                                                                                                         4
                                                                                      Таблица 2.
                                Расчет трехчленной скользящей средней
Месяцы             1       2       3     4      5    6     7      8       9     10     11    12
Объем,            12      35      42    47     56   48    53     58      46     57     53    48
продаж
(тыс. руб.)


Скользящие
средние                  29.7   41.3 48.3 50.3 52.3 53.0 52.3 53.7            52.0    52.7

       3.     Построим      систему    нормальных    уравнений       и   рассчитаем   константы
прогнозирующей функции (для вар.1 – степенной функции).
       Система нормальных уравнений строится на основе минимизации суммы
квадратов отклонения расчетных значений от фактических (метод наименьших квадратов
- МНК).
       Если аппроксимирующая зависимость является нелинейной, то зависимость
предварительно линеаризуется.
Функция                  Исходное уравнение Способ замены переменных            Линеаризованное
                                                                                уравнение
     степенная                   yt = atb           1. Логарифмируем                yt/ = a/+bt/
                                                    lnyt =ln a+bln t
                                                    2. Обозначим yt/ = lnyt
                                                    a/= ln a t/= ln t


y/t= na/ + bt/,

y/t t/ = a/ t/ + bt/2

       Сомножитель n в первом уравнении системы характеризует объем выборочной
совокупности (n = 12).          Определим все суммы, включенные в систему нормальных
уравнений. Результаты вычислений удобно записать в специальную таблицу (таблицу 3).
       Имеем:
                                   12a /  19.9872b  45,2685
                                   19.9872a /  39.5749b  71,8754

       Решаем систему уравнений, например, методом подстановки:
                                             45.2685  12a
                                       b
                                               19.9872


                                                                                              5
                                           45 .2685  12 a /
                     19 ,9872 a  39 .5749
                               /
                                                              71 .8754
                                               19 .9872
      Отсюда имеем:

      a /  2.98925
           45.2685  12 * 2.98925
      b                           0.47018
                 19.9872
      Мы нашли, таким образом, параметры регрессии: а’ =2,98925; b = 0,47018
      Находим a = exp(a’) = 19.871.

      Итак:
                                      Yt = 19.871*t0.47018
                                                                            Таблица 3

                 Объем
       Месяцы, t продаж y    y' = lny    t' = lnt     (t')2         y't'       yрасч
               1          12      2.4849       0.0000        0.0000 0.0000      19.871
               2          35      3.5553       0.6931        0.4805 2.4644     27.5269
               3          42      3.7377       1.0986        1.2069 4.1062     33.3083
               4          47      3.8501       1.3863        1.9218 5.3374     38.1326
               5          56      4.0254       1.6094        2.5903 6.4786     42.3508
               6          48      3.8712       1.7918        3.2104 6.9363     46.1414
               7          53      3.9703       1.9459        3.7866 7.7258     49.6099
               8          58      4.0604       2.0794        4.3241 8.4435     52.8244
               9          46      3.8286       2.1972        4.8278 8.4124     55.8323
             10           57      4.0431       2.3026        5.3019 9.3095     58.6678
             11           53      3.9703       2.3979        5.7499 9.5203     61.3567
             12           48      3.8712       2.4849        6.1748 9.6196     63.9189
Сумма:       78         555     45.2685      19.9872        39.5749 78.3539    549.541

      Зная параметры уравнения тренда, можно определить расчетные значения
переменной Y, для всех месяцев предпрогнозного периода, что и сделано в последней
колонке таблицы 3.
      4. Определим наиболее вероятные объемы продаж в 13, 14 и 15 месяцы.
      После того как мы получили прогнозирующую функцию, можно прогнозировать
развитие процесса в будущем. Для этого надо просто подставить в полученную формулу
                                      Yt = 19.871*t0.47018
      значения t = 13,14,15.
      Имеем:


                                                                                     6
                              Y13 = 19.871*130.47018 = 66,37
                              Y14 = 19.871*140.47018 = 68,72
                              Y15 = 19.871*150.47018 = 70,99
      5. Оценим правильность подбора прогнозирующей функции с помощью
остаточной дисперсии, остаточного среднеквадратического отклонения и индекса
корреляции.
      Рассчитаем правильность подбора прогнозирующей функции (в нашем случае -
степенной функции), сравнив её с другой прогнозной функцией — прямой линией.
Линейная функция задается формулой
                                        ўt = a+bt,
      а система нормальных уравнений для неё -
                           yt= na + bt,
                           
                           ytt = at + bt2
      Для расчета статистических показателей воспользуемся формулами (5)-(8).
      Искомые уравнения тренда:
      - степенная функция: Yt = 19.871*t0.47018
      - линейная функция: Yt = 2,3392*t + 31,045 (см. рис.2.)
      Вычислим значение средней арифметической уср = 555 / 12 = 46,25.
      Рассчитаем     статистические   показатели,    для   чего   промежуточные   данные
вычислений (для суммарных значений) запишем в табличной форме (табл. 4).
                                                                              Таблица 4.
          Месяцы,      Объем             Значения           Значения (y/t-yt)2      (yt-yср)2
             t        продаж y       прогнозирующей
                                         функции
                                   Степенная   Линейн    Степенная    Линейн
                 1            12         19.87     33.38       61.95      457.28    1173.06
                 2            35         27.53     35.72       55.85         0.52    126.56
                 3            42         33.31     38.06       75.55       15.50      18.06
                 4            47         38.13     40.40       78.63       43.54       0.56
                 5            56         42.35     42.74      186.30      175.80      95.06
                 6            48         46.14     45.08        3.45         8.53      3.06
                 7            53         49.61     47.42       11.49       31.14      45.56
                 8            58         52.82     49.76       26.79       67.92     138.06
                 9            46         55.83     52.10       96.67       37.18       0.06
                10            57         58.67     54.44        2.78         6.57    115.56
                11            53         61.36     56.78       69.83       14.26      45.56
                12            48         63.92     59.12      253.41      123.55       3.06
Сумма:          78           555        549.54    555.00      922.71      981.80    1764.25


                                                                                        7
                                                      Динамика объема продаж (тыс. руб.)+линейный тренд
                                            y = 2.3392x + 31.045
                                   70
                                                 R2 = 0.4435

                                   60
          объем продаж, тыс.руб.

                                   50


                                   40


                                   30


                                   20


                                   10


                                    0
                                        0                 2                 4             6            8   10   12   14
                                                                                              месяцы


                                             Рис. 2. Расчет параметров линейной регрессии средствами Excel
      Для степенной функции:

       2 ост 
                                             ( y  y )
                                                 t        t
                                                              2

                                                                   
                                                                       922 ,71
                                                                                76 ,89
                                                     n                   12

                      ост               76,89  8,77

                                    ост                      8,77
      V                                     *100%                 *100%  18.96%
                                    yср                       46,25

      Для линейной функции:

       2 ост 
                                             ( y  y )
                                                 t        t
                                                              2

                                                                   
                                                                       981,80
                                                                               81,82
                                                     n                   12

                      ост               81,82  9,05

                                    ост                      9,05
      V                                     *100%                 *100%  19,56%
                                    yср                       46,25

      Сравнив эти три показателя между собой, мы видим, что для линейной функции
они ниже, чем для степенной. Следовательно, степенная функция в нашем случае лучше
подходит для уравнения прогноза.
      Чтобы вычислить индекс корреляции R, необходимо вычислить общую дисперсию
по формуле:

       2 общ 
                                            (y   t    y ср ) 2
                                                                   
                                                                       1764 ,25
                                                                                 147 ,02
                                                      n                   12
      Рассчитаем значение индекса корреляции R:

                                                                                                                      8
      Для степенной функции:

               ост 2       76 ,89
       R  1          1          0.69
               общ 2
                           147 ,02

      Для линейной функции:

                   ост 2       81,82
       R  1              1          0.67
                   общ 2
                               147 ,02

      Чем больше индекс корреляции, тем сильнее взаимодействие между переменными t
и у. Полученное значение индекса корреляции удовлетворительную тесноту связи между
переменными.
      6. Рассчитать возможные ошибки прогноза, определив доверительные интервалы
для индивидуальных значений объема продаж в 13, 14, 15 месяцы.
      Для определения возможной ошибки прогноза доверительные интервалы для
индивидуальных значений объема продаж рассчитываются по формуле:

                                         ytв(н) =   y t ± ∆t ,

                                                (t1  t1ср ) 2                      1
                                                                                     1
                          ∆t = tТDост
                                            n(
                                               t       2
                                                        1
                                                                 t12ср )
                                                                                    n
                                                    n
      tт — табличное значение t-критерия Стьюдента. Определяется по таблице (см.
Приложение) для параметра k = n-2 и доверительной вероятности 0,95 или 0,99;
      Dост - остаточное среднее квадратическое отклонение:


                                      Dост =
                                                    (y     t    yt ) 2
                                                                            ,
                                                        (n  N )
      где N— количество констант в уравнении прогноза.
      Рассчитаем доверительный интервал для прогнозного значения объема продаж на
момент времени t =13 для степенной регрессии:
                                            Yt = 19.871*t0.47018
      tср = 78/12 = 6.5 – среднее арифметическое t.

      t   2
                650 - сумма квадратов t.
      Табличное значение критерия Стьюдента: tт = 3,17 для k =12-2=10 и Р = 0,99
             922 ,71
      Dост           9,606
               10



                                                                                           9
                                                    (13  6.5) 2    1
      t13  3.17 * 9,606 *                                          1  35,8
                                                  650  12 * 6.5 12
                                                                 2


      Аналогично рассчитываем доверительные интервалы для последующих точек
прогноза:

                              (14  6.5) 2    1
      t14  3.17 * 9,606 *                    1  37,1
                            650  12 * 6.5 12
                                           2



                                                    (15  6.5) 2    1
      t15  3.17 * 9,606 *                                          1  38,4
                                                  650  12 * 6.5 12
                                                                 2


                                                       Y13 = 19.871*130.47018 = 66,37
                                                       Y14 = 19.871*140.47018 = 68,72
                                                       Y15 = 19.871*150.47018 = 70,99


      В окончательном виде 99%-е прогнозы:
                                                        Y13 =66.4±35.8 (тыс. руб.)
                                                       Y14 = 68.7±37.1 (тыс. руб.)
                                                       Y15 = 71.0±38.4 (тыс. руб.)
      7. Построим графики изменения объема продаж во времени, скользящей средней и
прогнозирующей функции.

                                              Прогнозирование объема продаж (тыс. руб.)

                                 70                                                                    y = 19.871t0.4702
                                                                                                         R2 = 0.6832
                                 60
        объем продаж, тыс.руб.




                                 50


                                 40


                                 30


                                 20


                                 10


                                 0
                                      0   2        4           6          8          10           12          14           16
                                                                      месяцы
                                                          Объем, продаж (тыс. руб.)
                                                          Скользящая средняя
                                                          Степенной (Объем, продаж (тыс. руб.))




                                                                                                                                10
                                         Часть 2


      По данным, характеризующим изменение объема продаж (таблица 5), требуется
выполнить следующие задания, используя программу Excel:
      1. Построить графики исходной кривой, трехчленной скользящей средней, выбрать
линию тренда, указать уравнение этой функции.
      2. Используя функции ТЕНДЕНЦИЯ или РОСТ построить прогнозирующую
функцию.
      3. Используя функции программы Excel, посчитать доверительные интервалы для
25-ого месяца.
                                   Таблица 5. Исходные данные для выполнения заданий
Месяцы             1     2     3     4     5      6     7     8     9    10    11    12
Объем,           293   270   486   554   697    362   594   271   245   254   433   529
продаж (тыс.
руб.)

Месяцы            13    14    15    16    17     18    19    20    21    22    23    24
Объем,           694   619   610   748   832    393   720   435   536   850   601   833
продаж (тыс.
руб.)


                                         РЕШЕНИЕ


      1. Построить графики исходной кривой, трехчленной скользящей средней, выбрать
линию тренда, указать уравнение этой функции.
      Для того, чтобы построить графики, используя программу Excel, необходимо войти
в Excel, создать файл Контрольная работа, ввести столбец А -месяцы (1-24), столбец В -
объем продаж, в столбце С посчитать скользящие средние. Затем щелкнуть кнопкой
«Мастер диаграмм», расположенной на стандартной панели инструментов. Используя
ряды данных А, В и С можно построить график. Чтобы построить Линию тренда,
необходимо выделить ряд данных диаграммы, а затем выбрать команду Вставка и Линия
тренда. Для того, чтобы вывести на график уравнение тренда, необходимо в меню Линии
тренда в параметрах отметить пункт показывать уравнение на диаграмме.
      Выполнив указанные действия, получим:




                                                                                     11
                                                      Динамика объемов продаж (тыс. руб.)
                                         y = -0.0058x4 + 0.2932x3 - 4.6646x2 + 39.178x + 321.96
                           900
                                                              R2 = 0.3236
                           800


                           700
 объем продаж, тыс. руб.




                           600

                           500

                           400

                                                                                              Объем, продаж (тыс. руб.)
                           300
                                                                                              Скользащие средние
                           200
                                                                                              Полиномиальный (Объем, продаж
                           100                                                                (тыс. руб.))
                                                                                              Полиномиальный (Объем, продаж
                                                                                              (тыс. руб.))
                             0
                                 0   1   2   3   4   5   6   7   8    9   10   11   12   13    14   15   16   17   18   19   20   21   22   23   24
                                                                               месяцы

                           Рис.4. Динамика объемов продаж + скользящие средние + полиномиальный тренд
                                 2. Используя функция ТЕНДЕНЦИЯ или РОСТ построить прогнозирующую
функцию.
                                 Функция ТЕНДЕНЦИЯ вычисляет прогнозы, основанные на линейной связи между
результатом наблюдения и временем, в которое это наблюдение было зафиксировано.
Если взаимосвязь между объемом продаж у и t носит линейный характер, то линия на
графике будет либо прямой, слегка наклоненной в одну или другую сторону, либо
горизонтальной. В случае, когда линия скользящей средней приближается к прямой,
можно использовать функцию ТЕНДЕНЦИЯ.
                                 Если линия резко изгибается в одном из направлений, то это означает, что
взаимосвязь показателей носит нелинейный характер. Существует большое количество
данных, которые изменяются во времени нелинейным способом. В случае нелинейной
взаимосвязи функция Excel РОСТ поможет получить более точный прогноз.




                                                                                                                                                      12
                                                               Динамика объемов продаж (тыс. руб.)

                           900


                           800


                           700
 объем продаж, тыс. руб.




                           600

                           500

                           400


                           300

                                                                                               Объем, продаж (тыс. руб.)
                           200
                                                                                               Скользащие средние
                                                                                               РОСТ
                           100


                             0
                                 0   1   2   3   4         5    6   7   8   9   10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
                                                                                     месяцы

                                                           Рис.5. Прогноз при помощи функции «РОСТ»


                                 Точечный прогноз на 25 месяц по функции «РОСТ» составляет 737,2 тыс. руб.

                                 3. Используя функции программы Excel, посчитать доверительные интервалы для
25-ого месяца.
                                 Вычислим у, по формуле прогнозирующей функции для t = 25
                                 Y25 = 737,2 тыс. руб.
                                 Для того, чтобы посчитать доверительные интервалы, воспользуемся функцией
ДОВЕРИТ из программы Excel. Формат функции ДОВЕРИТ записывается следующим
образом:
                                 ДОВЕРИТ (альфа; стандартное отклонение; размер), где (1- альфа) - значение
вероятности, с которой значение уt+1, попадет в доверительный интервал, для нашего
примера Р = 0,99 следовательно 1 - альфа =0,99; альфа = 0,01;
                                 - стандартное отклонение - это σобщ, то есть общая дисперсия, учитывающая
отклонения исходных значений у, от средней арифметической.
                                 Вычисляем уср = 535,8;

                                  2 общ 
                                             (y   t    y ср ) 2
                                                                    
                                                                        862707
                                                                                35946 , то есть      общ    189 ,6
                                                       n                  24
                                 В ячейке вводим функцию ДОВЕРИТ (0,01; 95,7; 24). В результате Δt =99,7.



                                                                                                                                  13
                            Список литературы

1. Денискин    В.В.   Основы     экономического    прогнозирования   в   пищевой
   промышленности. М.: Колос, 1993.
2. Карданская Н.Л. Принятие управленческого решения. М.: ЮНИТИ, 1999.
3. Карлберг К. Бизнес-анализ с помощью Excel. Пер. с англ.
4. Кобулов Б.А., Паластина И.П. Прогнозирование принятия управленческих
   решений. Учебно-практическое пособие. – М.,МГТА, 2004.
5. Ремнников В.В. Разработка управленческого решения. М.: ЮНИТИ-ДАНА, 2000.
6. Юкаева В.С. Управленческие решения. М.: Изд. Дом «Дашков и К», 1999.




                                                                              14

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:20
posted:8/20/2012
language:Russian
pages:13