Информационные технологии в антикризисном управлении (стр. 2 из 2)
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 $.