Svinkovod.ru

Бытовая техника
2 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Изучаем варианты: анализ «Что-если» в Эксель

Изучаем варианты: анализ «Что-если» в Эксель

Всем доброго дня. У многих моих читателей ежедневная работа связана с поиском ответа на вопрос: «Какой будет ежемесячный доход, если…», «Сколько я переплачу, если…», и много других подобных «если», от которых зависят наши последующие действия. Поиски ответа на такие вопросы принято называть анализом «что если». Способам проведения такого анализа я решил посвятить сегодняшний пост.

К примеру, Вы просчитали депозит, который предлагает банк и хотите понимать, как изменится Ваш доход от депозита при различных параметрах начислений. На рисунке ниже в синей таблице исходные данные, а в желтой – результаты расчетов.

Теперь нам интересно, как будет меняться прибыль при изменении входных параметров. Можно просто изменять их в синей таблице и смотреть результат. Но этот «дедовский» метод не слишком наглядный, да и времени много потребуется. Я предлагаю Вам два более интересных способа провести анализ «Что-Если»:

  1. Таблица данных
  2. Сценарии

Используя их, Вы легко сопоставите многие варианты и выберите лучший.

Таблица данных (таблица подстановки)

Можно построить таблицу, которая будет отражать изменение результата расчета в зависимости от входных величин. Она строится благодаря инструменту «Таблица данных» (в версиях Excel до 2010 – «Таблица подстановки»). Такие таблицы можно строить с изменением одного или двух параметров.

Таблица данных с одним параметром

В такой таблице можно изменять одно исходное значение и смотреть, как изменятся результаты. Например, нам нужно узнать, как будет меняться капитал депозита и наша прибыль в зависимости от срока размещения. Делаем заготовку и просчитываем варианты.

  1. В ячейках F1:H1 запишем заголовки наших выходных формул: «Капитал», «Прибыль», «Прирост». Этот шаг необязательный, но он позволяет лучше понять итоговую таблицу;
  2. В диапазоне F2:H2 укажем ссылки на ячейки с соответствующими формулами в желтой таблице. Например, в F2 запишем «=В6», т.е. укажем программе откуда брать формулы для этого столбца;
  3. В ячейках Е3:Е14 запишем различные варианты сроков размещения, для которых будут рассчитаны наши показатели. Я взял периоды от 6 до 72 месяцев; (Е2:Н14) и выполняем на ленте: Данные – Работа с данными – Анализ «что если» – Таблица данных

  1. Откроется окно настройки таблицы с двумя полями. Поскольку у нас срок депозита изменяется в строках, то в поле «Подставлять значения по строкам в…» укажем ссылку на поле «Срок размещения» в голубой таблице.
  2. Нажимаем Ок и видим результат. Можно задать ячейкам соответствующие форматы.
Читайте так же:
Можно ли заправляться с включенным двигателем

Как видим, если сделать вложение на 6 мес, то заработаем 934 евро, что составит 9% изначального капитала. А если депозит будет размещен на 72 мес, то прибыль составит 19 211 евро (66% вложенной суммы).

Обратите внимание! Инструмент использует формулы массивов, просто выделить ячейку и изменить такую формулу не получится.

Таблица данных с двумя параметрами

Если же нам нужно узнать, как будет изменяться прирост капитала в зависимости от сроков размещения и первого взноса, сделаем другую заготовку, уже с двумя входными параметрами. Для этого делаем новую заготовку:

  1. В диапазоне Е3:Е14 снова запишем варианты сроков вклада;
  2. В ячейках F2:O2 – варианты сумм вкладов. Я взял от 1000 до 10000 с шагом в 1000.
  3. В верхнем левом углу таблицы (ячейка Е2) будет ссылка на ту величину, которую нужно отслеживать, т.е. «Прирост»
  1. Выделим всю таблицу с шапкой (Е2:О14) и выполним на ленте: Данные – Работа с данными – Анализ «что если» – Таблица данных
  2. И опять настраиваем таблицу:
    • «Подставлять значения по строкам в…» — ссылка на срок размещения в голубой таблице;
    • «Подставлять значения по столбцам в…» — ссылка на первый взнос в голубой таблице;
  3. Нажимаем Ок и получаем результат. В нашем случае применим процентный формат данных, добавим условное форматирование для наглядности.

Применяя такие таблицы, Вы можете визуально оценить преимущества различных комбинаций входных параметров и выбрать оптимальный.

Диспетчер сценариев

Таблицы данных, конечно, хороши. Но у них есть недостатки – можно использовать только один или два входных изменяющихся параметра. Если же нужно моделировать с большим количеством входных переменных, используйте диспетчер сценариев.

Этот инструмент позволяет задать несколько наборов параметров (сценариев) и при необходимости их применять, или же вывести отчет по ним, который будет очень похож на таблицу подстановки.

И так, пусть в нашем примере нужно изменять все три параметра: первый взнос, годовую ставку и длительность размещения депозита. Давайте создадим, например, три сценария и посмотрим, что из этого получится.

  1. На ленте вызываем команду Данные – Работа с данными – Анализ «что если» — Диспетчер сценариев . Откроется диалоговое окно создания и изменения сценариев.
Читайте так же:
Можно ли есть школьный белый мел

  1. Добавим первый сценарий. Нажмем «Добавить»

  1. Заполняем параметры сценария:
    • Название сценария – произвольное информативное название для нового набора входных данных;
    • Изменяемые ячейки – ссылка на ячейки, которые будут изменяться в этом сценарии;
    • Примечание – опишите Ваш сценарий, чтобы в будущем не забыть что он моделирует;
    • Защита – установка галок в группе защита позволит применить к сценариям средства защиты Excel;

Вот, что у меня получилось:

  1. Жмем Ок и переходим к окну задания указанных параметров. Вот какой набор я внес в первом нашем сценарии:

  1. Жмем Ок, чтобы сохранить сценарий и закрыть диспетчер. Либо, жмем «Добавить», чтобы создать еще один сценарий.

По приведенной выше схеме я создал 3 сценария с разным набором входных параметров (Первый взнос, годовая ставка, период размещения). Что теперь можно делать?

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

Кроме того, можно нажать «Отчет» и выбрать один из двух вариантов построения: структура или сводная таблица.

Выбирайте «Структура», чтобы получить обычный «плоский» отчет, который очень детально покажет Вам зависимость итоговых формул от исходных цифр.

Выберите «Сводная таблица», чтобы использовать для результатов расчета весь инструментарий сводных таблиц.

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

Вот такие у нас есть возможности моделирования расчетов в Microsoft Excel. Считаю, этого более чем достаточно, чтобы выполнять повседневные, простые и сложные задачи поиска «что если». Ну а в следующей статье мы рассмотрим обратную процедуру – подбор параметров для получения необходимого (известного заранее) результата.

5.3. Диспетчер сценариев в Excel

Для вариантных финансовых расчетов, основанных на задании раз­личных значений аргументов функции, целесообразно воспользоваться сценарным подходом, реализованным средствами Excel.

Диспетчер сценариев используется для создания списка значений для подстановки в изменяемые ячейки листа. Каждый сценарий явля­ется набором предположений, который можно использовать для про­гнозирования результатов пересчета листа. Используя диспетчер сце­нариев, можно: создавать несколько сценариев, в каждом из которых содержится до 32 значений подстановки в ячейки листа; присваивать имена, сохранять и выполнять сценарии листа; создавать итоговые отчеты по сценариям; объединять сценарии; защищать сценарии от изменений; скрывать сценарии; автоматически отслеживать измене­ния сценария.

Читайте так же:
Можно ли в бассейн в плавательных шортах

Сценарий — именованная совокупность значений изменяемых яче­ек. Для ячеек, являющихся аргументами функций, можно задавать раз­личные значения. Команда Сервис —> Сценарий вызывает диалоговое окно Диспетчер сценариев для ячеек текущего рабочего листа.

В окне Сценарии представлен список сценариев текущего рабочего листа. Возможно объединение сценариев, находящихся в открытых книгах или на других листах текущей рабочей книги при нажатии кнопки Объединить. Для создания нового сценария следует нажать кнопку <Добавить>, при этом появляется новое диалоговое окно.

В поле Название сценария вводится имя нового сценария — по­следовательность символов, максимальная длина имени не более 255 знаков.

В окне Примечание можно записать поясняющий сценарий текст. По умолчанию сюда заносится имя пользователя и дата созда­ния сценария. Имя пользователя можно изменить с помощью команды Сервис -> Параметры, вкладка Общие, поле Имя пользователя.

С помощью переключателя Запретить изменения реализуется за­щита значений изменяемых ячеек от редактирования. Переключатель Скрыть позволяет не показывать имя сценария в списке. При нажатии на <ОК> появляется диалоговое окно для ввода значений изме­няемых ячеек.

Для просмотра результатов подстановки значений изменяемых ячеек по определенному сценарию в диалоговом окне Диспетчера сценариев следует выбрать из списка имя сценария и нажать кнопку Вывести.

Excel выполняет подстановку значений изменяемых ячеек сценария и производит расчет значения функции. Все изменения будут отраже­ны на рабочем листе в ячейках, содержащих формулы и имеющих ссылки на изменяемые ячейки сценария, новые результаты выводятся.

Кнопка Закрыть обеспечивает выход из окна Диспетчера сценари­ев, при этом в изменяемых ячейках сохраняются значения последнего участвовавшего в просмотре сценария. Кнопка Отчет предназначена для подготовки отчетов по сценариям, при ее нажатии появляется диа­логовое окно для выбора типа итогового отчета.

В поле Ячейки результата указывается адрес ячеек, значения ко­торых зависят от изменяемых ячеек сценариев.

Формируется два вида отчетов:

итоги сценария — табличный отчет, содержащий для каждого сценария состав изменяемых ячеек и значение выбранных результат­ных ячеек;

свободная таблица результатов подстановки значений в изменяе­мые ячейки и вычисления результатов подстановки.

Сценарии в Excel

Сегодня мы поговорим о такой вещи как сценарии в Excel. Что, зачем и как. Попробуем возможности «Анализ что если» и создадим небольшой отчёт.

Читайте так же:
Как включить защиту листа в excel

Сценарии — это алгоритм заполнения определённого диапазона ячеек заранее подготовленными данными. Также сценарии в Excel позволяют быстро строить сравнительные отчёты и создавать сводные таблицы. Например, у меня есть таблица с вложенными средствами (инвестициями). Специально оговорюсь — знак минус говорит о том, что я эти деньги отдал и позволит рассчитать суммы доходов.

Сценарии в Excel

Я хочу добавить рядом новый источник инвестирования и рассчитать его доходность, а затем создать сценарий заполнения таблицы доходности по каждому из инвестобъектов.

Для строки сумма с учётом инвестиций воспользуемся функцией БС (будущая стоимость). Разделим процент годовых (8%) на количество периодов, чтобы узнать ежемесячную ставку, укажем количество периодов — 12, сумму ежемесячного пополнения и первоначальную стоимость.

Сценарии в Excel

Теперь посчитаем сумму вложенных средств.

Сценарии в Excel

Самое интересное — чтобы вручную не делать те же самые действия для всех остальных объектов, создадим сценарий. Переходим на вкладку «Данные», блок кнопок «Работа с данными», далее «Анализ что если».

Сценарии в Excel, анализ если что

В выпадающем списке выбираем «Диспетчер сценариев». Нажимаем «Добавить».

Сценарии в Excel

В появившемся окне нужно указать:

  • название сценария;
  • изменяемые ячейки;
  • примечание;
  • настройки защиты.

Сценарии в Excel

После нажимаем «ОК». В этом коне появятся значения для проекта TON.

Сценарии в Excel

Останется нажать «Добавить» и заполнить поля сценария для остальных проектов, получится вот такой список.

Сценарии в Excel

Нам останется нажимать кнопку «Вывести», чтобы в диапазон ячеек B9:B12 выводились соответствующие значения. Очень удобно.

Сценарии в Excel

Отчёты по сценариям

Когда все данные внесены и заполнены все поля, можно перейти к созданию отчёта. В окне «Диспетчера сценариев» нажимаем кнопку «Отчёт». Предлагается два варианта: сводная таблица и структура. В наш документ будут добавляться соответствующие листы. Нажимаем «ОК», выбрав первый вариант — «Структура».

Сценарии в Excel

На новом листе появится скомпонованная таблица с включённой группировкой ячеек, что крайне удобно для анализа.

Сценарии в Excel, анализ если что

В сводной таблице не будет ничего нового — можно посмотреть вот эту статью. Таким образом мы проводим первоначальную аналитику по своим проектам. Всем удачи!

Использование средства Диспетчер сценариев

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

Для моделирования реальных процессов предназначен Диспетчер сценариев. С помощью этого инструмента можно одновременно изменять до 32переменных. Дополнительно существует возможность сохранять (и легко восстанавливать) наборы входных значений.

Задание 6. Создание сценария.

Читайте так же:
Как в биос сделать загрузку с usb

1. Работайте с данными на листе Упр.10.

2. Присвойте всем значениям, расположенным в ячейках В2:В6 (см. рис. 10.2), имена из столбца А соответственно, используя команду Создать из выделенного фрагмента, расположенную на вкладке Формулы в группе команд Определенные имена.

3. Выполните командуДиспетчер сценариев(см. рис. 10.1).

4. В появившемся диалоговом окне Диспетчер сценариев (рис. 10.13) выполните команду Добавить.

Использование средства Диспетчер сценариев - №1 - открытая онлайн библиотека

Рис. 10.13. Диалоговое окно Диспетчер сценариев

5. Появится другое диалоговое окно Изменение сценария (рис. 10.14).

Использование средства Диспетчер сценариев - №2 - открытая онлайн библиотека

Рис. 10.14. диалоговое окно Изменение сценария

6. В поле Название сценария введите имя сценария, например, Минимальный.

7. Перейдите в поле Изменяемые ячейки, а затем с помощью мыши выделите каждую ячейку или каждый диапазон, в которые диспетчер сценариев должен поставить свои переменные, например, В2:В3.

8. Нажмите на кнопку ОК.

9. Появится диалоговое окно Значения ячеек сценария(рис. 10.15). Оно предназначено для ввода значений, которые Диспетчер сценариев должен подставить в указанные ячейки. Для ввода значения любой переменной следует щелкнуть в соответствующем поле и ввести значение с помощью клавиатуры.

10. Введите минимальные значения Платы за прокат, например, 1000, и Количества предметов, например, 100.

Использование средства Диспетчер сценариев - №3 - открытая онлайн библиотека

Рис. 10.14. диалоговое окно Значения ячеек сценария

Обратите внимание, что Диспетчер сценариев для идентификации ячеек использует присвоенные им имена. Если до создания сценария имена ячейкам присвоены не были, диспетчер сценариев для идентификации ячеек использует их адреса, что весьма усложняет процедуру ввода входных значений и повышает вероятность появления ошибок.

11. Нажмите на кнопку ОК. Теперь в списке Сценарии появится имя только что созданного сценария.

12. Аналогичным образом, выполнив пп.4-11, создайте еще два сценария с максимальными данными и средними, меняя соответственно входные значения.

Задание 7. Использование сценариев.

1. Для создания отчета, содержащего результаты применения всех сценариев:

— откройте диалоговое окно Диспетчер сценариев;

— выберите команду Отчет(см. рис. 10.13);

— в появившемся диалоговом окне выберите Тип отчета – структура;

— в поле Ячейки результата с помощью мыши выберите те ячейки, содержимое которых будет представлено в отчете, например, В6;

— нажмите на кнопку ОК. Excel создаст в вашей рабочей книге новый рабочий лист с именем Структура сценария, содержащий указанный отчет.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector