Реферат: Информационные технологии в антикризисном управлении

ОТЧЁТ ПО ЛАБОРАТОРНЫМ РАБОТАМ

Информационные технологии вантикризисном управлении

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. Структураежегодных выплат по кредиту

Период Выплаты по основному долгу Выплаты по процентам Остаток основного долга 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 441825,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 м-н «Медовик» 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 м-н «Райское наслаждение» 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

Поиск решения нашелоптимальный объем производства продукции А и В, что составляет 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$.