|
Архитектура Аудит Военная наука Иностранные языки Медицина Металлургия Метрология Образование Политология Производство Психология Стандартизация Технологии |
|
|
Архитектура Аудит Военная наука Иностранные языки Медицина Металлургия Метрология Образование Политология Производство Психология Стандартизация Технологии |
MS Excel для финансового анализаСтр 1 из 6Следующая ⇒
Использование программы MS Excel для финансового анализа Часть II
ОГЛАВЛЕНИЕ
Задание 1. РАБОТА СО СПИСКАМИ В MS EXCEL. ПОДВЕДЕНИЕ ИТОГОВ…………………………………………………………………..…………….3 Задание 2. РАБОТА СО СПИСКАМИ В MS EXCEL. ФИЛЬТРАЦИЯ СПИСКОВ………………………………………………………………………….…..8 Задание 3. ФИНАНСОВЫЙ АНАЛИЗ В MS EXCEL. АНАЛИЗ ИНВЕСТИЦИЙ………………………….……………………………………………13 Задание 4. РАСЧЕТ ЭФФЕКТИВНОСТИ НЕРАВНОМЕРНЫХ КАПИТАЛОВЛОЖЕНИЙ С ПОМОЩЬЮ…………………………………………36 Задание 5. РЕШЕНИЕ ЗАДАЧ ОПТИМИЗАЦИИ СРЕДСТВАМИ MS EXCEL………………………………………………………………………………....42 Задание 6. МОДЕЛИ ПРОГНОЗИРОВАНИЯ………………………………………56
Задание 1. РАБОТА СО СПИСКАМИ В MS EXCEL. ПОДВЕДЕНИЕ ИТОГОВ 1.1. Подведение промежуточных итогов Команда Данные Þ Итоги может быть использована для получения различной итоговой информации. Прежде чем подводить итоги, необходимо произвести сортировку списка соответствующим образом. Excel создает промежуточные и общие итоги. При выводе промежуточных итогов Excel всегда создает структуру списка; с помощью символов структуры можно отобразить список с нужным уровнем детализации данных. Ниже рассмотрим пример, когда необходимо подсчитать для каждого отдела предприятия сумму окладов сотрудников. Задание 1 1. Запустите Excel. 2. Переименуйте первый лист в ИТОГИ_1. 3. Создайте на листе базу данных сотрудников согласно табл. 1. Таблица 1 – База данных сотрудников
4. Отсортируйте исходный список по полю Отдел. 5. Выполните команду Данные Þ Итоги. 6. В списке при каждом изменении укажите поле Отдел (рис. 1). Так как список был отсортирован по полю Отдел, то строки с одинаковым отделом располагаются непосредственно рядом друг с другом. Как только происходит изменение в поле Отдел, значит, информация о сотрудниках одного отдела закончилась, и далее следуют строки, касающиеся сотрудников другого отдела. 7. В списке Операция выберите Сумма – это операция, с помощью которой будут подводиться итоги (данные будут суммироваться). 8. В списке Добавить итоги по укажите поля Оклад и Годовой фонд зарплаты – по этим полям будут подводиться итоги. 9. Установите соответствующие флажки, как показано на рис. 1. Нажмите кнопку ОК.
Рис. 1. Диалоговое окно «Промежуточные итоги» 10. Результат подведения итогов приведен на рис. 2.
Рис. 2. Результат применения функции «Промежуточные итоги» 11. Для того чтобы просмотреть только промежуточные и общие итоги, щелкните на кнопке
Рис. 3. Сворачивание «Промежуточных итогов» до 2-го уровня 12. Чтобы вернуть список в первоначальное положение, щелкните на кнопке
13. Отобразите только общие итоги (рис. 4).
Рис. 4. Сворачивание «Промежуточных итогов» до 3-го уровня 14. Разверните весь список. Исследуйте назначение кнопок 15. Сохраните файл и продемонстрируйте результат преподавателю.
1.2. Функции баз данных Функции баз данных имеют обобщенное название Д-функции. Д- функции оперируют только с элементами диапазона, которые удовлетворяют заданным условиям. У всех Д-функций один и тот же синтаксис: =Д-функция (база_данных; поле; критерий) Аргумент база_данных задает весь список, а не отдельный столбец. Аргумент поле определяет столбец, в котором производятся вычисления (суммирование, усреднение и т. п.). Если есть формула, с помощью мастера функций при заполнении второго аргумента достаточно указать ячейку рабочего листа, в котором хранится имя соответствующего поля. Аргумент критерий задает диапазон критериев.
1.2.1. Функция БДСУММ 1. Переименуйте второй лист в Д-Функ. 2. Скопируйте на лист базу данных сотрудников с листа ИТОГИ_1. 3. Выделите всю таблицу (диапазон А1: G16) и присвойте ей имя База (Выделите диапазон, вызовите контекстное меню правой кнопкой, выберите Имя диапазона…). 4. Определите сумму окладов, превышающих 8 000р. Для этого после таблицы введите следующий критерий (рис. 5).
Рис. 5. Сумма окладов 5. Активизируйте ячейку В20. Вызовите Мастер функций, выберите категорию функций Работа с базой данных и функцию БДСУММ. 6. Заполните поля Мастера функций (рис. 6).
Рис. 6. Диалоговое окно функции БДСУММ 7. Подсчитайте суммарный годовой фонд зарплаты по отделу Бухгалтерия. Для этого определите критерий (рис. 7).
Рис. 7. Фонд ЗП по отделу «Бухгалтерия» 8. Заполните поля мастера функций в соответствии с рис. 8.
Рис. 8. Диалоговое окно функции БДСУММ 9. Сравните полученное значение с промежуточными итогами с листа ИТОГИ_1. 10.Аналогично вычислите суммарные значения годового фонда зарплаты по остальным отделам. 11.Сохраните файл и продемонстрируйте результат преподавателю.
1.2.2. Функция БДСЧЕТ 1. Определите количество сотрудников, принятых в период с 01.01.2000 по 31.12.2003. Для этого оформите данные, как показано на рис. 9.
Рис. 9. Определение количества сотрудников 2. Заполните поля мастера функций в соответствии с рис. 10.
Рис. 10. Диалоговое окно функции БДСУММ
3. Подсчитайте количество сотрудников, имеющих оклад в диапазоне от 7 000 руб. до 10 000 руб.
1.2.3. Функция ДСРЗНАЧ 1. Самостоятельно ознакомьтесь с функцией ДСРЗНАЧ. 2. Определите величины средних окладов по каждому из отделов. 3. Определите среднюю величину годового фонда зарплаты сотрудников, принятых на работу в 2000 г.
1.2.4. Функции ДМАКС и ДМИН 1. Самостоятельно ознакомьтесь с функциями ДМАКС и ДМИН. 2. Определите величины наибольших и наименьших окладов по каждому из отделов. 3. Определите наибольшую и наименьшую величину годового фонда зарплаты сотрудников, принятых на работу в 1999 г.
Задание 2. РАБОТА СО СПИСКАМИ В MS EXCEL. ФИЛЬТРАЦИЯ СПИСКОВ Фильтрация списка – это процесс сокрытия всех строк, кроме тех, которые удовлетворяют определенным критериям. Например, если у вас есть список клиентов, его можно отфильтровать так, чтобы видеть фамилии только тех из них, которые живут в определенном городе. В данной работе будет рассмотрена автоматическая фильтрация. 2.1. Фильтрация по одному критерию 1. Создайте новую рабочую книгу Excel. 2. Переименуйте первый лист в Фильтр и введите данные, в соответствии с рис. 11, (можете использовать данные из предыдущего задания). 3. Установите табличный курсор на одну из ячеек списка. 4. Выполните команду Данные Þ Фильтр Þ Автофильтр. Excel проанализирует список и добавит в строку заголовков полей кнопки раскрывающихся списков (кнопки автофильтра):
Рис. 11.Список с фильтрами
5. Щелкните на кнопке автофильтра в ячейке Отдел. Список раскроется и покажет все значения, содержащиеся в этом столбце (рис. 12).
Рис. 12. Список с фильтрами
6. Выберите наименование какого-нибудь отдела, и Excel спрячет все строки, кроме тех, которые включают отмеченное значение (рис. 13). Другими словами, критерием отбора служит выбранное вами значение.
Рис. 13. Список с фильтрами
7. Обратите внимание на то, что после фильтрации в строке состояния появилась сообщение о том, сколько строк отобрано: 8. Снова щелкните на кнопке автофильтра и выберите опцию (Все). Программа отобразит весь список полностью. 9. Сохраните файл и продемонстрируйте результат преподавателю. 2.2. Фильтрация по нескольким критериям 1. Щелкните на кнопке автофильтра в ячейке Годовой фонд заработной платы. Выберите опцию (Условие …). Открывшееся диалоговое окно Пользовательский автофильтр позволяет фильтровать списки с использованием нескольких критериев. 2. В поле Годовой фонд заработной платы из раскрывающегося списка выберите критерий больше, в поле справа введите или выберите из списка значение критерия, например, 80 000 руб. Установите переключатель И. В нижнем поле выберите критерий меньше и установите для него значение, например, 100 000 руб. (рис. 14).
Рис. 14. Окно пользовательского фильтра
3. Нажмите кнопку ОК. Таким образом, вы вывели список сотрудников с годовым фондом зарплаты в диапазоне от 80 000 руб. до 100 000 р. 2.3. Наложение условия по списку 1. Из списка кнопки автофильтра столбца Оклад выберите опцию ( Первые 10…). Название данной опции общепринятое, действие опции не ограничивается нахождением только 10 элементов. С помощью данной опции вы можете выбрать наибольшие или наименьшие элементы из списка, а также указать их количество. 2. В диалоговом окне Наложение условия по списку установите критерий Показать 5 наибольших элементов списка (рис. 15).
Рис. 15. Диалоговое окно «Наложение условия по списку» 3. Программа выведет сведения о 5 сотрудниках, имеющих наибольший месячный оклад. 4. Сохраните файл и продемонстрируйте результат преподавателю.
Контрольное задание 1. Выведите список, состоящий из трех сотрудников, принятых на работу раньше других. Восстановите весь список, а затем найдите трех сотрудников, принятых позже всех. 2. Выведите список сотрудников, работающих в одном отделе. Среди оставшихся – сотрудника с наибольшей заработной платой. Для отмены режима Автофильтр и удаления кнопок раскрывающихся списков в именах полей выберите команду Данные Þ Фильтр Þ Автофильтр повторно. В результате будет удален флажок у пункта меню Автофильтр, и список вернется в обычное состояние. 2.4. Сортировка списка Сортировкой называется изменение порядка строк в списке. Сортировка применяется в случаях, когда необходимо, чтобы строки имели определенную последовательность. Excel – очень гибкая система в отношении методов сортировки данных.
2.4.1. Сортировка по одному полю 1. Активизируйте лист Сортировка. При отсутствии исходного файла перейдите на Лист 2, назовите его Сортировка и скопируйте на лист базу данных сотрудников. 2. Установите табличный курсор в ячейку с фамилией первого сотрудника. Щелкните на кнопке сортировки Сортировка по возрастанию 3. Щелкните на кнопке сортировки Сортировка по убыванию 4. Сохраните файл и продемонстрируйте результат преподавателю.
Контрольное задание 1. Отсортируйте список так, чтобы сведения о сотрудниках располагались в порядке возрастания (убывания) окладов. 2. Представьте список так, чтобы сведения о сотрудниках располагались в порядке возрастания (убывания) даты приема на работу.
2.4.2. Сортировка по нескольким полям 1. Выполните сортировку по нескольким полям, например, сначала по полю Фамилия, затем по полю Годовой фонд зарплаты. Для этого выберите команду Данные Þ Сортировка. 2. В диалоговом окне Сортировка диапазона в списке Сортировать по выберите поле ФИО, установите опцию по возрастанию. В списке Затем по выберите поле Годовой фонд зарплаты, установите опцию по убыванию (рис. 16). Нажмите кнопку ОК.
Рис. 16. Сортировка диапазона
3. Сохраните файл и продемонстрируйте результат преподавателю.
Задание 3. ФИНАНСОВЫЙ АНАЛИЗ В MS EXCEL. АНАЛИЗ ИНВЕСТИЦИЙ Финансовый анализ данных в MS Excel осуществляется с помощью финансовых функций. Категория финансовых функций включает функции, предназначенные для выполнения денежных расчетов. Среди финансовых функций можно выделить три группы: - функции для анализа инвестиций; - функции расчета амортизации; - функции работы с ценными бумагами. В данной работе рассмотрено использование функций анализа инвестиций. Многие функции для анализа инвестиций имеют одинаковые аргументы. Эти аргументы представлены в табл. 2. Таблица 2 – Аргументы для анализа инвестиций
Финансовые функции связаны с интервалами выплат. Аннуитет – это ряд постоянных денежных выплат, делаемых в течение длительного периода. Например, заем под автомобиль или заклад являются аннуитетами. В функциях, связанных с аннуитетами, выплачиваемые денежные средства, такие как депозит на сбережения, представляются отрицательным числом; полученные денежные средства, такие как чеки на дивиденды, представляются положительным числом. Например, депозит в банк на сумму 1000 руб. представляется аргументом –1000 – для вкладчика – и аргументом 1000 – для банка. 3.1. Расчет текущей стоимости инвестиции. Функция ПС (ПЗ) Функция ПС (приведенная стоимость) является одним из наиболее распространенных способов оценки привлекательности долговременных вложений. Текущее значение вложения (чистый текущий объем вклада) определяется дисконтированием (приведением к стоимости на настоящий момент) поступлений по этому вложению. Если текущая стоимость поступлений оказывается больше вклада, вложение считается удачным. Синтаксис функции: ПС (Ставка; Кпер; Плт; Бс; Тип). Функция ПС вычисляет текущую стоимость ряда равных по величине периодических выплат или единовременной выплаты. Для вычисления текущей стоимости ряда выплат используется аргумент Плт (выплата). Для вычисления текущей стоимости единовременной выплаты используется аргумент Бс (будущая стоимость). Для вложения с рядом периодических выплат и единовременной выплаты используются оба эти аргумента. Задание 1 Определение текущей стоимости ряда периодических выплат. Существует два варианта вложения денежных средств с целью заработать 5 000 руб. в течение 2, 5 лет: 1. Совершить сделку, которая каждые полгода возвращает 1 000 руб. в течение следующих 2, 5 лет. Для этого нужно вложить 4 000р. 2. Вложить деньги в банк на краткосрочный счет под 8, 0 % с выплатами каждые полгода в размере 1 000 руб. Определить, какой из вариантов вложения денежных средств является более выгодным. Пояснения к задаче Решение этой задачи требует оценить текущую стоимость ряда поступлений по 1 000 руб. Проценты, которые предлагает банк, послужат в качестве учетной ставки вложения. Учетная ставка является своего рода «барьером», который должен быть превзойден, прежде чем инвестиция станет привлекательной, поэтому ее часто называют барьерной ставкой. Для определения текущей стоимости вложения используется функция ПС, в которой будет задействован аргумент Плт и не задействован аргумент Бс. Ход решения задачи 1. Запустите Excel. 2. Переименуйте первый лист в ПС. 3. Оформите таблицу согласно (рис. 17).
Рис. 17. Исходные данные для расчета текущей стоимости ряда периодических выплат
4. Вычислите количество периодов выплат и ставку за период согласно рис. 18). 5. Запустите Мастер функций, ознакомьтесь с функцией ПС (ПЗ). 6. Вычислите текущую стоимость инвестиции. Результаты сравните с рис. 18.
Рис. 18. Результаты вычисления текущей стоимости ряда периодических выплат
7. Сохраните файл и продемонстрируйте результат преподавателю. Анализ результатов решения Функция возвращает значение –4 451, 82. Это означает, что нужно вложить в банк 4 451, 82 руб. под 8, 0 % годовых сегодня, чтобы получить 5 000 руб. в течение следующих 5 лет. Поскольку в предлагаемой сделке вклад равен 4 000 руб., можно считать, что совершить сделку выгоднее, чем вложить деньги в банк. Задание 2 Пояснения к задаче Решение этой задачи требует оценить текущую стоимость вложения в банк по условиям указанных сделок и сравнить полученные значения с текущей стоимостью сделок. На основании сравнения необходимо сделать выводы о выгодности вложений. Ход решения задачи 1. Запустите Excel. 2. Переименуйте первый лист в ПС. 3. Оформите таблицы согласно (рис. 23).
Рис. 23. Исходные данные для контрольного задания 4. Для каждого из вариантов вычислите текущую стоимость инвестиции. 5. Проанализируйте полученные значения, определите наиболее выгодный вариант вложения. 6. Сохраните файл и продемонстрируйте результат преподавателю. 3.2. Расчет будущей стоимости инвестиции. Функция БС (БЗ) Функция БС (будущая стоимость) вычисляет для некоторого будущего момента времени величину вложения, которая образуется в результате единовременной выплаты или/и ряда постоянных периодических платежей. Синтаксис функции: БС (Ставка; Кпер; Плт; Пс; Тип). Для определения будущего значения ряда выплат используется аргумент Плт (выплата), аргумент Пс (приведенная стоимость) служит для определения будущего значения единовременной выплаты. Задание 4 Определение наиболее выгодного варианта вложения денежных средств Вкладчик планирует открыть счет сроком на 5 лет с ежемесячными платежами и рассчитывает на среднюю скорость оборота 11 % в год. Выплаты производятся в начале периода. Существует несколько вариантов вложения: 1. Вносить на счет 2 000 руб. каждый месяц в течение 5 лет. 2. Вложить 100 000 руб. с целью получения по истечении 5 лет единовременной выплаты. 3. Внести на счет 30 000 р. и далее вносить на счет 1 500 руб. каждый месяц в течение 5 лет. Определите наиболее выгодный вариант вложения денежных средств. Пояснения к задаче Решение этой задачи требует оценить текущую стоимость вложения в банк по условиям указанных сделок и сравнить полученные значения с текущей стоимостью сделок. На основании сравнения необходимо сделать выводы о выгодности вложений. Для определения текущей стоимости вложения используется функция БС. Аргументу Тип задано значение 1 (вложения производятся в начале каждого периода). Ход решения задачи 1. Запустите Excel. 2. Переименуйте первый лист в БС. 3. Оформите таблицы согласно (рис. 24).
Рис. 24. Исходные данные для расчета будущих стоимостей инвестиций 4. Для каждого из вариантов вычислите будущую стоимость инвестиции. 5. Проанализируйте полученные значения, определите наиболее выгодный вариант вложения. 6. Сохраните файл и продемонстрируйте результат преподавателю.
3.3. Расчет периодического платежа. Функция ПЛТ (ППЛАТ) Функция ПЛТ вычисляет размер периодической выплаты, необходимой для погашения ссуды за указанное число периодов. Синтаксис функции: ПЛТ (Ставка; Кпер; Пс; Бс; Тип). Задание 5 Задание 6 Взята ссуда размером 50 000 руб. на срок 2 года под 6 % годовых с ежеквартальной выплатой платежей. Необходимо составить график погашения займа, включающий платежи по процентам и основные платежи. Ход решения задачи 1. Запустите Excel. 2. Переименуйте первый лист в ПЛТ. 3. Оформите таблицу согласно (рис. 27).
Рис. 27. Исходная таблица для составления графика погашения займа
4. Используя функцию ПРПЛТ, вычислите величину платежа по процентам для 1-го квартала, необходимые данные возьмите из задания 5 (рис. 26). 5. Предусмотрев абсолютные ссылки, скопируйте формулу в ячейки для значений платежей по процентам остальных кварталов (рис. 28).
Рис. 28. Расчет платежей по процентам
6. С помощью функции ОСПЛТ вычислите величину основного платежа для 1-го квартала, используя необходимые значения из условия задачи задания 5 (рис. 26). 7. Предусмотрев абсолютные ссылки, скопируйте формулу в ячейки для значений основных платежей остальных кварталов (рис. 29).
Рис. 29. Расчет платежей по основной сумме 8. Определите итоговые величины платежей, суммировав платежи по процентам и основные платежи. Обратите внимание, что платежи должны быть одинаковыми для всех кварталов и равны значению периодического платежа задачи 6. 9. Вычислите итоговые значения всех видов платежей. 10. Результаты сравните с рис. 30.
Рис. 30. Расчет платежей за период
11.Сохраните файл и продемонстрируйте результат преподавателю.
3.5. Расчет накоплений выплат по займу. Функции ОБЩПЛАТ, ОБЩДОХОД Функция ОБЩПЛАТ возвращает накопленный доход по займу между двумя периодами выплат. Эта функция будет доступной, если подключить надстройку Пакет анализа, выполнив команду Сервис Þ Надстройки. Синтаксис функции: ОБЩПЛАТ (Ставка; Кол_пер; Нз; Нач_период; Кон_период; Тип). - ставка – это процентная ставка; - кол_пер – это общее количество периодов выплат; - нз – это стоимость инвестиции на текущий момент. Аргумент Нач_период – это номер первого периода, участвующего в вычислениях. Периоды нумеруются, начиная с 1. Аргумент Кон_период служит для того, чтобы задать номер последнего периода, участвующего в вычислениях. Тип – это выбор времени платежа
Функция ОБЩПЛАТ очень близка по смыслу функции ПРПЛТ. Если функция ПРПЛТ вычисляет плату по процентам за отдельный период, то функция ОБЩПЛАТ может вычислить плату по процентам как за отдельный период, так и за несколько периодов. Примечание: Убедитесь, что вы последовательны в выборе единиц измерения аргументов ставка и кол_пер. Если по четырехгодичному займу при ставке 10 процентов годовых делаются ежемесячные выплаты, то нужно использовать значение 10 % / 12 для задания аргумента ставка и значение 4 · 12 для задания аргумента кол_пер. Если по тому же займу делаются ежегодные выплаты, то нужно использовать значение 10 % для задания аргумента ставка и значение 4 для задания аргумента кол_пер. Кол_пер, нач_период, кон_период и тип усекаются до целых. Если ставка ≤ 0, кол_пер ≤ 0 или нз ≤ 0, то функция ОБЩПЛАТ возвращает значение ошибки #ЧИСЛО! Если нач_период < 1, кон_период < 1 или нач_период > кон_период, то функция ОБЩПЛАТ возвращает значение ошибки #ЧИСЛО!. Если тип является любым числом, кроме 0 и 1, то функция ОБЩПЛАТ возвращает значение ошибки #ЧИСЛО! Функция ОБЩДОХОД возвращает основные выплаты по займу между двумя периодами. Эта функция будет доступной, если подключить надстройку Пакет анализа, выполнив команду Сервис Þ Надстройки. Синтаксис функции: ОБЩДОХОД (Ставка; Кол_пер; Нз; Нач_период; Кон_период; Тип). Аргумент Нач_период – это номер первого периода, участвующего в вычислениях. Периоды нумеруются, начиная с 1. Аргумент Кон_период служит для того, чтобы задать номер последнего периода, участвующего в вычислениях. Функцию ОБЩДОХОД можно назвать расширением функции ОСПЛТ. Функция ОСПЛТ вычисляет основные платежи в конкретный период. Результатом функции ОБЩДОХОД может быть размер основных платежей как за конкретный период, так и за несколько периодов.
Задание 7 Взята ссуда размером 50 000 руб. на срок 2 года под 6 % годовых с ежеквартальной выплатой платежей. Необходимо составить график погашения займа, включающий платежи по процентам и основные платежи. Ход решения задачи 1. Запустите Excel. 2. Переименуйте первый лист в ПЛТ. 3. Оформите таблицу согласно (рис. 31).
Рис. 31. Составление графика платежей
4. Вычислите величину выплаты по процентам с помощью функции ОБЩПЛАТ, используя необходимые значения из условия задачи 6 и указав в качестве начального и конечного периодов соответственно 1 и 8 квартал. 5. Аналогичным образом вычислите величину выплаты по основной сумме с помощью функции ОБЩДОХОД. 6. Определите общую сумму выплат, суммировав величины выплат по процентам и по основной сумме. 7. Результаты сравните с рис. 32.
Рис. 32. Результаты вычислений выплат
8. Определите величину накопления по процентам для 1-го квартала, используя необходимые значения из условия задачи 6. 9. Предусмотрев абсолютные ссылки, скопируйте формулу в ячейки для значений накоплений по процентам остальных кварталов. 10.Результаты сравните с рис. 33.
Рис. 33. Результаты вычислений выплат и накоплений по процентам
11.Вычислите величину накопления по основной сумме для 1-го квартала, используя необходимые значения из условия задачи 6. 12.Предусмотрев абсолютные ссылки, скопируйте формулу в ячейки для значений накоплений по основной сумме остальных кварталов. 13.Результаты сравните с рис. 34.
Рис. 34. Результаты вычислений выплат, накоплений по процентам и по основной сумме
14.Определите остаток долга, как разницу между суммой займа и накоплением по основной сумме. 15.Результаты сравните с рис. 35.
Рис. 35. Результаты вычислений выплат, накоплений по процентам, по основной сумме и остаток долга
16.Сохраните файл и продемонстрируйте результат преподавателю. Контрольное задание Рассчитайте размер ежегодной выплаты для погашения ссуды размером 220 000 руб., взятой на 7 лет под 9 % годовых. Определите основные платежи, платежи по процентам, остаток долга после очередной выплаты, общие суммы платежей по процентам, по основной сумме, платежей за период. Ход решения задачи 1. Запустите Excel. 2. Переименуйте первый лист в ПЛТ. 3. Оформите таблицу согласно (рис. 36).
Рис. 36. Размер ежегодной выплаты для погашения ссуды
4. Вычислите необходимые значения в соответствии с условием задачи. 5. Сохраните файл и продемонстрируйте результат преподавателю.
3.6. Расчет общего числа периодов выплат инвестиции. Функция КПЕР Функция КПЕР вычисляет, сколько периодов необходимо для погашения ссуды при заданной величине периодических выплат. Синтаксис функции: КПЕР (Ставка; Плт; Пс; Бс; Тип).
Задание 8 Расчет срока ссуды В какой срок будет погашен долг в размере 100 000 руб., взятый под 8 % годовых, если выплачивать ежемесячно по 1 000 руб.? Определите сумму комиссионных и общую сумму выплат по долгу. Ход решения задачи 1. Запустите Excel. 2. Переименуйте первый лист в КПЕР. 3. Оформите таблицу согласно (рис. 37).
Рис. 37. Исходные данные для расчета срока инвестиции
4. Вычислите количество периодов выплат с помощью функции КПЕР. 5. Определите срок инвестиции, разделив количество выплат на их периодичность. 6. Результаты сравните с рис. 38.
Рис. 38. Результаты вычисления срока инвестиции
7. Сохраните файл и продемонстрируйте результат преподавателю. Контрольное задание Вы берете в долг 320 000 руб. Под 4, 5 % годовых и собираетесь выплачивать по 36 000 руб. в год. Сколько лет займут выплаты? Определите размер комиссионных и сумму выплат по займу. Ход решения задачи 1. Запустите Excel. 2. Переименуйте первый лист в КПЕР. 3. Оформите таблицу согласно (рис. 39).
Рис. 39. Исходные данные для определения размера комиссионных выплат по займу
4. Вычислите необходимые значения в соответствии с условием задачи. 5. Сохраните файл и продемонстрируйте результат преподавателю.
3.7. Расчет нормы прибыли инвестиции. Функция СТАВКА (НОРМА) Функция СТАВКА позволяет определить скорость оборота (норму прибыли) вложения по ряду постоянных периодических выплат или/и по единовременной выплате. Другими словами, функция вычисляет процентную ставку за один период, необходимую для получения определенной суммы в течение заданного срока путем постоянных взносов. Синтаксис функции: СТАВКА (Кпер; Плт; Пс; Бс; Тип; Предположение).
Аргумент Плт (выплата) используется при определении скорости оборота для ряда постоянных периодических выплат, аргумент Бс (будущая стоимость) – при определении скорости оборота для единовременной выплаты. Аргумент Предположение (по умолчанию равен 10%) дает начальное приближение нормы, т.к. Excel использует итерационный процесс для вычисления.
Задание 9 Задание 5. РЕШЕНИЕ ЗАДАЧ ОПТИМИЗАЦИИ СРЕДСТВАМИ MS EXCEL Процесс изменения значений ячеек и анализа влияния этих изменений на результат вычисления формул в Excel называется анализом «что-если». В данной работе рассматривается процесс нахождения исходных данных, которые при подстановке в формулы, дают необходимое значение в ячейке результата. Если вы знаете, каким должен быть результат вычисления по формуле, то Excel подскажет вам значения одного или нескольких входных параметров, которые позволяют получить нужный результат. Другими словами, вы можете задать вопрос типа: Какой рост продаж необходим для получения дохода в $ 1 200 000? В Excel для этого предусмотрены два подходящих средства. 1. Подбор параметра – определяет значение одной входной ячейки, которое требуется для получения желаемого результата в зависимой ячейке (ячейке результата). 2. Поиск решения – определяет значения в нескольких входных ячейках, которые требуются для получения желаемого результата. Более того, можно накладывать ограничения на входные данные, поэтому здесь можно получить решение (если оно существует) многих практических задач. В данной работе рассмотрены обе процедуры.
5.1. Подбор параметра Подбор параметра является удобным средством для решения задач, которые имеют точное целевое значение, зависящее от одного неизвестного параметра. С помощью Подбора параметра можно определить значение, которое будет давать желаемый результат. Решите задачу Вы хотите положить деньги в банк под 4, 5 % и получить ровно 1 000 руб. по истечении года. Необходимо определить сумму вклада. Для решения данной задачи используем средство Подбор параметра. Задание 1 1. Запустите Excel. 2. Переименуйте первый лист в Подбор параметра. 3. Оформите таблицу (рис. 46). Введите указанную формулу.
Рис. 46. Исходные данные для подбора параметра 4. Перейдите в ячейку В3. Выполните команду Сервис Þ Подбор параметра. 5. В открывшемся диалоговом окне укажите значение необходимой конечной суммы и ссылку на ячейку с искомым значением вклада (рис. 47).
Рис. 47. Диалоговое окно «Подбор параметра» 6. Нажмите кнопку ОК. Средство Подбор параметра найдет решение и сообщит об этом (рис. 48). Нажмите кнопку ОК и убедитесь, что искомое значение помещено в ячейке В2.
Рис. 48. Диалоговое окно «Результат подбора параметра» 7. Сохраните файл и продемонстрируйте результат преподавателю.
5.2. Поиск решения Задачи, выполняемые с использованием процедуры поиска решения, относятся к сравнительно узкой области. Такие задачи называют оптимизационными. Обычно они затрагивают случаи, удовлетворяющие следующим условиям: 1. Значение в целевой ячейке зависит от других ячеек и формул. Нужно определить все исходные параметры, при которых значение в целевой ячейке будет максимальным, минимальным или заранее определенным. 2. Целевая ячейка зависит от группы ячеек, которые называются изменяемыми ячейками. Их значения надо подобрать так, чтобы получить желаемый результат в целевой ячейке. 3. Решение (значения изменяемых ячеек) должно находиться в определенных пределах или удовлетворять определенным ограничениям. После соответствующей подготовки рабочего листа можно использовать процедуру поиска решения для подбора значений в изменяемых ячейках и получения в целевой ячейке нужного результата, который одновременно удовлетворяет все установленным ограничениям. Примечание: если в меню Сервис не отображается команда Поиск решения, выполните команду Сервис Þ Надстройки и в диалоговом окне Надстройки установите доступность данной надстройки. Решите задачу Фирма производит две модели А и В сборных книжных полок. Их производство ограничено наличием сырья (высококачественных досок) и временем машинной обработки. Для каждого изделия модели А требуется 3 м2 досок, для изделия модели В – 4 м2. Фирма может получать от своих поставщиков до 1700 м2 досок в неделю. Для каждого изделия модели А требуется 12 мин. машинного времени, для изделия модели В – 30 мин. В неделю можно использовать 160 ч машинного времени. Сколько изделий какой модели следует выпускать фирме в неделю, если каждое изделие модели А приносит 2 доллара прибыли, каждое изделие В – 4 доллара прибыли?
Задание 2 1. Переименуйте второй лист в Поиск решения, создайте на нем таблицу согласно рис. 49.
Рис. 49. Исходные данные для задачи на поиск решения
2. Для решения задачи введем две переменные: - X – количество изделий модели А; - Y – количество изделий модели В. 3. Ячейки, содержащие переменные, будут являться изменяемыми, т.к. от них будет зависеть результат в целевой ячейке. Присвойте ячейкам В2 и В3 соответственно имена Х и Y. Для этого активизируйте ячейку В2 и выполните команду Вставка Þ Имя Þ Присвоить. В поле Имя введите новое имя для ячейки В2: Х. Нажмите кнопку ОК. Аналогичным способом присвойте ячейке В3 имя Y. Целевой функцией будет являться выражение: 2 · X + 4 · Y. В ячейку В6 введите формулу для вычисления прибыли (рис. 50).
Рис. 50. Формула для расчета прибыли
Но у нас присутствуют ограничения, описанные в условии задачи: - ограничение количества материала для полок в неделю: 3 · X + 4 · Y £ 1700, (6) - ограничение количества машинного времени в неделю: (12 / 60) · X + (30 / 60) · Y £ 160 Þ 0, 2 · X+0, 5 · Y £ 160. (7) 4. Введите формулы ограничений в ячейку В9: =3 · X + 4 · Y. (8) В ячейку В10: =0, 2 · X + 0, 5 · Y (рис. 51). (9)
Рис. 51. Формулы для расчета материала и времени изготовления
Необходимо учесть, что количество изделий – неотрицательное число, поэтому добавляются еще два ограничения: X ³ 0 и Y ³ 0. Количества изделий должны быть целыми числами: X - целое и Y - целое. 5. Установите курсор в ячейку целевой функции В6. 6. Выполните команду Сервис Þ Поиск решения. 7. В окне Поиск решения проверьте, чтобы в поле Установить целевую ячейку стояла ссылка на ячейку с целевой функцией (рис. 52).
Рис. 52. Диалоговое окно «Поиск решения: установление целевой ячейки»
8. В поле Равной установите переключатель Максимальному (если в условиях задачи будет задано «минимизировать затраты», то необходимо будет переключить в Минимальному, если же будет определено конкретное значение, то выбираем значение и указываем это число) значению (рис. 53).
Рис. 53. Диалоговое окно Поиск решения: установление изменяемых ячеек
9. В поле Изменения ячейки укажите диапазон изменяемых ячеек (это те ячейки в которых фиксируется количество выпускаемой продукции) (см. рис. 52). 10.В поле Ограничения задайте ограничения. Для этого нажмите кнопку Добавить, расположенную рядом с данным полем. 11.В появившемся диалоговом окне Добавление ограничения в поле Ссылка на ячейку укажите ячейку с функцией ограничения материала, в следующем поле из списка выберите оператор £ и в поле Ограничение введите число 1700 (рис. 54). Нажмите кнопку Добавить.
Рис. 54. Диалоговое окно «Добавление ограничения» 12.Таким же образом введите оставшиеся три ограничения и нажмите кнопку ОК. 13.Проверьте правильность ввода данных в окне Поиск решения (рис. 55) и нажмите кнопку Выполнить.
Рис. 55. Диалоговое окно «Поиск решения»
14.В появившемся окне Результаты поиска решения выберите переключатель Сохранить найденное решение (рис. 56).
Рис. 56. Диалоговое окно «Результаты поиска решения»
15.В изменяемых ячейках появятся значения, являющиеся оптимальными для поставленных условий, в ячейке с целевой функцией отобразится наибольшее значение прибыли (рис. 57).
Рис. 57. Результаты решения задачи на поиск решения
16.Сохраните файл и продемонстрируйте результат преподавателю.
5.3. Создание сценариев При решении оптимизационных задач часто возникает необходимость сохранить варианты решения, имеющие множество исходных данных, причем необходимо четко представлять, как изменения исходных данных первых влияют на результат. Ощутимую помощь в анализе такого рода задач могут оказать сценарии Excel. Сценарий Excel – это инструмент, позволяющий моделировать различные физические, экономические, математические и другие задачи. Он представляет собой зафиксированный в памяти компьютера набор значений ячеек рабочего листа. Используя сценарии, можно сохранить в памяти компьютера несколько наборов исходных данных так, чтобы их можно было быстро загрузить (и получить результат, соответствующий этому набору исходных данных). Таким образом, создав сценарий, пользователь получает возможность узнать, что произойдет с результатом, если поменять исходные значения в некоторых ячейках листа. Кроме того, в случае необходимости всегда можно вернуться к одному из вариантов, рассмотренных ранее. Сценарии Excel можно использовать не только при работе с решениями оптимизационных задач. Сценарии очень удобны при решении задач подбора параметров и вообще в тех случаях, когда необходимо зафиксировать несколько различных наборов исходных данных, содержащих большое количество.
Задание 3 1. Для раннее выполненной задачи (на рабочем листе Поиск решения) запустите вновь Поиск решения. 2. В появившемся окне Результаты поиска решения (рис. 58) нажмите на кнопку Сохранить сценарий...
Рис. 58. Диалоговое окно «Результаты поиска решения»
3. В новом окне задайте имя сценария Материал_1700 (рис. 59). Нажмите кнопку ОК. Далее закончите процедуру Поиска решения.
Рис. 59. Диалоговое окно «Сохранение сценария»
4. Снова запустите решение задачи. Измените ограничение на расход материала (рис. 60).
Рис. 60. Диалоговое окно «Изменение ограничения»
5. Выполните поиск решения и сохраните решение в сценарии под именем Материал_2000. 6. Выполните команду Сервис Þ Сценарии... 7. В открывшемся Диспетчере сценариев выделите сценарий в нажмите на кнопку Вывести (рис. 61).
Рис. 61. Диалоговое окно «Диспетчер сценариев»
8. Убедитесь, что значения в ячейках таблицы изменились в соответствии со сценарием. 9. Сохраните файл и продемонстрируйте результат преподавателю.
5.4. Создание таблиц подстановки При работе с моделью «что, если» в определенный момент времени можно использовать только один сценарий (только один набор исходных данных). Возникают случаи, когда необходимо сравнить результаты нескольких сценариев. Для этого используются так называемые таблицы подстановки. Таблицы подстановки позволяют производить вычисления по формулам, для одного из нижеприведенных случаев: - имеется один набор данных для одной ячейки (одной переменной), на которую ссылаются несколько формул. В этом случае создается так называемая таблица подстановки с одним входом; - имеются два набора данных для двух ячеек (две переменные), на которые ссылается одна формула. Создаваемая в этом случае таблица называется таблицей подстановки с двумя входами. 5.4.1. Создание таблиц подстановки с одним входом Задание 4 1. Для ранее рассмотренной задачи на рабочем листе Поиск решения загрузите сценарий Материал_1700. 2. Оформите таблицу подстановки (рис. 62). Введите количество изделия А от 25 до 500 с шагом 25 (используйте функцию Автозаполнение ).
Рис. 62. Макет таблицы 3. В ячейку В15 скопируйте формулу для расчета прибыли (ячейка В6). Таким же образом скопируйте формулы для расчета расхода материала и времени изготовления (рис. 63). Помните, что формулы в таблице подстановки должны в точности повторять формулы в исходной таблице поиска решения.
Рис. 63. Формулы для расчета 4. Выделите таблицу подстановки – диапазон А15: D35 (выделенный диапазон должен включать формулы и значения переменной). 5. Выполните команду Данные Þ Таблица подстановки... 6. Набор значений переменной (количество изделий А) расположен по строкам, поэтому в открывшемся диалоговом окне активизируйте поле Подставлять значения по строкам в. 7. При осуществлении поиска решений значения переменной будут подставляться в исходную таблицу поиска решения в ячейку с количеством изделий А (ячейка В2). В поле Подставлять значения по строкам в укажите адрес ячейки В2 (рис. 64). Нажмите кнопку ОК.
Рис. 64. Диалоговое окно «Таблица подстановки»
8. Убедитесь, что Excel заполнил таблицу соответствующими результатами. 9. Загрузите любой из имеющихся сценариев: значения в созданной таблице будут автоматически пересчитываться. 10.Сохраните файл и продемонстрируйте результат преподавателю и продемонстрируйте результат преподавателю.
5.4.2. Создание таблиц подстановки с двумя входами Задание 5 1. На листе Поиск решения оформите таблицу подстановки: используя Автозаполнение, введите по строкам значения количества изделия А от 25 до 500 с шагом 25, по столбцам – значения количества изделия В от 25 до 500 с шагом 25 (рис. 65).
Рис. 65. Исходные данные для создания таблицы подстановки 2. В ячейку В38 скопируйте формулу для расчета прибыли (рис. 66).
Рис. 66. Создание формулы для расчета прибыли 3. Выделите диапазон ячеек В38: V58 (диапазон должен включать наборы значений обеих переменных). 4. Выполните команду Данные Þ Таблица подстановки... 5. Набор значений количества изделий В расположен по столбцам, поэтому в поле Подставлять значения по столбцам в: укажите ссылку на соответствующую ячейку (рис. 66). 6. Набор значений количества изделий А расположен по строкам, поэтому в поле Подставлять значения по строкам в: укажите ссылку на соответствующую ячейку (рис. 67).
Рис.67. Диалоговое окно «Таблица подстановки» 7. Нажмите кнопку ОК. Таблица подстановки заполнится. 8. В таблице выделите значение прибыли, соответствующее текущим параметрам поиска решения (рис. 68).
Рис. 68. Заполненная данными таблица подстановки 9. В ячейку В50 вместо значения 300 введите значение 305, проследите, как изменятся значения прибыли. 10. Сохраните файл и продемонстрируйте результат преподавателю.
Задание 6. МОДЕЛИ ПРОГНОЗИРОВАНИЯ Изучение методов прогнозирования значений экономических показателей с помощью функций и пакета анализа в Excel. Для оценки будущих показателей на основе значений этих показателей за прошедшие периоды временного ряда используют различные методы прогнозирования. В данной работе предлагается изучить следующие методы прогнозирования: - скользящее среднее; - составление линейных прогнозов в однофакторном регрессионном анализе; - составление нелинейных прогнозов в однофакторном регрессионном анализе; - экспоненциальное сглаживание. 6.1. Прогнозы с применением метода скользящего среднего Задание 1 Составьте прогнозы объема продаж продукции предприятием в условиях, когда в течение последних двух лет происходило значительное сокращение штата торговых работников. Прогнозы выполните несколькими способами. Прогнозные значения сравните с фактическими данными. Ход решения задачи 1. Внесите в столбец А, начиная с ячейки А1, следующие фактические данные по объемам продаж за прошедшие месяцы: 593, 570, 486, 854, 797, 362, 594, 271, 45, 254, 433, 529, 994, 319, 610, 748. 832, 193, 720, 415, 536, 850, 201, 833. Эти данные называются базовой линией. Примечание: базовая линия – это числовое выражение результатов наблюдений на протяжении длительного периода времени. Каждое ее значение – это данное, снятое в одинаковые временные интервалы. Пропуск данных не допускается. Если данное отсутствует, то его нужно восполнить его хотя бы как среднее между соседними значениями. 2. Составьте прогноз на каждый месяц заданного временного ряда, используя метод скользящего среднего. В этом методе для усреднения используйте интервал в три месяца, предшествующих прогнозу. Составьте сначала прогноз по функции СРЗНАЧ, введя в ячейку В4 формулу =СРЗНАЧ(А1: АЗ) и затем скопировав ее в ячейки В5: В24. 3. Составьте этот же прогноз с помощью надстройки Пакет анализа, выполнив операции, перечисленные ниже: - выберите команду СЕРВИС > НАДСТРОЙКИ и в окне «Надстройки» установите флажок в строке Пакет анализа. Нажмите на кнопку ОК; - приступите к работе с методом: выберите СЕРВИС > АНАЛИЗ ДАННЫХ и в окне «Анализ данных» выберите инструмент анализа Скользящее среднее. Нажмите на кнопку ОК; - в окне «Скользящее среднее» в поле Входной интервал введите ряд данных вашей базовой линии А1: А24, выделив его в рабочем листе Excel (диапазон будет занесен в абсолютных ссылках); - в поле Интервал введите количество месяцев, которые вы хотите включить в подсчет скользящего среднего. В данном случае это будет число 3; - в поле Выходной интервал введите адрес ячейки, с которой хотите начать вывод результатов, щелкнув по этой ячейке. Пусть это будет ячейка С1 на том же рабочем листе. Щелкните ОК. Примечания: 1. Так как скользящее среднее в данном случае вычисляется по данным трех предшествующих месяцев, то в начальном периоде базовой линии будут потери прогнозов (Н/Д). 2. Значения скользящего среднего, полученные с помощью надстройки, смещены на одну строку вверх. 4. Составьте график данных прогноза. Для этого опять вызовите окно «Скользящее среднее» и в нем проставьте флажок Вывод графика. Будет выведена диаграмма с графиком фактических значений и прогнозом линией тренда скользящего среднего. 5. Выделите все значения прогнозов в столбце С и сместите их на одну ячейку вниз, присоединив прогнозы к тем периодам, с которыми они связаны. Сравните прогнозы с фактом. 6. Проиллюстрируйте прогноз скользящего среднего на диаграмме другим способом. Для этого: - выделите данные базовой линии А 1: А24; - выберите команду ВСТАВКА > ДИАГРАММА (можно щелкнуть кнопку Мастера диаграмм на панели инструментов) и укажите место для диаграммы на новом рабочем листе; - на первом шаге работы Мастера диаграмм проверьте правильность ссылок на ячейки базовой линии и нажмите на кнопку Далее; - на втором шаге выберите тип диаграммы График, и щелкните Далее; - на третьем шаге выберите вид графика, включающий линии и маркеры (например, вид 1, 4 или 5). Щелкните кнопку Далее; - на четвертом шаге установите для опции Ряды данных находятся переключатель В столбцах. Установите параметр 0 в опциях Считать столбцы метками оси Х и Считать стр. метками легенды. Щелкните Далее; - на последнем шаге работы Мастера определите названия диаграмм и осей, а также отображение легенды. Щелкните по кнопке Готово. Просмотрите построенный график; - откройте диаграмму с построенным графиком для редактирования, дважды щелкнув по ней кнопкой мыши. В контекстном меню на линии графика выберите команду ЛИНИИ ТРЕНДА. В окне «Линия тренда» во вкладке Тип выберите линию тренда Скользящее среднее, а затем выберите необходимый период с помощью счетчика Точки (период или интервал – это количество наблюдений, которые включаются в вычисление скользящего среднего. В данном примере это 3). Щелкните кнопку ОК; - просмотрите на диаграмме график фактических данных и линию тренда скользящего среднего (первых несколько прогнозов отсутствуют по той же причине, что и прогнозы в столбце С).
Задание 2 Используя данные задания 1, постройте прогнозы по методу скользящего среднего на интервалах данных в два месяца, а затем – в четыре месяца. Сравните прогнозы между собой и с фактическими данными. Проиллюстрируйте прогнозы на диаграммах.
Использование программы MS Excel для финансового анализа Часть II
ОГЛАВЛЕНИЕ
Задание 1. РАБОТА СО СПИСКАМИ В MS EXCEL. ПОДВЕДЕНИЕ ИТОГОВ…………………………………………………………………..…………….3 Задание 2. РАБОТА СО СПИСКАМИ В MS EXCEL. ФИЛЬТРАЦИЯ СПИСКОВ………………………………………………………………………….…..8 Задание 3. ФИНАНСОВЫЙ АНАЛИЗ В MS EXCEL. АНАЛИЗ ИНВЕСТИЦИЙ………………………….……………………………………………13 Задание 4. РАСЧЕТ ЭФФЕКТИВНОСТИ НЕРАВНОМЕРНЫХ КАПИТАЛОВЛОЖЕНИЙ С ПОМОЩЬЮ…………………………………………36 Задание 5. РЕШЕНИЕ ЗАДАЧ ОПТИМИЗАЦИИ СРЕДСТВАМИ MS EXCEL………………………………………………………………………………....42 Задание 6. МОДЕЛИ ПРОГНОЗИРОВАНИЯ………………………………………56
Задание 1. РАБОТА СО СПИСКАМИ В MS EXCEL. ПОДВЕДЕНИЕ ИТОГОВ 1.1. Подведение промежуточных итогов Команда Данные Þ Итоги может быть использована для получения различной итоговой информации. Прежде чем подводить итоги, необходимо произвести сортировку списка соответствующим образом. Excel создает промежуточные и общие итоги. При выводе промежуточных итогов Excel всегда создает структуру списка; с помощью символов структуры можно отобразить список с нужным уровнем детализации данных. Ниже рассмотрим пример, когда необходимо подсчитать для каждого отдела предприятия сумму окладов сотрудников. Задание 1 1. Запустите Excel. 2. Переименуйте первый лист в ИТОГИ_1. 3. Создайте на листе базу данных сотрудников согласно табл. 1. Таблица 1 – База данных сотрудников
4. Отсортируйте исходный список по полю Отдел. 5. Выполните команду Данные Þ Итоги. 6. В списке при каждом изменении укажите поле Отдел (рис. 1). Так как список был отсортирован по полю Отдел, то строки с одинаковым отделом располагаются непосредственно рядом друг с другом. Как только происходит изменение в поле Отдел, значит, информация о сотрудниках одного отдела закончилась, и далее следуют строки, касающиеся сотрудников другого отдела. 7. В списке Операция выберите Сумма – это операция, с помощью которой будут подводиться итоги (данные будут суммироваться). 8. В списке Добавить итоги по укажите поля Оклад и Годовой фонд зарплаты – по этим полям будут подводиться итоги. 9. Установите соответствующие флажки, как показано на рис. 1. Нажмите кнопку ОК.
Рис. 1. Диалоговое окно «Промежуточные итоги» 10. Результат подведения итогов приведен на рис. 2.
Рис. 2. Результат применения функции «Промежуточные итоги» 11. Для того чтобы просмотреть только промежуточные и общие итоги, щелкните на кнопке
Рис. 3. Сворачивание «Промежуточных итогов» до 2-го уровня 12. Чтобы вернуть список в первоначальное положение, щелкните на кнопке
13. Отобразите только общие итоги (рис. 4).
Рис. 4. Сворачивание «Промежуточных итогов» до 3-го уровня 14. Разверните весь список. Исследуйте назначение кнопок 15. Сохраните файл и продемонстрируйте результат преподавателю.
1.2. Функции баз данных Функции баз данных имеют обобщенное название Д-функции. Д- функции оперируют только с элементами диапазона, которые удовлетворяют заданным условиям. У всех Д-функций один и тот же синтаксис: =Д-функция (база_данных; поле; критерий) Аргумент база_данных задает весь список, а не отдельный столбец. Аргумент поле определяет столбец, в котором производятся вычисления (суммирование, усреднение и т. п.). Если есть формула, с помощью мастера функций при заполнении второго аргумента достаточно указать ячейку рабочего листа, в котором хранится имя соответствующего поля. Аргумент критерий задает диапазон критериев.
1.2.1. Функция БДСУММ 1. Переименуйте второй лист в Д-Функ. 2. Скопируйте на лист базу данных сотрудников с листа ИТОГИ_1. 3. Выделите всю таблицу (диапазон А1: G16) и присвойте ей имя База (Выделите диапазон, вызовите контекстное меню правой кнопкой, выберите Имя диапазона…). 4. Определите сумму окладов, превышающих 8 000р. Для этого после таблицы введите следующий критерий (рис. 5).
Рис. 5. Сумма окладов 5. Активизируйте ячейку В20. Вызовите Мастер функций, выберите категорию функций Работа с базой данных и функцию БДСУММ. 6. Заполните поля Мастера функций (рис. 6).
Рис. 6. Диалоговое окно функции БДСУММ 7. Подсчитайте суммарный годовой фонд зарплаты по отделу Бухгалтерия. Для этого определите критерий (рис. 7).
Рис. 7. Фонд ЗП по отделу «Бухгалтерия» 8. Заполните поля мастера функций в соответствии с рис. 8.
Рис. 8. Диалоговое окно функции БДСУММ 9. Сравните полученное значение с промежуточными итогами с листа ИТОГИ_1. 10.Аналогично вычислите суммарные значения годового фонда зарплаты по остальным отделам. 11.Сохраните файл и продемонстрируйте результат преподавателю.
1.2.2. Функция БДСЧЕТ 1. Определите количество сотрудников, принятых в период с 01.01.2000 по 31.12.2003. Для этого оформите данные, как показано на рис. 9.
Рис. 9. Определение количества сотрудников 2. Заполните поля мастера функций в соответствии с рис. 10.
Рис. 10. Диалоговое окно функции БДСУММ
3. Подсчитайте количество сотрудников, имеющих оклад в диапазоне от 7 000 руб. до 10 000 руб.
1.2.3. Функция ДСРЗНАЧ 1. Самостоятельно ознакомьтесь с функцией ДСРЗНАЧ. 2. Определите величины средних окладов по каждому из отделов. 3. Определите среднюю величину годового фонда зарплаты сотрудников, принятых на работу в 2000 г.
1.2.4. Функции ДМАКС и ДМИН 1. Самостоятельно ознакомьтесь с функциями ДМАКС и ДМИН. 2. Определите величины наибольших и наименьших окладов по каждому из отделов. 3. Определите наибольшую и наименьшую величину годового фонда зарплаты сотрудников, принятых на работу в 1999 г.
Задание 2. РАБОТА СО СПИСКАМИ В MS EXCEL. ФИЛЬТРАЦИЯ СПИСКОВ Фильтрация списка – это процесс сокрытия всех строк, кроме тех, которые удовлетворяют определенным критериям. Например, если у вас есть список клиентов, его можно отфильтровать так, чтобы видеть фамилии только тех из них, которые живут в определенном городе. В данной работе будет рассмотрена автоматическая фильтрация. 2.1. Фильтрация по одному критерию 1. Создайте новую рабочую книгу Excel. 2. Переименуйте первый лист в Фильтр и введите данные, в соответствии с рис. 11, (можете использовать данные из предыдущего задания). 3. Установите табличный курсор на одну из ячеек списка. 4. Выполните команду Данные Þ Фильтр Þ Автофильтр. Excel проанализирует список и добавит в строку заголовков полей кнопки раскрывающихся списков (кнопки автофильтра):
Рис. 11.Список с фильтрами
5. Щелкните на кнопке автофильтра в ячейке Отдел. Список раскроется и покажет все значения, содержащиеся в этом столбце (рис. 12).
Рис. 12. Список с фильтрами
6. Выберите наименование какого-нибудь отдела, и Excel спрячет все строки, кроме тех, которые включают отмеченное значение (рис. 13). Другими словами, критерием отбора служит выбранное вами значение.
Рис. 13. Список с фильтрами
7. Обратите внимание на то, что после фильтрации в строке состояния появилась сообщение о том, сколько строк отобрано: 8. Снова щелкните на кнопке автофильтра и выберите опцию (Все). Программа отобразит весь список полностью. 9. Сохраните файл и продемонстрируйте результат преподавателю. 2.2. Фильтрация по нескольким критериям 1. Щелкните на кнопке автофильтра в ячейке Годовой фонд заработной платы. Выберите опцию (Условие …). Открывшееся диалоговое окно Пользовательский автофильтр позволяет фильтровать списки с использованием нескольких критериев. 2. В поле Годовой фонд заработной платы из раскрывающегося списка выберите критерий больше, в поле справа введите или выберите из списка значение критерия, например, 80 000 руб. Установите переключатель И. В нижнем поле выберите критерий меньше и установите для него значение, например, 100 000 руб. (рис. 14).
Рис. 14. Окно пользовательского фильтра
3. Нажмите кнопку ОК. Таким образом, вы вывели список сотрудников с годовым фондом зарплаты в диапазоне от 80 000 руб. до 100 000 р. 2.3. Наложение условия по списку 1. Из списка кнопки автофильтра столбца Оклад выберите опцию ( Первые 10…). Название данной опции общепринятое, действие опции не ограничивается нахождением только 10 элементов. С помощью данной опции вы можете выбрать наибольшие или наименьшие элементы из списка, а также указать их количество. 2. В диалоговом окне Наложение условия по списку установите критерий Показать 5 наибольших элементов списка (рис. 15).
Рис. 15. Диалоговое окно «Наложение условия по списку» 3. Программа выведет сведения о 5 сотрудниках, имеющих наибольший месячный оклад. 4. Сохраните файл и продемонстрируйте результат преподавателю.
Контрольное задание 1. Выведите список, состоящий из трех сотрудников, принятых на работу раньше других. Восстановите весь список, а затем найдите трех сотрудников, принятых позже всех. 2. Выведите список сотрудников, работающих в одном отделе. Среди оставшихся – сотрудника с наибольшей заработной платой. Для отмены режима Автофильтр и удаления кнопок раскрывающихся списков в именах полей выберите команду Данные Þ Фильтр Þ Автофильтр повторно. В результате будет удален флажок у пункта меню Автофильтр, и список вернется в обычное состояние. 2.4. Сортировка списка Сортировкой называется изменение порядка строк в списке. Сортировка применяется в случаях, когда необходимо, чтобы строки имели определенную последовательность. Excel – очень гибкая система в отношении методов сортировки данных.
2.4.1. Сортировка по одному полю 1. Активизируйте лист Сортировка. При отсутствии исходного файла перейдите на Лист 2, назовите его Сортировка и скопируйте на лист базу данных сотрудников. 2. Установите табличный курсор в ячейку с фамилией первого сотрудника. Щелкните на кнопке сортировки Сортировка по возрастанию 3. Щелкните на кнопке сортировки Сортировка по убыванию 4. Сохраните файл и продемонстрируйте результат преподавателю.
Контрольное задание 1. Отсортируйте список так, чтобы сведения о сотрудниках располагались в порядке возрастания (убывания) окладов. 2. Представьте список так, чтобы сведения о сотрудниках располагались в порядке возрастания (убывания) даты приема на работу.
2.4.2. Сортировка по нескольким полям 1. Выполните сортировку по нескольким полям, например, сначала по полю Фамилия, затем по полю Годовой фонд зарплаты. Для этого выберите команду Данные Þ Сортировка. 2. В диалоговом окне Сортировка диапазона в списке Сортировать по выберите поле ФИО, установите опцию по возрастанию. В списке Затем по выберите поле Годовой фонд зарплаты, установите опцию по убыванию (рис. 16). Нажмите кнопку ОК.
Рис. 16. Сортировка диапазона
3. Сохраните файл и продемонстрируйте результат преподавателю.
Задание 3. ФИНАНСОВЫЙ АНАЛИЗ В MS EXCEL. АНАЛИЗ ИНВЕСТИЦИЙ Финансовый анализ данных в MS Excel осуществляется с помощью финансовых функций. Категория финансовых функций включает функции, предназначенные для выполнения денежных расчетов. Среди финансовых функций можно выделить три группы: - функции для анализа инвестиций; - функции расчета амортизации; - функции работы с ценными бумагами. В данной работе рассмотрено использование функций анализа инвестиций. Многие функции для анализа инвестиций имеют одинаковые аргументы. Эти аргументы представлены в табл. 2. Таблица 2 – Аргументы для анализа инвестиций
Финансовые функции связаны с интервалами выплат. Аннуитет – это ряд постоянных денежных выплат, делаемых в течение длительного периода. Например, заем под автомобиль или заклад являются аннуитетами. В функциях, связанных с аннуитетами, выплачиваемые денежные средства, такие как депозит на сбережения, представляются отрицательным числом; полученные денежные средства, такие как чеки на дивиденды, представляются положительным числом. Например, депозит в банк на сумму 1000 руб. представляется аргументом –1000 – для вкладчика – и аргументом 1000 – для банка. 3.1. Расчет текущей стоимости инвестиции. Функция ПС (ПЗ) Функция ПС (приведенная стоимость) является одним из наиболее распространенных способов оценки привлекательности долговременных вложений. Текущее значение вложения (чистый текущий объем вклада) определяется дисконтированием (приведением к стоимости на настоящий момент) поступлений по этому вложению. Если текущая стоимость поступлений оказывается больше вклада, вложение считается удачным. Синтаксис функции: ПС (Ставка; Кпер; Плт; Бс; Тип). Функция ПС вычисляет текущую стоимость ряда равных по величине периодических выплат или единовременной выплаты. Для вычисления текущей стоимости ряда выплат используется аргумент Плт (выплата). Для вычисления текущей стоимости единовременной выплаты используется аргумент Бс (будущая стоимость). Для вложения с рядом периодических выплат и единовременной выплаты используются оба эти аргумента. Задание 1 Определение текущей стоимости ряда периодических выплат. Существует два варианта вложения денежных средств с целью заработать 5 000 руб. в течение 2, 5 лет: 1. Совершить сделку, которая каждые полгода возвращает 1 000 руб. в течение следующих 2, 5 лет. Для этого нужно вложить 4 000р. 2. Вложить деньги в банк на краткосрочный счет под 8, 0 % с выплатами каждые полгода в размере 1 000 руб. Определить, какой из вариантов вложения денежных средств является более выгодным. Пояснения к задаче Решение этой задачи требует оценить текущую стоимость ряда поступлений по 1 000 руб. Проценты, которые предлагает банк, послужат в качестве учетной ставки вложения. Учетная ставка является своего рода «барьером», который должен быть превзойден, прежде чем инвестиция станет привлекательной, поэтому ее часто называют барьерной ставкой. Для определения текущей стоимости вложения используется функция ПС, в которой будет задействован аргумент Плт и не задействован аргумент Бс. Ход решения задачи 1. Запустите Excel. 2. Переименуйте первый лист в ПС. 3. Оформите таблицу согласно (рис. 17).
Рис. 17. Исходные данные для расчета текущей стоимости ряда периодических выплат
4. Вычислите количество периодов выплат и ставку за период согласно рис. 18). 5. Запустите Мастер функций, ознакомьтесь с функцией ПС (ПЗ). 6. Вычислите текущую стоимость инвестиции. Результаты сравните с рис. 18.
Рис. 18. Результаты вычисления текущей стоимости ряда периодических выплат
7. Сохраните файл и продемонстрируйте результат преподавателю. Анализ результатов решения Функция возвращает значение –4 451, 82. Это означает, что нужно вложить в банк 4 451, 82 руб. под 8, 0 % годовых сегодня, чтобы получить 5 000 руб. в течение следующих 5 лет. Поскольку в предлагаемой сделке вклад равен 4 000 руб., можно считать, что совершить сделку выгоднее, чем вложить деньги в банк. Задание 2 |
Последнее изменение этой страницы: 2019-06-19; Просмотров: 241; Нарушение авторского права страницы
(2-й уровень списка), расположенной в верхнем левом углу списка. Результат представлен на рис. 3.
(3-й уровень списка).
и 
.
(рис. 11).
. Кроме того, изменился цвет кнопки автофильтра 
, чтобы напомнить вам, что список отфильтрован по значениям, содержащимся в этом столбце.
, расположенной на стандартной панели инструментов. Программа расположила список в алфавитном порядке.
, расположенной на стандартной панели инструментов. Программа расположила список в обратном алфавитном порядке.
