p align="left">В таблице 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 $.
Страницы: 1, 2
|