Информационные системы и технологии в финансах (стр. 15 из 21)
Источник – блок ячеек А2-А7, содержащий инвентарные номера (см. рис).
В остальные ячейки поместите расчетные формулы:
В20: ПРОСМОТР(B19;A2:A7;B2:B7)
В21: ПРОСМОТР(B19;A2:A7;C2:C7)
В22: ПРОСМОТР(B19;A2:A7;G2:G7)
В23: ПРОСМОТР(B19;A2:A7;E2:E7)
В24: КОНМЕСЯЦА(B23;12-МЕСЯЦ(B23)) – Функция МЕСЯЦ возвращает месяц в дате, заданной в числовом формате. Месяц возвращается как целое число в диапазоне от 1 (январь) до 12 (декабрь). Возвращает числовой формат последнего дня месяца, отстоящего на указанное количество месяцев от начальной даты. Функция КОНМЕСЯЦА используется для вычисления даты вступления в силу или даты платежа, которая приходится на конец месяца.
В25: ПРОСМОТР(B19;A2:A7;D2:D7)
В26: Создадим раскрывающийся список. Выберите команду меню
Данные – Проверка, укажите тип данных – Список, источник – значения 0,
1, 3, 4.
Создадим таблицу для вычисления амортизации по периодам для искомых функций, начиная с ячейки А27. Таблица будет иметь вид:
| Период расчета | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 |
| Аморув | ||||||||
| Аморум |
В строку АМОРУВ добавьте формулу
АМОРУВ($B$21;$B$23;$B$24;$B$22;B27;$B$25;$B$26) и размножьте ее по всем ячейкам строки.
В строку АМОРУМ добавьте формулу
АМОРУМ($B$21;$B23;$B$24;$B$22;B27;$B$25;$B$26) и размножьте ее по всем ячейкам строки.
Проверьте работу расчетного блока, выбирая в ячейке В19 различные инвентарные номера.
Создадим диаграмму, отражающую сравнительные результаты применения функций расчета амортизации. 
Нажмите кнопку добавления диаграммы.Выберите тип: Обычная гистограмма.
Выберите вкладку РЯД.
Нажмите кнопку ДОБАВИТЬ. В поле ЗНАЧЕНИЯ укажите ряд значений функции АМОРУВ. В поле ПОДПИСИ ПО ОСИ Х укажите ряд номеров периодов (1, 2, …).
Нажмите кнопку ДОБАВИТЬ. В поле ЗНАЧЕНИЯ укажите ряд значений функции АМОРУМ. В поле ПОДПИСИ ПО ОСИ Х укажите ряд номеров периодов (1, 2, …). Нажмите кнопку ДАЛЕЕ и введите подписи по осям. Выберите размещение диаграммы на текущем листе. Разместите диаграмму таким образом, чтобы она была видна одновременно с расчетными данными.Проверьте автоматическое изменение диаграммы при выборе в ячейке В19 различных инвентарных номеров.
Сделайте вывод о различии алгоритмов функций АМОРУВ и АМОРУМ.
Контрольные вопросы:
1. Как использовать команду "Проверка"?
2. Каков синтаксис функции "Просмотр"?
3. Какие функции служат для вычисления амортизации и каков их синтаксис?
Практическое занятие № 6-7.
Решение задач оптимизации
Цель занятия: освоить методику решения оптимизационных задач при помощи средства поиска решения в MS Excel. Применить эту методику к решению задачи об оптимальном управлении капиталом.
План занятия:
1. Изучить постановку задачи об управлении портфелем активов.
2. Построить модель, описывающую данную задачу.
3. Решить задачу, используя средство подбора параметров.
4. Самостоятельно решить задачу управления активами с учетом возможных рыночных рисков.
Порядок выполнения работы
Рассмотрим задачу об управлении портфелем активов.
Пусть перед некоторым инвестором стоит проблема принятия решения о вложении имеющегося у него капитала. Набор характеристик потенциальных объектов для инвестирования, имеющих условные имена от А до F, задаются в таблице.
| Название | Доходность, в % | Срок выку- па, год | Надежность, баллы |
| A | 5,5 | 2001 | 5 |
| B | 6,0 | 2005 | 4 |
| C | 8,0 | 2010 | 2 |
| D | 7,5 | 2002 | 3 |
| E | 5,5 | 2000 | 5 |
| F | 7,0 | 2003 | 4 |
Предположим, что при принятии решения о приобретении активов должны быть соблюдены условия:
Суммарный объем капитала, который должен быть вложен, составляет $100 000. Доля средств, вложенная в один объект, не может превышать четверти от всего объема. Более половины всех средств должны быть вложены в долгосрочные активы (пусть к таковым относятся активы со сроком погашения после 2004 года). Доля активов, имеющих надежность менее 4 баллов, не может превышать трети суммарного объема.Опишем экономико-математическую модель данной задачи оптимизации. В качестве управляемых переменных выступают объемы средств, вложенных в активы той или иной фирмы. Обозначим их как X A ,
X B , X C , X D , X E , X F .
Тогда суммарная прибыль от размещенных активов, которую получит инвестор, может быть представлена в виде: P=0,055X A + 0,006X B + 0,08X C +0,075X D +0,055X E +0,07X F.
На следующем этапе моделирования необходимо формально описать перечисленные выше ограничения1 - 4 на структуру портфеля.
1. Ограничения на суммарный объем активов: X A + X B + X C + X D + X E + X F <= 100 000 2. Ограничение на размер доли каждого актива:
X A <=25 000, X B <= 25 000, X C <= 25 000, X D <= 25 000, X E <= 25
000, X F <= 25 000
3. Ограничение, связанное с необходимостью вкладывать половину средств в долгосрочные активы: X B + X C >= 50 000
4. Ограничение на долю ненадежных активов: X C + X D <= 30 000
5. Система ограничений в соответствии с экономическим смыслом задачи должна быть дополнена условиями неотрицательности для искомых переменных:
X A >= 0, X B >= 0, X C >= 0, X D >= 0, X E >= 0, X F >= 0.
Перечисленные условия образуют математическую модель поведения инвестора. В рамках этой модели может быть поставлена задача поиска таких значений переменных X A , X B , X C , X D , X E , X F, при которых достигается наибольшее значение прибыли и одновременно выполняются ограничения на структуру портфеля активов.
Перейдем к решению сформулированной задачи с помощью MS Excel. Оно распадается на следующие шаги:
На рабочем листе задать ячейки, которые будут предназначены для сохранения переменных решаемой задачи (X A , X B , X C , X D , X E , X F). Задать ячейку, содержащую формулу целевой функции Р (формулу см. выше) - ячейка Н2. Заполнить ячейки для формул "сложных ограничений" (типа X C + X D <= 30 000). В нашем примере это ячейки С4, С5, С6. 
Ввод ограничений осуществляется при помощи кнопки Добавить.Аналогично можно изменять и удалять ограничения
После ввода всех ограничений и заполнения окна Поискрешения нажать кнопку Выполнить, после чего будет осуществлена процедура поиска решения, удовлетворяющего всем ограничениям. По результатам выводится сообщение о найденном решении (или невозможности его обнаружить). 
Полученные результаты можно сохранить, изменив таким образом содержимое ячеек (нажать ОК) или отказаться от сохранения результатов. Можно сформировать отчет с более подробной информацией о том, как происходил процесс решения. Для этого необходимо выбрать тип отчета. После сохранения найденного решения, получим следующие результаты: 
Таким образом, получили, что при оптимальном распределении прибыль инвестора составит 6374,408.
Задания для самостоятельной работы:
| Вариант 1 | ||||
| фирма | процент | Риск | ограничения | Средневзвешенный риск не более 7% Общая сумма вклада 100000 руб. Повторить расчет модели при снятых ограничениях на минимальную сумму вклада. |
| A | 30 | 90 | <=10% | |
| B | 12 | 10 | <=30% | |
| C | 7 | 2 | <=50% | |
| D | 15 | 9 | <=25% | |
| E | 9 | 3 | <=50% | |
| Вариант 2 | ||||
| фирма | процент | Риск | ограничения | Средневзвешенный риск не более 5% Общая сумма вклада 100000 руб. Повторить расчет модели при снятых ограничениях на минимальную сумму вклада. |
| A | 19 | 12 | <=10% | |
| B | 18 | 10 | <=30% | |
| C | 7 | 2 | <=50% | |
| D | 15 | 7 | <=25% | |
| E | 9 | 3 | <=50% |
Средневзвешенный риск рассчитывается по формуле: