Методика расчета плановых ключевых показателей в среде MS Excel

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

Этап 1. Содержательная постановка задачи.

Этап 2. Разработка причинно-следственных связей целей и представление их в форме дерева целей. Определение расчетных формул для прямых вычислений.

Этап 3. Выбор или вывод формул для обратных вычислений.

Этап 4. Разработка технологического процесса расчета ключевых плановых значений показателей в среде MS Excel.

Продемонстрируем выполнение перечисленных этапов на примере.

Этап 1. Содержательная постановка задачи, решаемой в условиях определенности.

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

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

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

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

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

На предприятии имеется бизнес-процесс, представленный выше на рис. 3.20, который также требует управления. Поименованный как «Изготовление изделия-1», он характеризуется целью «Снизить себестоимость продукции», уровень достижения которой оценивается показателем «Себестоимость продукции». Кроме этой цели преследуется также цель «Повысить качество товаров», оцениваемая показателем «Процент бракованной продукции» .

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

• • материальные и [ рудовые затраты;
• • прочие затраты.

Показатель «Процент бракованной продукции» рассчитывается на основе следующих показателей:

• • количество бракованных товаров;
• • количество проданных товаров.

Этап 2. Разработка причинно-следственных связей целей и представление их в форме дерева целей. Определение расчетных формул для прямых вычислений.

Дерево целей, согласно которому происходят расчеты, представлено на рис. 4.1. Показатели в узлах дерева снабжены указателями подцелей («плюс» — увеличение, «минус» — снижение), отражающими направления их изменения, а греческими буквами представлены коэффициенты приоритетности в достижении подцелей. Пунктирными стрелками указаны качественные показатели, приросты для которых не рассчитываются, а задаются пользователем. Такие показатели, используемые в качестве констант, обозначены словом const.

Рис. 4.1. Дерево целей «Повышение уровня рентабельности собственного капитала» .

Для решения задачи можно воспользоваться следующими расчетными формулами:

где Rчист — рентабельность собственного капитала; Пчист — чистая прибыль; Асоб — величина собственного капитала;

где - общая стоимость активов; Зк — величина заемного капитала;

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

где - выручка (нетто); - себестоимость продукции;

где - количество реализованных товаров; - продажная цена товаров;

где - затраты на материалы и заработную плату; прочие затраты;

где - количество клиентов; - количество товаров, приходящихся на одного клиента;

где - процент сотрудников, повысивших квалификацию; - количество сотрудников, повысивших квалификацию; - общее количество сотрудников;

где - процент бракованной продукции; - количество бракованной продукции.

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

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

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

Таблица 4.1

Исходные данные для выполнения прямых расчетов

Код показателя.	Наименование показателя.	Исходное значение показателя.	Источник исходных данных.
	Общая стоимость активов, тыс. руб.		Бухгалтерский баланс, с. 1600 актива.
	Величина заемного капитала, тыс. руб.	57,15.	Бухгалтерский баланс, с. 1400 + с. 1500 пассива.
	Процент налога на прибыль.		НК РФ, гл. 25 «Налог на прибыль» .
	Управленческие и коммерческие расходы, тыс. руб.	4,6.	Отчет о финансовых результатах, с. 2210 +с. 2220.
	Количество реализованных товаров, комплекты.		Карточки аналитического учета товаров (регистры аналитического (складского учета)).
	Продажная цена товаров, тыс. руб.		Товарный отчет, форма ТОРГ-29.
	Затраты на материалы и заработную плату, тыс. руб.		Обороты по счетам бухгалтерского учета 10 «Материалы» и 70 «Расчеты с персоналом по оплате труда» .
	Прочие затраты, тыс. руб.		Отчет о финансовых результатах, с. 2350.
	Количество товаров, приходящихся на одного клиента, комплекты.		Отчетность отдела маркетинга.
	Количество клиентов, организации.		База данных по учету реализации (условно-постоянная информация по контрагентам).
	Количество бракованной продукции, комплекты.		Акт, документ «Оприходование материалов и производства» .
	Общее количество сотрудников, чел.		Личные карточки работников, отчет по персоналу.
	Количество сотрудников, повысивших квалификацию, чел.		Отчет о повышении квалификации в анализируемом периоде.

1. Указать название таблицы в ячейках А1-Е1, для чего следует их объединить: с помощью указателя мыши выделить ячейки, нажать CTRL + 1, на закладке «Выравнивание» поставить флажок «объединение ячеек», ввести название таблицы, нажать Enter.

Рис. 4.2. Вид листа MS Excel с результатами расчета рентабельности собственного капитала.

2. Указать шапку таблицы. Ввести наименования показателей, их обозначения и исходные (фактические) значения показателей в ячейки А3-В3; B3-B23, C3-C23, а в графу D — результаты расчета по формулам, представленным в графе Е.

В результате будет получена рентабельность, равная 14% (см. рис. 4.2).

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

Этап 3. Выбор или вывод формул для обратных вычислений.

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

1. Расчет для уровня рентабельности (рис. 4.3).

Рис. 4.3. Уровень рентабельности.

Целевая установка будет следующей:

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

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

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

Решив полученное уравнение относительно х, получим.

Зная величину х, можно рассчитать новую чистую прибыль и новую величину собственного капитала:

2. Расчет для уровня чистой прибыли (рис. 4.4).

Рис. 4.4. Уровень чистой прибыли.

Целевая установка будет следующей:

Здесь процент налога на прибыль выражается коэффициентом (например, 0,18, а не 18%). Аналогично предыдущему для поиска приростов можно ввести коэффициент у:

Из этого следует уравнение.

Решив данное уравнение относительно у получим.

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

3. Расчет для уровня собственного капитала (рис. 4.5).

Рис. 4.5. Уровень собственного капитала.

Целевая установка будет следующей:

Аналогично предыдущему для поиска приростов аргументов можно ввести коэффициент V.

Это позволяет записать уравнение.

Решив его относительно t, получим.

4. Расчет для уровня валовой прибыли (рис. 4.6).

Рис. 4.6. Уровень валовой прибыли.

Целевая установка будет следующей:

Аналогично предыдущему для поиска приростов аргументов можно ввести коэффициент z:

Тогда составляем уравнение.

Решив данное уравнение относительно 2, получим.

5. Расчет для уровня выручки (рис. 4.7).

Рис. 4.7. Уровень выручки.

Целевая установка будет следующей:

Введем, как и ранее, коэффициент f. Тогда имеем.

Из целевой установки следует равенство.

что позволяет получить квадратное уравнение.

Решив его, получим.

6. Расчет для уровня себестоимости (рис. 4.8).

Рис. 4.8. Уровень себестоимости.

Целевая установка будет следующей:

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

Составив соответствующее уравнение и решив его, получим.

7. Расчет количества клиентов (рис. 4.9).

Рис. 4.9. Количество клиентов.

Расчет прироста количества клиентов производится по формуле.

где - количество клиентов и его прирост; - количество реализованных товаров и его прирост; - количество товаров, приходящихся на одного клиента (рассматривается в качестве константы).

Знаменатель в течение какого-то времени можно рассматривать в качестве константы, поэтому новое количество клиентов рассчитывается делением нового количества товаров на старое значение количества товаров, приходящихся на одного клиента.

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

8. Расчет количества бракованных товаров (рис. 4.10).

Рис. 4.10. Бракованные товары.

Расчет отрицательного прироста количества бракованных товаров рассчитывается следующим образом:

где - количество бракованных товаров и его отрицательный прирост; - процент бракованных товаров и заданный его отрицательный прирост; - количество реализованных товаров и его прирост.

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

9. Расчет процента сотрудников, повысивших квалификацию (рис. 4.11).

Рис. 4.11. Повышение квалификации сотрудников.

Расчет прироста процента сотрудников, повысивших квалификацию, осуществляется gо формуле.

где - процент сотрудников, повысивших квалификацию, и его прирост; - количество сотрудников, повысивших квалификацию, и его прирост; - общее количество сотрудников на предприятии.

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

Этан 4. Разработка технологического процесса расчета ключевых плановых показателей в среде MS Excel.

Для определения как ключевых (плановых) показателей, так и показателей-индикаторов создадим три таблицы: «Расчет плановых показателей для структурных подразделений» (рис. 4.12), «Таблица коэффициентов приоритетности» (рис. 4.13) и «Таблица рассчитываемых вспомогательных коэффициентов» (рис. 4.14). Создаются они теми же операциями, что и таблица, изображенная на рис. 4.2. Первая таблица «Расчет плановых показателей для структурных подразделений», имеет ряд отличий от таблицы на рис. 4.2. Во-первых, перечень показателей поменял свою последовательность расчетов (направление снизу вверх на сверху вниз); во-вторых, таблица расширилась за счет дополнительной графы «Желаемый/требуемый прирост» (рентабельности) и других показателей, являющихся на данном этапе функционирования предприятия константами (процент налога на прибыль, общее количество сотрудников и количество товаров на одного клиента).

Так как копирование первых двух граф из таблицы на рис. 4.2 в таблицу на рис. 4.12 невозможно из-за измененной последова;

Рис. 4.12. Вид листа MS Excel с основной таблицей «Расчет плановых показателей для структурных подразделений» .

Рис. 4.13. Вид листа MS Excel с таблицей «Таблица коэффициентов приоритетности» .

Рис. 4.14. Вид листа MS Excel с таблицей «Таблица рассчитываемых вспомогательных коэффициентов» .

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

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

Рассмотрим порядок записи формул, которые следует вводить сверху вниз, слева направо.

Начнем с рентабельности, которая вначале является исходной информацией (ячейка B3), а затем требуемым значением рентабельности (ячейка С3). Для того чтобы получить требуемую величину, к исходной рентабельности добавляется требуемый ее прирост (ячейка Е3). Присвоим им имена RB (рентабельность исходная) и RP (рентабельность требуемая) с помощью функции Создание имени. Устанавливаем курсор в ячейку В3 и нажимаем кнопку Формулы. В результате в разделе Определенные имена появится вкладка Присвоить имя. Нажимая на нее, получим окно Создание имени (рис. 4.15), в которое вносим сокращенное имя ячейки (RM) и полное имя в примечание (Рентабельность исходная). Нажимаем кнопку ОК.

Аналогично поступаем с оставшимися ячейками, в которых находятся переменные или исходные данные. В результате получим перечень всех имен, который можно проверить, нажав кнопку Диспетчер имен во вкладке Формулы (рис. 4.16).

Начнем ввод формулы, выведенной на предыдущем этапе и необходимой для расчета коэффициента х. Формула, вводимая в ячейку, имеет вид.

=((АИ*(RИ+dR)-100*ПЧИ)/(100*альфа1+альфа2*((RИ+dR)))),.

где АИ — величина собственного капитала исходная; RИ — рентабельность исходная; dR — прирост рентабельности требуемый; ПЧИ — прибыль чистая исходная, альфа1, альфа2 коэффициенты приоритетности в достижении целей «увеличить прибыль» и «снизить себестоимость собственных средств» (табл. 4.2).

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

Рис. 4.15. Закрепление имени за ячейкой (переменной).

Рис. 4.16. Перечень используемых имен для ввода формул.

Таблица 4.2

Таблица рассчитываемых вспомогательных коэффициентов.

Уровень расчета.	Обозначение коэффициента.	Формула для расчета.
Рентабельность собственного капитала.
Прибыль чистая.
Прибыль валовая.
Выручка нетто.
Себестоимость продукции.
Величина собственного капитала.

формуле RP — RИ (для чего ввести в ячейку =RP-RИ). Далее следует по формулам из предыдущего этапа в ячейки С4 и С8 ввести формулы для расчета повой чистой прибыли (в ячейке ) и новой величины собственного капитала АИ — (в ячейке ). Это позволит рассчитать приросты показателей и .

Оставшиеся формулы вводятся аналогично. Здесь следует отметить, что в больших по размеру формулах для правильного их ввода в MS Excel полезно предварительно их разметить, воспользоваться дугами, скрепляющими парные скобки. Например, формула для расчета коэффициента /следующая:

Для ее проверки воспользуемся дугами:

Дуги показали, что непарных скобок нет.

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

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

Рис. 4.17. Вид листа MS Excel с формулами, используемыми для расчетов плановых показателей.

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

• • в окне открытого листа перейдите к вкладке «Формулы» ;
• • в группе «Зависимости формул» укажите «Показать формулы» .