Использование инструмента поиска целей анализа «что, если» в Excel

Хотя длинный список функций Excel является одной из наиболее заманчивых функций приложения Microsoft для работы с электронными таблицами, существует несколько недостаточно используемых драгоценных камней, которые улучшают эти функции. Одним из часто пропускаемых инструментов является анализ «что если».

Инструмент «Что делать, если» в Excel разбит на три основных компонента. Обсуждаемая здесь часть — это мощная функция поиска цели, которая позволяет вам работать в обратном направлении от функции и определять входные данные, необходимые для получения желаемого результата из формулы в ячейке. Читайте дальше, чтобы узнать, как использовать инструмент поиска целей анализа «что, если» в Excel.

Пример инструмента поиска цели в Excel

Предположим, что вы хотите взять ипотечный кредит на покупку дома, и вы обеспокоены тем, как процентная ставка по кредиту повлияет на ежегодные платежи. Сумма ипотеки составляет 100 000 долларов США, и вы будете погашать кредит в течение 30 лет.

Используя функцию Excel PMT, вы можете легко определить, какими будут годовые выплаты, если процентная ставка будет 0%. Электронная таблица, вероятно, будет выглядеть примерно так:

Программы для Windows, мобильные приложения, игры - ВСЁ БЕСПЛАТНО, в нашем закрытом телеграмм канале - Подписывайтесь:)

Простой расчет ипотечного платежа в Excel

Ячейка в A2 представляет годовую процентную ставку, ячейка в B2 — это продолжительность кредита в годах, а ячейка в C2 — это сумма ипотечного кредита. Формула в D2:

= ПМТ (А2, В2, С2)

и представляет собой ежегодные выплаты по 30-летней ипотеке стоимостью 100 000 долларов США под 0%. Обратите внимание, что показатель в D2 является отрицательным, поскольку Excel предполагает, что платежи являются отрицательным денежным потоком от вашей финансовой позиции.

К сожалению, ни один ипотечный кредитор не одолжит вам 100 000 долларов под 0%. Предположим, вы немного подумали и выяснили, что можете позволить себе выплачивать ипотечные платежи в размере 6000 долларов в год. Теперь вы задаетесь вопросом, какую самую высокую процентную ставку вы можете взять для получения кредита, чтобы убедиться, что вы не будете платить более 6000 долларов в год.

Многие люди в этой ситуации просто начнут набирать цифры в ячейке A2, пока цифра в D2 не достигнет приблизительно 6000 долларов. Тем не менее, вы можете сделать так, чтобы Excel выполнил эту работу за вас, используя инструмент поиска цели анализа «что, если». По сути, вы заставите Excel работать в обратном направлении от результата в D4 до достижения процентной ставки, которая удовлетворяет вашей максимальной выплате в размере 6000 долларов.

Начните с нажатия на вкладку «Данные» на ленте и поиска кнопки «Что, если анализ» в разделе «Инструменты данных». Нажмите кнопку «Что, если анализ» и выберите «Поиск цели» из меню.

Инструмент поиска целей анализа

Excel открывает маленькое окно и просит ввести только три переменные. Переменная Set Cell должна быть ячейкой, содержащей формулу. В нашем примере это D2. Переменная To Value — это величина, которую вы хотите, чтобы ячейка в D2 была в конце анализа.

Для нас это -6000. Помните, что Excel рассматривает платежи как отрицательный денежный поток. Переменная By Changing Cell — это процентная ставка, которую вы хотите, чтобы Excel нашел для вас, поэтому ипотека в 100 000 долларов будет стоить вам всего 6000 долларов в год. Итак, используйте ячейку A2.

Переменные поиска цели в Excel

Нажмите кнопку ОК, и вы можете заметить, что Excel мигает набор чисел в соответствующих ячейках, пока итерации окончательно не сойдутся в окончательное число. В нашем случае ячейка в A2 теперь должна показывать около 4,31%.

Результаты анализа поиска целей «что, если» в Excel

Этот анализ говорит нам о том, что для того, чтобы не тратить более 6000 долларов в год на 30-летнюю ипотеку в 100000 долларов, вам необходимо получить кредит под не более 4,31%. Если вы хотите продолжить анализ «что, если», вы можете попробовать различные комбинации чисел и переменных, чтобы изучить варианты, которые есть у вас при попытке обеспечить хорошую процентную ставку по ипотеке.

Инструмент Excel для поиска целей анализа «что, если» является мощным дополнением к различным функциям и формулам, содержащимся в типовой электронной таблице. Работая в обратном направлении с результатами формулы в ячейке, вы можете более четко исследовать различные переменные в своих вычислениях.

Программы для Windows, мобильные приложения, игры - ВСЁ БЕСПЛАТНО, в нашем закрытом телеграмм канале - Подписывайтесь:)

Похожие записи

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *