ОТЧЁТ ПО ЛАБОРАТОРНЫМ РАБОТАМ
Информационные технологии в антикризисном управлении
1. Определение стоимости денежных средств во времени
Задание 1
Ваша компания планирует взять кредит на сумму 4 000 000 руб. на 6 лет под 20 % годовых. Необходимо:
1. Определить размер выплат по кредитам, если: а) выплаты осуществляются ежегодно; б) выплаты осуществляются ежемесячно;
2. Рассчитать общий объем выплат, сумму переплаты, а также сравнить ее по двум вариантам выплат;
3. Составить структуру выплат по ежегодным платежам.
Решение
Для начала вводим в Excel исходные данные для решения задачи, которые примут следующий вид:
Таблица 1.
Исходные данные
| Сумма кредита |
4 000 000,00р. |
| Ставка процента |
20% |
| Срок погашения, лет |
6 |
1. Для определения объема ежегодных выплат используем функцию ПЛТ. Данная функция возвращает сумму периодического платежа для аннуитета на основе постоянства сумм платежей и постоянства процентной ставки: ПЛТ(ставка;кпер;пс;бс;тип).
Объем ежемесячных выплат рассчитывается аналогично, с учетом следующих особенностей. Чтобы получить месячную процентную ставку, годовую ставку необходимо разделить на 12. Чтобы узнать количество выплат, нужно умножить количество лет кредита на 12.
2. Для нахождения общей суммы, выплачиваемой на протяжении интервала выплат, необходимо умножить возвращаемое функцией ПЛТ значение на «кпер».
И наконец, объем переплаты рассчитывается как разница между объемом общей суммы выплат за 6 лет и суммой кредита.
Полученные результаты приведены в следующей таблице:
Таблица 2.
Объем выплат по кредиту
| Ежегодная оплата |
Ежемесячная оплата |
|
| Объём выплат |
1 202 822,98р. |
95 811,30р. |
| Общий объём платежей |
7 216 937,90р. |
6 898 413,90р. |
| Переплата |
3 216 937,90р. |
2 898 413,90р. |
Как видно из табл. 2, объем переплаты при ежегодной оплате кредита значительно превышает соответствующий показатель при ежемесячной оплате.
3. Выплаты по кредиту состоят из двух частей: выплат по основному долгу и выплат по процентам.
Выплаты по основному долгу в каждом из периодов определяются с помощью функции ОСПЛТ. Данная функция возвращает величину платежа в погашение основной суммы по инвестиции за данный период на основе постоянных периодических платежей и постоянной процентной ставки: ОСПЛТ (ставка;период;кпер;пс;бс;тип).
Выплаты по процентам в каждом из периодов определяются с помощью функции ПРПЛТ. Данная функция возвращает сумму платежей процентов по инвестиции за данный период на основе постоянства сумм периодических платежей и постоянства процентной ставки: ПРПЛТ(ставка ;период;кпер;пс;бс;тип).
Остаток основного долга в каждом из периодов определяется как разница между суммой основного долга на начало периода и суммой выплаты по основному долгу в рассматриваемом периоде.
Структура платежей по кредиту при осуществлении ежегодных выплат представлена в следующей таблице.
Таблица 3.
Структура ежегодных выплат по кредиту
| Период |
Выплаты по основному долгу |
Выплаты по процентам |
Остаток основного долга |
| 0 |
4 000 000,00р. |
||
| 1 |
402 822,98р. |
800 000,00р. |
3 597 177,02р. |
| 2 |
483 387,58р. |
719 435,40р. |
3 113 789,44р. |
| 3 |
580 065,10р. |
622 757,89р. |
2 533 724,34р. |
| 4 |
696 078,12р. |
506 744,87р. |
1 837 646,22р. |
| 5 |
835 293,74р. |
367 529,24р. |
1 002 352,49р. |
| 6 |
1 002 352,49р. |
200 470,50р. |
0,00р. |
Согласно данным табл. 3 на начальных периодах в структуре выплат преобладают выплаты по процентам, тогда как в последующих периодах доминируют выплаты по основному долгу. Данный факт наглядно проиллюстрирован на приведенном ниже рисунке:
Рис. 1 Структура ежегодных выплат по кредиту в руб.
Чистый приведенный доход банка определяется с помощью функции ПС. Данная функция возвращает приведенную (к текущему моменту) стоимость инвестиции: ПС (ставка;кпер;плт;бс;тип). Приведенная (нынешняя) стоимость представляет собой общую сумму, которая на данный момент равноценна ряду будущих выплат. Например, в момент займа его сумма является приведенной (нынешней) стоимостью для заимодавца. С учетом инфляции в 15 % чистый приведенный доход банка в данной задаче будет составлять 4 552 062,76р.
Задание 2
Вы планируете взять в долг у друга 700 000 руб. и собираетесь выплачивать ежемесячно 30 000 руб. Сколько месяцев займет выплата долга, если ставка процента составляет 15 % годовых?
Решение
Вводим в Excel исходные данные для решения задачи, которые примут следующий вид:
Таблица 4.
Исходные данные
| Сумма кредита, руб. |
700 000,00р. |
| Ставка, % |
15,00% |
| Ежемесячная выплата, руб. |
30 000,00р. |
| Срок погашения, месяцев |
? |
Срок погашения кредита рассчитывается с помощью функции Кпер. Данная функция возвращает общее количество периодов выплаты для инвестиции на основе периодических постоянных выплат и постоянной процентной ставки: КПЕР(ставка;плт;пс;бс;тип). Указав на ячейки, содержащие сумму кредита, процентную ставку и сумму ежемесячных выплат, получаем, что срок погашения кредита равен 28 месяцев.
Задание 3
Вы планируете зарезервировать средства для проекта, который будет осуществлен через 3 года. Для этого вы открываете депозитный счет с первоначальным взносом 300 000 руб. и затем вносите дополнительно 20 000 руб. в начале каждого следующего месяца. Процентная ставка банка – 17 %. Какая сумма будет накоплена к началу реализации проекта?
Решение
Исходные данные задачи примут следующий вид:
Таблица 5.
Исходные данные
| Первоначальный взнос |
300 000р. |
| Дополнительный ежемесячный взнос |
20 000р. |
| Ставка, % |
17,00% |
| Срок депозита, лет |
3 |
| Будущая стоимость депозита |
? |
Будущая стоимость депозита определяется с помощью функции БС. Данная функция возвращает будущую стоимость инвестиции на основе периодических постоянных (равных по величине сумм) платежей и постоянной процентной ставки: БС(ставка; кпер; плт; пс; тип). Используя данные задачи, получаем будущую стоимость депозита, равную 1 441 825,55р.
2. Определение показателей деятельности сети магазинов «Наслаждение»
Задание 1
Вы – управляющий сети магазинов, имеете информацию о выручке каждого из 6 магазинов за 4 квартала. Необходимо определить:
1. Объем выручки сети за квартал;
2. Сумму выручки и среднюю выручку каждого магазина за год;
3. Долю каждого магазина в суммарной выручке за год и ранг магазина в зависимости от доли;
4. Количество магазинов, входящих в тот или иной диапазон в зависимости от суммы выручки;
5. Размер премии каждого магазина, которая составляет 10 % от суммарной годовой выручки магазина при условии превышения средней выручки за квартал на 20 000 руб. При этом премия директору за 1-ое место – 30 000 руб., за 2-ое место – 15 000 руб., за 3-е место – 10 000 руб.;
6. Изобразить тренд годовой выручки сети магазинов и спрогнозировать суммарную выручку сети на 2 квартала вперед.
Сеть магазинов «Наслаждение занимается производством и продажей кондитерских изделий. Исходные данные представлены в следующей таблице:
Таблица 6.
Выручка сети магазинов "Наслаждение"
| Название магазина |
1 квартал, руб. |
2 квартал, руб. |
3 квартал, руб. |
4 квартал, руб. |
| "Сладкая жизнь" |
100 000,00 |
80 000,00 |
95 000,00 |
100 000,00 |
| "Райское наслаждение" |
50 000,00 |
65 000,00 |
70 000,00 |
80 000,00 |
| "Смак" |
75 000,00 |
55 000,00 |
60 000,00 |
70 000,00 |
| "Медовик" |
90 000,00 |
95 000,00 |
100 000,00 |
120 000,00 |
| "Наполеон" |
110 000,00 |
75 000,00 |
80 000,00 |
90 000,00 |
| "Сказки Шахерезады" |
200 000,00 |
100 000,00 |
120 000,00 |
110 000,00 |
Решение
1. Для определения общего объема сети за квартал используем автосуммирование значений по столбцам табл. 6
2. Сумма выручки каждого магазина за год рассчитывается автосуммированием соответствующих значений по строкам табл. 6. Средняя выручка каждого магазина рассчитывается с помощью функции СР ЗНАЧ, которая возвращает среднее значение выделенных ячеек.
3. Доля каждого магазина в суммарной выручке рассчитывается, как отношение годовой выручки магазина к суммарной выручке сети. Ранг магазина определяется с помощью функции РАНГ. Данная функция возвращает ранг числа в списке чисел. Ранг числа — это его позиция относительно других значений в списке.
Результаты решения п.1 – 3 данной задачи представлены в табл. 7:
Таблица 7
| Название /Кварталы |
1 |
2 |
3 |
4 |
Средняя выручка |
Сумма выручки |
Доля |
Ранг |
|
| м-н "Сладкая жизнь" |
100 000,00 |
80 000,00 |
95 000,00 |
100 000,00 |
93 750,00 |
375 000,00 |
17,12% |
3 |
|
| м-н "Райское наслаждение" |
50 000,00 |
65 000,00 |
70 000,00 |
80 000,00 |
66 250,00 |
265 000,00 |
12,10% |
5 |
|
| м-н "Смак" |
75 000,00 |
55 000,00 |
60 000,00 |
70 000,00 |
65 000,00 |
260 000,00 |
11,87% |
6 |
|
| м-н "Медовик"<
/p>
|
90 000,00 |
95 000,00 |
100 000,00 |
120 000,00 |
101 250,00 |
405 000,00 |
18,49% |
2 |
|
| м-н "Наполеон" |
110 000,00 |
75 000,00 |
80 000,00 |
90 000,00 |
88 750,00 |
355 000,00 |
16,21% |
4 |
|
| м-н "Сказки Шахерезады" |
200 000,00 |
100 000,00 |
120 000,00 |
110 000,00 |
132 500,00 |
530 000,00 |
24,20% |
1 |
|
| Итого
|
625 000,00 |
470 000,00 |
525 000,00 |
570 000,00 |
547 500,00 |
2 190 000,00 |
100,00% |
||
В таблице 7 применено условное форматирование, которое позволяет выделять ячейки с важной информацией и нестандартными значениями, а также улучшает восприятие данных с помощью набора значковых, гистограмм и цветовых шкал.
4. Предположим, существуют 4 диапазона суммы выручки: 0 – 200 000 руб., 200 000 – 350 000 руб., 350 000 – 400 000 руб., 400 000 – 600 000 руб. Для определения магазинов, входящих в тот или иной диапазон, используем функцию ЧАСТОТА, которая вычисляет частоту появления значений в интервале значений и возвращает массив чисел. Распределение магазинов по интервалам представлено в следующей таблице:
Таблица 8.
Распределение магазинов по интервалам в зависимости от выручки
| Сумма выручки |
Диапазон |
Частота |
|
| м-н "Сладкая жизнь" |
375 000,00 |
200 000,00 |
0 |
| м-н "Райское наслаждение" |
265 000,00 |
350 000,00 |
2 |
| м-н "Смак" |
260 000,00 |
400 000,00 |
2 |
| м-н "Медовик" |
405 000,00 |
600 000,00 |
2 |
| м-н "Наполеон" |
355 000,00 |
||
| м-н "Сказки Шахерезады" |
530 000,00 |
5. Для выделения магазинов, заслуживших премию, используем функцию ЕСЛИ. Возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ.
По данным задачи, если средняя выручка данного магазина превышает среднюю выручку сети (70 000 руб. – пороговое значение), то магазин получает премию. В обратном случае магазин остаётся без премии.
Для определения премии директоров магазина также используется функция ЕСЛИ. При этом используется сложное условие с несколькими параметрам для каждого магазина сети. Премия директора, зависит от ранга предприятия.
Результаты выполнения п.4 – 5 представлены в табл. 9
Таблица 9.
Премии магазинам и их директорам
| Средняя выручка |
Премия (исходя из выручки) |
Премия директору за место |
|
| м-н "Сладкая жизнь" |
93 750,00 |
37500 |
5000 |
| м-н "Райское наслаждение" |
66 250,00 |
без премии |
нет |
| м-н "Смак" |
65 000,00 |
без премии |
нет |
| м-н "Медовик" |
101 250,00 |
40500 |
15000 |
| м-н "Наполеон" |
88 750,00 |
35500 |
нет |
| м-н "Сказки Шахерезады" |
132 500,00 |
53000 |
30000 |
6. График выручки каждого магазина в течение года представлен на следующем рисунке:
Рис. 2 Выручка магазинов сети за 4 квартала, руб.
Аналогичным образом строится график суммарной выручки сети «Наслаждение» за год. Добавим к графику линию тренда (характеризует осовную тенденцию развития события или явления) и продлим полученную тенденцию на 2 квартала вперёд. Результат представлен на рис.3:
Рис. 3 Тенденция изменения суммарной выручки сети
Как видно на рис. 3, на предприятии существует тенденция снижения суммарной выручки.
3. Использование инструмента «Поиск решения» при выполнении задач
Задача 1
Небольшая фабрика выпускает 2 вида красок: для внутренних и наружных работ. Продукция двух видов поступает в оптовую продажу. Для производства используются два вида сырья: А и В. Максимально возможные суточные запасы этих продуктов – 6 т и 8 т. Расходы А и В на 1 тонну приведены в таблице:
| Исходный продукт |
Удельный расход на тонну, тонн |
Возможный запас, тонн |
|
| Краска 1 |
Краска 2 |
||
| А |
1 |
2 |
6 |
| В |
2 |
1 |
8 |
Оптовые цены – 3 000 руб. для краски 1 и 2 000 руб. для краски 2. Какое количество краски каждого вида должна производить фабрика, чтобы доход от реализации был максимальным?
Решение
Пусть Х1 и Х2 – суточный объем производства 1-ой и 2-ой краски, тогда целевая функция У = 3000* Х1 + 2000*Х2. Ограничения в запасах примут вид: Х1+2* Х2 ≤ 6, 2*Х1+ Х2 ≤ 8. Логическим ограничением является также то, что Х1 ≥ 0, Х2 ≥ 0.
Вводим вышеуказанные данные в соответствующие ячейки инструмента «Поиск решения», максимизируя целевую функцию. Поиск решения нашёл оптимальный вариант производства краски, дающий в сутки 3.33 т краски 1 и 1.33 т краски 2. Этот объем производства принесет 126 руб. дохода.
Решение данной задачи представлено в табл. 10
Таблица 10.
Оптимизация производства краски
| Переменные |
Суточный доход, руб. |
|
| Х1 |
Х2 |
|
| 3,33 |
1,33 |
|
| Функция цели |
12666,67 |
|
| Ограничения |
||
| 6 |
6 |
|
| 8 |
8 |
|
Задача 2 (вар.4)
Фирма производит 2 вида продукции: А и В. Объём сбыта продукции А составляет не менее 60 % общего объёма реализации. Для изготовления продукции А и В используется одно и то же сырьё, суточный запас которого ограничен величиной 100 кг. Расход сырья на единицу продукции А и В – 2 кг и 4 кг. Цены на продукцию – 20$ и 40$ соответственно. Определить оптимальное распределение сырья по двум видам продукции.
Решение
Пусть Х1 и Х2 – объем производства продукции А и В. Тогда доход от реализации рассчитывается следующим образом У = 20*Х1 + 40*Х2. Т.к. объём сбыта продукции А составляет не менее 60 % общего объёма реализации, то Х1 ≥ 0.6 * (Х1 + Х2). Отсюда следует, что Х1 – 1.5*Х2 ≥ 0. Ограничение в запасах сырья примет вид: 2*Х1 + 4*Х2 ≤ 100 кг.
Таблица 11.
Оптимальное распределение сырья
| Продукт, шт. |
|
| А |
В |
| 21,43 |
14,29 |
| Сырье, кг |
|
| 42,86 |
57,14 |
| Функция цели |
$1 000,00 |
| Ограничения |
|
| 100 |
100 |
| 0,00 |
0 |
Поиск решения нашел оптимальный объем производства продукции А и В, что составляет 21.43 и 14.29 единиц соответственно. При этом оптимальное распределение сырья – 42, 86 кг на продукцию А и 57,14 кг – на продукцию Б. Данное распределение сырья обеспечит максимальную суточную прибыль в 1000 $. Решение данной задачи представлено в табл. 11.
Задача 3 (вар.8)
Требуется распределить денежные средства по четырем альтернативным вариантам. Игра имеет 3 исхода. Ниже приведены размеры выигрыша (проигрыша) от каждого доллара, вложенного в один из альтернативных вариантов при любом исходе. У игрока имеется 500 $, которые он может использовать в игре только 1 раз. Исход игры заранее неизвестен, и, учитывая эту неопределённость, игрок решил распределить деньги так, чтобы максимизировать минимальную отдачу от вложенных средств.
| Исход |
Выигрыш или проигрыш по каждому доллару, вложенному в данный вариант |
|||
| 1 |
2 |
3 |
4 |
|
| 1 |
-3 |
4 |
-7 |
15 |
| 2 |
5 |
-3 |
9 |
4 |
| 3 |
3 |
-9 |
10 |
-10 |
Решение
Пусть А, В, С и D – денежные средства, вложенные в соответствующие альтернативные варианты. Тогда прибыль игрока в каждом из исходов будет составлять: П1 = -3*А + 4*В – 7*С + 15* D,
П2 = 5*А - 3*В + 9*С + 4* D,
П3 = 3*А - 9*В + 10*С - 10* D.
Т.к. исход заранее неизвестен, то необходимо максимизировать минимальную вероятную прибыль каждого из исходов: min (П1;П2;П3). Значит, целевая функция – минимальный возможный доход в каждом исходе. Ограничение в денежных средствах будет следующее: А + В +С + D ≤ 500. При этом нужно учитывать логические ограничения: А ≥ 0, В ≥ 0, С ≥ 0, D ≥ 0. Решение данной задачи представлено в табл. 12:
Таблица 12.
Оптимальное распределение денежных средств
| Денежные средства, распределенные по 4-ем вариантам |
|||
| А |
В |
С |
D |
| $0,00 |
$0,00 |
$297,62 |
$202,38 |
| Исход 1 |
$952,38 |
||
| Исход 2 |
$3 488,10 |
||
| Исход 3 |
$952,38 |
||
| Функция цели |
$952,38 |
||
| Ограничения |
|||
| 500,00 |
$500,00 |
||
После введения данных поиск решения нашел оптимальный вариант распределения денежных средств. Вложение 297,62 $ в 3-ий вариант и 202,38 $ в 4-ый вариант обеспечит максимизацию минимальной отдачи от вложенных средств, которая составит 952.38 $.