Главная | О нас | Обратная связь Автоматизация Автостроение Антропология Археология Архитектура Астрономия Предпринимательство Биология Биотехнология Ботаника Бухгалтерский учет Генетика География Геология Государство Демография Деревообработка Журналистика и СМИ Зоология Изобретательство Иностранные языки Информатика Информационные системы Искусство История Кинематография Кораблестроение Кулинария Культура Лексикология Литература Логика Маркетинг Математика Математический анализ Материаловедение Машиностроение Медицина Менеджмент Металлургия Метрология Механика ОБЖ Охрана Труда Педагогика Политология Правоотношение Программирование Производство Промышленность Психология Радио Разное Социология Спорт Статистика Строительство Теология Технологии Туризм Усадьба Физика Физиология Философия Финансы Химия Черчение Экология Экономика Электротехника Инструменты анализа «что-если» 2019-08-13 267 Обсуждений (0) Инструменты анализа «что-если» 0.00 из 5.00 0 оценок Скачать документ 1234Следующая ⇒ Министерство образования Республики Беларусь БЕЛОРУССКИЙ НАЦИОНАЛЬНЫЙ ТЕХНИЧЕСКИЙ УНИВЕРСИТЕТ Факультет технологий управления и гуманитаризации Кафедра «Таможенное дело» Компьютерные информационные технологии Методические указания и задания К контрольной работе для студентов заочного отделения специальности 1–25 01 07 “Экономика и управление на предприятии” Минск 2011 УДК [002.6+681.3](075.4)   Составитель О.В.Альшевская     В настоящем издании описаны краткие теоретические сведения, рассмотрены примеры выполнения и оформления заданий, даны контрольные задания и методические рекомендации по их выполнению для студентов заочного отделения специальности 1–25 01 07 “Экономика и управление на предприятии”. Целью контрольной работы является изучение возможностей электронных таблиц MS Excel применительно к финансово-экономической деятельности (финансовые функции, инструменты анализа «что-если») и обработке данных с помощью списков.     © Альшевская О.В., составление, 2011 Содержание I. Общие сведения.. 4 II. Теоретические сведения, методические указания по выполнению, примеры выполнения и оформления заданий.. 5 Финансовые функции. 5 Инструменты анализа «что-если». 8 Управление списками. 11 III. Варианты контрольных заданий.. 20 Раздел 1. Финансовые функции и инструменты анализа «что-если». 21 Задание № 1. Решение задач с использованием функций ЦЕНА, ДОХОД, ПОЛУЧЕНО.. 21 Задание № 2. Решение задач с использованием функций БС, ПС, КПЕР, СТАВКА, ПЛТ. 23 Задание № 3. Использование подбора параметра и построение сценариев. 25 Раздел 2. Управление списками в MS Excel. 32 Задание № 4. Обработка списков с помощью расширенного фильтра, функций работы с базой данных, подведения промежуточных итогов или построения сводных таблиц. 32 IV. Литература.. 39 I. Общие сведения   Контрольная работа должна быть выполнена в электронной таблице MS Excel, оформлена в текстовом редакторе MS Word и сдана на кафедру в распечатанном виде не позднее, чем за две недели до начала сессии. К распечатке необходимо также приложить диск с файлом решения задач в MS Excel. Титульный лист должен содержать следующие сведения: - название вуза, факультета, кафедры; - название дисциплины; - номер группы и зачетки, фамилию, имя и отчество студента; - номера заданий. Образец:     Белорусский национальный технический университет Факультет технологий управления и гуманитаризации Кафедра «Таможенное дело»     Контрольная работа по дисциплине «Компьютерные информационные технологии»   студента группы 308410 ( /15) Петрова Дмитрия Петровича   Задания 1.1.5 2.2.2 3.15 4.1.2 4.4.5   Минск 2011     Контрольная работа состоит из двух разделов: 1. Финансовые функции и инструменты анализа «что-если». Содержит две практические задачи, которые должны быть решены в MS Excel с применением финансовых функций, а также задание, выполняемое с использованием инструмента «Подбор параметра» или инструмента «Сценарии». 2. Обработка данных в MS Excel с помощью списков. Включает две задачи по обработке списка с помощью расширенного фильтра, функций работы с базой данных, подведения промежуточных итогов или построения сводных таблиц.   II. Теоретические сведения, методические указания по выполнению, примеры выполнения и оформления заданий   Финансовые функции Финансовые функции применяются для расчетов по ссудам, вкладам, инвестициям, ценным бумагам, амортизации. Для расчетов по ценным бумагам используются функции ДОХОД, ПОЛУЧЕНО, СКИДКА, ЦЕНА, ДНЕЙКУПОН и др. Функция ДОХОД вычисляет доход по облигациям, который составляют периодические процентные выплаты. Функция ЦЕНА возвращает цену за 100 руб. нарицательной стоимости ценных бумаг, по которым выплачивается периодический процент. Функция ПОЛУЧЕНО возвращает сумму, полученную в срок вступления в силу полностью обеспеченных ценных бумаг. Для расчетов по ссудам, вкладам и инвестициям используются функции БС, КПЕР, СТАВКА, ВНДОХ, ПЛТ, ОБЩПЛАТ, ЧПС и др. Функция БС возвращает будущее значение вклада. Функция ПС возвращает текущий объем вклада, т.е. общую сумму, которую составят будущие платежи. Функция ПЛТ определяет величину платежа по процентам за один период выплат. Функция КПЕР возвращает общее количество периодов выплаты для данного вклада. Функция СТАВКА возвращает норму прибыли за один период. Данные функции предназначены для расчетов на основе периодических платежей и постоянной процентной ставки, в них каждый финансовый аргумент выражается через другие. При заполнении аргументов необходимо учитывать следующее: 1) производится выплата или получение денежных средств: сумма, которая выплачивается, вводится со знаком минус; 2) периодичность выплат или начисления процентов: если этот период составляет месяц, квартал или полугодие, то годовая процентная ставка делится соответственно на 12, 4 или 2, а период выражается в месяцах, кварталах, полугодиях; 3) производится выплата в начале или в конце периода:  если выплаты проводятся в конце периода, то в рассматриваемых функциях аргумент «Тип» должен быть равен 0, а для выплат в начале периода, т.н. обязательных платежей, «Тип» = 1. Пример 1. Определить доходность облигаций, по которым производятся периодические выплаты процентов, если известны следующие данные: Дата соглашения Дата вступле­ния в силу Ставка Цена Погаше­ние Частота Базис 01.03.2003 01.12.2007 8,8% 96,545 100 2 1 Решение: 1. Введем исходные данные в ячейки листа Excel. 2. Для определения доходности облигаций используем функцию ДОХОД. Если ее нет в списке финансовых функций, то нужно подключить надстройку Excel «Пакет анализа» (кнопка Параметры Excel в Excel 2007). 3. Заполним все аргументы функции с помощью Мастера функций. 4. В соседнюю ячейку скопируем текст полученной формулы. Чтобы в ячейке выводилась формула, а не результат, перед знаком равенства ставится апостроф. 5. При распечатывании на странице должны быть заголовки строк и столбцов. Оформленная задача должна выглядеть следующим образом:   A B C D E F G H I 1 Задание 1.2.8
2	Дата со­глашения	Дата вступ­ления в силу	Ставка	Цена	Погаше­ние	Час­тота	Базис	Доход	Формула
3	01.03.2003	01.12.2007	8,8%	96,545	100	2	1	9,72%	=ДОХОД(А3;B3;C3; D3;E3;F3;G3)

Пример 2. Определить начальный размер вклада, если при ставке 6,7% годовых с ежеквартальным начислением процентов через 8 лет на счете стало 15 542 рублей.

Решение:

1. Введем исходные данные в ячейки листа Excel.

2. Для определения начального размера вклада используем функцию ПС.

3. Заполним все аргументы функции с помощью Мастера функций:

В данной задаче периодом начисления процентов является квартал, поэтому годовая процентная ставка делится на 4, а количество лет умножается на 4. Аргументы «ПЛТ» и «Тип» не заполнены, так как по условию задачи нет периодических платежей и не указано в начале или в конце периода начисляются проценты. Результат вычислений получился отрицательным, потому что внесение денег на счет является выплатой.

4. В соседнюю ячейку скопируем текст полученной формулы.

Оформленная задача должна выглядеть следующим образом:

Инструменты анализа «что-если»

Анализ «что-если» включает четыре инструмента: сценарии, таблица подстановки, подбор параметра и поиск решения. Одноименные команды для использования этих инструментов расположены в меню Сервис (для Excel 2003) или на закладке Данные (для Excel 2007).

Подбор параметра используется, если известен конечный результат, а значение, от которого он зависит, нет. Механизм подбора параметра заключается в изменении значения влияющей ячейки (параметра) до тех пор, пока формула, зависящая от этой ячейки, не возвратит заданное значение. В основу поиска решения положен алгоритм численного решения уравнения одной или нескольких переменных. По умолчанию для нахождения параметра Excel проводит максимум 100 итераций (промежуточных вычислений) с погрешностью 0,001. Изменить эти значения можно в диалоге Сервис—Параметры—закладка Вычисления.

Сценарием в Excel называется именованная комбинация значений подстановки, заданных для одной или нескольких изменяемых ячеек в модели «что-если». Сценарии используются для рассмотрения и сравнения вариантов поведения модели. Можно создавать и сохранять на листе различные сценарии и переключаться на любой из них для просмотра результатов. Например, если нужно сформировать бюджет, но годовой доход точно не известен, то создаются сценарии для различных значений дохода, а затем выполняется анализ «что-если».

Пример 3. Используя подбор параметра, определить для примера 1 значение ставки, при котором доходность будет равна 8,31%. Значения других аргументов не меняются.

Решение:

1. Скопируем первый пример на другой лист (в ячейки с такими же адресами).

2. Выполним команду Подбор параметра и заполним диалог:

В данном примере в ячейку Н3 введена функция =ДОХОД(А3;B3;C3;D3;E3;F3;G3). С помощью подбора параметра необходимо установить в этой ячейке значение 8,31%, изменяя при этом ячейку С3, в которой хранится искомая ставка.

3. После нажатия ОК в ячейке С3 будет найдено значение ставки à 7,42%.

При оформлении задачи с подбором параметра должен быть указан ее номер, таблица с исходными данными и результатами (как в примере 1), заполненное диалоговое окно[1] Подбор параметра.

Пример 4. Построить три сценария для примера 2 с различными значениями накопленной суммы, количества лет и процентной ставки. Вывести отчет по сценариям в виде структуры c результатами вычисления начального размера вклада для каждого сценария.

Решение:

1. Скопируем второй пример на другой лист (в ячейки с такими же адресами).

2. Выполним команду Сценарии и нажмем кнопку Добавить. В диалоге укажем название сценария и изменяемые ячейки. После нажатия ОК в диалоге Значения ячеек сценария введем значения накопленной суммы, количества лет и процентной ставки из первой строки таблицы с исходными данными:

3. При создании второго и третьего сценария будет меняться его название и значения изменяемых ячеек. Адреса изменяемых ячеек будут те же (B9:D9). Для изменения уже созданных сценариев в Диспетчере сценариев имеется кнопка Изменить.

4. С помощью кнопки Отчет выведем структуру созданных сценариев, указав ячейку результата à Е9. После нажатия ОК будет создан новый лист со структурой сценария:

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

Управление списками

Списком называют таблицу на рабочем листе, строки которой содержат связанные данные. Строки списка называются записями, а столбцы — полями. Первая строка списка содержит названия столбцов, которые являются именами полей базы данных.

Записи списка можно упорядочивать по одному или нескольким полям, что достигается с помощью сортировки. Для этого нужно указать любую ячейку списка, нажать кнопку Сортировка на закладке Данные и заполнить диалог (рис. 7-а).

Фильтрация списка

В Excel имеется три способа отображения записей списка, удовлетворяющих заданным условиям: автофильтр, пользовательский автофильтр, расширенный фильтр. После нажатия кнопки Фильтр на закладке Данные в строке имен полей появляются кнопки раскрывающихся списков (автофильтр), содержащих перечень всех имеющихся значений поля, а также команду Числовые фильтры или Текстовые фильтры в зависимости от типа данных, хранящихся в данном поле. В свою очередь данные команды также содержат ряд команд:

- для отображения необходимого количества наибольших или наименьших значений в списке используется командаПервые 10…;

- используя команду Настраиваемый фильтр, можно открыть диалог Пользовательский автофильтр, в котором допускается задание двух условий по выбранному полю. Условия должны выполняться одновременно (связь по И), либо достаточно выполнения одного из заданных условий (связь по ИЛИ) для отображения записей.

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

Расширенный фильтр используется для задания сложных условий фильтрации. Чтобы его применить, нужно сначала создать диапазон критериев. Первая строка этого диапазона должна содержать имена полей, по которым будут задаваться условия, в следующих строках вводятся условия. Между критериями в одной строке идет связь по И, между строками критериев — по ИЛИ.

После создания диапазона условий нужно указать любую ячейку списка, нажать кнопку Дополнительно на закладке Данные и заполнить диалог (рис. 5). В этом диалоге Исходный диапазон — это диапазон списка. В строке Диапазон условий нужно указать ссылку на созданный диапазон критериев. С помощью опций список можно фильтровать на месте или скопировать результат в другое место, указав при этом диапазон из одной строки и стольких столбцов, сколько полей в списке. Начиная с этой строки будет выведен отфильтрованный список.

В расширенном фильтре для задания условий можно также использовать символы шаблонов ( * — заменяет любое количество символов; ? — заменяет один символ) и формулы. Используемая в условии формула должна ссылаться на ячейку первой записи списка в поле, для которого задается условие. В диапазоне критериев с использованием формулы ячейку первой строки, в которой должно быть имя поля, нужно оставить пустой.

2019-08-13

267

Обсуждений (0)

Инструменты анализа «что-если» 0.00 из 5.00 0 оценок

Скачать документ