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

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

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

Задача 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		Подбор параметра   Установить в ячейке: G4 Значение: Изменяя значение ячейки: $B$4 ОК Отмена
	Вычет:			Расчет зарплаты			Рис. 1-1а
	Имя	Зарплата	Дети	Сумма обложения	Налог	Страховка	НА РУКИ
	Иван			=МАКС(B3-B$1*C3;0)	=13%*D3	=1%*B3	=B3-E3-F3
							Рис. 1-1в
	Иван
	Петр
	Олег								Рис.1-1б

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

	A	B	C	D		Подбор параметра
	Продуктовый расчет
	Объем сырья:				Установить в ячейке:	$D$6
	Этап	Коэфф. потерь	Объем потерь	Выход		Значение:
	Изменяя значение ячейки:	$C$2
		0,05	?	?
		0,11	?	?
		0,02	?	?
				Рис.		-2
	A	B	C			Подбор параметра
	Вклад:
	Сложные проценты			Установить в ячейке:	$C$7
	Норма	Доход	Сумма			Значение:
	10%	?	?			Изменяя значение ячейки:	$B$1
	12%	?	?
	15%	?	?
	18%	?	?	Рис.		-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,5 тысячи	Произведено единиц товара	Рыночная цена единицы	ВСЕГО расходов	Себестоим. единицы	Доход	Прибыль
	50000$		5$	7000$		20$	55000$	55$	20000$	-35000$	Рис.1-4а
	50000$		5$	7000$		20$	94667$	20$	94667$	0$	Рис.1-4б

Подбор параметра
Установить в ячейке: $L$2 Рис.1-4в Значение: Изменяя значение ячейки: $E$2 ОК Отмена
Рис.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
	Единиц	Расходы	Доходы	Прибыль	Себест.
				-35000	55,0
				-34500	43,0
				-27000	33,5
				-19500	27,8
				-19000	26,3
				-11500	23,3
				-4000	21,0
				-3500	20,8
					19,2
					17,9
					18,0
					17,0
					16,1
			Рис.1-4г

Точка безубыточности

Рис.1-4д

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

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

Дата добавления: 2015-07-15