ТЕМА 3. ПРОГНОЗ С ИСПОЛЬЗОВАНИЕМ ЛИНИИ ТРЕНДА

 

Цель работы: освоить возможности приложения Microsoft Excel, которые применяются при анализе экономического развития хозяйствующих субъектов.

 

Программа Microsoft Excel позволяет сделать приблизительный прогноз при наличии данных за определенный промежуток времени с помощью линии тренда[1].

Пример.

Задание.Используя данные о доходах бюджета г. Архангельска за 1999 – 2002 годы рассчитайте темп прироста доходов, общую сумму доходов за каждый год и структуру налоговых доходов по периодам. Спрогнозируйте итоговую сумму налогов на 2004 год (постройте линию тренда). Исходные данные смотрите в таблице на рис. 1.15.

 

Рис. 1.15. Доходы бюджета г. Пятигорска за 1999 – 2002 годы

Решение. Для того чтобы построить линию тренда необходимо сначала построить график, отражающий динамику итоговой суммы налогов за каждый год. Для этого на панели инструментов нажмите кнопку Мастер диаграмм, в появившемся окне установите курсор в строке Диапазон, нажмите клавишу CTRL и, удерживая ее, выделите ячейки, содержащие итоговые суммы налогов за каждый год и ячейки, содержащие обозначения годов. Нажмите ДАЛЕЕ, ГОТОВО. Получили график в виде восходящей кривой линии соединяющей четыре точки (рис. 1.16).

Вставка-Линейчатая- точечная с гладкими линиями.

Макет-анализ-линия тренда- дополнительн.(линейный прогноз и величину достоверности аппроксимации)

Рис. 1.16. Линия тренда

Для того, чтобы построить линию тренда, наведите курсор на кривую и нажмите правую клавишу мыши, в появившемся контекстном меню выберите. Добавить линию тренда. Выберите Линейный тип. Откройте закладку ПАРАМЕТРЫ. Установите Прогноз вперед на 2 периода, поставьте галочку напротив условия Поместить на диаграмму величину достоверности аппроксимации, ОК (рис. 1.17).

Рис. 1.17. Параметры линии тренда

На графике появилась прямая линия, показывающая, что в 2004 году в бюджет поступит около 990 000 тыс. рублей. Коэффициент достоверности аппроксимации составляет 0,97. Это свидетельствует о том, что вероятность получения спрогнозированной суммы налогов составляет 97% (рис. 1.16).

 

Контрольные вопросы

Для чего используется линия тренда?

Что показывает коэффициент достоверности аппроксимации?

Что необходимо сделать, прежде чем строить линию тренда?

ЗАДАНИЯ ДЛЯ САМОСТОЯТЕЛЬНОЙ РАБОТЫ ПО ТЕМЕ 3

Задача 1. Проанализируйте продажи макаронных изделий за 3 мес. с помощью программы Microsoft Excel. Исходные данные приведены в таблице на рис.1.18. Рассчитайте значения пустых ячеек. Определите объем продаж лапши в июне с помощью линии тренда, определите коэффициент достоверности прогноза.

Продажи: январь - март

Наименование товара	январь	февраль	март	Сумма	Уд. вес
Лапша	25000	34000	66521
Рожки	20000	76548	87654
Ушки	14000	10876	11113
Вермишель	47651	57654	90870
Спагетти	164198	875432	870965
Ракушки	112233	165743	111654
Сумма

Рис. 1.18. Продажи макаронных изделий за первый квартал

Задача 2. Проанализируйте расходы на коммунальные услуги за 3 мес. с помощью программы Microsoft Excel. Исходные данные приведены в таблице на рис. 1.19.

Рассчитайте значения пустых ячеек. Определите расходы на оплату за пользование телефоном в мае, рассчитайте коэффициент достоверности прогноза.

Рис. 1.19. Перечень расходов на коммунальные услуги

.

Задача 3. Определите товарооборот за год, рассчитайте сумму налога, которая равна 15% от суммы товарооборота. Определите чистую прибыль. Спрогнозируйте товарооборот предприятия за май 2004 года. Исходные данные смотрите в таблице на рис. 1.20.

В тыс. руб.													Товарооборот за 2003 год	Сумма налога
Филиал	Январь	Февраль	Март	Апрель	Май	Июнь	Июль	Август	Сентябрь	Октябрь	Ноябрь	Декабрь
1	2250	2354	2587	2459	2545	2354	2201	2502	2498	2526	2654	2547
2	125	354	541	541	654	654	320	302	425	489	347	410
3	845	785	859	941	1105	747	754	706	902	899	821	854
6	654	458	687	645	510	741	321	548	654	507	651	700
7	1154	1472	1587	1345	1341	1852	1747	1856	1759	1801	1954	1820
Итого

    Чистая прибыль _________

Рис. 1.20. Товарооборот предприятия по филиалам за год

 

Задача 4. Рассчитайте темпы прироста (фактический год/предыдущий) и структуру доходов (удельный вес каждого вида налога в общей сумме) для каждого года. Постройте график, показывающий изменения общей суммы налоговых доходов. Постройте линию тренда и определите, сколько поступит средств в бюджет в 2005 году.

Вид дохода	1999 год	темп прироста	структура доходов	2000 год	темп прироста	структура доходов	2001 год	темп прироста	Структура доходов
Налоговые доходы всего
Налоги на прибыль, доход, в т.ч.:	97559			143838			340290
Налоги на товары, услуги, сборы:	38131			49425			33601
Налоги на совокупный доход	18801			26807			33187

Рис. 1.21. Налоговые поступления в бюджет

 

 

[1] Линии тренда позволяют графически отображать тенденции данных и прогнозировать их дальнейшие изменения. Подобный анализ называется также регрессионным анализом. Регрессионный анализ - форма статистического анализа, используемого для прогнозов. Регрессионный анализ позволяет оценить степень связи между переменными, предлагая механизм вычисления предполагаемого значения переменной из нескольких уже известных значений. Используя регрессионный анализ, можно продлить линию тренда в диаграмме за пределы реальных данных для предсказания будущих значений.

Настройки программы Microsoft Excel позволяют дополнить линию тренда значением квадрата смешанной корреляции - R в квадрате. Коэффициент R в квадрате отражает близость значений линии тренда к фактическим данным и представляет собой значение в пределах от 0 до 1. Линия тренда в наибольшей степени приближается к представленной на диаграмме зависимости, если значение равно или близко к 1. При аппроксимации данных с помощью линии тренда значение R-квадрат рассчитывается автоматически. Полученный результат можно вывести на диаграмме.

---

Дата добавления: 2018-06-27; просмотров: 1212; Мы поможем в написании вашей работы!

Поделиться с друзьями:

---

---

Мы поможем в написании ваших работ!