I. Лабораторные работы

Министерство образования Республики Беларусь

БЕЛОРУССКИЙ НАЦИОНАЛЬНЫЙ ТЕХНИЧЕСКИЙ УНИВЕРСИТЕТ

Факультет технологий управления и гуманитаризации

Кафедра «Таможенное дело»

Обработка экономической информации

Методические указания и задания

По лабораторным работам

Для студентов дневного отделения

специальности I – 26.02.02 – «Менеджмент»

Минск 2005

УДК 330.47 (076.5)

ББК 65с.я73

О-23

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

Целью расчетно-графической работы является закрепление навыков студентов по применению электронных таблиц MS Excel в финансово-экономической сфере (финансовые функции, инструменты анализа «что-если»).

Составитель О.В.Альшевская

Рецензенты:

_____________________________________________________________________________

© Альшевская О.В.,

составление, 2005

I. Лабораторные работы

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

Финансово-экономические расчеты. Анализ «что-если»

Цель работы: научиться применять финансовые функции MS Excel для расчетов по ценным бумагам, инвестициям, вкладам и ссудам, использовать механизм подбора параметра, таблицы подстановки и сценарии для вариантного анализа.

Методические рекомендации

Финансовые функции применяются для расчетов по ссудам, вкладам, инвестициям, ценным бумагам, амортизации.

Для расчетов по ценным бумагам используются функции ДОХОД, ПОЛУЧЕНО, СКИДКА, ЦЕНА, ДНЕЙКУПОН и др.

Функция ДОХОД вычисляет доход по облигациям, который составляют периодические процентные выплаты. Функция ЦЕНА возвращает цену за 100 руб. нарицательной стоимости ценных бумаг, по которым выплачивается периодический процент. Функция ПОЛУЧЕНО возвращает сумму, полученную в срок вступления в силу полностью обеспеченных ценных бумаг.

Для расчетов по ссудам, вкладам и инвестициям используются функции БС (БЗ), ПС (ПЗ или НЗ), КПЕР, СТАВКА (НОРМА), ПЛТ (ППЛАТ), ОСПЛТ (ОСНПЛАТ), ПРПЛТ (ПЛПРОЦ), ВСД (ВНДОХ), ЧПС (НПЗ), ЧИСТВНДОХ и др. В скобках даны имена этих функций для Excel 2000.

Функция БС возвращает будущее (накопленное) значение вклада.

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

Функция ПЛТ определяет величину платежа по процентам за один период выплат.

Функция КПЕР возвращает общее количество периодов выплаты для данного вклада, например, если периодом выплат или начисления процентов был месяц, то функция возвратит количество месяцев.

Функция СТАВКА возвращает норму прибыли за один период, например, если периодом выплат или начисления процентов был квартал, то функция возвратит квартальную процентную ставку.

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

При заполнении аргументов необходимо учитывать следующее:

1) производится выплата или получение денежных средств: сумма, которая выплачивается, вводится со знаком минус;

2) периодичность выплат или начисления процентов: если этот период составляет месяц, квартал или полугодие, то годовая процентная ставка делится соответственно на 12, 4 или 2, а период выражается в месяцах, кварталах, полугодиях;

3) производится выплата в начале или в конце периода: если выплаты проводятся в конце периода, то в рассматриваемых функциях аргумент «Тип» должен быть равен 0, а для выплат в начале периода, т.н. обязательных платежей, «Тип» = 1.

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

Дата соглашения	Дата вступле­ния в силу	Ставка	Цена	Погаше­ние	Частота	Базис
01.03.2003	01.12.2007	8,8%	96,545

Решение:

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

2. Для определения доходности облигаций используем функцию ДОХОД. Если ее нет в списке финансовых функций, то на компьютере нужно установить надстройку Excel «Пакет анализа» и включить его командой Сервис — Надстройки.

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

	A	B	C	D	E	F	G	H	I
	Задание 1.2.4
	Дата со­глашения	Дата вступ­ления в силу	Ставка	Цена	Погаше­ние	Час­тота	Базис	Доход	Формула
	01.03.2003	01.12.2007	8,8%	96,545				9,72%	=ДОХОД(А3;B3;C3; D3;E3;F3;G3)

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

Решение:

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

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

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

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

	A	B	C	D	E	F
	Задание 2.2.3
		Накоплено	Кол-во лет	Ставка	Начальный вклад	Формула
				6,7%	-9 133,83р.	=ПС(D9/4;C9*4;;B9)

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

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

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

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

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

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

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

Решение:

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

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

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

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

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

Накоплено	Кол-во лет	Ставка
17946,0		7,4%
12455,5		5,8%
10336,7		8,3%

Решение:

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

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

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

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

Пример 1.5. Проанализировать величины ссуды, которую планируется взять на 10 лет с ежеквартальной выплатой по 100000, для различных процентных ставок (8%—20%).

Решение:

1. Ввести исходные данные, формулу и диапазон изменения ставки:

2. Выделить диапазон В1:С14 и выполнить команду Данные—Таблица подстановки;

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

Пример 1.6. В пример 1.5 добавляется переменная. Выплата изменяется от 100000 до 500000 с шагом 50000.

Решение:

1. Переместить формулу в В1, ячейки С1:К1 заполнить рядом выплат.

2. Выделить диапазон В1:К14 и выполнить команду Данные—Таблица подстановки;

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

Задания:

1. Определить доходность облигаций, по которым производятся периодические выплаты процентов.

Дата соглашения	Дата вступления в силу	Ставка	Цена	Погаше­ние	Частота	Базис
01.09.2006	01.11.2011	11,50%	96,2725

2. Определить сумму, полученную к сроку погашения полностью обеспеченных ценных бумаг.

Дата соглашения	Дата вступления в силу	Инвестиция	Скидка	Базис
01.08.2003	01.02.2011		6,75%

3. Вы собираетесь вкладывать по 164 руб. в начале каждого квартала в течение 13 лет при годовой ставке 2%. Сколько денег будет на счете через 13 лет?

4. Определить начальный размер вклада, если при ставке 5% годовых с ежеквартальным начислением процентов через 4 года на счете стало 10979,01 рублей.

5. Вы берете в долг 220000 руб. под годовую ставку 5% и собираетесь выплачивать по 3300 руб. в месяц. Сколько лет займут эти выплаты?

6. Определите годовую процентную ставку для 12-летнего займа в 3100 руб. с выплатой в 30 рублей в конце каждого месяца.

7. Вычислите величину периодических выплат, которые должны производится в начале каждого месяца, для 11-годичной ссуды размером 350000 руб. с годовой ставкой 9%.

8. Построить три сценария для задачи 6 с различными значениями займа (P), количества лет его выплаты (n) и величины периодических выплат (А). Вывести отчет по сценариям в виде структуры c результатами вычисления ставки для каждого сценария.

P	n	A

9. Используя подбор параметра, определить в задаче 1 значение цены, при котором доходность будет равна 13,70%. Значения других аргументов не меняются.

10. Используя подбор параметра, определить в задаче 2 значение инвестиции, при котором получаемая сумма будет равна 16200. Значения других аргументов не меняются.

11. На базе исходных данных задачи 3 с помощью таблицы подстановки проанализировать значения накопленной суммы при различных процентных ставках (2% à 2,5% à … à 7%).

12. На базе исходных данных задачи 7 с помощью таблицы подстановки проанализировать значения периодических выплат при различных процентных ставках (9% à 9,5% à … à 14%) и различных значениях ссуды (350000 à 400000 à … à 800000).

Контрольные вопросы:

1. Перечислите основные функции для расчета по вкладам, ссудам, займам. Что возвращает каждая из этих функций?

2. Назовите правила заполнения аргументов функций для расчета по вкладам, ссудам и займам.

3. Какие инструменты включает в себя анализ «что-если»?

4. Дайте характеристику одного из инструментов анализа «что-если» (по указанию преподавателя).

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

Поиск оптимальных решений в экономических задачах

Цель работы: изучить методы решения задач с использованием надстройки «Поиск решения», научиться проводить анализ оптимального решения.

Методические рекомендации

Процедура Поиска решения, реализованная в Excel, позволяет решить систему уравнений и неравенств с заданным критерием оптимизации.

Задача имеет оптимальное решение, если: 1) она имеет множество допустимых решений, т.е. решений, удовлетворяющих всем ограничениям и граничным условиям, и 2) критерий, по которому из допустимых выбирается оптимальное решение.

Общий случай математической модели для задачи оптимизации можно записать следующим образом:

ЦФ: F = f(x_j) à max (min, Const)

ОГР: a_i £ g_i (x_j) £ b_i

ГРУ: d_j £ x_j £ D_j

i = 1, m; j = 1, n

В данной системе:

- х₁, х₂, … х_j — искомые переменные, n — количество переменных, m — количество ограничений.

- ЦФ — целевая функция или критерий оптимизации, показывает в каком смысле решение должно быть оптимальным. Возможны 3 вида назначения целевой функции: максимизация, минимизация, назначение заданного значения.

- ОГР — ограничения устанавливают зависимости между переменными.

- ГРУ — граничные условия показывают, в каких пределах могут быть значения искомых переменных в оптимальном решении.

Без критерия система, в которой n > m, имеет множество допустимых решений. Из них выбирается оптимальное решение, удовлетворяющее заданному критерию.

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

Пример 2.1. Предприятие выпускает 3 вида продукции, расходуя при этом 4 типа ресурсов. Расход каждого ресурса на производство единицы продукции и удельная прибыль составляют:

	Продукция 1	Продукция 2	Продукция 3
Ресурс 1	4,5	2,6	2,4
Ресурс 2	6,2	4,2	4,0
Ресурс 3	5,3	3,8	3,5
Ресурс 4	3,1	2,0	1,8
Удельная прибыль	93,0	55,4	58,0

В распоряжении предприятия имеется 5100 ресурса 1, 8200 ресурса 2, 7000 ресурса 3, 3250 ресурса 4.

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

Решение:

1. Внесем исходные данные и формулы в ячейки рабочего листа:

В данной задаче искомыми переменными будут являться объемы выпуска трех видов продукции и дополнительные ресурсы четырех видов, поэтому резервируем 7 изменяемых ячеек В2:Н2. В ячейке I2 записана формула общей суммы дополнительных ресурсов, которая будет являться минимизируемой целевой функцией. В ячейке I3 — формула прибыли. В ячейках I5:I8 — формулы, вычисляющие расход ресурсов. Для удобства ввода формул и наглядности результата в ячейки E5, F6, G7, H8 внесены -1, т.е. дополнительный ресурс вычитается из имеющегося.

2. Заполним диалог Поиск решения:

Ограничения добавляются с помощью диалогового окна, вызываемого кнопкой Добавить:

Чтобы изменить или удалить уже имеющееся ограничение, нужно его выделить и нажать соответствующую кнопку в окне Поиск решения.

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

1. После нажатия кнопки Выполнить получим оптимальное решение:

Из полученного решения видно, что продукцию 2 выпускать не выгодно, продукции 1 и 3 должны быть выпущены в объеме 206,3 и 1738,1. При этом не­обходимо дополнительно иметь 31,7 ресурса 2, 177 ресурса 3 и 518,3 ресурса 4.

Пример 2.2. Требуется изготовить усеченный конус объемом V ≥ 35 л. Причем r может изменяться в пределах [1; 2].

Определить оптимальные размеры R, r, h, при которых длина сварного шва L будет минимальной.

Решение:

1. Внесем исходные данные и формулы в ячейки рабочего листа:

	A	B
	R
	r
	h
	V	= (ПИ()/3)*B3*(B1^2+B1*B2+B2^2)
	L	= 2*ПИ()*B1+2*ПИ()*B2+КОРЕНЬ(B3^2+(B1-B2)^2)

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

1. Заполним диалог Поиск решения и после нажатия кнопки Выполнить получим оптимальное решение:

Задания:

I. Задача оптимизации распределения ресурсов и ее вариантный анализ

Задача 1. Комбинат осваивает выпуск новых артикулов тканей.

В распоряжении имеются следующие ресурсы:

Сырье
Оборудование
Трудовые

На производство одного метра ткани каждого артикула расходуется (удельный расход):

	Артикул А	Артикул В	Артикул C	Артикул D
Сырье
Оборудование				165,5
Трудовые

Прибыль от реализации одного метра ткани составляет (удельная прибыль):

Артикул А	Артикул В	Артикул C	Артикул D
		87,5

Найти оптимальный план выпуска тканей каждого артикула, при котором прибыль комбината будет максимальной.

Установить флажок Линейная модель в параметрах поиска решения, так как данная задача является линейной. Вывести все доступные отчеты и проанализировать их.

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

Задача 3. Сделать копию листа с решением задачи 1. На скопированном листе найти оптимальный план выпуска тканей каждого артикула, при котором прибыль комбината будет равной 40 000.

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

Задача 4. Сделать копию листа с решением задачи 3. На скопированном листе найти оптимальный план выпуска тканей каждого артикула и минимальные дополнительные ресурсы, необходимые для получения прибыли 40 000. Сравнить результаты решения задач 3 и 4.

II. Оптимизация производственного плана

Фирма выпускает два типа деталей (А и В). Для этого она закупает литье, подвергаемое токарной обработке, сверловке и шлифовке. Данные производительности станков, стоимость часа станочного времени и рабочий фонд времени для каждого станка приведены в таблице:

Станки	Деталь А, шт/ч	Деталь В, шт/ч	Стоимость часа станочного времени	Рабочий фонд времени
Токарный
Сверлильный
Шлифовальный			17,5

Стоимость материала, расходуемого для детали А составляет 2 у.е., для детали В — 3 у.е. Отпускная цена детали А равна 5 у.е., детали В — 6 у.е.

Найти план выпуска продукции, дающий максимальную прибыль.

Указания по выполнению:

1. Определить время обработки одной детали на каждом станке

2. Вычислить затраты на обработку единицы детали каждого типа

3. Вычислить удельную прибыль для каждого типа деталей

4. Зарезервировать ячейки для количества штук деталей каждого типа

5. Ввести формулу общей прибыли фирмы

6. Ввести формулы для вычисления фактического времени работы каждого станка

7. Заполнить диалог Сервис àПоиск решения и выполнить поиск.

III. Транспортная задача

Предприятие имеет филиалы в нескольких городах. Затраты на перевозку единицы продукции из филиалов потребителям составляют:

Потребители Филиалы	Минск	Брест	Гродно	Лида
Витебск
Могилев
Гомель
Полоцк
Мозырь

Для удовлетворения нужд потребителей необходимо доставить следующее количество продукции:

Минск	Брест	Гродно	Лида

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

Витебск
Могилев
Гомель
Полоцк
Мозырь

Найти оптимальный план перевозок продукции из филиалов потребителям, при котором суммарные транспортные расходы будут минимальными.

Указания по выполнению: