🗊Презентация Анализ деловых данных. Решение задач оптимизации в MS Excel

Анализ деловых данных Решение задач оптимизации в MS Excel

Средства MS Excel для анализа данных Одно из наиболее важных достоинств Excel состоит в том, что он позволяет легко и быстро выполнять анализ «что-если» и на его основе составлять прогнозы на будущее. Анализ «что-если» - это процесс поиска ответов на вопросы типа: «Что будет, если процентная ставка кредита поднимется с 8,5% до 9%?» и т. д. Можно изменять основные переменные и в ячейках с формулами будут результаты этих изменений. Помимо такого анализа «вручную», Excel содержит целый ряд полезных средств планирования, к числу которых относятся процедуры Подбора параметра и Поиска решения.

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

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

Поиск решения Если решение найдено, его можно сохранить, нажав кнопку <OK> (подобранное значение параметра сохранится в изменяемой ячейке), или вернуться к исходному состоянию, нажав кнопку <Отмена>. Решение может быть не найдено, если результат зависит не от одного параметра или если изменяемая ячейка и целевая ячейка логически не связаны. В тех случаях, когда оптимизационная задача содержит несколько переменных величин, для анализа необходимо воспользоваться надстройкой Поиск решения.

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

Поиск решения Несмотря на многообразие таких задач, встречающихся в жизни и экономике на каждом шагу, Excel предлагает единый мощный инструмент их решения - средство поиска оптимального решения. Необходимо только грамотно сформулировать для Excel задачу (составить ее математическую модель), а оптимальное решение будет найдено быстро и точно. Рассмотрим решение линейных оптимизационных задач на примере типичных ситуаций: планирование производства (на лекции), планирование штатного расписания, составление сплавов и смесей, транспортная задача (на практике).

Математическая модель Математическая модель – достаточно точное описание с помощью математического аппарата (уравнений, неравенств или их систем) исследуемого экономического процесса или объекта.

Задача планирования производства Фирма производит две модели А и В сборных книжных полок. Их производство ограничено наличием сырья (высококачественных досок) и временем машинной обработки. Для каждого изделия модели А требуется 3 кв.м досок, а для изделия модели В - 4 кв.м. Фирма может получать от своих поставщиков до 1700 кв.м досок в неделю. Для каждого изделия модели А требуется 12 мин машинного времени, а для изделия модели В - 30 мин. В неделю можно использовать 160 ч машинного времени. Сколько изделий каждой модели следует выпускать фирме в неделю, если каждое изделие модели А приносит 2 долл. прибыли, а каждое изделие модели В - 4 долл. прибыли?

Математическая модель задачи Обозначим: х - количество изделий модели А, выпускаемых в течение недели, у - количество изделий модели В. Прибыль от этих изделий равна 2х+4у долл. Эту прибыль нужно максимизировать. Функция, для которой ищется экстремум (максимум или минимум), носит название целевой функции. Беспредельному увеличению количества изделий препятствуют ограничения. Ограничено количество материала для полок, отсюда неравенство Зх + 4у ≤1700 . Ограничено машинное время на изготовление полок. На изделие А уходит 0,2 часа, на изделие В - 0,5 часа, а всего не более 160 ч, поэтому 0,2х + 0,5у ≤ 160 . Кроме того, количество изделий - неотрицательное число, поэтому х ≥ 0, у ≥ 0.

Математическая модель задачи Формально наша задача оптимизации записывается так: Целевая функция – прибыль 2х + 4у → max Ограничения Зх + 4у ≤ 1700 0,2x + 0,5у ≤ 160 х ≥ 0, у ≥ 0 Теперь решим эту задачу в Excel.