ЛАБОРАТОРНАЯ РАБОТА

СРЕДСТВА ПОДДЕРЖКИ ПРИНЯТИЯ РЕШЕНИЙ КАК ФУНКЦИИ EXCEL

Команда Подбор параметра

Задание 1. Рассмотрим задачу, составленную на основании задачи по использованию функции ЧПС. Вас просят дать в долг 10000 руб. и обещают вернуть через год 2000 руб., через два года— 4000 руб., через три года 7 000 руб. При какой годовой процентной ставке эта сделка будет выгодной? Для решения задачи:

•              Создайте электронную таблицу (рис. 1.). 

•              В ячейке В6 наберите произвольный процент, например 3%

Рис. 1. Расчет годовой процент ставки

•              В ячейку В7 введите формулу = ЧПС (В6;В2:В4), которая свяжет все исходные данные.

•              Автоматизируйте набор текста в ячейке С5 исходя из данных в ячейке В5. 

•              Последовательно выберите на вкладке Данные в группе Работа с данными кнопку Анализ «что – если», в меню – команду Подбор параметра.

•              В окне диалога команды на рис.2. в строке Установить в ячейке наберите В7 (ячейка, которая содержит формулу), в строке Значение – 10000 (сумма, которую просят в долг), в Изменяя значение ячейки – В6 (адрес ячейки с результатом выполнения задания – процентной ставкой, при которой сделка будет выгодной). Для выполнения команды нажмите ОК.

Рис. 2. Окно диалога команда Подбор параметра

Результат вычисления выводится в ячейке В6 – 11,79%. Вывод: если банки предлагают большую

годовую процентную ставку, то предлагаемая сделка не выгодна.

Задание 2. Согласно данным затрат компании вычислите: 

1)      насколько нужно снизить транспортные расходы, чтобы они составили 40% от общих расходов компании; 

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

Задачи на оптимизацию, решаемые с помощью надстройки Поиск решения

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

Перед началом решения задачи необходимо определить, какая ячейка будет целевой, какие ячейки будут изменяться, и на какие ячейки будут накладываться условия. Обычными задачами, решаемыми с помощью надстройки «Поиск решения» являются: ассортимент продукции. Максимизация  выпуска  товара при ограничениях на сырье (или других ресурсов) для производства изделий; планирование перевозок; минимизация затрат на транспортировку; оптимизация финансовых показателей (например, максимизация доходов за счет оптимизации средств на разные инвестиционные проекты) и т.п. 

Команда Поиск решения находится на вкладке Данные в группе Анализ, если этот компонент не установлен, то необходимо выбрать Файл/Параметры/Надстройки/Управление (надстройки Excel)/Перейти/установить флажок Поиск решений/ОК. 

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

Такие задачи решаются при помощи инструмента «Поиск решения». В диалоговом окне установите исходные данные задачи согласно математической модели: 

Пусть продукция производится в количестве: 1-й вид – x1 единиц, 2-й вид – x2 единиц. Тогда стоимость произведенной продукции выражается целевой функцией: f(x1,x2)=25000 x1+50000x2, для которой необходимо найти максимум. При этом следует учесть ограничения по запасам сырья:

1,2 x₁+1,9 x₂ ≤ 37,

2,3 x₁+1,8 x₂ ≤ 57,6, 0,1 x₁+0,7 x₂ ≤ 7 и по смыслу задачи x1, x2 должны быть неотрицательными и целыми: x1≥0,  x2 ≥0.

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

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

•              Затем коэффициенты целевой функции и нормы расхода сырья расположите в ячейках С3и D3 и С6:D8 соответственно. 

•              Запасы сырья расположите справа от матрицы норм расхода в ячейках G6:G8.  • В ячейке F2 (целевая функция) наберите формулу, которая связывает исходные данные:

=С3*С2+D3*D2

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

•              Выберите команду Поиск решения.

•              В диалоговом окне Поиск  решения необходимо указать:

− адрес ячейки, в которой находится формула, вычисляющая значение целевой функция – F2;

− цель вычислений (задать критерий для нахождения экстремального значение целевой функции) – максимальное значение;

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

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

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

− параметры решения задачи, для чего нажимается кнопка Параметры. Для решения данной задачи параметры не изменяйте.

•              После ввода всех данных и задания параметров нажмите кнопку Найти  решение и

установите переключатель Сохранить найденное  решение.

Задание 2. Необходимо правильно распределить премии сотрудникам. Общая сумма премий составляет 100 000 рублей. Премии должны быть назначены пропорционально окладам сотрудников. Для решения задачи надо установить соответствующий коэффициент пропорциональности, чтобы определить размер премии относительно оклада. Электронная таблица для решения задачи представлена на рис.4.

Рис. 4

Создание сценариев для анализа «что-если»

Сценарии являются частью блока задач, который иногда называют инструментами анализа "чтоесли" Сценарий — это набор значений, которые в приложении Microsoft Office Excel сохраняются и могут автоматически подставляться в лист. Сценарии можно использовать для прогноза результатов моделей расчетов листа. Существует возможность создать и сохранить в листе различные группы значений, а затем переключаться на любой из этих новых сценариев, чтобы просматривать различные результаты.

Чтобы сравнить несколько сценариев, можно создать отчет, обобщающий их на одной странице. Сценарии в отчете могут располагаться один рядом с другим либо могут быть обобщены в отчете сводной таблицы. 

Задание 1. Необходимо выбрать банк для получения кредита, сравнивая исходные данные трех банков:

В такой ситуации используют инструмент Excel, который называется Диспетчер сценариев, который моделирует разные варианты развития событий и наглядно сравнивать их между собой при разных начальных условиях. В нашем случае каждый банк будет представлен отдельным сценарием, которые мы будем использовать

•              Для расчета составьте кредитный калькулятор с использованием функции ПЛТ:

•              Выполните следующие действия вкладка Данные /Анализ "что-если"/Диспетчер сценариев. 

•              В открывшемся окне нажмите кнопку Добавить и введите данные по первому банку - название и диапазон изменяемых ячеек (т.е. тех ячеек, значения которых будут меняться от банка к банку):

•              После нажатия на кнопку ОК в следующем окне введите значения изменяемых ячеек для этого банка:

•              После нажатия на ОК эти же действия повторите, создавая сценарии для каждого банка со своими значениями изменяемых ячеек соответственно. Когда сценарии готовы, просмотрите их с помощью кнопки Вывести и создайте сравнительный отчет по всем сценариям сразу, нажав кнопку Отчет:

MS Excel поддерживает два типа отчетов: структуры - обычные таблицы с группировкой и сводные таблицы. Отчеты типа Структура более красивые, а отчеты сводных таблиц имеют больше возможностей для дальнейшего анализа в сложных случаях. В качестве ячеек результата необходимо выделить те ячейки, которые нам интересно сравнивать для разных банков - в нашем случае можно смело выделить и сумму ежемесячных выплат, и общую стоимость и переплату - пусть покажет все!

После нажатия на ОК Excel добавит новый лист в книгу и выведет на него сравнительный отчет по сценариям  в форме:

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

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

зависимости от изменения доходов и расходов. Исходные данные представлены на рисунке:

Таблица должна содержать следующие формулы: 

С4 =C2-C3 

С6 =C4*C5 

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

Необходимо проанализировать, как изменяется прибыль при следующих значениях исходных данных: 

                                         Сценарий 2 Сценарий3

                Доход                    2,75           4,50 

Затраты                 1,30          2,70  Количество         15 730       17 65

Создайте три сценария. Первый сценарий должен содержать первоначальные значения и иметь имя Исходные данные.