Макрос как запрограммированная последовательность действий, записанная на языке программирования Visual Basic for Applications. Рассмотрение особенностей решения данных задач в Excel. Характеристика проблем создания пользовательских функций на VBA.
Аннотация к работе
Если Вы используете Excel 2007, то у Вас отобразится вкладка Надстройки и в ней будет панель со всеми командами(рис.1) и создано выпадающее меню, также со всеми командами(рис.2) Если же Вы поместили надстройку в другую папку, то в окне выбора надстроек(рис.3) жмете кнопку «Обзор», выбираете файл надстройки MYADDIN.xla и подтверждаете выбор. После подключения Вы сможете вызвать справку при использовании любой команды или функции, нажав на «Справка по этой функции/команде» Запускаем Excel и выбираем: · 2007 Excel - Меню-Параметры Excel-Надстройки-Перейти(рис.1) Чтобы добавить итоги к столбцу таблицы, добавьте строку итогов, а затем в каждой ячейке этой строки используйте функции из всплывающего меню.
Введение
При помощи Excel можно анализировать большие массивы данных. В Excel можно использовать более 400 математических, статистических, финансовых и других специализированных функций, связывать различные таблицы между собой, выбирать произвольные форматы представления данных, создавать иерархические структуры.
Программа MS Excel, являясь лидером на рынке программ обработки электронных таблиц, определяет тенденции развития в этой области. Вплоть до версии 4.0 программа Excel представляла собой фактический стандарт с точки зрения функциональных возможностей и удобства работы. Теперь на рынке появились гораздо более новые версии, которые содержат много улучшений и приятных неожиданностей.
В данной работе рассмотрены теоретические аспекты анализа инвестиционных проектов, рассмотрен порядок прогнозирования с использованием методов скользящего среднего и сглаживания, разобрано создание макросов и пользовательских функций в Excel.
Курсовая работа содержит теоретический и практический материал на тему «Анализ данных в MSEXCEL», теоретические вопросы по теме. Ключевые слова: финансовые функции, инвестиционный проект, эффективность, инвестиционный портфель, временные ряды, прогнозирование, макросы, пользовательские функции.
Курсовая работа содержит теоретические материалы по понятию, эффективности инвестиционных проектов, рассмотрен порядок прогнозирования с использованием методов скользящего среднего и сглаживания, разобрано создание макросов и пользовательских функций.
Практическая часть курсовой работы включает рекомендации по выполнению оценки инвестиционных проектов, использованию методов скользящего среднего и сглаживания анализа данных, созданию макросов и пользовательских функций.Xls ^
1. Теоретическая часть
1.1 Установка надстроек
Если Вы используете Excel 2007, то у Вас отобразится вкладка Надстройки и в ней будет панель со всеми командами(рис.1) и создано выпадающее меню, также со всеми командами(рис.2)
Рис.
Рис.
Если же Вы поместили надстройку в другую папку, то в окне выбора надстроек(рис.3) жмете кнопку «Обзор», выбираете файл надстройки MYADDIN.xla и подтверждаете выбор. Далее жмете «ОК». Надстройка подключена. Вы можете автоматически установить/удалить надстройку MYADDIN и файл помощи MYADDIN Help с помощью исполняющего приложения - «Установка и удаление надстройки».
Рис.
Вам достаточно запустить приложение и выбрать необходимое действие: Установить надстройку или Удалить надстройку. При выборе установки доступна опция - Подключить файл помощи. Если выбрать опцию, то файл помощи будет автоматически подключен к надстройке(файл помощи MYADDINHELP должен находиться в папке с исполняемым приложением, иначе справка не будет подключена). После подключения Вы сможете вызвать справку при использовании любой команды или функции, нажав на «Справка по этой функции/команде»
Если Вы все же хотите самостоятельно подключить надстройку, то: Извлекаем из архива файл MYADDIN.xla и помещаем его: 1. в папку надстроек Excel, как правило она расположена по адресу: C:\Documents and Settings\Учетная запись\Application Data\MICROSOFTADDINS;
2. в любое место на локальном диске;
Запускаем Excel и выбираем: · 2007 Excel - Меню-Параметры Excel-Надстройки-Перейти(рис.1)
Рис. 2003 Excel - Сервис-Надстройки
Рис.
Если надстройка была помещена в папку C:\Documents and Settings\Учетная запись\Application Data\MICROSOFTADDINS, то она будет отображена в списке и Вам надо будет только выбрать ее, поставив галочку(рис.5).Xls ^
1.2 Вычисление итогов
Можно также создать настраиваемое вычисление (Дополнительные вычисления. Способ сложения значений в области данных отчета сводной таблицы путем использования значений из других ячеек области данных. Для создания дополнительных вычислений служит список в группе Дополнительные вычисления диалогового окна «Вычисление поля сводной таблицы».).
1. В области значений выделите поле, для которого требуется изменить итоговую функцию отчета сводной таблицы.
2. На вкладке Параметры в группе Активное поле нажмите кнопку Активное поле, а затем - кнопку Параметры поля.
Будет открыто диалоговое окно Параметры поля значений.
В поле Имя источника отображается имя источника данных поля.
В поле Пользовательское имя отображается текущее имя отчета сводной таблицы или имя источника, если пользовательское имя не задано. Для изменения значения параметра Пользовательское имя щелкните текст в этом поле и внесите необходимые изменения.
3. Перейдите на вкладку Операция.
4. Выберите нужную итоговую функцию в списке Операция.
Для некоторых типов исходных данных (Исходные данные. Список или таблица, используемые для создания отчета сводной таблицы или отчета сводной диаграммы. Исходные данные могут быть взяты из списка или диапазона Microsoft Excel, внешней базы данных или куба либо из другого отчета сводной таблицы.), таких как данные OLAP, вычисляемых полей (Вычисляемое поле. Поле в отчете сводной таблицы или отчете сводной диаграммы, использующее созданную формулу. Вычисляемые поля могут выполнять вычисления, используя содержимое других полей отчета сводной таблицы или отчета сводной диаграммы.)
Если для данных используется формат таблицы, можно быстро вычислять итоговые значения в ее столбцах и выполнять другие действия. Чтобы добавить итоги к столбцу таблицы, добавьте строку итогов, а затем в каждой ячейке этой строки используйте функции из всплывающего меню.
Совет Теперь в строке итогов можно использовать не только функции из этого списка. Во всплывающем меню выберите пункт Другие функции, чтобы открыть построитель функций. В любой ячейке строки итогов можно ввести любую нужную формулу.
Данные в поле значений суммируют данные источников в отчете сводной таблицы следующим образом: для числовых значений используется функция СУММ, а для текстовых - функция СЧЕТ. Однако суммирующая функция (Итоговая функция. Тип вычисления при объединении данных в отчете сводной таблицы, в таблице консолидации или при вычислении итогов в списках или базах данных. Примерами итоговых функций могут служить функции СУММ, СЧЕТ и СРЕДНЕЕ.) может быть изменена.
Рис.
1. Щелкните любую ячейку таблицы.
2. На вкладке Таблицы, в разделе Параметры таблицы, выберите пункт Строка итогов.
Рис.
3. В строке итогов выберите ячейку, в которой требуется вычислить итог, а затем щелкните двустороннюю стрелку , чтобы открыть всплывающее меню.
4. Во всплывающем меню выберите функцию для расчета итога.
Изменение итоговой функции или настраиваемого вычисления для поля в отчете сводных таблиц Xls ^
1.3 Консолидация данных
Консолидация по категории
1. Организуйте консолидируемые данные на каждом отдельном листе.
· Необходимо, чтобы все диапазоны данных были представлены в формате списка (Список. Набор строк, содержащий взаимосвязанные данные, или набор строк, которому назначаются функции листа с помощью команды Создать список.): первая строка каждого столбца содержит название, остальные строки - однотипные данные; пустые строки или столбцы в списке отсутствуют.
· Разместите каждый диапазон на отдельном листе. Не помещайте диапазоны на лист, на котором должна выполняться консолидация.
· Убедитесь, что подписи столбцов или строк, которые требуется объединить, совпадают с учетом регистра букв. Например, заголовки «Ср. за год» и «Средний за год» различаются и не будут объединены в таблице консолидации.
· Присвойте каждому диапазону имя: выделите диапазон ячеек и на вкладке Формулы в группе Определенные имена щелкните стрелку, расположенную рядом с полем Присвоить имя и введите имя диапазона в поле Имя.
2. Щелкните левый верхний угол области основного листа, в которой требуется разместить консолидированные данные.
Убедитесь, что справа и снизу этой ячейки достаточно свободных ячеек для данных консолидации. Команда Консолидация заполнит столько ячеек, сколько потребуется.
3. На вкладке Данные в группе Средства обработки данных выберите команду Консолидация.
Рис.
4. Выберите в раскрывающемся списке Функция итоговую функцию (Итоговая функция. Тип вычисления при объединении данных в отчете сводной таблицы, в таблице консолидации или при вычислении итогов в списках или базах данных. Примерами итоговых функций могут служить функции СУММ, СЧЕТ и СРЕДНЕЕ.), которую требуется использовать для консолидации данных.
5. Если лист находится в другой книге, нажмите кнопку Обзор, чтобы найти файл, а затем - кнопку ОК, чтобы закрыть диалоговое окно Обзор.
Путь к файлу будет введен в поле Ссылка, он будет завершаться восклицательным знаком.
6. Введите имя, назначенное диапазону, и нажмите кнопку Добавить. Повторите этот шаг для всех диапазонов.
7. Выберите способ обновления консолидации. Выполните одно из следующих действий.
· Чтобы консолидация обновлялась автоматически при изменении исходных данных, установите флажок Создавать связи с исходными данными.
Этот флажок можно устанавливать только в случае, если лист с исходными данными находится в другой книге. После того как этот флажок установлен, изменить набор входящих в консолидацию ячеек и диапазонов невозможно.
· Чтобы получить возможность обновления консолидации вручную путем изменения включаемых в нее ячеек и диапазонов, снимите флажок Создавать связи с исходными данными.
8. Установите флажки в группе Использовать в качестве имен, указывающие, где в исходных диапазонах находятся названия: либо подписи верхней строки, либо Значения левого столбца, либо оба флажка одновременно.
· Все названия, не совпадающие с названиями в других исходных областях, приведут к появлению дополнительных строк или столбцов в консолидированных данных.
Все категории, которые не нужно консолидировать, должны иметь уникальные подписи, которые встречаются только в одном диапазоне исходных данных.
Недавно описывался вариант консолидации данных (агрегации) с применением VBA. На мой взгляд, это лучший способ и наиболее надежный с точки зрения, как отработки, так и контроля корректности загрузки данных.
Рис.
Однако нужно отметить, что разработчики Excel предусмотрели встроенный механизм консолидации данных - он не очень удобен, но в некоторых вариантах может быть вполне полезен! Итак, вспомним задачу - есть некая, достаточно крупная Группа компаний, в которой есть ряд дочерних компаний, которым мы отсылаем пакеты сбора данных и хотим, в дальнейшем, их получить и агрегировать
Для примера создадим три отдельных файла (file1.xls, file2.xls, file3.xls) с разной структурой.File1.xls.
Рис.
Теперь создадим консолидационный файл, который будет собирать данные из первых трех первичных файлов. Заметим - структура ВСЕХ, включая консолидационный, файлов может быть абсолютно разной!
Приступим к настройке консолидации. Для этого в консолидационном файле, открываем меню «Данные», далее «Консолидация».
Рис.
В открывшемся окне «Консолидация» вводим:• Функцию - выбираем, что необходимо сделать с данных исходных пакетов - просто сложить, посчитать среднее, найти максимум и т.д.
Это очень удобно! В одних случаях вы консолидируете данные (например, выручка), в других - получаете среднее значение (например, драйверы в бюджетировании).
Рис.
• Ссылку - основной пункт настройки. Здесь мы последовательно выбираем файлы, из которых будем подтягивать данные, а также не забываем указывать ссылку на диапазон. Не принципиально, что бы диапазоны совпадали, однако, важно, что бы размерность диапазонов была одинаковой. После того, как файл выбран и присвоен диапазон - нажимаем «Добавить.
Рис.
Важным пунктом является пункт «Создавать связи с исходными файлами». Если он выбран, то результат будет иметь следующий вид: Таким образом, мы можем уточнить структуру получаемого значения. Большим недостатком является то, что не отражаются имена файлов, откуда подтягиваются значения. Если пункт не отмечен, то результат будет следующим:
Рис.
Таким образом, для целей консолидации можно:• Использовать VBA и автоматически подгружать данные из файлов. При этом в цикле открывать файл, подтягивать данные и закрывать файл. Этот подход наиболее гибкий, так как можно отбирать данные, используя достаточно сложную логику.• Использоваться встроенный механизм «Консолидация» и настроить выгрузку вручную, без использования каких-либо скриптов. Сухо, примитивно, но, не вдаваясь в программерские подробности.• Использовать микс из встроенного механизма «Консолидация» и VBA. Подобно всему в Excel, этим механизмом можно управлять через VBA. Вот код, который запускает консолидационный процесс Xls ^
1.4 Поиск решения
Каждый инвестор пытается сформировать портфель ценных бумаг с возможно большей ожидаемой доходностью и возможно меньшим риском. Портфели, имеющие наименьший риск для заданной ожидаемой доходности или наибольшую ожидаемую доходность при заданном уровне риска, называются эффективными портфелями. Сформировать эффективный портфель значит найти доли капитала, которые следует вложить в ценные бумаги, чтобы получить наименьший риск при заданной ожидаемой доходности или наибольшую ожидаемую доходность при заданном уровне риска, т.е. необходимо решить соответствующие задачи [7]: Решение данных задач в Excel осуществляется при помощи надстройки Поиск решения (Меню/Сервис/Поиск решения) [3].
Рис. Окно Поиск решения
В окне (рис. 15) устанавливается ссылка на ячейки целевой функции (определяется также характер целевой функции), определяются ограничения и изменяемые ячейки. Совокупность долговременных затрат финансовых, трудовых и материальных ресурсов с целью увеличения накоплений и получения прибыли называется инвестициями.
Инвестиционная деятельность в той или иной степени присуща любому предприятию. Она представляет собой один из наиболее важных аспектов функционирования любой коммерческой организации. Причинами, обуславливающими необходимость инвестиций, являются обновление имеющейся материально-технической базы, наращивание объемов производства, освоение новых видов деятельности [4].
Управление инвестиционными процессами, связанными с вложениями денежных средств в долгосрочные материальные и финансовые активы, представляет собой наиболее важный и сложный раздел финансового менеджмента [3]. Принимаемые в этой области решения рассчитаны на длительные периоды времени и, как правило: • являются частью стратегии развития фирмы в перспективе;
• влекут за собой значительные оттоки средств;
• с определенного момента времени могут стать необратимыми;
• опираются на прогнозные оценки будущих затрат и доходов.
Поэтому, значение экономического анализа для планирования и осуществления инвестиционной деятельности трудно переоценить. При этом особую важность имеет предварительный анализ, который проводится на стадии разработки инвестиционных проектов и способствует принятию разумных и обоснованных управленческих решений.
Анализ эффективности намечаемых капиталовложений - это процесс анализа потенциальных расходов на финансирование активов и решений, следует ли фирме делать такие капиталовложения. Процесс анализа эффективности намечаемых капиталовложений требует, чтобы фирма: 1) определила издержки проекта;
2) оценила ожидаемые потоки денежных средств от проекта и рисковость этих потоков денежных средств;
3) определила соответствующую стоимость капитала, по которой дисконтируются потоки денежных средств;
4) определила дисконтированную стоимость ожидаемых потоков денежных средств и этого проекта. [6].Xls ^
1.5 Подбор параметра
Временной ряд - это ряд наблюдений, проводившихся регулярно через равные интервалы времени. Он может быть равен году, неделе, суткам или даже минутам, в зависимости от характера рассматриваемой переменной.
Сглаживание ряда динамики с помощью скользящей средней заключается в том, что вычисляется средний уровень из определенного числа первых по порядку уровней ряда, затем - средний уровень из такого же числа уровней, начиная со второго, далее - начиная с третьего и т.д. Таким образом, при расчетах среднего уровня как бы «скользят» по ряду динамики от его начала к концу, каждый раз отбрасывая один уровень в начале и добавляя один следующий [5]. Интервал сглаживания, т.е. число входящих в него уровней определяют, используя следующее правило: если необходимо сгладить мелкие, беспорядочные колебания, то интервал сглаживания берут по возможности большим; В Excelпрогнозирование с использованием метода скользящего среднего осуществляется при анализе данных (Меню/Сервис/Анализ данных) [3].
Рис. Анализ данных (скользящее среднее)
После выбора в окне анализа данных пункта Скользящее среднее появляется окно, в котором необходимо ввести соответствующую информацию: входной интервал, выходной интервал, задать вывод графика и определение стандартных погрешностей, т.е. точности оценки.Xls ^
Рис. Окно параметров скользящего среднего
1.6 Прогнозирование с использованием метода Экспоненциального сглаживания
Если при сравнении прогноза с реальными значениями сглаженные данные при выбранном a значительно отличаются от исходного ряда, необходимо перейти к другому параметру сглаживания (чем больше значение a, тем больше сглаживание).
Аналогично прогнозированию с использованием скользящей средней, ВEXCELПРОГНОЗИРОВАНИЕ с использованием метода экспоненциального сглаживания осуществляется при анализе данных (Меню/Сервис/Анализ данных) [2].
Рис. Анализ данных (экспоненциальное сглаживание)
После выбора в окне анализа данных пункта Экспоненциальное сглаживание появляется окно, в котором необходимо ввести соответствующую информацию: входной интервал, выходной интервал, задать вывод графика и определение погрешностей, фактор затухания.Xls ^
Макрос - это запрограммированная последовательность действий (программа, процедура), записанная на языке программирования Visual Basic for Applications (VBA). Мы можем запускать макрос сколько угодно раз, заставляя Excel выполнять последовательность любых нужных нам действий, которые нам не хочется выполнять вручную.Xls ^
1.8 Создание макросов в редакторе Visual Basic
Для ввода команд, т.е. создания макроса необходимо открыть специальное окно - редактор программ на VBA в меню Сервис - Макрос - Редактор Visual Basic (Tools - Macros - Visual Basic Editor) [3]:
Рис. Окно Visual Basic
Макросы (т.е. наборы команд на языке VBA) хранятся в программных модулях. В любой книге Excel мы можем создать любое количество программных модулей и разместить там наши макросы.
Обычные модули - используются в большинстве случаев, когда речь идет о макросах. Для создания нового модуля выберите в меню Insert - Module. В появившееся окно нового пустого модуля можно вводить команды на VBA, набирая их с клавиатуры.
Рис. Окно кода модуля в VBA
Модуль Эта книга - виден в левом верхнем углу редактора Visual Basic в окне, которое называется Project Explorer. В этот модуль обычно записываются макросы, которые должны выполнятся при наступлении каких-либо событий в книге (открытие или сохранение книги, печать файла и т.п.).
Модуль листа - доступен через контекстное меню листа, команда Исходный текст (View Source). Сюда записывают макросы, которые должны выполняться при наступлении определенных событий на листе (пересчет или изменение листа, копирование или удаление листа и т.д.).
Любой макрос должен начинаться с оператора Sub, за которым идет имя макроса и список аргументов (входных значений) в скобках. Если аргументов нет, то скобки надо оставить пустыми.
Любой макрос должен заканчиваться оператором End Sub.
Все, что находится между Sub и End Sub - тело макроса, т.е. команды, которые будут выполняться при запуске макроса.Xls ^
1.9 Запись макросов макрорекордером
Макрорекордер - это небольшая программа, встроенная в Excel, которая переводит любое действие пользователя на язык программирования VBA и записывает получившуюся команду в программный модуль. Если мы включим макрорекордер на запись, а затем начнем создавать свой еженедельный отчет, то макрорекордер начнет записывать команды вслед за каждым нашим действием и, в итоге, мы получим макрос создающий отчет как если бы он был написан программистом. Такой способ создания макросов не требует знаний пользователя о программировании и VBA и позволяет пользоваться макросами как неким аналогом видеозаписи: включил запись, выполнил операцию, перемотал пленку и запустил выполнение тех же действий еще раз. Естественно у такого способа есть свои плюсы и минусы: Макрорекордер записывает только те действия, которые выполняются в пределах окна Microsoft Excel. Как только вы закрываете Excel или переключаетесь в другую программу - запись останавливается.
Макрорекордер может записать только те действия, для которых есть команды меню или кнопки в Excel. Программист же может написать макрос, который делает то, что Excel никогда не умел (сортировку по цвету, например или что-то подобное).
Если во время записи макроса макрорекордером вы ошиблись - ошибка будет записана. Однако смело можете давить на кнопку отмены последнего действия (Undo) - во время записи макроса макрорекордером она не просто возрвращает Вас в предыдущее состояние, но и стирает последнюю записанную команду на VBA.
Чтобы включить запись необходимо выбрать в меню Сервис - Макрос - Начать запись (Tools - Macro - Record New Macro) и настроить параметры записываемого макроса в окне Запись макроса [3]:
Рис. Окно записи макроса
Имя макроса - подойдет любое имя на русском или английском языке. Имя должно начинаться с буквы и не содержать пробелов и знаков препинания.
Сочетание клавиш - будет потом использоваться для быстрого запуска макроса. Если забудете сочетание или вообще его не введете, то макрос можно будет запустить через меню Сервис - Макрос - Макросы - Выполнить (Tools - Macro - Macros - Run).
Сохранить в... - здесь задается место, куда будет сохранен текст макроса, т.е. набор команд на VBA из которых и состоит макрос.: Эта книга - макрос сохраняется в модуль текущей книги и, как следствие, будет выполнятся только пока эта книга открыта в Excel
Новая книга - макрос сохраняется в шаблон, на основе которого создается любая новая пустая книга в Excel, т.е. макрос будет содержаться во всех новых книгах, создаваемых на данном компьютере начиная с текущего момента
Личная книга макросов - это специальная книга Excel с именем Personal.xls, которая используется как хранилище макросов. Все макросы из Personal.xls загружаются в память при старте Excel и могут быть запущены в любой момент и в любой книге.
После включения записи и выполнения действий, которые необходимо записать, запись можно остановить командой Сервис - Макрос - Остановить запись (Tools - Macro - Stop Recording).
Запуск и редактирование макросов. Управление всеми доступными макросами производится в окне, которое можно открыть через меню Сервис - Макрос - Макросы (Tools - Macro - Macros) [7]:
Рис. Окно управления макросами
Любой выделенный в списке макрос можно запустить кнопкой Выполнить (Run).
Кнопка Параметры (Options) позволяет посмотреть и отредактировать сочетание клавиш для быстрого запуска макроса.
Кнопка Изменить (Edit) открывает редактор Visual Basic (см. выше) и позволяет просмотреть и отредактировать текст макроса на VBA.Xls ^
1.10 Создание пользовательских функций на VBA запрограммированный задача excel
Создание пользовательских функций или, как их иногда еще называют, UDF-функций (User Defined Functions) принципиально не отличается от создания макроса в обычном программном модуле [3]. Разница только в том, что макрос выполняет последовательность действий с объектами книги (ячейками, формулами и значениями, листами, диаграммами и т.д.), а пользовательская функция - только с теми значениями, которые мы передадим ей как аргументы (исходные данные для расчета).Xls ^
2. Практическая часть
2.1 Практические задания для закрепления теоретического материала
Прогнозирование (метод скользящего среднего)Xls ^
Для прогнозирования объема продаж необходимы средние значения данного показателя за последние несколько лет. В таблице представлены ежеквартальные объемы продаж компании, специализирующегося на розничной продаже. В меню Сервис выбрать команду Анализ данных. Появится диалоговое окно Анализ данных, где из списка выбрать инструмент Скользящее среднее.
В поле Входной интервал указываем диапазон С2:С17.
В поле Интервал вводим количество кварталов, которое включается в подсчет скользящего среднего - 4. Интервал сглаживания (по умолчанию используется значение 3) определяют, используя правило: если необходимо сгладить мелкие колебания временного ряда, то интервал берут по возможности большим, если нужно сохранить мелкие волны - интервал сглаживания уменьшают. В поле Выходной интервал вводим адрес ячейки (или щелкнуть на этой ячейке в рабочем листе) для вывода результатов.
Рис.
Ставить значки для вывода графика и стандартных погрешностей.
Excel представит результаты решения в столбцах D и E с выводом графика. Если предшествующих данных недостаточно для построения прогноза, Excel возвратит ошибочное значение #Н/Д.
Упражнение 2
Экспоненциальное сглаживание.
Для тех же данных заполняем окно анализа данных по экспоненциальному сглаживанию: Для составления прогноза используем инструмент Экспоненциальное сглаживание пакета Анализ данных.
Выполняем следующие команды - Сервис - Анализ данных - Экспоненциальное сглаживание - ОК. В появившемся окне инструмента Экспоненциальное сглаживание указать следующие значения:
Рис.
Входной интервал - С2:С17;
Фактор затухания (константа сглаживания a) - 0,3 (значение по умолчанию - 0,3);
Выходной интервал: D12;
Поставим флажки для вывода графика и стандартных погрешностей.
Excel представит результаты решения в следующем виде (рис. 9).
Диапазон D2:D17 - рассчитанные значения экспоненциального сглаживания по формуле;
Диапазон E2:E17 - рассчитанные значения стандартных погрешностей.
Упражнение 3
Макросы.
В модуле книги Макрос создаем макрос следующего вида:
Рис.
Данный макрос после запуска будет умножать значение из ячейки А4 на 0,18 и складывать со значением этой же ячейки А4.
Создание пользовательской функции.
В книге Макрос создаем модуль и составляем для него программу.
Рис.
Проверим работу макроса и пользовательской функции.
Вводим исходные данные (ячейки А3:А4) и формулу (ячейка В4, формула =NDS(A3) A3).
Запускаем макрос (Сервис/Макрос/Макросы…) и нажимаем в появившемся окне кнопку Выполнить.
В итоге в ячейке В4 установится значение 1180.
Упражнение 4 (самостоятельно)
За компаниями А, В, С проводились наблюдения в течение трех периодов. Данные в процентах приводятся в таблице ниже. Оценить ожидаемую доходность и риск каждой акции, на основании этих оценок дать сравнительную характеристику. Рассчитать ковариации доходностей акций друг с другом. Построить модели, позволяющие определить структуру эффективных портфелей.
Таблица
Период наблюдения Доходность акции компании
А В С
Первый 16 21 15
Второй 19 23 18
Третий 17 26 21
Упражнение 5 (самостоятельно)
Фирма выясняет возможность производства новой продукции. Чтобы запустить проект, понадобится потратить в начальный момент $100000. В первый, второй, третий и четвертые годы реализация новой продукции принесет доход в размерах, соответственно, $30000, $70000. $180000 и $90000. На пятый год продукция перестанет быть популярной, и доход упадет до $10000. Дальнейший выпуск этой продукции не предполагается. Ставку дисконтирования принять 10%.Обоснуйте экономическую целесообразность реализации инвестиционного проекта.
Рис.
Упражнение 6 (самостоятельно)
Создать аналогичную диаграмму
Рис.
2.2 Словарь терминов
Для финансового анализ в MSEXCELВКЛЮЧЕНЫ специальные финансовые функции. Приведем список финансовых функций MSEXCELC кратким описанием .
ДАТАКУПОНДО. Возвращает предыдущую дату купона перед датой соглашения.
ОБЩПЛАТ. Возвращает общую выплату, произведенную между двумя периодическими выплатами.
ОБЩДОХОД. Возвращает общую выплату по займу между двумя периодами.
ФУО. Возвращает амортизацию имущества на заданный период, используя метод постоянного учета амортизации.
ДДОБ. Возвращает величину амортизации имущества для указанного периода при использовании метода двукратного учета амортизации или иного явно указанного метода.
СКИДКА. Возвращает норму скидки для ценных бумаг.
РУБЛЬ.ДЕС .Преобразует цену в рублях, выраженную в виде дроби, в цену в рублях, выраженную десятичным числом.
РУБЛЬ.ДРОБЬ. Преобразует цену в рублях, выраженную десятичным числом, в цену в рублях, выраженную в виде дроби.
ДЛИТ. Возвращает ежегодную продолжительность действия ценных бумаг с периодическими выплатами по процентам.
КПЕР. Возвращает общее количество периодов выплаты для данного вклада.
ЧПС. Возвращает чистую приведенную стоимость инвестиции, основанной на серии периодических денежных потоков и ставке дисконтирования.
ЦЕНАПЕРВНЕРЕГ. Возвращает цену за 100 рублей нарицательной стоимости ценных бумаг с нерегулярным первым периодом.
ДОХОДПЕРВНЕРЕГ. Возвращает доход по ценным бумагам с нерегулярным первым периодом.
ЦЕНАПОСЛНЕРЕГ. Возвращает цену за 100 рублей нарицательной стоимости ценных бумаг с нерегулярным последним периодом.
ДОХОДПОСЛНЕРЕГ. Возвращает доход по ценным бумагам с нерегулярным последним периодом.
ПЛТ. Возвращает величину выплаты за один период годовой ренты.
ОСПЛТ. Возвращает величину выплат на основной капитал для вклада в заданный период.
ЦЕНА. Возвращает цену за 100 рублей нарицательной стоимости ценных бумаг, по которым производится периодическая выплата процентов.
ЦЕНАСКИДКА. Возвращает цену за 100 рублей нарицательной стоимости ценных бумаг, на которые сделана скидка.
ЦЕНАПОГАШ. Возвращает цену за 100 рублей нарицательной стоимости ценных бумаг, по которым выплачивается прибыль в момент вступления в силу.
ПС. Возвращает приведенную (к настоящему моменту) стоимость инвестиции.
СТАВКА. Возвращает процентную ставку по аннуитету за один период.
ПОЛУЧЕНО. Возвращает сумму, полученную в срок вступления в силу полностью обеспеченных ценных бумаг.
АПЛ. Возвращает величину непосредственной амортизации имущества за один период.
АСЧ. Возвращает величину амортизации актива за данный период, рассчитанную методом "суммы (годовых) чисел".
НАКОПДОХОД. Возвращает накопленный доход по ценным бумагам с периодической выплатой процентов.
НАКОПДОХОДПОГАШ. Возвращает накопленный доход по ценным бумагам, процент по которым выплачивается в срок вступления в силу.
АМОРУМ. Возвращает величину амортизации для каждого периода, используя коэффициент амортизации.
АМОРУВ. Возвращает величину амортизации для каждого отчетного периода.
ДНЕЙКУПОНДО. Возвращает количество дней между началом периода купона и датой соглашения.
ДНЕЙКУПОН. Возвращает число дней в периоде купона, который содержит дату соглашения.
ДНЕЙКУПОНПОСЛЕ. Возвращает число дней от даты соглашения до срока следующего купона.
ДАТАКУПОНПОСЛЕ. Возвращает следующую дату купона после даты соглашения.
ЧИСЛКУПОН. Возвращает количество купонов, которые могут быть оплачены между датой соглашения и сроком вступления в силу.Xls ^
2.3 Тестовое задание
1. Что такое MSEXCEL: а) Система управления реляционными базами данных б) Средство представления данных в печатном формате в) Таблица с вычисляемыми полями г) Табличный процессор
2. Для каких целей предназначен MS Excel: а) Создание текстовых документов б) Создание БД (баз данных) в) Создание таблиц, расчет и анализ информации
3. Компонентом какого пакета является MSEXCEL: а) MS Access б) MS Office в) MS Word г) MS Publisher
4. Является ли MSEXCELПРИЛОЖЕНИЕМ Windows: а) Да б) Нет в) Не является приложением г) Excel- часть операционной системы Windows
5. Функция ВСД используется для расчета: а) NPV б) IRR в)PI
6. Функция ВСД помогает оценить: а) банковский депозит б) инвестиционный проект в) прогнозные значения
7. Функция ЧПС рассчитывает: а) PI б) ставку дисконтирования в) PV г) IRR
8. NPVЭТО: а) величина дисконтированных доходов б) норма дисконтирования в) период реализации проекта г) чистая приведенная стоимость
9. PVЭТО: а) величина дисконтированных доходов б) норма дисконтирования в) инвестиции проекта г) чистая приведенная стоимость
10. РІЭТО: а) индекс рентабельности проекта б) чистая приведенная стоимость в) внутренняя норма рентабельности г) накопленные инвестиции
Xls ^
Ответы на тест: 1) г
2) в
3) б
4) a 5) б
6) б
7) в
8) г
9) а 10) а
Список литературы
1. Пикуза В., Гаращенко А. Экономические и финансовые расчеты в Excel. Самоучитель. - СПБ.: Питер; К.: Изд. группа BHV, 2003. - 400 с.
2. Экономическая информатика: Учебник/Под ред. В.П.Косарева и Л.В. Еремина - М.: Финансы и статистика, 2001. - 592 с: ил.
3. Козырев А.А. Информационные технологии в экономике и управлении. С-Пб. И-во Михайлова, 2000. - 297 с.: ил.
4. Замков О.О., Толстопятенко А.В., Черемных Ю. Н. Математические методы в экономике. М.: АО «ДИС», 2002. - 185 с.: ил.
5.Исследование операций в экономике. Под редакцией Н.Ш.Кремера. М., ЮНИТИ, 2004. - 305 с..
6. Дубина С., Орлова И., Шубина А. Excel для экономистов и менеджеров. -Спб.: Питер, 2004.-295с.
7. Блатнер П. и др. Использование MICROSOFTEXCEL 2002: Спец. изд.: Учеб пособие. - М.: Вильямс, 2002.- 1024с.
8. Гарнаев А. Использование MSEXCELИ VBA в экономике и финансах. СПБ: BHV, 1999.-332с.
9. Козырев А.А. Информационные технологии в экономике и управлении: Учебник. 2-е изд. - СПБ.: Изд-во Михайлова В.А., 2001. - 360 с.
10. Новиков Ф.А., Яценко А.Д. MICROSOFTOFFICE 2000 в целом. - СПБ.: БХВ -Санкт-Петербург, 2000. - 728 с: ил.
11. Карлберг Конрад. Бизнес-анализ с помощью Microsoft Excel, 2-е изд./ Пер.с англ. - М.: Изд. дом "Вильяме", 2003. - 448 с: ил. - Парал. тит. англ.
Заключение
Характерной чертой современности является стремительный научно-технический прогресс, что требует от менеджеров и бизнесменов значительного повышения ответственности за качество принятия решений. Это основная причина, которая обусловливает необходимость научного принятия управленческих решений.
Электронная таблица Excel является одним из наиболее популярных пакетов программ, предназначенных для создания табличных документов. Система обладает мощными вычислительными возможностями, великолепными средствами составления деловой графики, обработки текстов, ведения баз данных. Кроме того, Excelобладает расширенным набором финансовых функций, облегчающих решение данной задачи.Xls ^