Материал: _индив анализ данных
Таким образом, стоимость пакета акций не должна превышать 64,72 млн. USD. То есть, инвестиционной компании «Аргон-Инвест» нецелесообразно приобретать предлагаемый пакет акций, так как его сумма завышена.
Расчеты прогнозных данных в ms Excel
Использование линии тренда. Для того, чтобы построить линию тренда необходимо сначала построить точечную диаграмму того же самого процесса. Возвратимся к задаче 1 (рис.7) и построим точечную диаграмму (рис. 13).
Рис. 13
Для построения линии тренда необходимо в меню Диаграмма выполнить команду Добавить линию тренда, которая вызовет окно рис. 14.
а) |
б) |
Рис. 14
На вкладке Тип (рис. 14а) выбирается тип аппроксимации данных (в нашем случае – линейная), а на вкладыше Параметры (рис. 14б) – необходимо у
становить флажки Показывать уравнение на диаграмме и Поместить на диаграмму величину достоверности аппроксимации (R2) (коэффициент детерминации R2). Вид построенной линии тренда показан на рис. 15. Из рисунка видно, что уравнение регрессии не отличается от полученного в разделе .
Рис. 15
Если в качестве типа аппроксимации избрать полином второй степени (параболу), то построенная линия тренда будет иметь вид рис. 16.
Рис. 16
Из этого рисунка видно, что полученные в данном случае результаты будут более адекватны, чем полученные ранее.
На вкладке Параметры (рис. 14б) кроме того, можно указать количество единиц прогноза, тем самым спрогнозировать искомую величину (рис. 17).
Рис. 17
Использование функции ПРЕДСКАЗ(). Функция ПРЕДСКАЗ() предназначена для расчета значений зависимой случайной величины по известным статистическим данным на основе линейного приближения методом наименьших квадратов и имеет следующий синтаксис:
Предсказ(X;изв_значY;изв_значX),
где X – элемент независимой переменной для которой вычисляется прогнозное значение; изв_значY – известные значения зависимой переменной; изв_значX - известные значения независимой переменной.
Для той же задачи спрогнозируем цену на сахар.
Пример заполнения параметров функции приведен на рис. 18, а пример расчета на рис. 19.
Рис. 18
а) |
б) |
Рис. 19
Использование функции ТЕНДЕНЦИЯ(). Назначение функции ТЕНДЕНЦИЯ() очень близко к функции ПРЕДСКАЗ(), но ее возможно употреблять для прогноза не только линейно зависимых величин (неявная линейная регрессия). Функция ТЕНДЕНЦИЯ() имеет следующий синтаксис:
Тенденция(изв_значY; изв_значX; нов_значX;константа),
где изв_значY – множество известных значений зависимой переменной; изв_значX – множество известных значений независимой переменной; нов_значX – новые значения независимой переменной; константа – логическая константа, если она принимает значения ИСТИНА, то свободный член уравнения регрессии рассчитывается обычным образом, в противоположном случае он равен 0, то есть график функции линейной регрессии будет проходить через начало координат. Пример использования функции в случае линейной зависимости приведен на рис. 20 и 21.
Рис. 20
|
|
|
|
а) |
б) |
Рис. 21
Внимание! Функция ТЕНДЕНЦИЯ() может быть использована не только для расчета одного прогнозного значения, но и для расчета массива значений, как это приведено на рис. 21. Необходимо помнить, что после заполнения одной ячейки (C10 на рис. 21) , надо выделить массив ячеек (C10:C13), перевести в режим редактирования (клавиша F2), потом заполнить его комбинацией клавиш CTRL-SHIFT-ENTER.
Таким образом, прогнозное значение цены составляет 1856,39 грн/т. Следовательно, фирма «Альфа» может принять решение о закупке, так как прогнозное значение цены выше предлагаемой на фьючерсных торгах.
Рассмотрим пример использования функции ТЕНДЕНЦИЯ(), когда одна величина зависит от нескольких независимых переменных. Вернёмся к примеру 3 (рис. 10).
Бюджета; 2) бюджета и цены.
Бюджет |
Цена за шт. |
Объём продаж |
$71 000 |
$60 |
47800 |
$82 107 |
$62 |
44598 |
$83 100 |
$63 |
45257 |
$90 496 |
$63 |
48068 |
$100 000 |
$66 |
50800 |
$102 100 |
$64 |
63200 |
$132 222 |
$65 |
69675 |
$136 297 |
$65 |
65715 |
$139 114 |
$67 |
75886 |
$165 575 |
$69 |
83360 |
$170 000 |
$70 |
|
$183 100 |
$72 |
|
$194 215 |
$74 |
|
$200 000 |
$74 |
|
Бюджет |
Объём продаж |
$71 000 |
47800 |
$82 107 |
44598 |
$83 100 |
45257 |
$90 496 |
48068 |
$100 000 |
50800 |
$102 100 |
63200 |
$132 222 |
69675 |
$136 297 |
65715 |
$139 114 |
75886 |
$165 575 |
83360 |
Результаты приведены на рис. 22 - 24.
Рис. 22
Рис. 23
Рис. 24
Выше было подчеркнуто, что использования всех описанных методов и функций возможно лишь тогда, когда проверена адекватность принятых аппроксимаций.
Тексты заданий
Вариант 1.
Компания рассматривает возможность увеличения сметы на рекламу конкретной продукции, с помощью чего рассчитывает добиться увеличения объёма продаж.
Имеются данные сметы на рекламу и объёмы продаж в единицах продукции, проданной компанией (табл.1).
Задания
Охарактеризовать совокупность данных объёма продаж.
Оценить взаимосвязь и взаимное влияние объёма продаж и сметы на рекламу.
Построить график зависимости между объёмом продаж и сметой на рекламу. Используя возможности Excel, подобрать наиболее подходящий тип тренда. Сделать выводы.
Установить функциональную зависимость между объёмом продаж и сметой на рекламу. Проанализировать адекватность полученного уравнения. Сравнить результаты пунктов
3 и 4.
Спрогнозировать объём продаж для бюджета $170000.
Спрогнозировать объём продаж для бюджета: $170000; $183100; $194215; $200000.
Кроме увеличения расходов на рекламу продукции, компания исследует последствия изменения цены на единицу этой продукции. Добавить столбец данных цены за шт. (табл.2).
Выполнить задания пунктов 2; 4; 5; 6, учитывая зависимость объёма продаж от бюджета и цены за шт. (табл.2).
Сравнить спрогнозированные результаты для объёма продаж, полученные с учётом
Табл. 2
Вариант 2
Фирма производит оправы для очков. Даны результаты для определенного вида оправ по точкам сбыта, занимающихся распространением оправ фирм (табл.1). Фирма хочет добиться повышения прибыли от продажи данной продукции.
Задания
Охарактеризовать совокупность данных валовой прибыли.
Оценить взаимосвязь и взаимное влияние валовой прибыли и объема продаж.
Построить график зависимости между валовой прибылью и объемом продаж. Используя возможности Excel, подобрать наиболее подходящий тип тренда. Сделать выводы
Установить функциональную зависимость между валовой прибылью и объемом продаж. Проанализировать адекватность полученного уравнения. Сравнить результаты пунктов
3 и 4.
Спрогнозировать валовую прибыль для объема продаж 260.
Спрогнозировать валовую прибыль для объема продаж: 260; 265; 270; 280.
Добавить данные цены на единицу продукции (табл.2).
Выполнить задания пунктов 2; 4; 5; 6, учитывая зависимость валовой прибыли от объема продаж и цены на единицу продукции.
Сравнить спрогнозированные результаты для валовой прибыли, полученные с учётом
1) Объема производства; 2) объема производства и цены.
объём произ.
Табл. 1
|
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Табл. 2 |