Лабораторная работа №2




НазваниеЛабораторная работа №2
страница2/6
Дата публикации22.10.2014
Размер0.8 Mb.
ТипЛабораторная работа
5-bal.ru > Информатика > Лабораторная работа
1   2   3   4   5   6
Часть 1

Изучив материал этой лекции, Вы научитесь эффективно использовать инструментарий MS Excel при решении задач анализа: определять значение исследуемой переменной, необходимой для получения требуемого результата, с помощью подбора параметра и строить сценарии для оценки влияния одного или нескольких факторах на результат.

Анализ «что-если»

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

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

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

Информационная технология подбора параметра

В процессе бизнес - анализа часто требуется определить входное значение параметра формулы для получения нужного результата. При решении подобных задач целесообразно использовать средство Подбор параметра.

Подбор параметра является основным методом исследования области допустимых значений для параметров модели. Данная технология позволяет вычислить значения аргумента для заданного значения функции методом последовательных итераций [1]. Подбор параметра является средством анализа «что если» MS Excel 2010 (вкладка Данные/ Анализ «что если»).

Технология подбора параметра используется для решения алгебраических уравнений и задач анализа.

Значение функции, для которого определяется искомый параметр, устанавливается в диалоговом окне Подбор параметра (поле Значение). В поле Установить в ячейке указывается ссылка на ячейку, в которую введена функция или формула. В поле Изменяя значение ячейки вводится ссылка на ячейку, в которой записан изменяемый параметр, входящий в выражение функции. В эту ячейку после нажатия кнопки ОК выводится значение вычисленного параметра. Вводить ссылки на ячейки в поля диалогового окна Подбор параметра удобнее не с клавиатуры, а щелчком мыши на соответствующей ячейке. В этом случае MS Excel автоматически устанавливает абсолютные ссылки на ячейки.

 В процессе итерационного подбора на экране появится окно сообщений. При нажатии кнопки ОК значение найденного параметра запишется в ячейку рабочего листа. Если подбор значения аргумента при заданном значении функции невозможен, выводится сообщение, что значение не найдено. Результат вычислений необходимо оценить с точки зрения допустимости подобранного решения.

Рассмотрим возможности технологии подбора параметра для решения задач финансового менеджмента.

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

Последовательность действий:

1. Установим курсор в ячейку с формулой (в нашем примере B6).

2. Выполним команду Подбор параметра меню Анализ «что если» вкладки Данные. Заполним диалоговое окно подбора параметра:

  • в поле «Установить в ячейке» автоматически выводится адрес ячейки с формулой, если курсор изначально был установлен в клетку B6 (п. 1).  Метку можно ввести и вручную: установить курсор в поле и щелкнуть левой клавишей мыши в ячейке B6;

  • в поле «Значение» введем требуемую величину накоплений (150000);

  • в поле «Изменяя значение ячейки» укажем адрес ячейки, содержащей размер выплат (B3).

  • после подбора параметра закрываем диалоговое окно кнопкой ОК (рисунок 1).

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l10_1.gif
Рисунок 1 – Диалоговое окно Подбор параметра

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l10_2.gif
Рисунок 2 – Результат подбора параметра

Рассмотрим еще один пример использования технологии подбора параметра. Предположим, что фирме через три года предстоит покупка нового оборудования. Необходимо рассчитать, какова должна быть величина ежегодных инвестиций при ставке годового дохода равной 6%. Порядок решения задачи проиллюстрирован рисунками 96, 97 и 98.

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l10_3.gif
Рисунок 3 – Расчет будущей стоимости инвестиций

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l10_4.gif
Рисунок 4 – Окно подбора параметра

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l10_5.gif
Рисунок 5 – Результат подбора параметра

Таким образом, размер ежегодных инвестиций для покупки оборудования при заданной процентной ставке должен составлять -   157 054,91р.

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

Предположим, что компания вводит новую услугу по обслуживанию абонентов. Плановая рентабельность по новой  услуге  в течение первого года должна составлять 25%. Спрос на услугу должен составлять 50000, средняя скидка, предоставляемая абонентам составит 30%, общие фиксированные затраты составят 200000р., затраты на одну услугу составляют 50р. нужно рассчитать, какая цена продукции обеспечит заданную рентабельность. На рисунке и показан вид рабочего листа с исходными данными и окна Подбор параметра. В качестве начального значения изменяемого параметра  здесь введена цена 10р. результат подбора параметра приведен на рисунке 6.

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l10_6.gif
Рисунок 6 – Окно Подбор параметра

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l10_7.gif
Рисунок 7 – Результат подбора цены услуги

Результат подбора параметра не является точным. В Excel по умолчанию используется компромиссный вариант  между точностью решения и временем вычисления. Изменить точность вычисления можно с помощью меню Параметры вкладки Файл. В группе Параметры вычислений можно изменить предельное число итераций и относительную погрешность.

ЛЕКЦИЯ 10

Часть 2

Информационная технология построения сценариев

В Excel имеются инструменты, позволяющие исследовать факторы, оказывающие влияние на основные показатели деятельности предприятия. Одним из таких инструментов являются сценарии, которые позволяют управлять входными данными для выполнения бизнес – анализа. Управление с помощью сценариев представляет собой процесс изучения отдельных переменных и присвоения им диапазонов значений. Комбинацию таких значений, представляющую конкретный набор предположений и прогнозов, которые используются в модели, называют сценарием.

В MS Excel существует специальный инструмент построения сценариев – Диспетчер сценариев. Сценарий в Excel – это именованный набор значений указанных ячеек листа рабочей книги. Сценарии используются для подстановки значений параметров в ячейки таблицы и вычисления зависящих от них формул. Сценарный подход дает возможность проанализировать влияние одного или нескольких параметров на вычисляемую функцию и выводить результаты в удобной для пользователя форме. MS Excel позволяет редактировать значения и состав параметров, входящих в сценарий, добавлять и удалять сценарии, снабжать их комментариями. Сценарии могут содержать лишь ссылки на ячейки одного листа рабочей книги, но при подготовке отчетов можно объединять сценарии нескольких листов и сценарии разных рабочих книг. После создания или сбора всех необходимых сценариев можно создать итоговый отчет по сценариям. В отчете по сценариям все сведения сценариев отображаются в одной таблице на новом листе.

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

В MS Excel 2010 Сценарии создаются с помощью вкладки Данные меню Анализ «что если»/Диспетчер сценариев.

Пример 1. Допустим, что у вас просят в долг 30000 руб. и обещают возвращать по 5000 руб. в течение 7 лет. Что выгоднее: отдать деньги в долг или  положить деньги в банк при годовой процентной ставке 8% при тех же условиях?

Порядок действий:

1. Создадим таблицу с исходными данными (рисунок 8).

2. С помощью функции ПС() в ячейке В7 рассчитаем текущую сумму вклада   =ПС(B2;B3;B4;0;B6).  В ячейку В8 введем формулу для оценки выгодности сделки:  =ЕСЛИ(B7>B1;"Выгодно положить в банк"; "Выгодно отдать в долг"). Результат расчета представлен на рисунке 8.

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l10_8.gif
Рисунок 8 – Исходные данные с результатами

3. Щелкнем по кнопке Анализ  «что – если» вкладки Данные и выберем Диспетчер сценариев... В диалоговом окне Диспетчера сценариев щелкнем по кнопке Добавить.

4. В следующем окне введем название сценария, а в поле Изменяемые ячейки – ссылку на ячейки В3 и В4, в которых содержатся значения параметров задачи (срок и сумма возвращаемых денег). Установим флажок Запретить изменения (рисунок 9).

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l10_9.gif
Рисунок 9 – Окно изменения сценария

5. После нажатия ОК появится окно Значения ячеек сценария, в поля которого введем значения параметров для первого сценария (рисунок 10).

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l10_10.gif
Рисунок 10 – Ввод значений для расчетов сценария 

Таким образом, с помощью кнопки Добавить создаем несколько сценариев. С помощью кнопки Вывести можно вывести результаты, соответствующие выбранному сценарию (рисунок 11).

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l10_11.gif
Рисунок 11 – Пример диалогового окна сценария

Нажатие кнопки Отчет открывается диалоговое окно Отчет по сценарию (рисунок 12). Можно выбрать отчет в виде структуры или в виде сводной таблицы.

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l10_12.gif
Рисунок 12 – Диалоговое окно Отчет по сценарию

В поле Ячейки результата введем ссылку на ячейки, в которых вычисляются значения результирующих функций (В7-В8). После нажатия ОК формируется отчет по сценариям (рисунок 13).

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l10_13.gif
Рисунок 13 – Пример отчета по сценарию в виде структуры

Вернемся к примеру, об оценки эффективности проектов с учетом критерия чистой приведенной стоимости денежных потоков (лекция 9). С помощью технологии построения сценариев ответим на вопрос: как изменится эффективность инвестиционных вложений в первый проект, если перераспределяются суммы денежных потоков во времени (изменяемые ячейки В3, D3). В качестве ячеек результата выбраны ячейки С9 (то же В9) и С17.

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

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\l10_14.gif
Рисунок 14 – Отчет по сценариям в виде структуры

Лабораторная работа № 5 

Использование таблиц данных для решения задач финансового менеджмента 

Цель работы: Приобрести навыки использования таблиц данных для решения прикладных задач.

Задание к лабораторной работе: 

1. Изучите материалы лекции 11 и решите приведенные примеры.

2. Выполните контрольные задания на новом листе или в отдельной книге MS Excel.

Контрольные задания:

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

Какой должна быть эта сумма при ставке 12% годовых?

Как процентная ставка влияет на размер ежемесячных вложений? Рассмотрите диапазон от 10% до 14% с шагом 0,5%.

Проведите анализ чувствительности размера ежемесячных вложений от ставки (диапазон от 10% до 14% с шагом 0,5%) и количества периодов (диапазон от 2 до 7 лет).

Задание 2. Используя данные списка из 25 записей

a) рассчитайте максимальную, минимальную величину вкладов в каждом отделении банка;

b) используя технологию таблиц подстановок с двумя переменными, определите среднюю величину вкладов в каждом квартале для каждого типа вклада.

f:\учеба\учеба 3 год\5 семестр\отпрпакеты прикладных программ в экономике\разное\course345\img\lr5_1.gif 

3. Добавьте в архив файлы с решениями и результатами.

 ЛЕКЦИЯ 11

Как часто приходится оценивать степень влияния каких – либо параметров на результат! Научитесь делать это быстро и эффективно с использованием технологий MS Excel. 
1   2   3   4   5   6

Похожие:

Лабораторная работа №2 iconЛабораторная работа. Получение и свойства оксидов, гидроксидов и солей
Лабораторная работа. Ряд напряжений металлов. Гальванические элементы. Электролиз юююююю

Лабораторная работа №2 iconЛабораторная работа по курсу «Теория языков программирования и методов...
Данная лабораторная работа предназначается для студентов специальности пвс изучающих «Теория языков программирования и методов трансляции»....

Лабораторная работа №2 iconЛабораторная работа №5
Лабораторная работа №5 включает 5 заданий. Для выполнения этих заданий необходимо ознакомиться с теоретическим материалом, приведенным...

Лабораторная работа №2 iconУрока физики по теме “ Измерение массы тела с помощью весов лабораторная...
Технологическая карта конструирования урока физики по теме “Измерение массы тела с помощью весов – лабораторная работа №3”

Лабораторная работа №2 iconЛабораторная работа
Уоу деревьев и кустарников, их биологией, значением в жизни человека, в мае –июне в период летней практики с многообразием травянистых...

Лабораторная работа №2 iconДата Группа
«Лабораторная работа Получение входных и выходных характеристик биполярного транзистора в схеме с оэ»

Лабораторная работа №2 iconЛабораторная работа 1
Цель работы: ознакомиться с топографической картой и научиться решать следующие задачи

Лабораторная работа №2 iconЛабораторная работа №14
Цель работы: определить экспериментально с помощью оборотного маятника ускорение свободного падения

Лабораторная работа №2 iconЛабораторная работа №4
Целью работы является изучение возможностей субд db2 по созданию и ведению баз данных

Лабораторная работа №2 iconКонспект открытого урока Предмет- биология
Методы и приемы: объяснительно иллюстративный, проблемный, лабораторная работа (исследовательский)


Учебный материал


При копировании материала укажите ссылку © 2013
контакты
5-bal.ru