На этом шаге мы рассмотрим анализ данных: подбор параметра.
Рассмотрим следующий типичный вопрос, анализа "что-если": "Каким станет общий доход, если объем продаж возрастет на 20%?" Если рабочий лист создан правильно, то, изменив значение в одной из ячеек, Вы увидите, что получится в ячейке, содержащей значение дохода. При выполнении процедуры подбора параметров используется противоположный подход. Если вы знаете, каким должен быть результат вычисления по формуле, то Excel подскажет Вам значения одного или нескольких входных параметров, которые позволят получить нужный результат. Другими словами, вы можете задать вопрос такого типа: "Какой рост продаж необходим для получения дохода в 1 200 000 руб.?" В Excel для этой цели предусмотрено два средства:
- Подбор параметра. Определяет значение одной входной ячейки, которое требуется для получения желаемого результата в зависимой ячейке (ячейке результата).
- Поиск решения. Определяет значения в нескольких входных ячейках, которые требуются для получения желаемого результата. Более того, можно накладывать ограничения на входные данные, поэтому здесь можно получить решение (если оно существует) многих практически важных задач.
Процедура подбора параметров для одной ячейки выполняется достаточно просто: Excel определит то значение во входной ячейке, которое приведет к нужному результату. Чтобы понять принцип анализа данных с помощью средства Подбор параметра рассмотрим пример.
На рис. 1 показан рабочий лист с выполненным расчетом ипотечной ссуды. В этом листе предусмотрено четыре ячейки для ввода параметров и столько же для результатов. Прежде чем вводить разные значения во входные ячейки, а затем оценивать полученный результат, позволим программе самостоятельно определить одно из входных значений.
Рис. 1. Рабочий лист, иллюстрирующий использование процедуры подбора параметров
Вам известно, что в месяц Вы в состоянии погашать не больше 1 200 взятой ссуды. Вы также знаете, что кредитор даст вам ссуду под фиксированный процент (скажем 8,25%), рассчитывая на то, что Вы должны погасить за определенное время 80% ссуды (т.е. первоначальный взнос составляет 20%). Вопрос состоит в следующем: "Какова максимальная стоимость покупки, которую Вы себе можете позволить?" Другими словами, какое значение должно быть в ячейке С4, чтобы результат в ячейке С11 равнялся 1 200? Один способ решения - изменять значения в ячейке С4 до тех пор, пока значение в ячейке С11 не станет равным 1200. Более эффективный способ - позволить Excel найти ответ, то есть использовать процедуру подбора параметра.
Чтобы ответить на этот вопрос, выберите команду Сервис | Подбор параметра. Появится диалоговое окно, показанное на рис. 2.
Рис. 2. Диалоговое окно Подбор параметра
Заполнение этого диалогового окна подобно составлению предложения: нужно получить 1200 в ячейке С11, изменяя значение в ячейке С4. Ввести эту информацию в диалоговое окно Подбор параметра можно, либо непосредственно набрав адреса ячеек с клавиатуры, либо щелкнув указателем мыши на нужных ячейках. Чтобы начать процесс подбора параметра, щелкните на кнопке OK. Через секунду Excel объявит, что решение найдено, и выведет окно Результат подбора параметра (рис. 3).
Рис. 3. Диалоговое окно Результат подбора параметра
В этом диалоговом окне будет отображено подбираемое значение и значение, предложенное Excel. В данном случае программа нашла точное значение. В ячейке С4 рабочего листа теперь будет находиться искомое значение ($199 663). Взяв такую ссуду, в месяц Вы должны будете погашать 1 200. На данном этапе у Вас есть две возможности:
- Щелкнуть на кнопке OK, чтобы заменить прежнее значение найденным.
- Щелкнуть на кнопке Отмена, чтобы вернуть рабочий лист в прежнее состояние - как до выполнения команды Сервис | Подбор параметра.
Excel не всегда сможет найти значение, дающее нужный результат, поскольку решение иногда просто не существует. В этом случае в окне Результат подбора параметра Вам будет выдано сообщение об этом.
Другая ситуация - программа сообщает, что не может найти решения, но Вы точно знаете, что оно существует. В таких случаях можно попробовать выполнить следующее:
- Изменить величину в подбираемой ячейке на значение, более близкое к решению, а затем выполнить команду еще раз.
- Изменить значение опции Предельное число итераций, которая расположена во вкладке Вычисления диалогового окна Параметры. Увеличение числа итераций увеличит вероятность нахождения нужного решения.
- Еще раз проверить свои логические рассуждения, и убедиться, что выходная ячейка действительно зависит от выбранной входной ячейки.
Графический подбор параметра
Excel предоставляет проведение подбора параметра с помощью манипулирования диаграммами. На рис. 4 показан рабочий лист, отображающий предполагаемый объем продаж развивающейся компании. Предположим, что из опыта известно, что рост объема продаж компаний, работающих в этой отрасли, может увеличиваться по показательному закону: =y*(b^x)
В таблице 1 перечислены и описаны все переменные этой формулы.
| Переменная | Описание | ||||
|---|---|---|---|---|---|
| Константа, равная росту продаж за первый год
| Коэффициент роста
| Переменная, выражающая время
| |
Рис. 4. Рабочий лист, иллюстрирующий рост объема продаж по показательному закону
Менеджеры компании знают, что объем продаж за первый год будет составлять 250000 и хотят довести его к 2010 г. до 10000000. Таким образом, для построения финансовой модели нужно знать точный коэффициент роста, ведущий к заданному объему продаж. В рабочем листе (рис. 4) находятся формулы для прогнозирования ежегодного объема продаж с использованием коэффициента роста в ячейке B1, а также диаграмма, отображающая ежегодный объем продаж.
Предположительным значением коэффициента роста было число 1,40. Как видно из графика, это число слишком мало, поскольку в результате объем продаж в 2010 г. составит только 7231366. Хотя для получения точного значения коэффициента роста можно использовать команду Сервис | Подбор параметра, есть и другой способ.
Дважды щелкните левой кнопкой мыши на диаграмме, в результате ее можно будет редактировать. После этого выберите столбики диаграммы. Щелкните на последнем столбике, чтобы его выделить. Поместите указатель курсора на вершину столбика, при этом он должен изменить форму. Перетащите столбик вверх и наблюдайте за изменением значения в поле адреса ячейки. Когда значение станет равным 10000000, отпустите кнопку мыши.
Описанные действия приводят к появлению диалогового окна Подбор параметра. Вам остается указать программе, какую ячейку использовать для ввода данных. Введите в это текстовое поле адрес ячейки B1. Программа вычислит значение коэффициента, необходимое для получения результата указанного на диаграмме. Если Вам нужно сохранить это число, щелкните на кнопке OK. Excel заменит текущее значение коэффициента новым, а диаграмма изменится автоматически.
Основным ограничением средства Подбор параметра является то, что оно может находить значение только в одной входной ячейке. Для изменения нескольких переменных одновременно необходимо использовать процедуру Поиск решения, которую рассмотрим на следующем шаге.
