Як використовувати пошук цілей у вашій фінансовій моделі Excel

Одним із інструментів, який дуже корисний для фінансового моделювання, є пошук цілі. Якщо ви знаєте потрібну відповідь, але хочете знати, які внески вам потрібні для її досягнення, ви можете працювати назад, використовуючи пошук цілі.

Щоб запустити пошук цілі, ви повинні мати

• Формула
• Жорстко закодована комірка введення, яка керує цією формулою

Неважливо, наскільки складна модель. Поки існує прямий зв’язок між формулою та вхідною коміркою, пошук мети правильно обчислить результат.

Вхідна комірка повинна бути жорстко закодована. Це не спрацює, якщо комірка введення містить формулу.

Обмеження витрат проекту з метою пошуку

Що таке пошук цілі та як він працює, найкраще продемонструвати за допомогою простої моделі. Щоб отримати практичний приклад того, як використовувати ціль, щоб обмежити витрати на проект, виконайте цю серію кроків, як показано.

Давайте візьмемо цей аналіз витрат на проект.

Розрахунок денної норми персоналу з використанням абсолютного посилання.

Як показано нижче, ви можете використовувати прості формули для обчислення загальної вартості проекту на основі кількості відпрацьованих днів, що дає загальну вартість 146 769 доларів США. Однак, на жаль, цей модельєр зарахував лише $130 000 витрат на персонал. Якщо ви хочете, щоб проект входив до бюджету, вам потрібно знати, на скільки вам потрібно скоротити відпрацьовані дні. Ви можете вручну налаштувати кількість днів, яка була введена в клітинку D3, але це займе багато часу, щоб отримати точне число. Використовуючи пошук цілі, ви можете зробити це за лічені секунди:

На вкладці «Дані» стрічки в розділі «Прогноз» виберіть «Аналіз «що якщо», а потім виберіть «Пошук цілі».

З’явиться діалогове вікно пошуку цілі.

У полі Set Cell переконайтеся, що клітинка містить потрібний результат, загальну вартість у клітинці D10.

У полі To Value введіть число, яке ви хочете, щоб D10 становив 130 000 доларів США.

У полі «Змінивши клітинку» введіть комірку, яку потрібно змінити, дні проекту в комірці $D$3.

Натисніть OK.

Кількість проектних днів у комірці D3 автоматично змінюється на 53,1446540880503, а це набагато більше інформації, ніж вам, ймовірно, потрібно! Округліть його вручну, ввівши 53 у клітинку D3, що змінить загальні витрати так, щоб вони перевищували потрібну цільову суму в 130 000 доларів США.

Використовуючи ціль, намагайтеся обмежити витрати проекту.

Оскільки пошук цілі по суті вставляє число в комірку, він обходить правило перевірки даних, як ніби ви скопіювали та вставили значення.

Розрахунок точки беззбитковості з пошуком мети

Використання пошуку цілі також дуже корисно для аналізу беззбитковості. Тут ви виконуєте простий розрахунок беззбитковості за допомогою пошуку цілі.

Для практичного прикладу використання мети спробуйте обчислити точку беззбитковості. Виконайте такі дії:

Завантажте та відкрийте файл 0603.xlsx .

Перейдіть до аркуша Припущень і спробуйте змінити кількість проданих одиниць з 8940 до 8000.

Поверніться до таблиці IS, і ви побачите, що прибутковість впала з 20% до 14%.

Ви можете продовжувати робити це вручну, поки не досягнете нуля, але пошук мети буде набагато швидшим і точнішим.

На вкладці «Дані» на стрічці в розділі «Прогноз» виберіть «Аналіз що-якщо», а потім виберіть «Пошук цілі».

З’явиться діалогове вікно пошуку цілі.

У полі Set Cell введіть клітинку, яка містить бажаний результат (прибуток), C24.

У полі To Value введіть число, яким має бути C24, 0.

У полі «Змінивши клітинку» введіть клітинку, яку потрібно змінити (кількість одиниць на сторінці «Припущення»), $A$3.

Натисніть OK.

Кількість одиниць у клітинці A3 на сторінці Припущень автоматично змінюється на 6424, що є точкою беззбитковості.

Використовуючи ціль, намагайтеся обчислити точку беззбитковості.