Дана калькуляция доходов и расходов от издания партии компакт дисковCD. Какой должна быть цена одного диска, чтобы получить доход 200000 руб. вместо188000 руб

I. Подбор параметра

Подбор параметра – удобное средство Excel для анализа " Что-если ". Подбор параметра предназначен для определения значения, которое надо подставить в одну ячейку, чтобы получить требуемое значение в другой ячейке. Эту процедуру используют для поиска такого значения ячейки, при котором значение другой ячейки вычисляемой по формуле, заранее задано. В формуле должна быть ссылка на ячейку, значения которой ищут. Ограничения на искомое значение ячейки не налагают. При этом значения для ячеек-параметров изменяется так, чтобы число в целевой ячейке стало равно заданному числу. Для подбора в диалоговом окне задается требуемое значение функции: в поле изменения значения ячейки указывается адрес ячейки, содержащий значение одного из аргументов функции.

Подбор параметра – простейший метод нахождения оптимального желаемого решения за счет изменения одного из параметров. Суть этого метода лучше всего рассмотреть на примере.

Пример 1. Пусть известно, что в штате больницы состоит 6 санитарок, 8 медсестер, 10 врачей, 2 заведующих отделениями, главный врач. Общий месячный фонд зарплаты составляет 437 тыс.руб. Необходимо определить, какими должны быть оклады каждого сотрудников.

За основу берется оклад санитарки, а все остальное вычисляется через него: "во столько-то раз или на столько-то больше". Каждый оклад является линейной функцией от оклада санитарки: A*C+B, где C – оклад санитарки; A и B – коэффициенты, которые для каждой должности определяются советом трудового коллектива.

Допустим, медсестра получает в 1,5 раза больше санитарки: A=1,5 и B=0;

врач в 3 раза больше санитарки: A=3 и B=0;

заведующий отделением на 1000руб. больше, чем врач: A=3 и B=1000;

главный врач – в 4 раза больше санитарки: A=4 и B=0.

Зная количество человек на каждой должности (6– санитарок, 8 – медсестер, 10 – врачей, 2 – заведующих, 1 – главный врач), можно составить уравнение:

6*C+8*1,5*C+10*3*C+2*(3*C+1000)+4*C=437000.

Подбора параметра.

Можно найти значение оклада санитаркипутем подбора параметра. Зададим этот оклад, например, 8000руб. Рассчитаем зарплату каждого сотрудника с учетом данного оклада.

Получим итоговую сумму 466000. Но мы должны уложиться в сумму 437000 руб. Для этого осуществим подбор оклада санитарки.

При установке курсора в ячейку F8, и выполнении команды Данные-Анализ"что если"-Подбор параметра появляется диалоговое окно, в котором задается требуемое значение функции.

В поле Изменяя значение ячейки указывается адрес ячейки H2, значение которой нужно изменить, для того чтобы достичь в ячейке F8 значения 437000. При нажатии кнопки ОК подобранное значение аргумента сохраняется в ячейке аргумента. В нашем случае оклад санитарки изменится с 8000 руб. до 7500 руб.

Задание 1.

Дана калькуляция доходов и расходов от издания партии компакт дисковCD. Какой должна быть цена одного диска, чтобы получить доход 200000 руб. вместо188000 руб.

Сделать подбор параметра, используя для расчетов следующие формулы:

II. Поиск решения

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

Средство анализа Поиск решения– это определение оптимального значения целевой ячейки, изменяя значения ячеек, которые используются для расчета значения целевой ячейки.

К этому виду задач относятся следующие:

· определение максимальной прибыли от продажи при ограничениях на ресурсы;

· выбор оптимального плана перевозок продукции из складов в пункты назначения.

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

Пример 2. Мебельная фабрика производит столы и стулья. Расходы на их производство и прибыль от их реализации представлены в таблице.

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

Пусть Х1 – количество столов;

Х2 – количество стульев.

Тогда система ограничений запишется следующим образом:

0,5*X1 + 0,04*X2 <=200 (ограничения по древесине);

12*X1+0,6*X2<=1800 (ограничения по труду);

3000*Х1+1500*X2"MAX (целевая функция должна стремиться к максимуму, так как надо обеспечить максимальную прибыль);

Для решения задачи запишем ее в следующем виде. В ячейках B2:C2 находятся коэффициенты первого неравенства, в ячейках B3:C3 коэффициенты второго неравенства, в ячейках B4:C4 коэффициенты целевой функции.

Для решения задачи вызовем диалоговое окноПоиск решения.В нем укажем:

· адрес целевой ячейки (E4);

· диапазон искомых ячеек (B5:C5);

· ограничения:

B5>=80;(количество столов должно быть больше 80).

B5:C5=целое; (количество столов и стульев это целые величины).

B5:C5 >=0; (количество столов и стульев это положительные величины).

E2<=G2;

E3<=G3.

Как видно из результатов решения, предприятию производить столы не очень выгодно. Поэтому оно ограничило объем их выпуска в количестве, необходимом для выполнения контракта. Остальные ресурсы направлены на производство стульев.

Диалоговое окно Результата поиска решения позволяет

· сохранить на текущем рабочем листе найденное оптимальное решение;

· восстановить первоначальные значения;

· сохранить сценарий;

· выдать отчеты по результатам, устойчивости, пределам, необходимые для анализа найденного решения.

Задание 3.

Кондитерская фабрика производит несколько видов конфет. Назовем их условно А, В, С. Известно, что реализация 10-и килограммов конфет А дает прибыль 90 руб., В – 100 руб. и С – 160 рублей. Конфеты можно производить в любом количестве (сбыт обеспечен), но запасы сырья ограничены. Необходимо определить, каких конфет и сколько необходимо произвести, чтобы общая прибыль от реализации была максимальной.

Нормы расхода сырья на производство 10 кг конфет каждого вида приведены в таблице.