Сценарий расчета это

Одно из главных
преимуществ анализа данных – предсказание
будущих событий на основе сегодняшней
информации.

Сценарии являются
частью блока задач, который иногда
называют инструментами анализа
«что-если» (Анализ
«что-если». Процесс изменения значений
ячеек и анализа влияния этих изменений
на результат вычисления формул на листе,
например изменение процентной ставки,
используемой в таблице амортизации для
определения сумм платежей.).

Сценарий
— это набор значений, которые в приложении
Microsoft Office Excel сохраняются и могут
автоматически подставляться в лист.
Сценарии можно использовать для прогноза
результатов моделей расчетов листа.
Существует возможность создать и
сохранить в листе различные группы
значений, а затем переключаться на любой
из этих новых сценариев, чтобы просматривать
различные результаты. Или можно создать
несколько входных наборов данных
(изменяемых ячеек) для любого количества
переменных и присвоить имя каждому
набору. По имени выбранного набора
данных MS
Excel
сформирует на рабочем листе результаты
анализа. Кроме этого, диспетчер сценариев
позволяет создать итоговый отчет по
сценариям, в котором отображаются
результаты подстановки различных
комбинаций входных параметров.

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

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

Для защиты сценария
используются флажки, которые выставляются
в нижней части диалогового окна Добавление
сценария.
Флажок Запретить
изменения
не позволяет пользователям изменить
сценарий. Если активизирован флажок
Скрыть,
то пользователи не смогут, открыв лист,
увидеть сценарий. Эти опции применяются
только тогда, когда установлена защита
листа.

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

Во многих
экономических задачах результат расчета
зависит от нескольких параметров,
которыми можно управлять.

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

2.3 Пример расчета внутренней скорости оборота инвестиций

Исходные данные:
затраты по проекту составляют 700 млн.
руб. Ожидаемые доходы в течение последующих
пяти лет, составят: 70, 90, 300, 250, 300 млн. руб.
Рассмотреть также следующие варианты
(затраты на проект представлены со
знаком минус):

-600; 50;100; 200; 200; 300;
-650; 90;120;200;250; 250;
-500, 100,100, 200, 250, 250.

Рис 1. Окно
Диспетчер сценариев

Решение:

Для вычисления
внутренней скорости оборота инвестиции
(внутренней нормы доходности) используется
функция ВСД
(в ранних версиях — вндох):

ВСД
— Возвращает
внутреннюю ставку доходности для ряда
потоков денежных средств, представленных
их численными значениями. Эти денежные
потоки не обязательно должны быть
равными по величине, как в случае
аннуитета. Однако они должны иметь место
через равные промежутки времени, например
ежемесячно или ежегодно.

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

ВСД (Значения;
Предположения)

Значения —
это массив или ссылка на ячейки, содержащие
числа, для которых требуется подсчитать
внутреннюю ставку доходности.

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

Предположение
— это
величина, о которой предполагается, что
она близка к результату ВСД.

В нашем случае
функция для решения задачи использует
только

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

Решение приведено
на рис. 2. Формулы для расчета:

• в ячейке В14:

=ВСД(В5:В10)

• в ячейке С14:

=ЕСЛИ(В14>В12);»Проект
экономически целесообразен»;

«Проект необходимо
отвергнуть»)

Рис 2. Расчет
внутренней скорости оборота инвестиций

2. Рассмотрим этот
пример для всех комбинаций исходных
данных. Для создания сценария следует
использовать команду Сервис
| Сценарии |
кнопка Добавить
(рис. 3).
После нажатия
на кнопку ОК
появляется
возможность внесения новых значений
для изменяемых ячеек (рис. 4).

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

Рис 3. Добавление
сценария для комбинации исходных данных

Рис 4. Окно для
изменения значений ячеек.

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

Сценарий «Скорость
оборота 1»
соответствует данным (-700; 70; 90; 300; 250;
300), Сценарий «Скорость
оборота 2»
— (-600; 50; 100; 200; 200; 300),

Сценарий «Скорость
оборота 3»
— (-650; 90; 120; 200; 250; 250).

Нажав кнопку
Вывести,
можно
просмотреть на рабочем листе

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

Рис 5. Окно
Диспетчер сценариев с добавленными
сценариями

4. Для получения
итогового отчета по всем добавленным
сценариям следует нажать кнопку Отчет
в окне
диспетчера сценариев. В появившемся
окне отчет по сценарию выбрать необходимый
тип отчета и дать ссылки на ячейки, в
которых вычисляются результирующие
функции. При нажатии на кнопку ОК
на
соответствующий лист рабочей книги
выводится отчет по сценариям (рис. 6).

Рис 6. Отчет по
сценариям расчета скорости оборота
инвестиций

Соседние файлы в предмете [НЕСОРТИРОВАННОЕ]

Источник

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

Создание сценариев в Excel

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

банки повысят процентные ставки по кредитам;
существенно измениться уровень инфляции;
поднимут налоги и т.п.

Тестировать план бюджета в оригинальном файле крайне не рекомендуется. Создавая новые копии документов для теста можно нарушить адресации во множестве трехмерных ссылок формул и функций. Наиболее рациональное решения для данной ситуации – это использование сценариев Excel.

Пример сценариев в Excel

Для примера применения сценариев в практике, будем использовать простые задачи. Допустим нам нужно накопить 13 800$ за 10 лет на банковском депозите с определенной процентной ставкой. Нам нужно узнать какой будем делать ежегодный взнос на депозит. И какая процентная ставка нас устроит для накопления денежных средств.

Составьте таблицу так как указано на рисунке:

Выделите диапазон ячеек B1:B2 и выберите инструмент: «Данные»-«Работа с данными»-«Анализ что если»-«Диспетчер сценариев».

В диспетчере нажмите на кнопку «Добавить».

В окне «Добавление сценария» укажите имя «Макс.ставка%» и ссылку на диапазон изменяемых ячеек. И нажмите ОК.

Появится окно «Значения ячеек сценария», в нем введите новое значение 7% для ячейки B1, а в B2 не изменяйте как выше указано на рисунке. И нажмите ОК.
Повторите выше указанные пункты с 3 по 5. Только на этот раз в 4-ом пункте укажите имя «Макс.взнос»; в 5-том пункте укажите новое значение взноса -1100 для ячейки B2, а B1 оставьте без изменений как ниже на рисунке:

Теперь в диспетчере сценариев нажмите на кнопку отчет.

Ничего не меняя жмем ОК.

Готово!!!

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

Источник

Содержание

Формализация функциональных требований к системе с помощью диаграммы вариантов использования
Создание сценариев в Excel
Пример сценариев в Excel
Сценарии Excel. С помощью диспетчера сценариев возможно:
Просмотр результатов работы сценария
Некоторые замечания
Шаги 10 для создания сценариев в Excel
Создание различных сценариев

Формализация функциональных требований к системе с помощью диаграммы вариантов использования

Отдельные варианты использования могут применяться как для спецификации требований к проектируемой системе, так и для документирования процесса поведения имеющейся системы. Кроме этого, варианты использования неявно специфицируют требования, определяющие особенности взаимодействия пользователей с системой, которые специфицируют возможность корректной работы с предоставляемыми данной системой сервисами.

Требование (requirement) – желательное свойство, характеристика или условие, которым должна удовлетворять система в процессе своей эксплуатации.

Применительно к программным системам предложена следующая классификация требований, которая получила название модели FURPS+, что соответствует первым буквам соответствующих категорий требований на английском языке:

функциональные требования (Functionality)
требования удобства использования (Usability)
требования надежности (Reliability)
требования производительности (Performance)
требования возможности сопровождения (Supportability)

При этом символом “+” обозначены дополнительные условия, к которым относятся:

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

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

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

Сценарий (scenario) – определенная последовательность действий, которая описывает действия актеров и поведение моделируемой системы в форме обычного текста.

В контексте языка UML сценарий используется для дополнительной иллюстрации взаимодействия актеров и вариантов использования. Предлагаются различные способы представления или написания подобных сценариев. Один из таких шаблонов рассматривается ниже и может быть рекомендован читателям для применения на начальных этапах концептуального моделирования (табл. 4.1).

Таблица 4.1. Шаблон для написания сценария отдельного варианта использования

Главный раздел	Раздел “Типичный ход событий”	Раздел “Исключения”	Раздел “Примечания”
Имя варианта использования	Типичный ход событий, приводящий к успешному выполнению варианта использования	Исключение № 1	Примечания № 1
Актеры	Исключение № 2	Примечания № 2
Цель	…	…
Краткое описание
Тип
Ссылки на другие варианты использования	Исключение № N	Примечания № N

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

Построение диаграммы вариантов использования – самый первый этап процесса объектно-ориентированного анализа и проектирования, цель которого – представить совокупность функциональных требований к поведению проектируемой системы. Спецификация требований к проектируемой системе в форме диаграммы вариантов использования и дополнительных сценариев может представлять собой самостоятельную модель, которая в языке UML получила название модели вариантов использования и имеет свое специальное стандартное имя или стереотип

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

банки повысят процентные ставки по кредитам;
существенно измениться уровень инфляции;
поднимут налоги и т.п.

Пример сценариев в Excel

Составьте таблицу так как указано на рисунке:
Выделите диапазон ячеек B1:B2 и выберите инструмент: «Данные»-«Работа с данными»-«Анализ что если»-«Диспетчер сценариев».
В диспетчере нажмите на кнопку «Добавить».
В окне «Добавление сценария» укажите имя «Макс.ставка%» и ссылку на диапазон изменяемых ячеек. И нажмите ОК.
Появится окно «Значения ячеек сценария», в нем введите новое значение 7% для ячейки B1, а в B2 не изменяйте как выше указано на рисунке. И нажмите ОК.
Повторите выше указанные пункты с 3 по 5. Только на этот раз в 4-ом пункте укажите имя «Макс.взнос»; в 5-том пункте укажите новое значение взноса -1100 для ячейки B2, а B1 оставьте без изменений как ниже на рисунке:
Теперь в диспетчере сценариев нажмите на кнопку отчет.
Ничего не меняя жмем ОК.

Готово!!!

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

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

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

Сценарии Excel могут применяться для прогнозов результатов моделей расчета листа.

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

Сценарии Excel. С помощью диспетчера сценариев возможно:

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

Сценарием называют именованную совокупность данных изменяемых ячеек. Отметим, что для ячеек аргументов функций можно задать разные значения. С помощью команды Меню Данные — группа Работа с данными — Анализ «что-если» — можно вызвать диалоговое окно «Диспетчер сценариев», для значений ячеек текущего листа Excel. Как показано на первом рисунке

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

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

В графе «Название сценария» следует ввести имя созданного сценария, причем, максимальная его длина не может быть больше 255 знаков. Поле «Примечание» вы можете заполнить поясняющим текстом к сценарию. По умолчанию в данное поле прописывается дата создания сценария и имя пользователя.

Последний показатель — автора, можно изменить, используя команду Сервис -> Параметры, графа «Общие», поле «Имя пользователя».

Переключатель «Запретить изменения» реализует защиту данных изменяемых ячеек приложения от какого-либо редактирования. Устанавливая флажок переключателя «Скрыть», можно добиться того, что имя сценария не будет показываться в списке. Нажимая на «ОК» вы увидите диалоговое окно, с помощью которого можно будет ввести значения изменяемых ячеек.

Просмотр результатов работы сценария

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

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

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

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

Можно наблюдать два типа отчетов:

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

Некоторое время назад мне пришлось столкнуться с такой функцией MS Excel, как Сценарии. В процессе освоения этого «НЕЧТО» у меня созрел некий небольшой обзор, которым и хочу поделиться с вами.

Для чего можно использовать функцию Сценарии? Попробую ответить на этот вопрос. Большинство людей, рано или поздно, приходят к мысли о необходимости взять кредит или же наоборот, возникает мысль вложить деньги в какой-либо инвестиционный проект, чтобы они не «лежали мертвым грузом», а «работали». И в том и в другом случае перед человеком встает множество вариантов: множество банков, предлагающих кредиты на различных условиях, множество инвестиционных проектов с различными ставками и прибылью. Какой же вариант выбрать? Как наглядно увидеть, какой проект наиболее выгоден, при каком кредите будет наименьшая переплата, ведь условий множество. Например, кредит: некоторые банки предлагают, казалось бы, выгодный процент, но берут при этом плату за открытие и обслуживание счета, другие банки не взимают платежей за обслуживание счета, но у них больший процент, а кто-то вообще берет первоначальный взнос и процент у них грабительский. Как разобраться во всем этом многообразии? Эту проблему помогают решить экселевские Сценарии. Нет, конечно, Excel не сможет принять решение за Вас куда вкладывать деньги или в какой банк идти за кредитом, но с задачей подсчета и наглядного представления информации он справляется великолепно! Итак, наглядно рассмотрим 2 варианта использования функции Сценарии: в первом варианте сравним 3 инвестиционных проекта, во втором выберем один банк из трех, предлагающий наилучшие условии кредитования.

1 вариант.

Инвестор имеет возможность выбрать один из трех инвестиционных проектов, требующих начальных инвестиций и предполагающих денежные поступления в последующие 3 года. Годовая процентная ставка для всех трех проектов одинакова и составляет 15%.

Год	I проект	II проект	III проект
Начальные инвестиции (год 0)	— 17 000 000 р.	-20 000 000 р.	-30 000 000 р.
Год 1	3 000 000 р.	14 000 000 р.	12 000 000 р.
Год 2	4 000 000 р	8 000 000 р.	12 000 000 р.
Год 3	17 000 000 р	4 000 000 р.	16 000 000 р.

Перенесем исходные данные в Excel.

Посчитаем простую сумму денежных вложений и поступлений для каждого проекта.

Для сравнения этих проектов нам понадобятся такие показатели как 1. чистый приведенный доход или NPV (он показывает величину денежных средств, которую инвестор ожидает получить от проекта, после того, как денежные притоки окупят его первоначальные инвестиционные затраты и периодические денежные оттоки, связанные с осуществлением проекта); и 2. Внутренняя ставка доходности или IRR (это процентная ставка, при которой чистый приведенный доход (NPV) равен 0).

Чистый приведенный доход определяется функцией ЧПС. Рассчитаем ЧПС для всех трех проектов.

Делается это c помощью Мастера функций. Выделим ячейку B9, на панели инструментов, в закладке «формулы» найдем кнопку f x. В диалоговом окне мастера функций в разделе «категория» выберем «финансовые», ниже, в разделе «выберите функцию» найдем ЧПС и выделим ее, нажмем ОК.

Появилось второе диалоговое окно мастера функций для ввода аргументов.

В поле «ставка» введем адрес ячейки B2 просто щелкнув по этой ячейке мышкой.

В поле «значение 1» — адрес ячейки B5

В поле «значение 2» — адрес ячейки B6

В поле «значение 3» — адрес ячейки B7.

Нажмем ОК. В ячейке B9 появилось значение чистого приведенного дохода. Растянем эту формулу на соответствующие ячейки двух других проектов.

В ячейке B10 рассчитаем внутреннюю ставку доходности для первого проекта посредством введения финансовой функции ВСД. В открывшемся диалоговом окне в поле аргументы «значения» введем блок ячеек B4:B7.

Нажмем ОК. В ячейке B10 появилось значение внутренней ставки доходности. Растянем формулу на соответствующие ячейки двух других проектов.

Необходимые экономические показатели для сравнения проектов вычислены.

Приступаем к построению сценариев.

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

Нажимаем кнопку «добавить». В диалоговом окне «добавление сценария», поле ввода «Название сценария» пишем «проект 1». В поле ввода «изменяемые ячейки» запишем латинскими буквами абсолютные адреса блока ячеек B4:B7 ($B$4:$B$7) или просто выделим этот блок ячеек мышкой. Внизу окна необходимо отключить опцию «запретить изменения».

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

поэтому просто нажмем кнопку ОК. У нас снова открылось диалоговое окно, но теперь в поле сценарий уже есть сценарий для проекта 1, который мы только что внесли.

Но поскольку проектов у нас 3, нужно создать сценарии для проектов 2 и 3. Нажимаем кнопку «добавить». Снова открывается окно «Добавление сценария». В поле «Название сценария» пишем «проект 2». В поле ввода «изменяемые ячейки» запишем латинскими буквами абсолютные адреса блока ячеек B4:B7 ($B$4:$B$7) или просто выделим этот блок ячеек мышкой. Внизу окна необходимо отключить опцию «запретить изменения».

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

Нажмем кнопку ОК. У нас снова открылось диалоговое окно, но теперь в поле сценарий уже есть сценарии для проекта 1 и для проекта 2. Создаем сценарий для третьего проекта. Нажимаем кнопку «добавить». В диалоговом окне «добавление сценария», поле ввода «Название сценария» пишем «проект 3». В поле ввода «изменяемые ячейки» запишем латинскими буквами абсолютные адреса блока ячеек B4:B7 ($B$4:$B$7) или просто выделим этот блок ячеек мышкой. Внизу окна необходимо отключить опцию «запретить изменения».

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

Нажимаем кнопку ОК. В уже хорошо знакомом нам окне «диспетчер сценариев» появились сценарии для всех трех наших проектов.

Сделаем отчет по этим сценариям. Для этого нажмем кнопку «отчет».

В открывшемся диалоговом окне «отчет по сценарию» выберем типа отчета «структура», в качестве ячеек результата выберем блок ячеек В9:В10

Нажмем ОК. Открылся вновь созданный лист с названием «Структура сценария»

Это и есть итоговая таблица – результат работы Сценариев. Теперь на листе «проекты» мы можем переключаться между различными сценариями при помощи одной кнопки. Для этого на панели инструментов, в закладке «данные» снова находим кнопку «Анализ «что-если». Нажимаем. Снова открывается окно диспетчера сценариев.

В окне «Сценарии» выделим вариант 2. Внизу диалогового окна нажмем кнопку «вывести». В ячейках В4:В10 появились значения для второго сценария.

Таким образом можно переключаться между проектами.

Вернемся к листу «Структура сценариев». Для наглядности внесем в полученную таблицу поясняющий текст и удалим всю лишнюю информацию.

2 вариант.

Например Вы задумались о покупке машины. Для этого Вам необходим кредит. Нужно определить в каком из имеющихся банков наиболее оптимальные условия. Одним из самых главных вопросов для каждого человека, задумавшегося о кредите, является вопрос «сколько мне нужно будет отдавать в месяц своих кровно заработанных денег на погашение кредита», этот показатель необходимо просчитать и оценить заранее, чтобы потом, когда Вы уже ввяжетесь в кредит, не оказаться в ситуации, когда придется отдавать большую часть зарплаты, а оставшихся денег будет хватать только на макароны и сыр. Но помимо этого, очень важным, также считаю, переплату по кредиту. При кредите переплата непременно будет, на этом банки и зарабатывают деньги, но итоговая сумма переплаты должна быть разумной, к чему брать кредит на машину, если переплата по нему через 3 года составит 2 или 3 стоимости этого самого автомобиля?

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

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

Сумма ежемесячных выплат высчитывается при помощи функции ПЛТ. На панели инструментов, в закладке «формулы» найдем кнопку f x. В диалоговом окне мастера функций в разделе «категория» выберем «финансовые», ниже, в разделе «выберите функцию» найдем ПЛТ и выделим ее

нажмем ОК.

Заполним аргументы функции. В поле «ставка» заносим адрес ячейки В2. Если бы мы рассчитывали ежегодные платежи, то мы бы ничего менять не стали в этой ячейке, но поскольку мы рассчитываем ежемесячные платежи, а размер ставка – годовой, то после адреса ячейки ставим «/12». Таким образом мы высчитываем ежемесячную процентную ставку. Поле «Кпер» заполняем адресом ячейки В4. Количество периодов у нас выжарено в месяцах, поэтому здесь больше ничего делать не нужно. В поле «Пс» нужно указать адрес ячейки, которая отображает сумму, которую мы хотим взять в кредит. Если бы у нас не было первоначального взноса, то в данном поле мы бы просто указали адрес ячейки В1, но поскольку мы берем в кредит не полную стоимость автомобиля, а стоимость автомобиля за вычетом первоначального взноса. Таким образом в это поле вносим: В1-В3. В поле Бс ставим 0, т.к. после последней выплаты наш долг банку должен быть полностью погашен, т.е. равен 0. В поле Тип также ставим 0, т.к. выплаты в нашем случае производятся в конце периода.

Нажем ОК.

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

Реальная стоимость покупки = (сумма ежемесячных выплат * срок кредита) – начальный взнос – (сумма кредита * % за открытие счета) – (ежемесячные платежи за обслуживание счета * срок кредита)

Рассчитаем Суммы переплаты. Она рассчитывается по формуле: Сумма кредита + реальная стоимость покупки

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

Нажимаем «добавить». Вносим название банка и диапазон изменяемых ячеек (т.е. тех ячеек, значения которых для разных банков будут изменяться).

Отключаем опцию «запр

етить изменения». Нажимаем ОК. В открывшемся окне «значения ячеек сценария» меняем значения в полях на соответствующие значения для Бака А.

Нажимаем ОК. Снова появляется окно диспетчера сценариев. Нажимаем кнопку «добавить». Вносим название банка и диапазон изменяемых ячеек (он соответствует диапазону ячеек для Банка А)

Отключаем опцию «запретить изменения». Нажимаем ОК. В открывшемся окне «значения ячеек сценария» меняем значения в полях на соответствующие значения для Бака ББ.

Нажимаем ОК.

Теперь в диалоговом окне диспетчера сценариев появились сценарии для двух банков: Банка А и Банка ББ.

Подобным образом создадим сценарий для Банка ВВВ. Нажимаем кнопку «добавить». Вносим название банка и диапазон изменяемых ячеек (он соответствует диапазонам ячеек для Банка А и Банка ББ). Отключаем опцию «запретить изменения».

Нажимаем ОК. В открывшемся окне «значения ячеек сценария» меняем значения в полях на соответствующие значения для Бака ВВВ.

Нажимаем ОК. Теперь в диалоговом окне диспетчера сценариев появились сценарии для двух банков: Банка А, Банка ББ и Банка ВВВ.

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

Выбираем тип отчета «структура», в поле ячейки результата вносим адреса блока ячеек В9:В11.

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

Мы оказываемся на новом листе, только что созданным Excel-ем, который называется «Структура сценария»

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

Некоторые замечания

При установке в диалоговом окне Отчет по сценарию переключателя в положение сводная таблица результаты по сценариям представляются в формате сводных таблиц (рис. 8). На мой взгляд, такое представление менее интересно.

Рис. 8. Отчет по сценариям в виде сводной таблицы

Если в диалоговом окне Диспетчер сценариев выбрать один из сценариев и нажать кнопку Вывести, на листе с моделью (рис. 9) появятся значения входных ячеек для выбранного сценария, и все формулы будут автоматически пересчитаны для выбранного сценария. Этот инструмент отлично подходит для подготовки презентации. Ctrl+Z отменяет работу сценария, и возвращает лист в исходное состояние.

Рис. 9. На лист с моделью выведены расчет для наиболее благоприятного сценария

С помощью инструмента Диспетчер сценариев трудно создать много сценариев, поскольку приходится вводить значения для каждого сценария отдельно. Большое количество сценариев можно создать с помощью моделирования по методу Монте-Карло. При использовании метода Монте-Карло можно найти, например, вероятность того, что чистая приведенная стоимость денежных потоков проекта является неотрицательной. Это важный показатель, поскольку такая вероятность показывает, повышает ли проект стоимость компании.

Как и в любой структуре данных при нажатии на знак «минус» (–) в строках 5 и 9 отчета Структура сценария (см. рис. 7) строки с предполагаемыми значениями скрываются, а отображаются только результаты. При нажатии на знак «плюс» (+) отчет восстанавливается в полном объеме.

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

Шаги 10 для создания сценариев в Excel

1. Создайте таблицу, которая станет основой для нового сценария. В нашем примере мы создаем бюджетный лист для аренды жилья.

2. Выберите диапазон значений, которые вы хотите сохранить в сценарии:

3. Нажмите Данные, а в группе Инструмент данных, нажмите Тестирование данных.

4. В появившемся меню нажмите Менеджер сценариев.

5. Это будет окно, которое появляется Менеджер сценариев, В этом окне нажмите Добавлять.

6. В поле Название пейзажа, назовите свой новый сценарий. В нашем примере мы создаем таблицу бюджета за август.

7. Это будет окно, которое появляется Величины сценария. При необходимости измените эти значения.

8. Нажмите Ok, Ваш новый будет создан и будет отображаться в Менеджер сценариев.

9. Повторите операцию и создайте столько сценариев, сколько вам нужно.

10. Теперь вы можете просматривать сценарий, который вы хотите. Просто выберите его в окне Менеджер сценариев, и нажмите на опцию шоу.

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

Создание различных сценариев

Что будет, если Вы продадите 70% книг по высокой цене? А что будет, если Вы продадите 80% книг? Или 90%, или 100%? Каждый другой процент продажи книг – это различный сценарий.
Вы можете использовать “Диспетчер сценариев” для создания этих сценариев.

Примечание: Вы можете просто ввести другой процент в ячейку C4, что бы увидеть результат в ячейке C10. Однако, Анализ “что если” позволит Вам сравнить результаты различных сценариев.

Итак, приступим.

1. На вкладке Данные выберите Анализ “что если” и выберите Диспетчер сценариев из списка.
Откроется диалоговое окно Диспетчер сценариев.

2. Добавьте сценарий, нажав на кнопку Добавить.

3. Введите имя (60% книг по высокой цене), выберите ячейку C4 (% книг, которые продаются по высокой цене) для изменяемой ячейки и нажмите на кнопку OK.

4. Введите соответствующее значение 0,6 и нажмите на кнопку OK еще раз.

5. Далее, добавьте еще 4 других сценария (70%, 80%, 90% и 100% соответсвенно).

И, наконец, ваш Диспетчер сценариев должен соответствовать картинке ниже:

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

Источники

https://www.intuit.ru/studies/courses/32/32/lecture/1006
https://exceltable.com/vozmojnosti-excel/dispetcher-scenariev-v-excel
http://word-office.ru/kak-sdelat-scenariy-v-excel.html
https://baguzin.ru/wp/dispetcher-stsenariev-dlya-analiza-prognoznoj-modeli/
https://blog.luz.vc/ru/%D0%BF%D1%80%D0%B5%D0%B2%D0%BE%D1%81%D1%85%D0%BE%D0%B4%D0%B8%D1%82%D1%8C/%D0%BD%D0%B0%D1%83%D1%87%D0%B8%D1%82%D1%8C%D1%81%D1%8F-%D1%81%D0%BE%D0%B7%D0%B4%D0%B0%D0%B2%D0%B0%D1%82%D1%8C-%D1%81%D1%86%D0%B5%D0%BD%D0%B0%D1%80%D0%B8%D0%B8-excel/
http://www.excelguide.ru/2016/03/what-if-analysis.html

Источник

Сценарии — это инструмент MS EXCEL из группы Анализ «что-если» (

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

Рассмотрим инструмент Диспетчер сценариев на конкретном примере.

Задача

Определить, на какой срок можно получить кредит для следующих вариантов:

Необходимо составить Сценарии для следующих вариантов:

размер кредита – $8 000, ежегодн. выпл. – $1 500, годовая проц. ставка – 16%;размер кредита – $12 000, ежегодн. выпл. – $2 000, годовая проц. ставка – 15%;размер кредита – $15 000, ежегодн. выпл. – $3 000, годовая проц. ставка – 14%;размер кредита – $20 000, ежегодн. выпл. – $3 500, годовая проц. ставка – 13%;размер кредита – $25 000, ежегодн. выпл. – $4 000, годовая проц. ставка – 12%.

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

Решение

Создадим простую модель для расчета количества периодов выплаты.

Наша «Модель» состоит из одной формулы

=КПЕР(B17;B18;B16)

и 3-х значений аргументов (исходных данных).

Всего необходимо рассчитать модель для 5-и различных сценариев. В каждом сценарии задаются различные значения суммы Кредита, Ставки и суммы Ежегодных выплат.

Сначала рассмотрим создание сценариев без использования Диспетчера сценариев.

Недостатком этого подхода является, то что нам пришлось создать 5 одинаковых моделей. В случае более сложных моделей, такой подход является очень затратным (время создания копий модели и дополнительные вычислительные ресурсы для одновременного расчета 5-и моделей), а также не гибким: при изменении модели ее придется переделывать в нескольких экземплярах (переписывать функции 5 раз).

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

Выпадающего списка

(см.

Файл примера лист Без сценариев2

).

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

=СМЕЩ(D16;;ПОИСКПОЗ($B$15;$D$15:$H$15;0)-1)

из отдельного диапазона с исходными данными.

Сравним рассмотренный нами подход с работой Диспетчера сценариев.

Для создания сценария сделайте следующее:

Вызовите Диспетчер сценариев

(

);

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

Файл примера лист Сценарии

);

Нажмите ОК, откроется еще одно диалоговое окно для ввода данных;

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

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

Нажмите кнопку Отчет…, чтобы вывести на отдельный лист все сценарии и полученные результаты. Результаты могут быть выведены в виде Структуры или

Сводной таблицы

Не забудьте указать ячейки результата (в которые выводятся результаты вышей модели). Для указания несвязных диапазонов

используйте клавишу

CTRL

.

Автоматически созданный отчет в виде

Сводной таблицы

, конечно, требует доработки.

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

Источник

Открыть эту статью в PDF

Место сценариев в финансовых моделях

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

Эти разные мнения формируют различные наборы исходных данных — сценарии.

Когда модель может быть рассчитана с несколькими сценариями, у автора модели есть три варианта:

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

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

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

Вот, например, как выглядит управление сценариями в финансовой модели, входящей в состав курса «Финансовое моделирование инвестиционных проектов в Excel»:

Сценарный анализ и анализ чувствительности

Сценарный анализ (англ. Scenario Analysis) относится к той же группе методов моделирования, что и анализ чувствительности (англ. Sensitivity Analysis). К обоим вариантам также применяют термин What-If анализ («что, если»). Оба метода служат для того, чтобы проанализировать результаты проекта или бизнеса в условиях неопределенности исходных данных, но подход к параметрам отличается.

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

Сценарный анализ не изучает весь спектр возможных колебаний. При этом подходе мы фокусируем внимание на небольшом количестве сценариев, обычно это 2-3 варианта. Но каждый сценарий изучается полностью, начиная с того, насколько он вообще вероятен, далее — всё его влияние на модель, и, наконец, как в будущем можно сделать вывод, что развитие ситуации пошло именно по этому сценарию.

Принципы создания сценариев для моделирования

При подготовке сценариев для модели инвестиционного проекта (или оценки бизнеса) можно придерживаться следующего алгоритма:

Выбрать ключевые факторы неопределенности проекта. Обычно это цены, объем продаж и график выхода на плановые объемы, сумма инвестиционных затрат. Необходимо убедиться, что выбранные параметры имеют качественное обоснование в маркетинговом или технологическом анализе проекта.
Определить сценарии. Изучая обоснование данных, на котором построен проект, необходимо выделить несколько сценариев. Если в обосновании дан только один сценарий или интервалы значений, то его следует дополнить и скорректировать.
Учесть последствия смены сценариев. Это очень важный этап. Как правило, если один из ключевых параметров проекта меняется, то компания может изменить и ряд других своих характеристики. Например, если объем продаж резко упал, можно сократить персонал, уменьшить производственные мощности и т. п. С учетом вторичных факторов окажется, что в каждом сценарии меняется не один-два параметра, а целый ряд характеристик.
Определить «длинный список» сценариев. С учетом разных изменений параметров, а также возможных изменений в технологии, мощностях и других характеристиках бизнеса, мы получим много разных сценариев. На этом этапе надо составить перечень возможных комбинаций.
Сократить список до 2-3 вариантов. Теперь мы готовим «короткий список» наиболее важных сценариев, которые и будут реализованы в модели.

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

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

Спасибо, Вы зарегистрированы
на семинар «Альт-Инвест»!

Наш менеджер свяжется с Вами в ближайшее время.

Спасибо, Ваша заявка принята!

Мы отправили Вам письмо для проверки контактной информации на адрес info@alt-invest.ru.

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

Спасибо, Вы почти подписаны на новостную рассылку «Альт-Инвест»!

Мы отправили Вам письмо для подтверждения вашего e-mail на адрес info@alt-invest.ru.

Теперь проверьте почту.

Спасибо за интерес к нашим программам!

Мы отправили Вам письмо, где сказано как получить демо-версию, на адрес info@alt-invest.ru.

Теперь проверьте свою почту.

Источник

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

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

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

Сценарий является необходимым реквизитом всех движений данных подсистемы бюджетирования.

Необходимые сценарии настраиваются в справочнике «Сценарии бюджетирования» (раздел «Бюджетирование»).

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

Все бюджетные операции в системе отражаются в четырех валютах: в валюте управленческого учета, в валюте сценария, в валюте регламентированного учета и в валюте операции (документа). Валюта управленческого учета задается в Константах (БИТ) на уровне всей системы в целом.

Для каждого сценария задается своя периодичность — временной интервал, который определяет детализацию планирования по календарным периодам (год, квартал, месяц, декада, неделя, день), и продолжительность периода планирования (например, 12 месяцев).

На закладке «Настройка контроля» устанавливается периодичность контроля (в рамках какого периода происходит контроль), а также «включается» контроль одного бюджета по данным другого бюджета. Для установки контроля бюджета по другому бюджету необходимо в «контролируемом» сценарии бюджетирования на закладке «Настройка контроля» добавить строку, в которой нужно указать дату начала и дату окончания контроля. Если дату окончания не указать, то контроль будет продолжаться неограниченное количество времени. Для контроля заявок по бюджету периоды контроля устанавливать не нужно.

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

Далее, при проведении документов, отражающих плановые данные по «контролируемому» сценарию бюджетирования («Форма ввода бюджета», «Бюджетная операция» и др.), происходит сравнение с данными «контролирующего» сценария. Данные по «контролируемому» сценарию не будут записаны, если суммы оборотов превышают установленные контрольные значения. Об этом будет указано в строке сообщений.

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

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

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

Значение курсов можно вводить вручную либо воспользоваться кнопкой «Заполнить», которая позволяет автоматизировать этот процесс. Возможны следующие способы автоматизированного заполнения курсов валют:

копировать из курсов валют;
линейное заполнение;
произвольный алгоритм.

Копировать из курсов валют. При выборе этого способа открывается форма, в которой нужно указать период, за который будут загружены курсы выбранной валюты из регистра сведений «Курсы валют» и нажать кнопку «Выполнить».

Результат автозаполнения курсов будет выглядеть следующим образом:

Линейное заполнение. В открывшейся форме необходимо указать Дату начала заполнения курсов, Периодичность, с которой необходимо заполнить курсы, Длину интервала. Например, если выбрана периодичность – месяц, длина интервала – 1, это означает, что курсы валюты будут заполнены на каждый месяц, начиная с даты начала заполнения. Если при этом указать длину интервала – 3, то курсы валюты будут заполнены с интервалом 3 месяца.

Количество шагов показывает, какое количество шагов нужно сделать при заполнении курсов валюты в соответствии с периодичностью и длиной интервала.

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

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

При создании новой функции открывается форма элемента справочника «Пользовательские функции», в которой задается произвольная формула расчета курсов валюты.

В справочнике «Сценарии бюджетирования» присутствует один предопределенный элемент – сценарий «ФАКТ». По этому сценарию отражаются фактические данные по бюджетам. Например, при проведении платежных документов («Списание с расчетного счета», «Расходный кассовый ордер» и др.) по сценарию «ФАКТ» отражаются фактические данные по Бюджету движения денежных средств. В дальнейшем, с помощью отчета «План-фактный анализ» можно будет сравнить и проанализировать плановые (в соответствии с бюджетом) и фактические (в соответствии с реальными платежами) данные.

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

Источник

Запустить новый проект или новое направление бизнеса, изменить структуру отдела продаж для роста выручки — эти и аналогичные задачи невозможно реализовать без прогнозирования.

Чтобы сделать это корректно, лучше всего создать финансовую модель — рассказываем, как её построить.

Финансовая модель, или финмодель — это форма отчётности, которая объединяет показатели выручки, затрат, прибыли бизнеса и отражает связи между ними.

Благодаря расчёту базового, пессимистичного и оптимистичного сценариев можно задать план продаж и расходов в будущем. Финмодель помогает принимать грамотные управленческие решения, которые основаны на цифрах.

Примеры управленческих решений:

стратегическое развитие бизнеса;
получение кредита в банке;
оценка стоимости бизнеса для сделок слияния и поглощения.

Финансовая модель позволяет:

протестировать гипотезу — например, сколько нужно нанять новых продавцов при открытии новой точки продаж;
посчитать рентабельность на разных этапах работы компании с учётом таких показателей, как сезонность, рыночный рост, риски;
поставить цели в компании по SMART — например, в 2022 году увеличить выручку компании на 30% по отношению к предыдущему году;
минимизировать риск кассовых разрывов и рассчитать необходимость в привлечении сторонних инвестиций: кредитов, займов, собственных средств.

Не следует путать финансовую модель с отчётом о прибылях и убытках, который отражает исторические данные. Финансовая модель — это прогноз будущих событий.

При необходимости переместиться из точки А в точку Б нужно определить исходные условия. Для этого достаточно задать несколько вопросов: как мы будем перемещаться ― пешком или на машине; какое расстояние предстоит преодолеть и хватит ли на этот путь ресурсов ― сил или бензина; как скоро мы хотим переместиться в точку Б.

Перед созданием финансовой модели точно так же стоит задать себе определённые вопросы, чтобы определить характер дальнейших расчётов. Можно выделить два базовых вопроса:

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

Обычно финмодель делают на 12 месяцев. На более длительный срок строят модели в случае понимания долгосрочной стратегии развития компании и рынка.

Финансовая модель собирает прогнозируемые показатели от:

отдела продаж — лиды, конверсии, средний чек;
отдела работы с персоналом — фонд оплаты труда, план и стоимость найма, отток сотрудников;
производственного отдела — план производимых товаров и услуг.

Один отчёт — финмодель — может показать, какую прибыль и при каких условиях компания или проект могут получить.

Для построения финмодели сначала проводятся расчёты по выручке, потом учитываются все расходы и получается итоговое значение по прибыли. Как в учебниках по бизнесу: доходы ‒ расходы = прибыль.

Рассмотрим подробнее составляющие финмодели.

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

Количество продаж за отчётный период х Cредний чек = Выручка

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

Например, кофейня у дома имеет всего три этапа в воронке:

количество проходящих мимо кофейни людей за промежуток времени;
количество вошедших в кафе;
количество купивших.

Дальше количество покупателей умножается на средний чек и получается выручка кофейни.

В качестве другого примера можно рассмотреть онлайн-школу. Здесь воронка будет длиннее:

органический и платный охват — просмотры объявления;
переходы на посадочную страницу бесплатного продукта — лид-магнит;
количество лидов;
количество покупок трипваера;
количество покупок основного продукта;
количество повторных покупок.

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

При расчёте выручки важно учитывать внешние факторы, которые могут повлиять на конверсии и количество продаж: сезонность, темп роста рынка, колебания курса валют.

Например, курсы подготовки к экзаменам имеют явно выраженные высокие сезоны продаж в сентябре и феврале, а магазины подарков — в ноябре и декабре.

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

Первый аналитический показатель в финансовой модели — валовая прибыль.

Выручка ‒ Себестоимость = Валовая прибыль

Валовая прибыль показывает объём средств, которые остаются в распоряжении бизнеса после каждого производственного цикла: деньги > запасы > готовая продукция > деньги.

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

Валовая прибыль / Выручка = Маржа

Маржа по определению не может быть больше 100%. К примеру, у компаний из сферы услуг маржинальность может составлять 30‒70%, у ритейла — от 1% до 5%.

Часто предприниматели путают маржинальность и наценку. Маржа — часть выручки, оставшаяся после вычета расходов на себестоимость. Наценка — превышение выручки над себестоимостью.

К постоянным расходам относят:

ФОТ бухгалтерии, кадров и менеджмента;
аренду офиса;
коммунальные платежи;
расчётно-кассовое обслуживание;
оплату сервисов.

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

Earnings before interest, taxes, depreciation and amortization — EBITDA — это операционная прибыль компании до выплаты налогов, оплаты процентов по кредитам и списания амортизации. Это второй аналитический показатель финмодели.

Валовая прибыль ‒ Постоянные расходы = EBITDA

Здесь же считается маржинальность по операционной прибыли ⟶ EBITDA / Выручка

По тому, какая маржинальность по EBITDA, можно судить о том, на сколько компания успешна в своей операционной деятельности. Как и с маржинальностью по валовой прибыли, показатели зависят от бизнес-модели.

Налоги делятся на два типа в зависимости от выбранной системы налогообложения.

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

Изымаем из операционной прибыли налоги, проценты по кредитам и амортизацию.

В остатке — чистая прибыль, которая становится базой для расчёта дивидендов и выплат акционерам или реинвестирования обратно в бизнес.

Узнаете, как различные бизнес-процессы влияют на развитие компании
Поймёте, как влиять на показатели эффективности и принимать управленческие решения на основе финансовой отчётности
Разработаете стратегию развития собственного бизнеса на основе финансовых показателей

Финансовая модель составляется с помесячным учётом доходов и расходов.

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

Маржинальность по валовой прибыли меняется в пределах 13% — от 47% до 60%. Это свидетельствует о том, что к себестоимости относят не только ежемесячные расходы, но и разовые траты

Сравнивая маржинальность по валовой прибыли (первый скрин) и по чистой (последний) можно сказать, что постоянные расходы занимают до 4% в общей структуре расходов, что говорит об устойчивости выбранной бизнес-модели. Нужно мало средств на поддержание компании в случае падения количества продаж.

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

Важный показатель: в течение года маржинальность по чистой прибыли (последняя строка) должна вырасти на 2.5%, а в абсолютных значениях в 6.5 раз — 15,4 млн в декабре против 2,4 млн в январе

От редакции

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

Часто менеджменту кажется, что мелкие расходы — например, на канцелярию или фрукты в офис — так незначительны, что их не нужно учитывать. Но, как показывает практика, дисциплина с масштабированием бизнеса только улучшается, а хаос ― увеличивается.

Достаточно зафиксировать точное количество средств на всех счетах компании на первое число месяца и начать учитывать все транзакции. Главное — делать это регулярно.

Важно разделять деньги собственника и деньги бизнеса.

На первом этапе работы любого бизнеса собственник выполняет сразу несколько ролей. Очень часто это роль акционера, директора и линейного специалиста. Поэтому в первую очередь нужно зафиксировать, что акционер получает свой профит только в виде дивидендов, а роль директора предполагает регулярную выплату заработной платы.

Собственнику бизнеса следует установить себе приемлемый уровень зарплаты и внести её в блок с постоянными расходами. При этом должна сходиться финансовая модель бизнеса — при любых сценариях он должен быть прибыльным.

Очень часто при построении финансовых моделей игнорируют внешние переменные факторы, которые влияют на выручку компании. Самый существенный из них — сезонность. Она существует в любом бизнесе, а не только в продаже, например, ёлок на Новый год или тюльпанов на 8 Марта.

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

Финансовая модель — инструмент управления бизнесом или проектом.

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

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

Мнение автора и редакции может не совпадать. Хотите написать колонку для Нетологии? Читайте наши условия публикации. Чтобы быть в курсе всех новостей и читать новые статьи, присоединяйтесь к Телеграм-каналу Нетологии.

Источник

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

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

Сценарии Excel могут применяться для прогнозов результатов моделей расчета листа.

Содержание

Сценарии Excel. С помощью диспетчера сценариев возможно:
Просмотр результатов работы сценария
Похожие статьи

Сценарии Excel. С помощью диспетчера сценариев возможно:

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

Просмотр результатов работы сценария

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

Можно наблюдать два типа отчетов:

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

Источник

8 апреля 2022 г.

Microsoft Excel позволяет вам управлять, анализировать и сравнивать данные, чтобы помочь вам продвинуться по карьерной лестнице. Некоторые функции Excel, такие как менеджер сценариев, могут позволить вам принимать важные бизнес-решения и прогнозы на основе различных ситуаций. Понимание того, как использовать эту функцию, может помочь вам продвинуться по карьерной лестнице в бизнесе по мере развития навыков анализа данных. В этой статье мы определяем, что такое диспетчер сценариев в Excel, и даем инструкции по эффективному использованию этой функции.

Менеджер сценариев Excel представляет собой набор цифровых инструментов, которые позволяют пользователю создавать, анализировать и сравнивать результаты данных в различных бизнес-ситуациях. Вы можете хранить несколько версий данных в одной ячейке, а затем изменять их в зависимости от цели сценария. Если у вас есть несколько потенциальных наборов данных, эта функция может помочь определить окончательное значение метрики. Например, компания может создать бюджет на основе сценария с высоким или низким доходом, а затем сравнить результаты, чтобы понять, как прогнозируемый доход может повлиять на планы бюджета.

Некоторые другие функции диспетчера сценариев включают сохранение различных групп значений, объединение различных сценариев и создание сводок по каждой ситуации. Использование этого инструмента может помочь бизнесу понять и предсказать важную информацию о процессе принятия решений. Это также может помочь заинтересованным сторонам эффективно сотрудничать в проекте, основанном на данных.

Как использовать диспетчер сценариев в Excel

Вот шесть шагов, которые вы можете предпринять, чтобы перемещаться по диспетчеру сценариев:

1. Найдите диспетчер сценариев

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

2. Введите данные в электронную таблицу

Чтобы превратить набор значений в сценарий, введите свои данные в электронную таблицу. Сценарий может иметь не более 32 меняющихся ячеек, поэтому часто бывает полезно подготовить эту информацию перед началом этого проекта. Вы можете начать с ввода меняющихся ячеек, которые представляют значения, составляющие сценарий. Например, если вы создаете сценарий для описания потенциальных затрат на бизнес-операции, вы можете ввести такие значения, как трудозатраты или материальные затраты.

После того, как вы ввели эти изменяющиеся ячейки, вы можете добавить формулу, отражающую, как изменяющиеся ячейки соотносятся друг с другом. Чтобы ввести формулу, выберите ячейку, в которую вы хотите вставить свои вычисления, и введите знак равенства. Затем вы можете добавить остальную часть формулы. Например, если вы хотите использовать формулу, которая складывает значения затрат на рабочую силу в одной ячейке с пометкой A1 и стоимость материала в другой ячейке с пометкой A2, вы можете ввести «=A1+A2». После этого вы можете нажать «Enter», чтобы применить формулу.

3. Создайте сценарий

Выберите опцию «Добавить» в диспетчере сценариев. В некоторых версиях Excel этот параметр может выглядеть как знак «плюс». Когда откроется экран «Добавить», вы можете ввести имя своего сценария в разделе «Имя сценария». Придумайте описательное имя, которое вы сможете легко распознать при анализе каждого набора значений. Например, если вы хотите создать сценарий для высоких эксплуатационных расходов, вы можете назвать свой сценарий «Сценарий наихудшего случая» или «Максимально возможные эксплуатационные расходы».

После того, как вы назвали свой сценарий, вы можете ввести имена изменяемых ячеек в разделе «Изменение ячеек», разделенные запятой. В зависимости от предпочтений компании вы также можете выбрать «Предотвратить изменения», чтобы защититься от редактирования сценария. Сделав это, нажмите кнопку «ОК», чтобы сгенерировать сценарий.

4. Добавьте еще один сценарий

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

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

5. Слияние сценариев

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

Для начала соберите все соответствующие электронные таблицы, содержащие сценарии. Убедитесь, что все участники написали сценарии в одном и том же формате ячеек, чтобы упростить сравнение данных. Например, если один отдел вводит стоимость рабочей силы в ячейку A1, может быть полезно, если другой отдел поместит свои затраты в ту же ячейку. После. вы можете открыть диспетчер сценариев и выбрать опцию «Объединить». Когда появится список возможных сценариев, выберите те, которые вы хотите объединить, и нажмите кнопку «ОК». Это действие помещает все сценарии на один рабочий лист.

6. Создайте сводный отчет по сценарию

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

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

Обратите внимание, что ни одна из компаний, упомянутых в этой статье, не связана с компанией Indeed.

Источник