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

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

Задача 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 не заполняются в ручную, а вычисляются компьютером.

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

Задача 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 штук товара. Превышение этого значения уже будет приносить прибыль владельцам предприятия. Замечание. Запустив первый раз Подбор параметра, вы возможно не получите желаемый результат, но согласитесь с ним и снова запустите Подбор. Поскольку задача существенно нелинейна результат здесь зависит от стартовых значений.

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

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

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

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

Поиск по сайту: