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