 ЗАДАЧИ ОПТИМИЗАЦИИ

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

Инструмент Подбор параметра (вкладка Данные > группа Работа с данными> пиктограмма Анализ “что-если” > пункт Подбор параметра) позволяет найти значение аргумента, удовлетворяющее желаемому значению функции. С его помощью можно получать результаты, которые трудно или невозможно получить прямо.

Замечание. Здесь и далее клетки, содержащие формулы выделены фоном (в электронной версии – желтым).

Задача 1. Пусть расчет зарплаты выполняется следующим образом (рис.1-1а). Налогом облагается не вся зарплата. Сумма обложения меньше заработка на налоговый вычет (1000р) на самого работника и на каждого ребенка. Здесь же учитываются вычеты медицинской страховки (2%). Что бы избежать отрицательности суммы обложения, в D3 используется функция МАКС(), которая обеспечивает равенство нулю суммы обложения, если она становится отрицательной. На рис. 1-1а представлены формулы. На рис. 1-1в – результат для штатного работника Ивана. Для него расчет ведется в обычном порядке – вводится заработок (30000р) и автоматически вычисляется результат (26060р).

Теперь решим обратную задачу. Пусть вы договорились с некоторым исполнителем (Петром) о выполнении разовой работы за 5000 руб. “чистыми” и с Олегом за 500р. Чтобы провести эти выплаты в бухгалтерии следует определить исходную зарплату до изъятия всех вычетов. Т.е. решить обратную задачу – определить неизвестную исходную зарплату Петра по известной сумме “на руки”. Вызвав Подбор параметра, зададим (рис. 1-1б) аргументы: Установить в ячейке: G4 , в новое Значение: 5000 , Изменяя значение ячейки: B4 . После нажатия ОK, Excel выдает окно Результат подбора параметра, где отображаются ожидаемые результаты операции. В данном случае системе удалось подобрать аргумент (B4=5663) при котором для Петра результат равен 5000. Далее, если Решение найдено и пользователь согласен с ними, следует нажать ОК, если нет – кнопку Отмена (произойдет возврат к исходным значениям). Аналогичные действия совершаем для Олега. Здесь (маленькая зарплата) видим, что налог с него не удерживается

При наборе, клетки в строках 4 и 5, заполняются формулами, скопированными из строки 3. Клетки В4 и В5 не заполняются в ручную, а вычисляются компьютером.

	A	B	C	D	E	F	G		Подбор параметра            Установить в ячейке: Значение: Изменяя значение ячейки:
1	Вычет:	1000		Расчет зарплаты			Рис. 1-1а
2	Имя	Зарплата	Дети	Сумма обложения	Налог	Страховка	НА РУКИ
3	Иван	30000	2	=МАКС(B3-B$1*C3;0)	=13%*D3	=1%*B3	=B3-E3-F3
							Рис. 1-1в
3	Иван	30000	2	28000	3640	300	26060
4	Петр	5663	1	4663	606	57	5000
5	Олег	505	2	0	0	5	500		Рис.1-1б

Как видим, предложенное средство удобно для разного рода обратных расчетов.

	A	B	C	D				Подбор параметра
1	Продуктовый расчет
2	Объем сырья:		1000					Установить в ячейке:		$D$6
3	Этап	Коэфф. потерь	Объем потерь	Выход				Значение:		2000
								Изменяя значение ячейки:		$C$2
4	1	0,05	?	?
5	2	0,11	?	?
6	3	0,02	?	?
				Рис.		1		-2
	A	B	C					Подбор параметра
1	Вклад:	100
2	Сложные проценты								Установить в ячейке:		$C$7
3	Норма	Доход	Сумма					Значение:		500
4	10%	?	?					Изменяя значение ячейки:		$B$1
5	12%	?	?
6	15%	?	?
7	18%	?	?	Рис.		1		-3

Задача 2. Продуктовый расчет. Пусть (рис. 1-2) нужно по известному объему используемого сырья (С2) вычислить выход некоторого продукта на каждом этапе обработки (столбец D) и итоговый выход D6. Известны потери продукта на каждом этапе обработки относительно предыдущего этапа (столбец В). Написать формулы расчета. Это была формулировка прямого продуктового расчета. Однако часто нужно вычислить требуемый объем сырья для производства заданного количества продукта (обратный продуктовый расчет). Пусть, мы хотим выяснить, сколько сырья требуется для выпуска 2000 единиц продукции. Для этого в окне Подбор параметра нужно задать аргументы. В результате мы должны получить объем потребного сырья в 2414. Кроме этого, будет произведен перерасчет потерь и остатков на выходе для всех этапов технологического процесса. Введите нужные формулы. Выполните подбор.

Задача 3. Расчет дохода. Обычно требуется найти сумму на счету от начального вклада (у нас 100 т. руб.) в течение нескольких лет при известной ежегодной норме прибыли. Заполните таблицу рис.1-3 необходимыми формулами. Поставим задачу обратным образом. Пусть нужно выяснить, сколько средств следует положить на счет, чтобы в конце расчетного периода накопить 500 т.руб. (клетка С7). Используя Подбор параметра. можно выяснить, что нужно вложить 299,1 т.руб. Введите формулы. Сделайте подбор.

Задача 4. Пусть нужно выяснить перспективы производства некоторого продукта. Известно, что понадобятся первоначальные инвестиции на строительство цеха и закупку минимального оборудования в объеме 50000$ для выпуска первых 1000 единиц продукции в месяц. Изготовление одного изделия требует сырья на 5$. Расширение выпуска возможно только партиями до 1500 штук для чего каждый раз требуется покупка оборудования (станка) на 7000$. Известна рыночная цена изделия 20$. Нужно найти уровень производства, обеспечивающий его безубыточность, а также графически проанализировать динамику доходов, расходов, прибыли и себестоимости в зависимости от количества выпущенного товара. Отобразим наши данные и формулы в таблице на рис. 1-4а.

Здесь: Расходы=Строительство+Сырье+Затраты_на_расширение

или G2=A2+E2*C2+ОКРУГЛВВЕРХ((E2-B2)/1500;0)*D2.

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

Себестоимость=Расходы/Произведено_товара или H2=G2/E2.

Доход=Произведено_товара*Рыночная_цена или I2=E2*F2.

Прибыль=Доход–Расходы или J2=I2-G2.

Первоначальный выпуск установлен в 1000 штук. Видим, что при этом результаты нашей деятельности принесут только убытки в объеме 35000$.

Задача состоит в том, чтобы определить минимальное количество единиц выпускаемого товара, которое обеспечит безубыточность производства, т.е. когда прибыль=0 или, что тоже самое, когда себестоимость=рыноч­ная цена.

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

	A	B	C	D	E	F	G	H	I	J
1	Строи- тельство	Начальный выпуск	Расходы сырья на 1 штуку	Затраты на следующие 1,5 тысячи	Произведено единиц товара	Рыночная цена единицы	ВСЕГО расходов	Себестоим. единицы	Доход	Прибыль
2	50000$	1000	5$	7000$	1000	20$	55000$	55$	20000$	-35000$	Рис.1-4а
2	50000$	1000	5$	7000$	4733	20$	94667$	20$	94667$	0$	Рис.1-4б

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

Установить в ячейке: Значение: Изменяя значение ячейки:

Рис.1-4в

С тем, чтобы проанализировать динамику бизнеса в наглядном виде, на том же листе ниже построим таблицу (рис.1-4г), содержащую формулы

B6=ОКРУГЛВВЕРХ((A6-$B$2)/1500;)*$D$2+$A$2+A6*$C$2, C6=A6*$F$2, D6=C6-B6, E6=B6/A6.

	A	B	C	D	E
5	Единиц	Расходы	Доходы	Прибыль	Себест.
6	1000	55000	20000	-35000	55,0
7	1500	64500	30000	-34500	43,0
8	2000	67000	40000	-27000	33,5
9	2500	69500	50000	-19500	27,8
10	3000	79000	60000	-19000	26,3
11	3500	81500	70000	-11500	23,3
12	4000	84000	80000	-4000	21,0
13	4500	93500	90000	-3500	20,8
14	5000	96000	100000	4000	19,2
15	5500	98500	110000	11500	17,9
16	6000	108000	120000	12000	18,0
17	6500	110500	130000	19500	17,0
18	7000	113000	140000	27000	16,1
			Рис.1-4г

Аргументом таблицы является объем выпуска товара, начиная с 1000 шагом 500. Из нее построим (рис.1-4д) графики изменения расходов, доходов, прибыли (единицы измерения слева) и себестоимости (единицы справа) товара. Ступенчатый характер кривых объясняется вливанием очередных инвестиций (покупок станков) в расширение производства.

Инструмент Подбор параметра позволяет решать сравнительно простые задачи с единственным неизвестным значением. Ниже будет рассмотрено более сильное средство.