Поиск цели — это инструмент анализа «что, если», который помогает вам найти входное значение, которое приводит к целевому значению, которое вы хотите. Поиск цели требует формулы, которая использует входное значение для получения результата в целевом значении. Затем, изменяя входное значение в формуле, Goal Seek пытается найти решение для входного значения.
Поиск цели работает только с одним входным значением переменной. Если вам нужно определить более одного входного значения, вы должны использовать надстройку Solver. См. Главу « Оптимизация с помощью Excel Solver» в этом руководстве.
Анализ с поиском цели
Предположим, вы хотите взять кредит в 5 000 000 и погасить кредит через 25 лет. Вы можете заплатить EMI 50000. Вы хотите знать, по какой процентной ставке вы можете взять кредит.
Вы можете использовать поиск цели, чтобы найти процентную ставку, по которой вы можете взять кредит, следующим образом:
Шаг 1 — Настройте ячейки Excel для поиска цели, как указано ниже.
Шаг 2 — Введите значения в столбце C, соответствующем столбцу D. Ячейка Interest_Rate остается пустой, так как вы должны получить это значение. Кроме того, хотя вы знаете EMI, которую вы можете оплатить (50000), это значение не включено, так как для его получения необходимо использовать функцию Excel PMT. Поиск цели требует формулы, чтобы найти результат. Функция PMT размещена в EMI ячейки, чтобы ее можно было использовать для поиска цели.
Excel вычисляет EMI с помощью функции PMT. Теперь таблица выглядит так:
Поскольку ячейка Interest_Rate пуста, Excel принимает это значение как 0 и вычисляет EMI. Вы можете проигнорировать результат -13 888,89 .
Выполните анализ с поиском цели следующим образом:
Шаг 1 — Перейдите в ДАННЫЕ > Что, если анализ > Поиск цели на ленте.
Откроется диалоговое окно «Поиск цели».
Шаг 2 — Введите EMI в поле Set cell . Это поле является ссылкой для ячейки, содержащей формулу, которую вы хотите разрешить, в данном случае это функция PMT. Это ячейка C6, которую вы назвали EMI.
Шаг 3 — Введите -50000 в поле Значение . Здесь вы получите результат формулы, в данном случае EMI, которую вы хотите оплатить. Число является отрицательным, поскольку оно представляет платеж.
Шаг 4 — Введите Interest_Rate в поле « Изменение ячейки» . В этом поле есть ссылка на ячейку, которая содержит значение, которое вы хотите отрегулировать, в данном случае процентную ставку. Это ячейка C2, которую вы назвали Interest_Rate.
Шаг 5 — На эту ячейку, в которой изменяется поиск цели, должна ссылаться формула в ячейке, которую вы указали в поле «Задать ячейку». Нажмите ОК.
Поиск цели дает результат, как показано ниже —
Как вы можете заметить, поиск цели нашел решение, используя ячейку C6 (содержащую формулу) в виде 12%, которое отображается в ячейке C2, которая является процентной ставкой. Нажмите ОК.
Решение проблем истории
Вы можете легко решить проблемы с историей с Goal Seek. Позвольте нам понять это с помощью примера.
пример
Предположим, есть книжный магазин, в котором хранится 100 книг. Первоначальная цена книги составляет 250, и определенное количество книг было продано по этой цене. Позже книжный магазин объявил 10% скидку на эту книгу и распродал ее. Возможно, вы захотите узнать, сколько книг продано по первоначальной цене, чтобы получить общий доход 24 500 человек.
Вы можете использовать поиск цели, чтобы найти решение. Следуйте инструкциям ниже
Шаг 1 — Установите рабочий лист, как указано ниже.
Шаг 2 — Перейдите в ДАННЫЕ> Что, если анализ> Поиск цели на ленте.
Откроется диалоговое окно «Поиск цели».
Шаг 3 — Введите « Доход», 24500 и «Books_OriginalPrice» в полях «Задать ячейку», «Значение» и «Изменяя ячейку» соответственно. Нажмите ОК.
Поиск цели отображает статус и решение.
Если бы 80 книг были проданы по первоначальной цене, доход был бы 24500.
Выполнение анализа безубыточности
В экономике точка безубыточности — это точка, в которой нет ни прибылей, ни убытков. Это будет означать —
Доход = расходы, или
Доход — Расходы = 0
Вы можете сделать анализ безубыточности с поиском цели в Excel.
пример
Предположим, есть магазин по продаже игрушек. Возможно, вы захотите сделать анализ безубыточности магазина. Соберите следующую информацию из магазина —
- Фиксированная стоимость магазина.
- Стоимость единицы игрушки.
- Количество игрушек для продажи.
Нужно выяснить, по какой цене они должны продавать игрушки, чтобы безубыточности.
Шаг 1 — Установите рабочий лист, как указано ниже.
Шаг 2 — Перейдите в ДАННЫЕ> Что, если анализ> Поиск цели на ленте. Откроется диалоговое окно «Поиск цели».
Шаг 3 — Введите Break_even_Point, 0 и Unit_Price в полях Задать ячейку, В поле Значение и Изменяя ячейку соответственно. Нажмите ОК.
Как вы можете заметить, Goal Seek дал результат, что, если цена за единицу равна 35, магазин станет безубыточным.