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

4. Предположим, существуют 4 диапазона суммы выручки: 0 – 200 000 руб., 200 000 – 350 000 руб., 350 000 – 400 000 руб., 400 000 – 600 000 руб. Для определения магазинов, входящих в тот или иной диапазон, используем функцию ЧАСТОТА, которая вычисляет частоту появления значений в интервале значений и возвращает массив чисел. Распределение магазинов по интервалам представлено в следующей таблице:

Таблица 8. Распределение магазинов по интервалам в зависимости от выручки

5. Для выделения магазинов, заслуживших премию, используем функцию ЕСЛИ. Возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ.

По данным задачи, если средняя выручка данного магазина превышает среднюю выручку сети (70 000 руб. – пороговое значение), то магазин получает премию. В обратном случае магазин остаётся без премии.

Для определения премии директоров магазина также используется функция ЕСЛИ. При этом используется сложное условие с несколькими параметрам для каждого магазина сети. Премия директора, зависит от ранга предприятия.

Результаты выполнения п.4 – 5 представлены в табл. 9

Таблица 9. Премии магазинам и их директорам

6. График выручки каждого магазина в течение года представлен на следующем рисунке:

Рис. 2 Выручка магазинов сети за 4 квартала, руб.

Аналогичным образом строится график суммарной выручки сети «Наслаждение» за год. Добавим к графику линию тренда (характеризует осовную тенденцию развития события или явления) и продлим полученную тенденцию на 2 квартала вперёд. Результат представлен на рис.3:

Рис. 3 Тенденция изменения суммарной выручки сети

Как видно на рис. 3, на предприятии существует тенденция снижения суммарной выручки.

3. Использование инструмента «Поиск решения» при выполнении задач

Задача 1

Небольшая фабрика выпускает 2 вида красок: для внутренних и наружных работ. Продукция двух видов поступает в оптовую продажу. Для производства используются два вида сырья: А и В. Максимально возможные суточные запасы этих продуктов – 6 т и 8 т. Расходы А и В на 1 тонну приведены в таблице:

Оптовые цены – 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. Оптимизация производства краски

Задача 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 кг – на продукцию Б. Данное распределение сырья обеспечит максимальную суточную прибыль в 1000 $. Решение данной задачи представлено в табл. 11.

Задача 3 (вар.8)

Требуется распределить денежные средства по четырем альтернативным вариантам. Игра имеет 3 исхода. Ниже приведены размеры выигрыша (проигрыша) от каждого доллара, вложенного в один из альтернативных вариантов при любом исходе. У игрока имеется 500 $, которые он может использовать в игре только 1 раз. Исход игры заранее неизвестен, и, учитывая эту неопределённость, игрок решил распределить деньги так, чтобы максимизировать минимальную отдачу от вложенных средств.

Решение

Пусть А, В, С и 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. Оптимальное распределение денежных средств

После введения данных поиск решения нашел оптимальный вариант распределения денежных средств. Вложение 297,62 $ в 3-ий вариант и 202,38 $ в 4-ый вариант обеспечит максимизацию минимальной отдачи от вложенных средств, которая составит 952.38 $.