​​Типы анализа “что если”

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

Анализ "что если" - это процесс поиска ответов, например, на следующие вопросы: "Что будет, если процентная ставка кредита поднимется до 7,5%?" или "Что будет, если мы поднимем цену на нашу продукцию на 5%?"

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

▪️ Анализ "что если" вручную. Вводите новые значения во входные ячейки и наблюдайте, как изменяются результаты расчетов в ячейках с формулами.
▪️ Таблицы данных. Создайте таблицу, в которой будут представлены результаты вычислений по формулам из выделенных ячеек, в зависимости от систематического изменения одной или двух ячеек.
▪️ Диспетчер сценариев. Этот инструмент создает именованный сценарий и генерирует отчеты с использованием средств структуризации данных или сводных таблиц.

#АнализДанных #ЧтоЕсли

​​📊 Самые престижные и высокооплачиваемые позиции в бизнесе требуют уверенных навыков работы в Excel. Именно поэтому в SF Education создали курс, раскрывающий весь функционал этой программы и показывающий на кейсах возможности для оптимизации и автоматизации рабочих задач.

📈 Курс «Excel Acadamy» подходит широкому кругу профессионалов, регулярно работающим с бизнес-данными.

Переходи по ссылке https://bit.ly/2NTXU84 и записывайся на курс прямо сейчас! А по промокоду «billgeyts» ты получишь скидку 10% на все курсы SF Education.
⠀
Чему ты научишься:

✔️ Применять продвинутые функции и формулы для быстрой и качественной работы с данными
✔️ Строить сводные таблицы и использовать Power Pivot для выполнения мощного анализа данных и создания сложных моделей
✔️ Использовать VBA для автоматизации повторяющихся задач и экономии рабочего времени - своего и компании
✔️ Визуализировать данные в Power BI и создавать управленческие дэшборды для менеджмента и собственников компаний
✔️ Эффективно работать в Google Sheets, используя все преимущества и возможности этой программы

Доступ ко всем материалам курса сохраняется после его окончания!
⠀
✈️  Твой карьерный рост начинается с SF Education.

Переходи по ссылке https://bit.ly/2NTXU84 и записывайся на курс прямо сейчас! А по промокоду «billgeyts» ты получишь скидку 10% на все курсы SF Education.

​​Проведение анализа "что если" вручную

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

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

#АнализДанных #ЧтоЕсли

​​Создание таблиц данных с одним входом

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

❗️ Не путайте таблицы данных со стандартными таблицами Excel создаваемыми с помощью команды Вставка => Таблицы => Таблица. Это два совершенно разных объекта Excel, полностью независимых один от другого.

В таблице данных с одним входом приводятся результаты расчетов по одной или нескольким формулам при различных значениях одного входного параметра. Создавать такую таблицу придется вручную.
Таблицу можно расположить в любом месте рабочего листа. В левом столбце содержатся различные значения входного параметра. В верхней строке содержатся формулы или (чаще всего) ссылки на ячейки с формулами, по которым рассчитывается результат. (Сами формулы могут находиться в другом месте рабочего листа.) Можно использовать любое количество ссылок на формулы (или только одну). Excel вычисляет значения, которые получаются в результате подстановки каждого из исходных значений во входную ячейку, и помещает результат в соответствующий столбец (в ячейку, которая находится под ячейкой с соответствующей формулой или со ссылкой на формулу).
В примере использован рабочий лист, по которому рассчитывается ипотечная ссуда. Наша цель - создать таблицу данных, в которой отражались бы значения, рассчитанные по формулам, находящимся в четырех ячейках (Размер ссуды, Месячная плата, Общая сумма выплат, Общая сумма комиссионных), при изменении процентных ставок от 4,5% до 6,5% с шагом 0,25%.

Чтобы создать таблицу данных, выделите диапазон ячеек (в данном случае – E3:L12) и выберите команду Данные => Прогноз => Анализ "что-если" => Таблица данных. В открывшемся диалоговом окне Таблица данных необходимо определить ячейку листа, в которую должны подставляться исходные данные.

Воспользовавшись этой таблицей данных, мы получили интересующую нас информацию о различных характеристиках займа при различных значениях процентной ставки. Обратите внимание, что сам размер ссуды (столбец F) в данном случае оставался неизменным. Так происходит потому, что формула в ячейке C10 не зависит от изменяемого параметра - размера процентной ставки.

#АнализДанных #ЧтоЕсли

​Создание таблицы данных с двумя входами

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

В нашей модели используются две ячейки для ввода информации: количество разосланных рекламных материалов и предполагаемый процент ответов с заказами. В таблице Параметры присутствуют следующие элементы:
▪️ Стоимость единицы материалов. Стоимость печати одного рекламного буклета. Цена изменяется в зависимости от их количества: 0,20 руб. - если количество экземпляров не превышает 200 000; 0,15 руб. - для 200 001-300 000 экземпляров; 0,10 руб. - если количество экземпляров превышает 300 000. Стоимость отпечатанных материалов (в зависимости от их количества) определяется по следующей формуле, расположенной в ячейке В8:

=ЕСЛИ(B4<200000;0,2;ЕСЛИ(B4<300000;0,15;0,1))

▪️ Почтовые расходы на единицу. Почтовые расходы фиксированы и составляют 0,28 руб. за одно почтовое отправление.
▪️ Число полученных ответов. Количество ответов с заказами, которое предполагается получить. Определяется в зависимости от процента предполагаемых ответов и количества разосланных материалов. Расчет выполняется по следующей формуле, расположенной в ячейке В10:

=B4*B5

▪️ Доход на один полученный ответ. Фиксированное значение. Компании известно, что за каждый заказ она получит 18,5 руб. дохода.
▪️ Общий доход. Суммарный доход вычисляется по следующей простой формуле, расположенной в ячейке В12: здесь величина дохода, полученного от одного заказа, умножается на количество поступивших ответов с заказами:

=B10*B11

▪️ Издержки на печать и рассылку. По приведенной ниже формуле, находящейся в ячейке В13, вычисляются суммарные расходы на рекламу, в которые входит стоимость печатных материалов и почтовых услуг:

=B4*(B8+B9)

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

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

#АнализДанных #ЧтоЕсли

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

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

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

#АнализДанных #ЧтоЕсли

​Хотите оптимизировать работу с Excel?
Освоить продвинутые методы обработки данных с помощью SQL и Python?

Чтобы автоматизировать работу с данными и научиться формировать автоматически обновляемые отчеты, самое время начать изучать Python и сделать его одним из главных инструментов в своей работе.

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

Ozon New Skills ведет набор на популярный курс "Аналитика данных".
Преподаватели - практикующие специалисты с большим опытом, в т.ч. продуктовые аналитики, разработчики самого курса.

Бесплатный мастер-класс по аналитике данных с нуля пройдёт 17 марта в 20-00 по московскому времени.

С помощью Python мы соберем данные и исследуем влияние эпидемии COVID-19 на акции фармацевтических компаний. Научимся строить визуализации в Tableau.

🔥 Участие бесплатное!

Записывайся по ссылке:
https://ozon.ru/t/uUTBT

​Определение сценариев

Чтобы получить общее представление о средстве Диспетчер сценариев, начнем с простого примера - производственной модели.
Рабочий лист содержит две ячейки с исходными данными: стоимость нормо-часа (ячейка В2) и стоимость единицы материала (ячейка ВЗ). Компания производит три изделия, причем на производство каждого из них необходимо затратить разное количество материала и времени.
В таблице вычисляются общая прибыль по каждому изделию (строка 13) и суммарная прибыль (ячейка В15). Правление компании пытается спрогнозировать суммарную прибыль на следующий год, но при этом будущие стоимости материалов и нормо-часа точно не известны. Поэтому были определены три сценария.
В оптимистическом сценарии стоимости нормо-часа и материалов наименьшие. В пессимистическом сценарии эти значения самые большие. В третьем сценарии, или в наиболее вероятном случае, используются некоторые промежуточные значения этих параметров (они определяются с учетом ряда факторов). Менеджеры должны готовиться к худшему, однако им также необходимо знать, что будет, если сработает оптимистический сценарий.
Доступ к средству Диспетчер сценариев можно получить с помощью команды Данные => Прогноз => Анализ "что-если" => Диспетчер сценариев. После выбора этой команды откроется диалоговое окно Диспетчер сценариев.

#АнализДанных #ЧтоЕсли #ДиспетчерСценариев

​​Создание сценария

Чтобы добавить сценарий, щелкните на кнопке Добавить диалогового окна Диспетчер сценариев. Откроется диалоговое окно Добавление сценария.

Это диалоговое окно состоит из четырех полей:
▪️ Название сценария. В этом поле для создаваемого сценария можно указать любое имя, которое покажется вам подходящим.
▪️ Изменяемые ячейки. Ячейки, в которых находятся исходные данные для сценария. Можно ввести в это поле абсолютный адрес ячейки или просто указать на нее. Если ячейке было присвоено имя, здесь его также можно ввести. Разрешается указывать несколько ячеек, причем они необязательно должны быть смежными. Если ячейки задаются прямым их указанием, при указании нескольких ячеек удерживайте нажатой клавишу <Ctrl>. В каждом сценарии, которому присвоено имя, можно использовать одни и те же наборы изменяемых ячеек или разные изменяемые ячейки. Количество изменяемых ячеек для одного сценария ограничено числом 32.
▪️ Примечание. По умолчанию программа Excel помещает в это поле информацию о том, кто создал сценарий, а также дату его создания. Однако эту информацию можно отредактировать, добавить к ней новые данные или вовсе ее удалить. Если сценарию было присвоено подходящее имя, в больших комментариях нужды не будет. Тем не менее некоторые сценарии могут быть настолько сложными, что ввод дополнительных достаточно подробных комментариев будет весьма желателен как для его создателя, так и для всех тех, кто будет им пользоваться.
▪️ Защита. Два флажка, которые позволяют защитить сценарий от изменений и скрыть его, можно активизировать только в том случае, если рабочий лист защищен и в диалоговом окне Защита листа (Главная => Ячейки => Формат => Защитить лист) установлен флажок Изменение сценариев. Защита сценария предотвращает модификацию его кем-либо другим, а скрытый сценарий вообще не появляется в диалоговом окне Диспетчер сценариев.

#АнализДанных #ЧтоЕсли #ДиспетчерСценариев

​​За тридцать лет существования MS Excel превратился в мощный инструмент для работы с массивами данных.

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

На бесплатном курсе Нетологии вы научитесь применять инструменты Excel, которые в разы увеличат вашу производительность. Узнаете, как наглядно представлять результаты своей работы и как заставить Excel работать вместо вас.

Упростите себе работу с Excel ↓
https://netolo.gy/gkR

​​Изменение сценария

После создания сценариев может потребоваться изменить их параметры. Для этого выполните следующие действия:
1. Для того чтобы заменить одно или более значений для изменения ячеек сценария, щелкните на кнопке Изменить в диалоговом окне Диспетчер сценариев;
2. В списке Сценарии выберите сценарий, который необходимо откорректировать, и щелкните на кнопке Изменить. Откроется диалоговое окно Изменение сценария;
3. Щелкните на кнопке ОК. Откроется диалоговое окно Значения ячеек сценария.
4. Внесите необходимые изменения и, чтобы возвратиться в диалоговое окно Диспетчер сценариев, снова щелкните на кнопке ОК. Обратите внимание на то, что Excel в этом случае автоматически модифицирует содержание окна Примечание. Новый текст в нем будет указывать на дату изменения сценария.

#АнализДанных #ЧтоЕсли #ДиспетчерСценариев

​​Все ещё считаете, что работать в Excel — это сложно? Пройдите бесплатный онлайн-интенсив и освойте формулы и функции для простых вычислений.

Для участия нужна только регистрация: 👉 https://clc.am/RKyF3Q

Вы научитесь:

💫 использовать разные способы ввода данных;
💫 применять пользовательские форматы чисел;
💫 создавать формулы с абсолютными и относительными ссылками;
💫 рассчитывать рабочие дни с учётом праздников в таблице.

💣Авторы трёх лучших проектов по мнению спикера получат сертификат на 5 000 рублей на любой курс Skillbox!

​​Объединение сценариев

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

1. Щелкните на кнопке Объединить диалогового окна Диспетчер сценариев.
2. В открывшемся диалоговом окне Объединение сценариев выберите в раскрывающемся списке Книга рабочую книгу, в которой находится рабочий лист с нужным сценарием.
3. В списке Лист выберите требуемый рабочий лист. Обратите внимание на то, что по мере того, как вы будете проходить по этому списку, в диалоговом окне Объединение сценариев будет отображаться, какое количество сценариев есть на каждом листе.
4. Щелкните на кнопке ОК, чтобы вернуться к предыдущему диалоговому окну, в котором теперь будут отображаться названия сценариев, добавленных из выбранной рабочей книги.

#АнализДанных #ЧтоЕсли #ДиспетчерСценариев

​​Получите мощный заряд знаний с онлайн-университетом SF Education — до 31 марта курсы по финансам, бизнесу и аналитике можно приобрести с выгодой до 80%!

На все курсы действуют:
⁃ скидка до 60%;
⁃ кэшбэк и скидка 5% при оплате картой банков-партнеров;
⁃ кэшбэк 5% за каждого приведенного друга;
⁃ trade-in политика с возвратом до 70% от стоимости обучения;
⁃ беспроцентная рассрочка от банков-партнеров

А для подписчиков канала мы собрали специальную подборку:

⁃ курс «Excel Academy» от 729 руб. в мес.
⁃ курс «Школа инвестиций» от 1 688 руб. в мес.
⁃ программа «Бизнес-аналитик» от 2 188 руб. в мес.

Если ты хочешь освоить новую профессию, повысить продуктивность или подняться по карьерной лестнице — время действовать!

Переходи по ссылке https://bit.ly/3vCMMNJ и выбирай программу прямо сейчас!

​​Создание отчета по сценарию

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

Для простых случаев использования сценариев часто подходит обычный отчет в виде структурированной таблицы. Однако, если у вас много сценариев с различными ячейками результата, лучше использовать сводную таблицу, поскольку она предоставляет более гибкие возможности анализа данных.
Также в диалоговом окне Отчет по сценарию нужно указать ячейки результата, в которых содержатся интересующие вас формулы. Для нашего примера выберите (В13:D13) и (В15) (множественный выбор ячеек). В отчете будут представлены значения каждого из исходных параметров и суммарная прибыль.
Excel создаст новый лист и поместит на него итоговою таблицу.
Если изменяемым ячейкам и ячейкам результата были присвоены имена, то в таблице будут использованы эти названия. В противном случае будут отображаться только адреса ячеек.

#АнализДанных #ЧтоЕсли #ДиспетчерСценариев #Отчет