Расчет затрат на технологические инновации в Excel. Построение графиков и диаграмм (стр. 2 из 2)

Рис.10. Окно настроек параметров линии тренда

Используем в контекстном меню команду Исходные данные в поле Подписи по оси Х указываю годы 2005;2006;2007;2008;2009. Для удобства восприятия диаграмма методом копирования и удаления рядов данных была разделена на две, группируя данные с близкими числовыми показателями.

В результате моих действий получились следующая диаграмма (Рис. 11, 12.1, 12.2).

Рис.11. Общие затраты на технологические инновации в 2005 – 2007 г.г. с прогнозом на 2008 – 2009 г.г.

12.1)

12.2)

Рис.12. Затраты на технологические инновации по видам в 2005 – 2007 г.г. с прогнозом на 2008 – 2009 г.г.

Для определения прогнозируемых значений добавляю расчетную таблицу столбцами с названием расчетных годов. Также необходимо полученные формулы линий тренда адаптировать для расчетов в таблице.

Так, на примере, формулы линии тренда для затрат на исследования и разработки (без амортизации) имеет следующий вид: y = –12,95x + 196,93. Для вычисления значения по формуле в 2005 году ввожу в ячейку G3 формулу: = -12,95*(G$2+1-$G$2)+ 196.93.

Для других регионов заполним диапазон G4:G11 формулами соответствующими определенным видам инновационных затрат:

– приобретения прав на патенты, лицензий, промышленных образцов, полезных моделей

y = 336,5x - 324,07= 336,5*(G$2+1-$G$2) - 324,07;

– технологическая подготовка производства, пробное производство и испытания

y = 41,2x + 51,9= 41,2*(G$2+1-$G$2) + 51,9;

– обучение и подготовка персонала, связанные с инновациями

y = 0,2x + 3,4667= 0,2*(G$2+1-$G$2) + 3,4667;

– приобретение программных средств

y = -2,8x + 10,833= -2,8*(G$2+1-$G$2) + 10,833;

– маркетинговые исследования

y = 0,6x + 20,167= 0,6*(G$2+1-$G$2) + 20,167;

– приобретение машин и оборудования, связанные с технологическими инновациями

y = 254,55x + 285,7= 254,55*(G$2+1-$G$2) + 285,7;

– прочие затраты на технологические инновации

y = 21,25x + 27,8= 21,25*(G$2+1-$G$2) + 27,8;

– суммарные затраты на инновационную деятельность

y = 638,55x + 272,73y = 638,55*(G$2+1-$G$2) + 272,73.

Размножим формулы в диапазоне G3:G11 до K3:K11.

В результате проделанных процедур расчетная таблица приняла следующий вид (Рис.13):

Виды затрат	Прогнозируемые значения
Виды затрат	2005	2006	2007	2008	2009
– исследования и разработки (без амортизации)	183,98	171,03	158,08	145,13	132,18
– приобретения прав на патенты, лицензий, промышленных образцов, полезных моделей	12,43	348,93	685,43	1021,93	1358,43
– технологическая подготовка производства, пробное производство и испытания	93,10	134,30	175,50	216,70	257,90
– обучение и подготовка персонала, связанные с инновациями	3,67	3,87	4,07	4,27	4,47
– приобретение программных средств	8,03	5,23	2,43	-0,37	-3,17
– маркетинговые исследования	20,77	21,37	21,97	22,57	23,17
– приобретение машин и оборудования, связанные с технологическими инновациями	540,25	794,80	1049,35	1303,90	1558,45
– прочие затраты на технологические инновации	49,05	70,30	91,55	112,80	134,05
Всего	911,28	1549,83	2188,38	2826,93	3465,48

Рис. 13. Рассчитанные затраты на технологические инновации по видам в 2005 – 2009 г.г..

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

6. Рассчитаем ошибку прогноза по существующим данным.

Достроим существующую таблицу, заполнив ее расчетными данными, показывающими разность между расчетными и фактическими значениями.

Для этого в ячейку L3 ввожу формулу =G3-B3, а затем размножу формулу на диапазон до N11.

В ячейку О1 ввела заголовок «Вероятность», ячейку Р1 предполагаемый уровень вероятности (96%), ячейку О2 заголовок «Стандартное откл.», ячейку Р2 заголовок «Ошибка прогноза».

Для определения ошибки прогноза по определенному уровню вероятности воспользуюсь встроенными в Ms Excel статистическими функциями. Для определения стандартного отклонения по виду инновационных затрат в ячейку О3 вставляю функцию СТАНДОТКЛОП в качестве аргумента указываю диапазон L3:N3 (рис. 14).

Рис. 14. Этапы работы с мастером функций, подстановка аргументов функции

В ячейку Р3 вставляю функцию ДОВЕРИТ с аргументами, указанными на рис.15.

Далее тиражирую ячейки с формулами (диапазон О3:Р3) до О11:Р11.

Рассчитанные ошибки прогноза представлены в таблице 2. Следует отметить, что в любой момент можно рассчитать ошибку прогноза задав другой уровень вероятности.

Рис. 15. Диалоговое окно функции ДОВЕРИТ

Таблица 2. Определение ошибки прогноза по затратам на технологические инновации (млн. руб.)

Виды затрат	Отклонения прогнозир. значений			Вероятность	96%
Виды затрат	2005	2006	2007	Стандартное откл.	Ошибка прогноза
– исследования и разработки (без амортизации)	-22,82	45,63	-22,82	32,26763945	38,26
– приобретения прав на патенты, лицензий, промышленных образцов, полезных моделей	-4,77	9,53	-4,77	6,741084647	7,99
– технологическая подготовка производства, пробное производство и испытания	32,70	-65,40	32,70	46,24478349	54,83
– обучение и подготовка персонала, связанные с инновациями	1,07	-2,13	1,07	1,508494467	1,79
– приобретение программных средств	-0,17	0,33	-0,17	0,23570226	0,28
– маркетинговые исследования	2,37	-4,73	2,37	3,346972098	3,97
– приобретение машин и оборудования, связанные с технологическими инновациями	-95,55	191,10	-95,55	135,1281059	160,23
– прочие затраты на технологические инновации	-19,15	38,30	-19,15	27,08218972	32,11
Всего	-106,32	212,63	-106,32	150,3544719	178,28

8. Создания совмещенного графика по видам затрат.

Для выполнения данной задачи создаю на основе диапазона А3:D11 обычную гистограмму (данные определяю в строках, подписи по оси Х – диапазон В2:D2). Результат представлен на рисунке 16.

Рис.16. Затраты на технологические инновации по видам инновационной деятельности в 2007 –2009 г.г. (млн. руб.)

Литература

1. Excel. Единый справочник/В. Н. Шитов. — М.: ГроссМедиа, 2005. – 512 с.

2. В. Пикуза, А. Гаращенко. Экономические и финансовые расчеты в Excel. Самоучитель (+дискета) – СПб.: Питер, 2004. – 397 с.: ил.

3. Корнелл П. Анализ данных в Excel. Просто как дважды два / П. Корнелл; пер. с англ. – М.: Эксмо, 2007. – 224 с: ил.

4. Уокенбах, Джон. Microsoft Office Excel 2003. Библия пользователя.: Пер. с англ. – М.: ООО "И.Д.Вильямс", 2004. — 768 с.: ил. – Парал. тит. англ.