[МУЗЫКА]
[МУЗЫКА] Здравствуйте!
Меня зовут Лезина Татьяна Андреевна, и вашему вниманию
предлагается курс «Практики управленческой аналитики в 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
изделий, для того чтобы выйти на заданный ответ.
Этот инструмент позволяет подгонять один из управляющих параметров под тот ответ,
который нужен управленцу.
И, в принципе, самостоятельно попробуйте,
возможно хранение вот этих управляющих параметров не обязательно на том же листе,
на котором у вас есть расчет, но управляющий параметр может храниться
и на любом листе вашей книги.
