Исходные данные
Затраты на технологические инновации по видам инновационнойдеятельности (млн. руб.) 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 с.: ил. – Парал. тит. англ.