Лабораторная работа № 7. Средства поддержки принятия решений как функции MS Excel

Цель работы: приобрести навыки применения инструментов поддержки принятия решений и научиться анализировать полученные с их помощью данные.

Материал для работы: файл-заготовка ЛР7.xlsx.

Результат работы: файлы ЛР7_ФАМ.xlsx.

Выполнение работы

Подбор параметра. Команда Подбор параметра используется для решения задач, в которых имеются конечный результат и значения исходных данных, кроме одного; требуется получить неизвестное значение (уравнение с одним неизвестным). Команда Подбор параметра располагается на вкладке Данные в группе Работа с данными, группа команд Анализ «что если». Исходные данные и результат должны быть связаны формулой.

С помощью подбора параметра решите следующую задачу: Вы хотите накопить 2000 руб., открыв в банке на год депозит с ежемесячным пополнением 200 руб. Годовая процентная ставка составляет 11,5 %. Требуется определить размер ежемесячного платежа.

Для создания формулы, которая свяжет исходные данные с результатом (заносится в ячейку с требуемой суммой), используйте функцию БС с синтаксисом:

=БС(процентная ставка/12; срок депозита; ежемесячный взнос; [первый взнос]; [Тип начисления процентов])

[] означают, что данный аргумент может отсутствовать.

В задаче первый взнос – это та сумма, которая кладется в банк на депозит.

Тип начисления процентов выставляется 0, если начисляются в конце периода, 1 – если в начале периода. Не указываем.

 откройте файл ЛР7.xlsx. На листе Депозит создайте структуру таблицы с исходными данными;

 занесите в ячейку с ежемесячным платежом (значение, которое требуется определить) пока любое значение;

 наберите в ячейке с требуемой суммой формулу с использованием функции БС. Как и для функции ПЛТ, для значений денежных сумм, которые отдаете в банк ставьте знак «минус»;

выберите команду Подбор параметра;

в окне диалога установите следующие параметры: Установить в ячейке

–     адрес ячейки с требуемой суммой, Значение – требуемое значение суммы

2000, Изменяя значение ячейки – адрес ячейки ежемесячного платежа. Дважды нажмите ОК;

 искомое значение найдено. Ежемесячный платеж составляет 140,34.

Поиск решения. Поиск решения – это надстройка MS Excel, предназначенная для нахождения оптимального решения задачи с учетом установленных пользователем ограничений. Если команда отсутствует на вкладках, то установить ее можно с помощью последовательности действий: вкладка Файл/команда Параметры/ категория Надстройки/ поле Управление/ параметр Надстройки Excel/ кнопка Перейти/ область Доступные надстройки/ флажок Поиск решения/ОК.

 установите Поиск решения, если команда недоступна в группе Анализ

вкладки Данные.

Для изучения технологии выполнения поиска решения решите классическую задачу об оптимальном ассортименте. Предприятие выпускает два вида продукции. Цена единицы первого вида продукции – 25000, второго вида продукции – 50000. Для изготовления продукции используются три вида сырья, запасы которого 37, 57,6 и 7 условных единиц. Требуется определить плановое количество выпускаемой продукции таким образом, чтобы стоимость произведенной продукции была максимальной.

Решение задач такого рода требует предварительного математического описания условий (построения математической модели).

 получите математическое описание задачи:

Пусть продукция производится в количестве: 1-й вид – x₁ единиц, 2-й вид – x₂ единиц.

Тогда стоимость произведенной продукции выражается целевой функцией f(x₁,x₂)=25000 x₁+50000x₂, для которой необходимо найти максимальное значение.

При этом следует учесть ограничения по запасам сырья: 1,2 x₁+1,9 x₂          37,

2,3 x₁+1,8 x₂    57,6,

0,1 x₁+0,7 x₂    7

По смыслу задачи x₁, x₂ должны быть неотрицательными и целыми: x₁ 0,

 создайте таблицу для решения поставленной задачи (рисунок 20).

Рисунок 20 – Таблица для решения задачи и окно диалога команды Поиск решения.

 для переменных, которые требуется найти, x1 и x2 определите соответственно ячейки С2 и D2, задайте им начальные значения, равные нулю;

 цену за единицу продукции каждого вида (коэффициенты целевой функции) и нормы расхода сырья расположите в ячейках С3и D3 и С6:D8 соответственно;

запасы сырья расположите справа от норм расхода в ячейках G6:G8;

в ячейке F2 (целевая функция) наберите формулу, которая связывает исходные данные: =С3*С2+D3*D2

 в ячейках F6:F8 вычислите реальный расход сырья с использованием функции СУММПРОИЗВ. Функция позволяет одновременно умножать и суммировать результат умножения по блокам ячеек и заменяет собой запись формулы с использованием знаков умножения и сложения (как в ячейке F2):

F6           = СУММПРОИЗВ($C$2:$D$2;C6:D6) F7                = СУММПРОИЗВ($C$2:$D$2;C7:D7) F8                = СУММПРОИЗВ($C$2:$D$2;C8:D8)

 выберите команду Поиск решения. В диалоговом окне Поиск решения укажите необходимые параметры (рис.3). Установить целевую – адрес ячейки F2, в которой находится формула, вычисляющая значение целевой функция. Равной – задать критерий для нахождения экстремального значение целевой функции – максимальное значение. Изменяя ячейки – адреса ячеек

$C$2:$D$2, в которых находятся значения изменяемых переменных х₁, х₂;

 в области Ограничения задайте матрицу ограничений, для чего последовательно нажимайте кнопку Добавить: $C$2:$D$2 – целое,

$C$2:$D$2>=0, $C$2:$D$2<=$G$6:$G$8 (ограничения на запасы сырья);

 для решения данной задачи параметры не изменяйте. После ввода всех данных нажмите кнопку Найти решение и установите переключатель Сохранить найденное решение;

 проанализируйте полученное решение.

Создание сценариев. Сценарии являются частью блока задач, который иногда называют инструментами анализа «что-если». Сценарий представляет процесс изменения значений ячеек и анализа влияния этих изменений на результат вычисления формул на листе. Команда по созданию сценариев Диспетчер сценариев находится на вкладке Данные в области Работа с данными.

Выберите банк для получения кредита, сравнивая данные трех банков.      создайте таблицу с исходными данными. Используйте функцию ПЛТ

для связи имеющихся данных (рисунок 21).

Рисунок 21 – Таблица с исходными данными для создания сценария.

Таблица 3 – Данные банков.

 выполните действия вкладка Данные /Анализ "что-если"/Диспетчер сценариев;

 в открывшемся окне нажмите кнопку Добавить и введите данные по первому банку (таблица 3): Название сценария и Изменяемые ячейки (значения, которые будут меняться от банка к банку);

 нажмите ОК. В следующем окне введите значения изменяемых ячеек для первого банка. Нажмите ОК;

аналогично создайте сценарии для двух оставшихся банков;

 просмотрите готовые сценарии с помощью кнопки Вывести и создайте сравнительный отчет по всем сценариям сразу, нажав кнопку Отчет (рисунок 22 ). В строке Ячейки результата укажите блок ячеек с суммой ежемесячных выплат, реальной стоимостью покупки и переплатами. Нажмите ОК. Будет добавлен новый лист и выведен на сравнительный отчет по сценариям;

Рисунок 22 – Окно диалога Отчеты по сценарию.

 удалите лишние данные и замените адреса ячеек на поясняющий текст.

Постройте гистограмму. Проанализируйте полученные данные.

Задания для самостоятельной работы

1.   Согласно данным затрат компании (файл ЛР7, лист Затраты компании) вычислите насколько нужно снизить:

транспортные расходы, чтобы они составили 40% от общих расходов компании;

стоимость работ, чтобы она составляла 20% от общих затрат.

Предварительно определите общую сумму затрат (Всего) и Долю каждой статьи затрат (рисунок 23).

Рисунок 23 – Результаты дополнительных вычислений.

Результаты вычислений копируйте на новые листы (Транспортные расходы, Стоимость работ).

2.   Цех выпускает три вида продукции. Для изготовления продукции используется три вида сырья. Запасы сырья ограничены: сырьѐ первого вида имеется в количестве 2660 единиц, сырьѐ второго вида – в количестве 2000 единиц, сырьѐ третьего вида – в количестве 3030 единиц. Известны нормы расхода сырья на единицу продукции: для выпуска единицы продукции первого

вида требуется 2 единицы сырья первого вида, 1 единица сырья второго, 3 единицы сырья третьего вида; для выпуска единицы продукции второго вида требуется 1 единица сырья первого вида, 3 единицы сырья второго, 4 единицы сырья третьего вида; для выпуска единицы продукции третьего вида требуется 3 единицы сырья первого вида, 2 единицы сырья второго, 1 единица сырья третьего вида. Известна прибыль от реализации единицы продукции: первого вида – 20 единиц, второго – в размере 24 единиц, третьего вида продукции – в размере 28 единиц. Требуется определить оптимальное количество выпуска продукции, исходя из ограничений по запасам сырья, чтобы прибыль от их реализации была максимальной.

3.   Директору магазина необходимо определить, как будет изменяться прибыль в зависимости от изменения доходов и расходов. Исходные данные представлены на рисунке 24.

Рисунок 24 – Данные для решения задачи.

Таблица должна содержать следующие формулы: С4 =C2-C3

С6 =C4*C5

С14 =СУММ(C8:C13) С16 =D6-C14

Определите, как изменится чистая прибыль и расходы при различных значениях исходных данных (таблица 4). Создайте три сценария. Первый сценарий должен содержать первоначальные значения и иметь имя Исходные данные.

Таблица 4 – Исходные данные для решения задачи.