При помощи какого сценария excel можно создать план пенсионных отчислений

При составлении бизнес-плана/бюджета проекта/компании необходимо рассчитать размер социальных взносов с фонда оплаты труда (далее – ФОТ).

Исчисление и уплата социальных взносов регулируется рядом нормативно-правовых документов, в том числе:

Налоговый Кодекс РФ Часть 2 (Глава 11)
ФЗ от 29.11.2010 N 326-ФЗ "Об обязательном медицинском страховании в Российской Федерации"
ФЗ от 15.12.2001 N 167-ФЗ "Об обязательном пенсионном страховании в Российской Федерации"
Федеральный закон от 29.12.2006 N 255-ФЗ "Об обязательном социальном страховании на случай временной нетрудоспособности и в связи с материнством"
В части размеров взносов на 2020 год - Постановление Правительства РФ от 06.11.2019 N 1407 "О предельной величине базы для исчисления страховых взносов на обязательное социальное страхование на случай временной нетрудоспособности и в связи с материнством и на обязательное пенсионное страхование с 1 января 2020 г."

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

На пенсионное страхование (ОПС)
На медицинское страхование (ОМС)
На социальное страхование (ОСС)

Взносы исчисляются и уплачиваются с большинства производимых в пользу сотрудников выплат (зарплаты, премии, отпускные и др.) и зависят в части ОПС и ОСС от суммы выплат, исчисляемой нарастающим итогом с начала года. Например, все выплаты до достижения суммы в 1,292 млн. по ОПС будут облагаться по ставке 22%, а все, что выше этой суммы – по ставке 10%.

Предельная база по каждому виду взносов ежегодно актуализируется в Постановлении Правительства РФ и на 2020 год (Постановление N 1407 от 06.11.2019) имеет следующие значения:

Для написания этого материала я искал изящное решение расчета социальных взносов в Excel с использованием встроенных функций, в частности ЕСЛИ() и пришел к выводу, что такой способ логично применять только в случае, когда мы считаем социальные взносы за весь период выплат сразу. При усложнении задачи до расчета взносов с каждой выплаты – количество вложенных формул увеличивается – нужно отработать проблему, когда очередная выплата превышает, с учетом накопления, предельную базу и фактически надо вычислять часть по базовой ставке, а часть по ставке после превышения предельной базы. В итоге, остановился на том, что наиболее простой способ решения – написание собственной функции при помощи VBA в Excel.

В целом, я пока не публиковал материалов по написанию макросов, хотя сам в работе достаточно часто их использую. Опять же, если не погружаться в детали, весь пакет программ MS Office (Excel, Word, Power Point…) имеет мощный инструмент, позволяющий расширить возможности программного пакета под потребности бизнеса/пользователя – встроенный язык программирования Visual Basic for Application (VBA).

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

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

Далее необходимо вызвать редактор VBA, обычно это делается по нажатию горячих клавиш ALT + F11 (MAC: fn+alt+F11) либо через панель задач, нажав соответствующую кнопку во вкладке «Разработчик». Эта панель по - умолчанию скрыта, чтобы она появилась необходимо включить ее в настройках: «Файл-Параметры-Настроить ленту» и поставить «галочку» в правой части окна в области «Основные вкладки» на вкладе «Разработчик».

После сохранения книги и запуска редактора VBA необходимо создать новый модуль, куда мы поместим код нашей функции. Модуль можно создать через меню «Insert-Module», модуль появится в левой части окна в блоке «Project-VBA Project»:

После добавления модуля, необходимо по нему кликнуть и в правой части окна, появится окно редактирования кода. В него необходимо добавить следующий код:

Function СОЦВЗНОС(начисление_т As Double, начисление_итог As Range, пр_база As Double, ставка_1, ставка_2)

Dim social_contr As Double 'итоговое начисление

Total = WorksheetFunction.Sum(начисление_итог) 'общая сумма начислений за год сотруднику

social_contr = начисление_т * ставка_1

ElseIf пр_база Total - начисление_т Then

social_contr = (пр_база - (Total - начисление_т)) * ставка_1 + (Total - пр_база) * ставка_2

social_contr = начисление_т * ставка_2

В окне редактора VBA это должно выглядеть примерно так:

После копирования исходного кода в модуль, редактор VBA необходимо закрыть и сохранить результат. Если вы все сделали правильно, в рабочей книге должна появится функция СОЦВЗНОС():

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

Предлагаю дать имена каждой ячейке, которая нам понадобиться для расчетов. Для ОПС имена будут следующие: ОПС_база, ОПС_Ставка1, ОПС_Ставка2. Правила работы с именами диапазонов в Excel уже есть на этом канале. Аналогично переименовываем ячейки по ОСС и ОМС. В случае изменения ставок или предельных баз, всегда можно будет внести корректировки.

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

Вернемся к нашей функции СОЦВЗНОС . Ей, для расчета, необходимо передать следующие данные:

начисление_т - начисление текущего месяца, на который выполняем расчет взносов
начисление_итог - итоговое начисление ФОТ с начала года
пр_база - размер предельной базы по данному взносу
ставка_1 - размер ставки №1 в %-х по данному взносу
ставка_2 - размер ставки №2 в %-х по данному взносу

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

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

Обратите внимание на параметр "начисление_итог", в функцию необходимо передать массив ячеек включая текущее начисление. Чтобы корректно копировать формулу в следующие ячейки, необходимо закрепить в массиве ссылку на первую ячейку, в нашем примере это $B$2

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

Сценарии - это инструмент 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 .

Автоматически созданный отчет в виде Сводной таблицы , конечно, требует доработки.

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

Excel работает за вас

Залогиниться
Зарегистрироваться

Забыли пароль?

Excel works!

Thanks for Visiting

Сценарии Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Поделитесь нашей статьей в ваших соцсетях:

1.3. Сведения о документе, шаблоны и сценарии

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

Шаблоны и сценарии для Office. На сайте Шаблоны Office Online можно найти тысячи шаблонов Office, предназначенных для всевозможных потребностей.

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

Добавление сведений в область сведений о документе

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

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

Добавление элементов управления свойством в документ

Щелкните в документе место, куда нужно добавить элемент управления свойством.
На вкладке Вставка в группе Текст щелкните Быстрая вставка.
Щелкните Свойство документа и щелкните нужный элемент управления.

Например, нужно добавить в документ элемент управления свойством Адрес компании. Если элемент управления свойством Адрес компании появляется повсюду в документе, как, например, в контракте, можно обновить один элемент управления, и все элементы управления свойством Адрес компании обновятся.

Обновление свойств документа

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

Обновление свойств в области сведений о документе

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

Просмотр и изменение любых сведений о документе в области сведений о документе.

Обновление элементов управления свойством документа в документе

При обновлении свойств в документе эти свойства будут также обновлены в области сведений о документе. ПРИМЕЧАНИЕ. В области сведений о документе отображаются только те элементы управления свойством, которые можно добавлять в документ.

Выделите в документе элемент управления свойством, который нужно изменить. Например, нужно изменить в документе "Адрес организации".
Измените сведения. При изменении сведений в одном элементе управления эти сведения меняются во всех элементах управления в этом документе.

Сценарии для Excel

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

Панели мониторинга административно-управленческой аналитики

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

Сегодня очень много говорится о накопительной пенсии. Правительство готовит к запуску Гарантированный пенсионный план (ГПП). Основное нововведение – это «добровольность» накопительной пенсии. Это значит, что каждый из нас будет сам решать, довольствоваться ли в преклонном возрасте исключительно социальной пенсией или дополнительно создавать пенсионные накопления.

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

После решения заниматься своими пенсионными накоплениями немедленно встает вопрос. А сколько нужно накопить, чтобы не почувствовать себя «у разбитого корыта»?

Пенсионный калькулятор в EXCEL

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

Размер необходимых пенсионных накоплений (к моменту выхода на пенсию)
Размер ежегодных отчислений, необходимых для формирования накоплений

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

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

Для правильного расчета потребуется:

Планируемый возраст выхода на пенсию
Текущий возраст
Планируемый размер ежегодной пенсии
Срок дожития
Доходность инвестиционного портфеля ДО пенсии
Доходность инвестиционного портфеля ПОСЛЕ пенсии
Средняя инфляция

Срок дожития

Отдельного комментария требует срок дожития. Необходимо указать, сколько лет вы планируете получать пенсию из своих накоплений. Что будет после того, как эти деньги закончатся? Ничего особенного … вы продолжите жить, довольствуясь только социальной пенсией и, возможно, опираясь на взаимопомощь внутри семьи. В любом случае можно указать срок достаточно длинный. Типичными сроками дожития являются 20 или 30 лет.

Пример расчета пенсионных накоплений

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

Для этого ему потребуется инвестировать ежегодно до выхода на пенсию 114 тыс. рублей.

Предположения, на которых основывается расчет:

Доходность инвестиционного портфеля до выхода на пенсию – 14%
Доходность портфеля после выхода на пенсию – 11%
Средняя инфляция – 8% (мы всегда стараемся брать несколько завышенные цифры)
Стартовые накопления – 100 тыс. руб. (в 35 лет)

В возрасте 45 лет для достижения аналогичных доходов на пенсии придется откладывать ежегодно уже 245 тыс. руб. В 55 лет – 674 тыс. в год.

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

Досрочный выход на пенсию

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

Например, в том же случае (возраст - 35 лет) будущий пенсионер решил выйти на пенсию раньше - уже в 55 лет. Что для этого необходимо сделать?

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

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

Ограничения калькулятора

Калькулятор дает только примерные значения. Точные значения назвать невозможно по разным причинам:

Неизвестна точная доходность инвестиционного портфеля
Неизвестен точный размер инфляции

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

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

UPDATE 17.02.2020

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

Калькулятор размера пенсионных накоплений в EXCEL
Файл: retirement_calculator_rev2.xlsx
Размер: 58185 байт

Для скачивания файлов необходимо зарегистрироваться или авторизоваться

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

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

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

Управление сценариями выполняется с помощью диспетчера сценариев в группе Анализ "что если" на вкладке Данные.

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

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

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

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

Предположим, например, что в худшем случае ожидается доход в 50 000 ₽, а стоимость проданной продукции составляет 13 200 ₽, в результате чего получается 36 800 ₽ валовой прибыли. Чтобы определить этот набор переменных в качестве сценария, сначала введите на лист значения, как показано на следующем рисунке:

Изменяемые ячейки содержат введенные значения, а ячейка результата — формулу, основанную на изменяемых ячейках (на этом рисунке в ячейке B4 указана формула =B2-B3).

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

прогнозом? Анализ "что если"" />

В диалоговом окне имя сценария — наихудшее дело с именем сценария и укажите, что ячейки B2 и B3 — это значения, которые меняются между сценариями. Если вы выберете Изменяемые ячейки на листе, прежде чем добавлять сценарий, Диспетчер сценариев автоматически вставит нужные ячейки, в противном случае вы сможете ввести их вручную или воспользоваться диалоговым окном выбора ячейки справа от поля изменения ячеек.

Примечание: Хотя в этом примере только две изменяющихся ячейки (B2 и B3), в сценарии может быть до 32 ячеек.

Защита: вы также можете защитить сценарии, выбрав нужные параметры в разделе "Защита".

Чтобы запретить изменение сценария на защищенном листе, установите флажок запретить изменения.

Чтобы при защите листа сценарий не отображался, установите флажок скрыть.

Примечание: Эти параметры применяются только к защищенным листам. Дополнительные сведения о защищенных листах можно найти в разделе Защита листа

Теперь предположим, что в лучшем случае ожидается доход в 150 000 ₽, а стоимость проданной продукции составляет 26 000 ₽, в результате чего получается 124 000 ₽ валовой прибыли. Чтобы определить этот набор значений как сценарий, создается другой сценарий с именем "Лучший случай" и для него вводятся другие значения ячеек B2 (150 000) и B3 (26 000). Поскольку ячейка валовой прибыли (B4) представляет собой формулу — разницу между доходами (B2) и расходами (B3) — ячейка B4 для сценария "Лучший случай" не изменяется.

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

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

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

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

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

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

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

Сводный отчет по сценариям, основанный на двух приведенных выше примерах, может выглядеть так:

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

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

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

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

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

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

См. также

Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

2.3 Задача: Расчет размера пенсионных накоплений

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

Использование средства подбора параметра

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

Рис. 6 - Таблица для расчета размера пенсионных накоплений

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

то есть предполагается, что речь идет о мужчине, который выйдет на пенсию в 60 лет (С2), а также величина процентной ставки (D2).

Сумма накоплений рассчитывается с помощью функции по следующей формуле:

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

БС (ставка; кпер: плата; нз: тип)

Аргументы функции: ставка — размер процентной ставки за период; кпер - общее число периодов выплат годовой ренты; плата - выплата, производимая в каждый период (это значение не может меняться на протяжении всего времени выплат), причем обычно плата состоит из основного платежа и платежа по процентам; нз — текущая стоимость или общая сумма всех будущих платежей, начиная с настоящего момента (по умолчанию — 0); тип — число, которое определяет, когда должна производиться выплата (0 — в конце периода, задается по умолчанию, 1 — в начале периода).

Формула имеет такой вид, так как предполагается, что проценты начисляются не ежемесячно, а в начале, следующего года за предыдущий год. Допустим, необходимо определить, в каком возрасте будущему пенсионеру надо начинать выплаты, чтобы потом получить прибавку к пенсии в размере 1000 руб. Для этого следует выделить ячейку, отведенную для представления результата (в данном случае F2), и вызвать команду “Сервис » Подбор параметра”. Когда появится диалоговое окно Подбор параметра (рис. 7), адрес выделенной ячейки будет автоматически вставлен в поле “Установить в ячейке”. Нужно указать в поле “Значение” целевое значение - 1000. Нужно поместить курсор ввода в поле “Изменяя значение ячейки” и выделить ячейку А2, после чего ее адрес отобразится в указанном поле.

Рис. 7 Диалоговое окно ”Подбор параметра” с заданными параметрами

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

После выполнения всех установок нужно нажать кнопку К, и поиск нужного значения будет начат. Результат вычисления отобразится в диалоговом окне “Результат подбора параметра”, а также в исходной таблице (рис, 8). После нажатия кнопки 0К полученные значения будут вставлены в таблицу.

Рис. 8 - Результаты подбора параметра

Если поиск нужного значения продолжается слишком долго, прервать его на время можно с помощью кнопки “Пауза”. Кнопка “Шаг” позволяет просмотреть промежуточные результаты вычисления.

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

2. Вызвать команду “Сервис » Подбор параметра”. В поле “Установить в ячейке” появившегося диалогового окна будет отображаться адрес целевой ячейки.

3. Задать в поле “Значение” значение, которое должна содержать целевая ячейка

4. Указать в поле “Изменяя значение ячейки” адрес ячейки, значение которой необходимо установить таким, чтобы в целевой ячейке получить заданное значение.

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

6. Нажать кнопку ОК, если вы хотите заменить значения в ячейках на рабочем листе новыми, или кнопку “Отмена” в противном случае.

2.4 Применение функции подбора параметра при работе с диаграммами

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

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

Когда кнопка мыши будет отпущена, откроется диалоговое окно “Подбор параметра. В поле “Установить в ячейке” появится имя ячейки В5, а в поле “Значение” — число, соответствующее последнему значению, которое отображалось в поле подсказки. Курсор ввода будет находиться в поле “Изменяя значение ячейки”, поэтому остается лишь ввести в данное поле значение В2. Нужно щелкнуть на кнопке ОК, после чего появится диалоговое окно “Результат подбора параметра”, в котором будет содержаться нужная информация. Щелкнуть на кнопке ОК для возврата на рабочий лист. В ячейках уже содержатся новые значения и что в соответствии с ними настроена высота полос гистограммы (рис. 9).

Рисунок 8 - Гистограмма “Выручка от продажи изделий в трех регионах”

Рисунок 9 - Гистограмма “Выручка от продажи изделий в трех регионах” c применением подбора параметров

2.5 Задача: Вычисление радиуса описанной окружности по трем сторонам треугольника с помощью подбора параметра

Вычисляем радиус описанной окружности R по формуле:

где а = 2, b = 4, с_ = 5 – стороны треугольника

S – площадь треугольника

Площадь треугольника S вычисляется по формуле Герона:

где а = 2, b = 4, с_ = 5 – стороны треугольника

р – полупериметр треугольника

Полупериметр треугольника p вычисляем по формуле:

Пример вычисления радиуса описанной окружности в программе Excel показан на рисунке 10

Рисунок 10 - Вычисление радиуса описанной окружности по трем сторонам треугольника

Итак, радиус описанной окружности R по трем сторонам треугольника а, b, с вычислен. Если зафиксировать длины сторон b и с, то можно считать, что R вычисляется как функция а. Но Ехсе1 дает возможность решить обратную задачу: по заданному R вычислить а. При этом не нужно решать вручную громоздкую задачу отыскания а как функции R. Формул на рабочем листе для этой цели вполне достаточно. Например, надо определить величину а при R = 3. Для это нужно выделить ячейку В9, в которой вычисляется R. В меню нужно выбрать "Сервис/ Подбор параметра". Выводится диалоговое окно "Подбор параметра". Поле "Установить в ячейке:" уже содержит адрес выделенной ячейки B9. В поле "Значение:" нужно ввести 3. В поле "Изменяя значение ячейки:" нужно ввести адрес ячейки В2, содержащей величину стороны а (если щелкнуть мышью по этой ячейке, то в поле ввода окажется адрес $В$2). После нажатия кнопки "ОК" выводится новое окно "Результаты подбора параметра". Если увеличить разрядность числа в ячейке B9, то можно увидеть, что R достигло значения 2.9999172. При этом а = 1.515753171.

Результаты выполнения показаны на рисунке 11

Рисунок 11 - Вычисление радиуса описанной окружности по трем сторонам треугольника с помощью подбора параметра

Единственное ли значение a соответствует R = 3? На этот вопрос изложенный метод подбора параметра не дает ответа.

Список используемой литературы

1. Справка MS Excel

2. Microsoft Office Excel 2003. Учебный курс / В. Кузьмин, - СПб.: Питер: Издательская группа BHV, 2004. – 493 с.

3. Excel 2003. Эффективный самоучитель / В.В. Серогородский, - СПб.: Наука и техника, 2005. – 400 с.

4. Excel: Сборник примеров и задач / С.М. Лавренов, - М.: Финансы и статистика, 2003. – 336 с.

Читайте также: