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

Исходные данные

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

	2005	2006	2007
Всего
в том числе для:
– исследования и разработки (без амортизации)	206,8	125,4	180,9
– приобретения прав на патенты, лицензий, промышленных образцов, полезных моделей	17,2	339,4	690,2
– технологическая подготовка производства, пробное производство и испытания	60,4	199,7	142,8
– обучение и подготовка персонала, связанные с инновациями	2,6	6,0	3,0
– приобретение программных средств	8,2	4,9	2,6
– маркетинговые исследования	18,4	26,1	19,6
– приобретение машин и оборудования, связанные с технологическими инновациями	635,8	603,7	1144,9
– прочие затраты на технологические инновации	68,2	32,0	110,7

Решение

Для решения задачи использован табличный процессор Microsoft Excel входящий в состав пакета программ Microsoft Office 2003.

1. После заполнения листа программы исходными данными он принял следующий вид:

	А	В	С	D
1	Виды затрат	Период
2	2005	2006	2007
3	– исследования и разработки (без амортизации)	206,8	125,4	180,9
4	– приобретения прав на патенты, лицензий, промышленных образцов, полезных моделей	17,2	339,4	690,2
5	– технологическая подготовка производства, пробное производство и испытания	60,4	199,7	142,8
6	– обучение и подготовка персонала, связанные с инновациями	2,6	6,0	3,0
7	– приобретение программных средств	8,2	4,9	2,6
8	– маркетинговые исследования	18,4	26,1	19,6
9	– приобретение машин и оборудования, связанные с технологическими инновациями	635,8	603,7	1144,9
10	– прочие затраты на технологические инновации	68,2	32,0	110,7
11	Всего

Рис. 1. Вид рабочего листа заполненного исходными данными

Проанализируем ежегодную структуру затрат по видам инновационной деятельности.

Для затрат 2005 года: надо выделить диапазон А2:В10 и дать команду меню Вставка – Диаграмма.

На первом шаге Мастера диаграмм определяю Тип диаграммы – Круговая (рис. 2.1).

Нажав кнопку Далее, перешла ко второму шагу. Убедившись, что Мастер распознал ряд, как данные в столбцах, нажала кнопку Далее (рис. 2.2).

На третьем шаге Мастера диаграмм на вкладке Заголовки ввела название диаграммы (рис. 2.3), а на вкладке Подписи данных (рис. 2.4) установила флажок параметра Доли блока Включить в подписи и нажала кнопку Готово.

2.1

2.2

2.3

2.4)

Рис. 2. Этапы создания диаграммы с помощью Мастера диаграмм

Щелкнув правой клавишей на значении доли и выбрав в контекстном меню команду Формат подписей данных, установила для выбранного числового формата число десятичных знаков равным двум (рис. 3).

Рис. 3. Настройка числового формата

Я использовала контекстное меню и приемы работы с графическими объектами, я увеличила размер области диаграммы и уменьшила размер шрифта легенды. В результате диаграмма приняла следующий вид (рис. 4).

Рис. 4. Структура затрат на инновационную деятельность в 2005 г.

Повторив процедуру создания диаграммы для диапазонов А3:А10; С3:С10 и А3:А10; D3:D10, получила диаграмму для 2006 и 2007 годов (рис. 5 и 6).

Рис. 5. Структура затрат на инновационную деятельность в 2006 г.

Рис. 6. Структура затрат на инновационную деятельность в 2007 г.

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

2. Определим рост инновационных затрат

В начале, необходимо определить суммарные затраты, просуммировав по годам виды затрат.

Для расчета за 2005 год, необходимо установить курсор в ячейку В11 и щелкнуть мышкой по кнопке «Автосумма» Σ на стандартной панели инструментов. Т.к. программа автоматически для суммирования выделяет диапазон В2:В10, захватывая ячейку с номером года, необходимо выделить необходимый диапазон (В3:В11) мышкой и подтвердить суммирование нажатием клавиши Enter. Для расчета данных за 2006 и 2007 годы достаточно размножить формулу в ячейке В11 на диапазон С11:D11.

Я использовала диапазон А3:D10 строю диаграмму, отражающую рост (снижение) затрат на технологические инновации по видам инновационной деятельности (при создании диаграммы задаю Тип – График, Вид – График с маркерами, помечающими точки данных, расположение данных задаю в строках, а диапазон подписей В2:D2). Аналогично строю диаграмму для инновационных затрат в целом (исходные данные заданы диапазоном А11:D11).

В итоге получились диаграммы представленные на рисунках 7 и 8.

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

Рис.8. Общие затраты на технологические инновации (млн. руб.) в 2005 - 2007 г.г.

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

3. Создание таблицы прироста (снижения) затрат на инновационную деятельность.

Я добавляю исходную таблицу двумя столбцами. В объединенных ячейках Е1:F1 ввожу заголовок «Прирост», а в Ячейки Е2; F2 данные о годах – 2006 и 2007, соответственно.

В ячейку Е3 ввожу формулу, рассчитывающую рост (снижение) затрат на соответствующий вид инновационной деятельности в текущем году, по сравнению с предыдущим: =C3–B3. Тиражирую полученную формулу на диапазон до F11.

В результате таблица приняла следующий вид:

Таблица 1. Затраты на технологические инновации (млн. руб.)

Виды затрат	Период	Прирост
2005	2006	2007	2006	2007
– исследования и разработки (без амортизации)	206,8	125,4	180,9	-81,4	55,5
– приобретения прав на патенты, лицензий, промышленных образцов, полезных моделей	17,2	339,4	690,2	322,2	350,8
– технологическая подготовка производства, пробное производство и испытания	60,4	199,7	142,8	139,3	-56,9
– обучение и подготовка персонала, связанные с инновациями	2,6	6,0	3,0	3,4	-3,0
– приобретение программных средств	8,2	4,9	2,6	-3,3	-2,3
– маркетинговые исследования	18,4	26,1	19,6	7,7	-6,5
– приобретение машин и оборудования, связанные с технологическими инновациями	635,8	603,7	1144,9	-32,1	541,2
– прочие затраты на технологические инновации	68,2	32,0	110,7	-36,2	78,7
Всего	1017,6	1337,2	2294,7	319,6	957,5

4. Строим диаграмму, отображающую прирост (снижение) затрат на инновационную деятельность.

В основе диапазона расчетных данных Е3:F11 и диапазона названий затрат, включая итоговую строку (А3:А11) строю объемного вида линейчатую диаграмму. Результат представлен на рисунке 9.

Рис.9. Прирост предложений рынка информационных технологий в 2006-2007 г.г.

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

5. Используя возможности табличного процессора Microsoft Excel, на основании построенных ранее диаграмм спрогнозирую развитие инновационных затрат. Для этого по очереди выделяя линии рядов данных необходимо дать команду Добавить линию тренда в контекстном меню. На вкладке Тип выбираю Линейная, а на вкладке Параметры в блоке Прогноз устанавливаю значение 2 в поле вперед на и включаю флажок параметра Показывать уравнение на диаграмме (рис. 10).

Рис.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 с.: ил. – Парал. тит. англ.