Принятие управленческих решений с помощью Excel

Принятие управленческих решений с помощью Excel
Категория статьи:
IT, софт

Введение

Подсчет суммы с несколькими условиями

Формула массива для принятия управленческого решения

Подсчет ячеек при подготовке отчета

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

 

 Введение

    В работе каждого руководителя предприятия, как минимум, раз в год возникает вопрос: куда двигаться дальше? Что производить? Сколько? И как реализовывать? Как правило, за основу анализа берутся данные предыдущих периодов, подготовкой которых занимается экономист, бухгалтер или же сам директор, если фирма небольшая. Облегчить принятие многих важных управленческих решений поможет Excel.

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

    Исходные данные, которые вам, к примеру, предоставил менеджер по продажам, выглядит так:

Исходная таблица с данными

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

    Итак, перед нами стоят следующие вопросы:

  1. Какой из товаров наиболее востребован на рынке?
  2. В каком городе наши товары раскупаются больше всего?
  3. В какой период наши товары наиболее популярны?
  4. Какой из товаров приносит наибольшую прибыль?
  5. Прибыль какого именно магазина выше всего?

 

Подсчет суммы с несколькими условиями

    Разберемся с первыми двумя вопросами. Для этого представим исходную таблицу в следующем, упрощенном виде:

Шаблон для таблицы 2

    Нам нужно из исходной таблицы выбрать годовую сумму реализации по каждому товару в конкретном городе. Сделать это легко с помощью такой функции Excel, как СУММЕСЛИМН(). Она позволяет суммировать числовые данные с учетом множества условий. В нашем случае их всего два: наименование товара и город реализации. Встаем в ячейку V2, нажимаем знак «=» и набираем СУММЕСЛИМН. Как только вы введете первые буквы, Excel сам предложит вам список и функций с похожим названием. Вам надо только выбрать требуемую. Начинаем через точку с запятой вводить аргументы:

  1. Диапазон суммирования, то есть та часть таблицы, из которой Excel будет складывать итоговую сумму по указанным условия. В нашем случае (см. исходную таблицу) это диапазон $D$2:$D$15. Про знаки $ не поясняю, потому как к тому моменту, когда вы созрели до подготовки финансового отчета, пусть и наипростейшего, вы уже должны иметь понятие что такое закрепление, абсолютная и относительная ссылка.
  2. Диапазон первого условия – это та часть исходной таблицы, где Excel будет искать ваше первое условие. Для нас это $B$2:$B$15.
  3. Первое условие, то есть то, что нам должен найти Excel в указанном в п2 диапазоне. Начнем с Товара 1, поэтому указываем ячейку с его названием - $U
  4. Диапазон второго условия - это та часть исходной таблицы, где Excel будет искать ваше второе условие. В данном случае это $C$2:$C$15.
  5. Второе условие – то, что ищем в диапазоне, указанном в п4, - V$1.

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

 Готовая таблица 2

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

График из таблицы 2

   Глядя на данный график, можно принять предварительное управленческое решение о том, в каком именно городе увеличить/сократить реализацию того или иного товара

 

Формула массива для принятия управленческого решения

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

 Шаблон для таблицы 3

    Если мы воспользуемся формулой, рассмотренной в предыдущем разделе, то получим следующий результат:

неверная формула

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

    Формула для Товара 1 в 1 квартале выглядит так:

 

    =СУММ(($B$2:$B$15=$AD2)*($E$1:$H$1=AE$1)*$E$2:$H$15)

 

    Разберем ее по элементам:

  1. $B$2:$B$15 – список с наименованием товаров в исходной таблице.
  2. =$AD2 – наименование товара, по которому нужно произвести расчет, т. е. Товар 1
  3. $E$1:$H$1 – список кварталов в исходной таблице.
  4. AE$1 – указание на квартал.
  5. $E$2:$H$15 – диапазон, из которого Excel соберет сумму по двум указанным выше условиям.

 

    А теперь закрываем скобку и нажимаем не просто Enter, Ctrl+Shift+Enter и наша формула приобретет вид:

 

    {=СУММ(($B$2:$B$15=$AD2)*($E$1:$H$1=AE$1)*$E$2:$H$15)}

 

    Если вы нажмете просто Enter, как обычно, то получите #ЗНАЧ!

   Протягиваем формулу во всех ячейка и получаем конечную таблицу:

Верная формула

    Снова для наглядности строим график:

График по таблице 3

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

 

Подсчет ячеек при подготовке отчета

   

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

шаблон для таблицы 4

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

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

   Итак, в первой части формулы мы посчитаем общую сумму затрат, по указанному товару, используя формулу СУММ (). В ней может быть только одно условие, а порядок аргументов отличается от СУММЕСЛИМН ().

    Встаем в ячейку M23, ставим знак «=» и вписываем название функции, а далее указываем аргументы:

      1.Диапазон условия – где Excel будет искать наше условие: $B$2:$B$15.

      2.Условие – что ищет Excel в диапазоне условия из п.1: $L23.

      3.Диапазон суммирования – в каком столбце Excel будет искать сумму по указанному условию: $I$2:$I$15.

    Теперь полученную сумму необходимо разделить на количество повторений Товара 1. Используем для этого функцию подсчета ячеек с условием СЧЕТЕСЛИ(). Она содержит два аргумента:

      1.Диапазон, в котором Excel будет искать наше условие, т. е. Товар 1: $B$2:$B$15.

      2.Критерий – что Excel будет искать в диапазоне условий: $L23.

    Таким образом, наша законченная формула выглядит так:

 

    =СУММЕСЛИ($B$2:$B$15;$L23;$I$2:$I$15)/СЧЁТЕСЛИ($B$2:$B$15;$L23)

 

    Протягиваем ее во всех ячейках и получаем таблицу:

таблица 4

    Строим график:

график к таблице 4

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

 

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

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

Таблица 5

   Строим по данной таблице график:

График к таблице 5

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

 

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

  

ОЦЕНИТЕ полезность статьи:
0/100
Нет комментариев. Ваш будет первым!
Загрузка...
Использование материалов данного сайта допускается только с указанием активной гиперссылки на источник с данного сайта.