Данный курс - второй в рамках специализации «Практики анализа экономических данных: от простого к сложному». Курс посвящен «продвинутым» инструментам работы в среде MS Excel: применению сценариев, использованию инструмента «Подбор параметра», формированию дэшбордов и интерактивных диаграмм, использованию функций массивов, и др. Анализ данных – актуальная проблема для всех без исключения компаний. Главная цель анализа – дать информацию, полезную для принятия управленческих решений, лучшего понимания бизнеса. Научить анализировать данные сложно. Представленные курсы охватывают практики оперативной и управленческой аналитики в среде электронных таблиц MS Office. Используется подход к обучению анализу данных в среде Microsoft Excel через решение типовых задач, проецируемых на любую предметную область. Данный подход апробирован авторами на большом количестве групп повышения квалификации экономистов и менеджеров. В данном курсе слушатель сможет применить знания инструментов MS Excel для анализа деятельности компании. По завершении курса слушатели будут: Знать: - инструменты разработки аналитических отчетов: функции, фильтры, формулы массивов, сводные таблицы; - инструменты графического анализа экономических данных: интерактивных и других диаграмм; - правила применения инструментов ЧТО-ЕСЛИ; - инструменты разработки информационно-аналитических панелей. Уметь: - формировать аналитические отчеты, используя весь спектр инструментов MS Excel; - моделировать поведение показателей компании с помощью инструментов ЧТО-ЕСЛИ; - пользоваться инструментами фильтрации при сложных условиях; - формировать дэшборды с показателями деятельности компании. Владеть: - навыками выбора адекватных аналитических инструментов; - навыками совместной работы с экономическими данными; - инструментами работы с текстовыми данными.
Видео 1.1 "Подбор значений управляющих параметров"
Loading...
From the course by Saint Petersburg State University
Практики управленческой аналитики в MS Excel
6 оценки
From the lesson
Инструменты эффективного анализа
Meet the Instructors
Лезина Татьяна Андреевнадоцент
Кафедра информационных систем в экономике
Иванова Виктория Валерьевнадоцент
Кафедра информационных систем в экономике
Гадасина Людмила Викторовнадоцент
Кафедра информационных систем в экономике
[МУЗЫКА]
[МУЗЫКА] Здравствуйте!
Меня зовут Лезина Татьяна Андреевна, и вашему вниманию
предлагается курс «Практики управленческой аналитики в Microsoft Excel».
Этот курс является логическим продолжением нашего предыдущего курса,
и в нем рассматриваются продвинутые инструменты анализа данных,
которые, как правило, нужны для лиц, принимающих управленческие решения.
Начнем с инструментов, которые объединены общим названием «что если».
В каких ситуациях они применяются?
В первом курсе мы рассмотрели историю, когда разным менеджерам
по разным регламентам назначаются премии за проделанную работу.
Мы обсудили ситуацию и управленческую, и,
соответственно, расчетную, когда бизнес-правило определяет расчеты.
И одним из регламентов была ситуация, когда менеджер получал определенный
процент от суммы сделок в случае, если менеджер перевыполнял установленный план.
Естественный вопрос, который возникает: «А откуда берутся эти значения
премиального процента или плановой цифры, плановой планки?
Как лицо, принимающее решения, определяет,
какой должен быть процент и какова должна быть вот эта плановая цифра,
после которой менеджер уже получает какое-то вознаграждение?».
Следующий вопрос: «А каковы сочетания этих параметров,
план и премиальный процент, которые позволяли бы выйти на премиальный фонд?
Или какова должна быть эта величина премиального процента, если управленец,
лицо, принимающее решения, знает, какой объем средств он может выделить на
премии?» Все эти задачи, они разные по своей постановке,
разные по своей сути, решаются с помощью разных инструментов,
которые относятся к этой группе инструментов «что если».
Рассмотрим первую проблему.
В предыдущем курсе мы уже рассматривали ситуацию, которая сейчас представлена
на экране, когда менеджерам назначается премия в размере 5 % от суммы сделок,
если менеджер за определенный период выполнил план — 40 тысяч,
и сумма сделок превышает эту сумму, то есть сумма сделок больше 40 тысяч.
Мы обсуждали, как формализовать расчет премиальных вознаграждений,
и делалось это с помощью функции ЕСЛИ.
Позвольте это напомнить.
Итак, если сумма сделок меньше плана, то менеджер премию не получал.
Если сумма сделок превышала установленный план,
то менеджеру назначалась премия в размере 5 % от суммы сделок.
А затем мы премиальный процент умножали на сумму сделок.
В итоге у нас рассчитаны и автоматически получилось,
что у нас только два менеджера получают премию.
И премиальный фонд в этой ячейке, давайте я его сделаю желтеньким цветом,
премиальный фонд в данный момент у нас 10145 рублей и сколько-то копеек.
А теперь как раз первая задача «что если».
Предположим, менеджер, принимающий решения управленческие,
готов выделить на премирование управленцев большую сумму,
рассмотрим такую оптимистичную ситуацию.
Предположим, он готов потратить на премию менеджерам 22 тысячи рублей.
Вопрос: какова должна быть величина премиального процента,
чтобы исчерпать вот такой премиальный фонд?
Итак, у нас есть ответ.
Нам нужно подогнать, по существу, под этот ответ один из управляющих параметров.
Итак, для этого нам понадобится специальный инструмент,
который находится в группе «что если».
Его можно найти на закладке «Данные»: анализ «что если» и инструмент,
который называется «Подбор параметра».
Я его вызываю, и дальше достаточно простая логика.
В ячейке, которая у меня была изначально выделена, премиальный фонд,
я хочу указать оптимистичное значение 22 тысячи рублей, указав,
какую из участвующих в расчете ячеек можно изменять.
Было объявлено, что я готова увеличить премиальный процент.
Нажимаю ОК, и мы видим, что совершенно волшебно у меня
подогналось значение премиального процента до вот такого точного значения.
Конечно, на практике такое точное значение не нужно, но уже каждый менеджер
округляет до того правильного значения, которое его устраивает на практике.
Какие ограничения у этого инструмента.
Ограничение следующее: можно подогнать только один из параметров,
участвующих в расчетах.
Многие, знающие школьную математику,
тут же могут задать вопрос: «А всегда ли решаются обратные задачи?
Математика нам приводит много примеров, когда не все обратные задачи решаются».
И здесь точно такой же ответ: конечно, не всегда.
И, например, если вы самостоятельно попробуете подогнать, поуправлять,
давайте говорить более профессионально, управлять параметром «План»,
а не величиной премии, вы увидите, что такая задача не решается.
И попробуйте сами понять, в чем природа отсутствия точного значения
при другой содержательно задаче: подгонке плановой величины
для получения нужного объема премиального фонда.
Что важно при применении такого инструмента?
Важно, что на первом шаге задача обязательно должна быть решена при любых
значениях входных параметров.
Именно поэтому у меня уже был проведен заранее расчет при наличии
плана в 40 тысяч и премиального процента величиной 5 %.
Рассмотрим следующую ситуацию.
Рассмотрим другой регламент назначения премий,
который очень часто находит отклик в душах слушателей, которые приходят на программу
повышения квалификации по анализу данных в среде Microsoft Excel.
Во многих компаниях сейчас практикуется такой регламент назначения премий.
Менеджерам назначают некоторое количество
условных баллов за их деятельность, которые потом превращаются уже,
собственно, в величину премиального вознаграждения.
Поэтому я сейчас введу условные коэффициенты,
извините, и,
не обсуждая, откуда такие баллы берутся у менеджеров,
— это будет другой регламент назначения премии — предположим,
вот так вот, попробуем формализовать задачу по расчету...
Хочется, чтобы было симпатично, поэтому я потрачу полсекунды на оформление таблицы.
И давайте посмотрим, как сейчас с помощью подбора параметров можно решить задачу
по распределению некоторой премии, предположим, 25 тысяч рублей.
Итак, я бы стала делать так.
В одну из ячеек я ввожу величину
веса одного балла, то есть сколько весит один балл.
И давайте обязательно этой ячейке присвоим имя,
с именем работать удобнее и профессиональнее.
И дальше, по другому регламенту,
Премия2 у нас будет рассчитываться по правилу: равно, количество
вот этих условных баллов — может быть, лучше было бы назвать столбик «Баллы»,
но давайте оставим это так — умножаем на вес одного балла.
И, соответственно, у нас получится вот такая информация с
вознаграждением менеджерам.
И я получаю премиальный фонд, премиальный фонд у меня получился 5300.
А как вы помните, было объявлено,
что топ-менеджер готов распределить 25 тысяч рублей.
Мы сейчас решаем обратную задачу следующим образом.
Данные > Анализ «что если» > Подбор параметров.
Мы готовы в этой ячейке, даже мы хотим в этой ячейке получить
25 тысяч рублей, разрешая изменять ячейку со 100 рублями.
Нажимаем ОК, и мы получили точное значение веса одного балла,
который по каким-то регламентам компании
был назначен менеджеру для его дальнейшего премирования.
Скептики на показе этого примера иногда говорят,
что проще решить эту задачу так, как мы решали ее в школе: сложить
все коэффициенты, потом премиальный фонд разделить на коэффициенты,
и потом уже получить премиальное вознаграждение каждого менеджера.
Может быть, это кто как привык.
Но если вы регулярно распределяете по такому правилу заданный премиальный фонд,
то так достаточно получается экономно и более красиво.
И даже скептики признавались, что при рутинном формировании
премий по этому регламенту, этот способ более удобный.
И еще один пример применения инструмента подбора параметра.
Сейчас перед вами на экране расчеты общего
прогнозируемого денежного потока некоторой компании,
которая собирается продавать изделия по цене 2 тысячи и ожидает,
что продаст его в объеме 20 тысяч штук.
Общий прогнозируемый денежный поток — это, собственно, остаток денежных средств,
которые остаются в компании.
И нам от этой большой таблички важно только то,
что денежный поток рассчитывается за 14 шагов, эти формулы здесь приведены,
они мне нужны были для расчета, я их сейчас уже удаляю.
И вопрос следующий: в такой более серьезной,
более громоздкой расчетной ситуации сработает ли подбор параметров?
Итак, у меня есть величина общего прогнозируемого
денежного потока в первый год, которая рассчитывается
с использованием цены изделия и ожидаемого объема реализации.
Поставим задачу, соответственно, таким образом.
Задача, которая, как правило, волнует управленцев,
пользователей: сколько мне нужно продать изделий,
для того чтобы в первый год получить, например, 5 миллионов?
Для этого запускаем подбор параметров, указываем,
что мы хотим в первый год получить прогнозируемый денежный поток 5 миллионов,
а управлять мы готовы объемом проданных изделий.
Мы видим, что нам нужно продать 22811
изделий, для того чтобы выйти на заданный ответ.
Этот инструмент позволяет подгонять один из управляющих параметров под тот ответ,
который нужен управленцу.
И, в принципе, самостоятельно попробуйте,
возможно хранение вот этих управляющих параметров не обязательно на том же листе,
на котором у вас есть расчет, но управляющий параметр может храниться
и на любом листе вашей книги.
Ознакомьтесь с нашим каталогом
Присоединяйтесь бесплатно и получайте персонализированные рекомендации, обновления и предложения.
Coursera делает лучшее в мире образование доступным каждому, предлагая онлайн-курсы от ведущих университетов и организаций.
© Coursera Inc., 2018 Все права защищены.
