Автоматизация и моделирование бизнес-процессов в Excel

Владимир Пикуза, vpikuza@voliacable.com

Оглавление

Следующая глава

Глава 12
Модель по управлению затратами Создание предпосылок для создания модели управления затратами

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

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

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

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

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

Эффективное управление затратами предполагает следующие преимущества предприятия и его продукции на рынке:

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

Первый элемент моделирования - желательно знать эффект, который может принести снижение затрат. Например, теоретически известно, что снижение затрат на 5,82% приносит такую же дополнительную абсолютную прибыль, как и увеличение объема продаж на 33%. Из этого следует, что снижение затрат на 1% равноценно увеличению объема продаж на 5,67%. Это верно при соотношении исходных величин затраты/прибыль равному 85/15. А если на предприятии другое соотношение величин затраты/прибыль, то каков будет эффект? Стоит ли снижать затраты или же попытаться увеличить объем реализации, если такая возможность есть у предприятия. Ведь увеличение объема продаж иногда может быть просто невозможно, например, в силу ограниченности спроса на данную продукцию или по каким-либо другим причинам.

Создание таблицы сравнения влияния сокращения уровня затрат и увеличения объема продаж на величину получаемой прибыли

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

На рис. 12.1. показана эта таблица с формулами расчета и она состоит из четырех областей:

для ввода исходных данных (ячейки С5:D7);
показывающая изменение суммы объема реализации, затрат и прибыли при изменении объема продаж на заданный коэффициент (ячейки С9:D12);
показывающая - на сколько процентов необходимо снизить затраты, чтобы получить такую же прибыль, как и при увеличении продаж, а также объем реализации, издержек и прибыли (ячейки С14:D17);
сравнения - показывает увеличение прибыли в абсолютном и процентном отношении при увеличении объема реализации или снижении затрат, а также сравнивающая абсолютные значения дополнительно получаемой прибыли при этих методиках.

Исходное соотношение затраты/прибыль принимаем равным 80/20, поэтому в области исходных данных в ячейки С5 и С6 введите числовые значения объема выручки (800) и затрат (200). Значение получаемой прибыли в ячейке С7 будет определяться как разность между значениями ячеек С5 и С6. В диапазон ячеек D5:D7 введите формулы расчета процентного соотношения затрат и прибыли в сумме выручки.

В области, показывающей увеличение объема реализации, затрат и прибыли при увеличении объема продаж, соотношение затраты/прибыль остается без изменений при увеличении объема продаж. Величина скорректированного объема продаж в ячейке С10 будет зависеть от величины объема продаж, указанной в области с исходными данными, умноженному на индекс, учитывающий введенный процент увеличения объема реализации в ячейку С9. При условии, что уровень затрат будет изменяться пропорционально уровню продаж, введите в ячейку С11 формулу умножения скорректированного объема продаж (ячейка С10) и процента уровня издержек, вычисленной в области исходных данных (ячейка D6). Таким образом, при изменении содержимого ячейки С9, будет изменяться и уровень затрат в ячейке С11. А величина прибыли в ячейке С12, по прежнему, будет определяться, как разность между значениями объема реализации и издержками.

В третьей области, показывающей влияние снижения издержек на уровень дополнительной прибыли:

содержимое значения ячейки С14, с введенным коэффициентом снижения издержек, будет влиять на результат в ячейке С16, показывающий абсолютную величину издержек. При этом значение величины издержек в ячейке С16 будет уменьшаться по сравнению с исходным значением на заданный в ячейке C14 коэффициент;
в ячейку С15, показывающей абсолютную величину выручки, введите ссылку на ячейку С5, с введенной исходной величиной выручки. То есть снижение издержек никак не влияет на величину выручки;
прибыль, вычисляемая в ячейке С17 по прежнему определяется как разность величины выручки и издержек. Следовательно, при уменьшении уровня издержек, значение дополнительной прибыли будет возрастать. Как следствие, в этой области при изменении значения издержек в диапазоне ячеек D16:D17, будут изменяться и пропорции затраты/прибыль, что нарушит соотношение затраты/прибыль 80/20.

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

Рис. 12.1. Таблица для расчета показателей сравнения сокращения затрат и увеличения объема продаж с позиции их влияния на величину прибыли с формулами

Если не вводить значения в ячейки С9 и С14, показывающие относительное увеличение объема продаж или уменьшение объема издержек (в процентах), то в ячейках С19 и С20 значения получаемой дополнительной прибыли будут равны нулю. Как следствие, будет равно нулю и значение ячейки С21, сравнивающей дополнительную прибыль, полученную при применении различных методов получения дополнительной суммы прибыли.

Анализ созданной таблицы

Работа с таблицей заключается во вводе в ячейку С9 процента увеличения объема продаж и последующему подбору процента уменьшения объема затрат в ячейке С14 с целью получения одинакового уровня получаемого абсолютного значения дополнительной прибыли и в том и в другом случае (ячейки С19 и С20). Как следствие значение в ячейке С21 должно быть равно нулю. Либо наоборот, подбор значения в ячейке С9 при вводе коэффициента в ячейку С14.

Проверьте алгоритм работы, введя в ячейку С14 (процент уменьшения уровня издержек) значение равное 5% (рис. 12.2.). Полученная дополнительная прибыль при уменьшении издержек на этот коэффициент (ячейка С20) будет равна 40 единицам.

Рис. 12.2. Таблица для расчета показателей сравнения сокращения затрат и увеличения объема продаж с позиции их влияния на величину прибыли с числовым примером, показывающим это влияние при уменьшении издержек на 5%

Для определения значения - на сколько процентов нужно увеличить объем продаж, чтобы получить такую же дополнительную прибыль, как и при сокращении издержек на 5%:

установите табличный курсор в ячейку С21 (Сравнение увеличения прибыли);
выполните команду Сервис/Подбор параметра и в диалоговом окне Подбор параметра (рис. 11.7.) в поле Значение введите значение 0, а в поле Изменяя значение ячейки укажите ссылку на ячейку С9;
нажмите на кнопку ОК и Excel подберет значение в ячейке С9 равное 20%.

Значит при уровне затрат в объеме выручки 80%, снижение затрат на 1% равно увеличению объема продаж на 4%. Полученные результаты отражены в таблице на рис. 12.3. и на диаграмме на рис. 12.4.

Рис. 12.3. Таблица с полученным результатом получаемой равной прибыли при различных методах ее увеличения

Рис. 12.4. Диаграмма показывающая распределение затрат и прибыли при различных подходах увеличения прибыли и исходных соотношениях прибыль/затраты 20/80

Если повторить решение задачи, но задать в исходных данных уровень затрат в объеме продаж равный 90%, то обнаружите, что снижение затрат на 1% равно увеличению объема продаж на 9%.

Следовательно, вывод - чем большую долю затраты имеют в объеме продаж, тем больше их рычаг влияния на увеличение прибыли по сравнению с необходимым увеличением объема продаж для получения такой же величины дополнительной прибыли. И наоборот рычаг в пользу увеличения объема продаж при уровне менее 50% объема затрат в сумме выручки.

Необходимость и задачи модели по исследованию влияния объема затрат/объема реализации на полученную дополнительную прибыль

Как следует из вышеописанного, "рычаги" влияния уровня затрат и уровня повышения объема реализации по разному влияют на уровень полученной прибыли в зависимости от рентабельности бизнеса. Но исследование этих зависимостей в созданной таблице утомительно - для получения результата требуется произвести довольно много действий. Поэтому на базе созданной таблицы создайте модель по управлению различными параметрами, позволяющую увидеть в совокупности их влияние на дополнительно полученную прибыль. Создаваемая модель позволит не только уяснить вышеописанные закономерности, но и получить навыки при создании динамических компьютерных моделей. Термин "динамическая" приведен не даром. Сейчас на практике для анализа большой популярностью пользуются OLAP-кубические модели, которые позволяют проанализировать информацию в самых различных разрезах. Создаваемая модель будет представлять из себя OLAP-куб в миниатюре - позволит увидеть зависимости: объем реализации - затраты - рентабельность в любом разрезе.

Создаваемая модель должна автоматически:

восстанавливать исходные формулы, приведенные выше (рис. 12.1.) для возможности ручного ввода данных и последующего подбора параметров;
изменять соотношение прибыль/затраты в этой таблице с исходными данными в пропорции от нуля до бесконечности;
при фиксации предполагаемого увеличения объема продаж, проводить моделирование - как это влияет на коэффициент уменьшения затрат для получения такой же суммы дополнительной прибыли при неизменном объеме реализации. Изменяя при этом соотношение прибыль/затраты можно исследовать зависимости: объем продаж - снижение издержек;
при фиксации предполагаемого коэффициента уменьшения затрат, проводить моделирование - как это влияет на увеличение объема продаж для получения такой же суммы прибыли, при увеличивающемся объеме реализации. Изменяя при этом соотношение прибыль/затраты можно исследовать зависимости: снижение издержек - объем продаж;
и так далее. То есть задача модели - всестороннее исследование процесса влияния уровня затраты/продажи и затраты/прибыль на получение уровня дополнительной прибыли.

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

Для управления создаваемой моделью внедрите четыре элемента управления (рис. 12.5.): одну кнопку и три полосы прокрутки.

Рис. 12.5. Таблица для расчета показателей сравнения сокращения затрат и увеличения объема продаж с позиции их влияния на величину прибыли с внедренными элементами управления

Восстановление исходного состояния

Для того чтобы всегда была возможность вернуть таблицу в исходное состояние с формулами и значениями, приведенными выше (рис. 12.1.), запишите макрос ИсходноеСостояние, производящий ввод этих формул и значений. Для записи макроса последовательно выделите каждую ячейку с созданными формулами и введенными значениями, нажимая каждый раз после выделении ячейки на клавиши F2 (режим редактирования) и Enter (подтверждение ввода данных в ячейку). Дополнительно запишите очистку содержимого ячейки F19. После записи макроса отредактируйте его код VBA (рис. 12.6.). Назначьте макросу кнопку Исходное состояние.

Рис. 12.6. Макрос восстановления исходных формул

Режим уменьшения объема затрат

При выборе элемента управления по изменению уровня затрат предварительно предъявите к нему следующие требования:

изменение содержимого ячейки С14 с шагом 1% и 10%;
начальное (минимальное) значение 0%;
максимальное значение 250%;
быстрое перемещение в начальное или конечное значение, а также в любую точку выбранного диапазона;
автоматический подбор параметра (значения) в ячейке С9, показывающий - на сколько процентов необходимо увеличить уровень объема продаж, чтобы достичь такого же уровня прибыли при любом исходном соотношении продажи/затраты от нуля до 100%;
формирование текста - в состоянии какого режима расчета находится созданная модель.

Наиболее подходящий элемент управления для выполнения поставленной задачи - Полоса прокрутки. Первые четыре пункта требований выполняемы при помощи диалогового окна Формат элемента управления, во вкладке Элемент управления которого задайте шаг изменения, минимальное и максимальное значение, а также в поле Помещать результат в ячейку введите адрес ячейки I15. Все остальное поручите макросу УменьшениеЗатрат (рис. 12.7.).

Перед записью макроса введите формулы в ячейки С14, С10 и С9:

=I15/100
=C5*(1+D20)
=(C10-C5)/C5

и в ячейку F19 введите текст Режим изменения уровня издержек.

Смысл введенных формул и текста будет раскрыт далее. После записи макроса, заключающемся во вводе формул в этих ячейках, отредактируйте код VBA, как это показано на рис. 12.7. и дополните подпрограмму инструкцией Select Case.

Описание последовательности выполнения действий подпрограммой УменьшениеЗатрат:

проверить содержимое ячейки F19 и если в нее введен текст Режим изменения объема продаж, то выполнить подпрограмму ИсходноеСостояние, которая обновит все формулы таблицы (приведет в исходное состояние) и очистит содержимое ячейки F19 с введенным текстом. После этого перейти к выполнению следующей строки кода VBA; если же в ячейке F19 не содержится ничего или введены другие данные, то перейти к выполнению следующей строки, игнорируя выполнение подпрограммы ИсходноеСостояние; ввести в ячейку F19 текст Режим изменения уровня издержек; в ячейку С14 (процент уменьшения затрат) ввести формулу деления содержимого ячейки I15 на 100. Значение ячейки I15 изменяется с помощью элемента управления Полоса прокрутки. Операция деления необходима, потому что элемент управления может иметь шаг изменения - только целое число и при изменении на значение 1, Excel воспринимает единицу как 100%; в ячейку С10 (увеличение объема продаж), вводится формула, производящая умножение значения объема продаж в исходном состоянии на индекс (1+D20) - увеличения получаемой прибыли, при уменьшении уровня издержек на значение в ячейке С14 (процент уменьшения затрат). За счет изменения объема продаж, соответственно изменятся в такое же количество раз значения и в ячейках С11 (затраты) и С12 (прибыль). Изменение объема прибыли при изменении объема продаж повлечет за собой изменение значения возвращаемого ячейкой С19 (абсолютное изменение прибыли по отношению к исходной ситуации) до уровня ячейки С20 и таким образом значения в ячейках С19 и С20 сравняются. Если же в обоих ячейках (С19 и С20) значения будут равны, то это говорит о правильном подборе параметров; формула в ячейке С9 сравнит значения увеличенного объема продаж (ячейка С10) и исходного (ячейка С5). После этого разделит результат этого сравнения на значение исходного объема продаж (ячейка С5), за счет чего будет получено значение коэффициента необходимого увеличения объема продаж для вычисления такого же значения полученной дополнительной прибыли (ячейка С19).

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

Рис. 12.7. Подпрограмма УменьшениеЗатрат, переключающая модель в режим изменения уровня издержек и подбора требуемого объема продаж для достижения того же результата

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

Режим увеличения объема продаж

К следующему элементу управления по изменению уровня объема продаж предъявлены похожие требования:

изменение содержимого ячейки С9 с шагом 1% и 10%;
начальное (минимальное) значение 0%;
максимальное значение 250%;
быстрое перемещение в начальное или конечное значение, а также в любую точку выбранного диапазона;
автоматический подбор параметра (значения) в ячейке С14, показывающий - на сколько процентов необходимо уменьшить уровень издержек, чтобы достичь такого же уровня прибыли при любом исходном соотношении продажи/затраты от нуля до 100%;
формирование текста - в состоянии какого режима расчета находится созданная модель.

Отличие элемента управления изменения уровня объема реализации в том, что он помещает результат в ячейку I10 и ему назначен макрос УвеличениеПродаж (рис. 12.8.).

Перед записью макроса введите формулы в ячейки С6, С9 и С14:

=I5
=I10/100
=(C6-C16)/C6

и в ячейку F19 введите текст Режим изменения объема продаж.

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

проверить содержимое ячейки F19 и если в нее введен текст Режим изменения уровня издержек, то выполнить подпрограмму ИсходноеСостояние, которая обновит все формулы таблицы (приведет в исходное состояние) и очистит содержимое ячейки F19 с введенным текстом. После этого переходит к выполнению следующей строки кода VBA;
если же в ячейке F19 не содержится ничего или введены другие данные, то перейти к выполнению следующей строки, игнорируя выполнение подпрограммы УвеличениеПродаж;
ввести в ячейку F19 текст Режим изменения уровня издержек;
в ячейку С6 ввести ссылку на ячейку I6 (заданный уровень затрат в выручке при исходном состоянии). Содержимое ячейки I6 изменяется при помощи следующего элемента управления и будет раскрыто далее.
в ячейку С9 (процент увеличения объема продаж) ввести формулу деления содержимого ячейки I10 на 100. Содержимое ячейки I10 изменяется с помощью элемента управления Полоса прокрутки;
в ячейку С14 (процент изменения уровня затрат) вводится формула, производящая вычитание значения затрат, полученное после изменения издержек (ячейка С16) из значения затрат в исходном состоянии (ячейка С6), после чего результат делится на сумму затрат исходного состояния (ячейка С6);
значению (Value) в ячейке С16 присваивается прежнее значение этой же ячейки, которое находилось в ней до выполнения подпрограммы за минусом значения, возвращаемого формулой в ячейке С21, которая сравнивает полученную дополнительную прибыль от изменения уровня объема продаж. Обратите внимание - в ячейку С16 вводится не формула, а значение. Это объясняется тем, что в ячейку нельзя вводить формулу, которая бы ссылалась на саму себя. И далее - на ячейку С16 присутствует ряд ссылок в формулах других ячеек, вводимых с помощью кнопки ИсходноеСостояние. Поэтому в конечном итоге значение ячейки С16 обновляет значение, возвращаемое формулой в ячейке С20, в результате чего результат вычисления в ячейке С21 равен нулю, что говорит о правильном подборе параметра..

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

Рис. 12.8. Подпрограмма УвеличениеПродаж, переключающая модель в режим увеличения объема продаж и подбора требуемого уменьшения объема затрат для достижения того же результата

Режим изменения пропорций затраты/прибыль в исходных данных

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

Преимущество подпрограммы УвеличениеПродаж перед УменьшениеЗатрат заключается в том, что при ее запуске она принимает во внимание не вводимые числовые значения соотношения затраты/прибыль, а установленное соотношение при помощи третьего элемента управления, регулирующего эти пропорции.

Созданные две подпрограммы имеют один общий недостаток - они требуют проведения перерасчета формул практически после каждой строки кода. И поэтому, если в Excel будет установлен ручной режим перерасчета формул, то модель не сможет произвести вычисления и подобрать требуемые параметры. Для перевода Excel в ручной режим вычислений выполните команду Сервис/Параметры и в открывшемся диалоговом окне Параметры (рис. 12.8.) на вкладке Вычисления активизируйте переключатель Вручную в области Вычисления.

Рис. 12.9. Диалоговое окно Параметры вкладка Вычисления

Методы управления вычислениями в Excel

В Excel существует три режима вычислений:

в автоматическом режиме - происходит автоматический перерасчет всех зависимых формул при проведении любых изменений в исходной;
в полуавтоматическом режиме - также вычисляются все формулы, кроме тех, которые находятся в табличных базах данных;
в ручном режиме вычислений - перерасчет формул происходит при наступлении одного из событий; нажатии на клавишу F9, нажатии на кнопку Вычислить на вкладке Вычисления (рис. 12.9.) или из подпрограммы VBA при вызове метода Calculate.

После перевода Excel в ручной режим вычислений созданные подпрограммы не выполнят возложенную на них миссию Такая ситуация может быть и на практике. Чтобы исключить ее, запишите макрос выполнения процедуры перевода Excel в автоматический режим перерасчета формул. Для этого, после начала записи макроса, выполните команду вызова диалогового окна Параметры и активизируйте переключатель Автоматически (рис. 12.9.). Макрос зафиксирует все необходимые параметры вкладки Вычисления. Отредактируйте код VBA для получения только одной строки (первая строка кода на рис. 12.10.), в которой:

Application - объект (сам Excel);
Calculation - свойство объекта Application, которое может принимать значения: xlAutomatic, xlSemiautomatic и xlManual, соответствующие перечисленным режимам вычислений

Полученную строку кода затем скопируете в следующий макрос.

Подпрограмма изменения пропорций затраты/прибыль в исходных данных

Подпрограмма ИзменитьПропорцииЗатратыПрибыль (рис. 12.10.) изменения уровня пропорций затраты/прибыль в исходных данных создается практически только методом копирования отдельных фрагментов текста кода VBA из прежде созданных в этой главе подпрограмм.

До перехода к конструктору Select Case, подпрограмма переводит Excel в режим автоматического вычисления и присваивает ячейке С6 значение ячейки I5 (уровень затрат в получаемой выручке), которой управляет третий элемент управления, изменяющий значения в этой ячейке в диапазоне от нуля до 1000 с шагом 10 или 100.

Если ранее был активизирован один из режимов, о чем говорит введенный в ячейку F19 текст, то подпрограмма на основании конструктора Select Case выбирает три режима:

Режим изменения объема продаж - который указывает на то, что перед переходом к режиму изменения исходных параметров затраты/прибыль, производилось манипулирование значением изменения объема продаж и последующим подбором параметра уменьшения издержек. В таком случае изменение исходного соотношения затраты/прибыль фиксирует уровень повышения уровня продаж и производит подбор параметра изменения объема издержек, для получения такого же уровня прибыли. Для этого после первого элемента Case Is следуют три строки кода VBA, скопированные из подпрограммы УвеличениеПродаж;

Режим изменения уровня издержек - который указывает на то, что перед переходом к режиму изменения исходных параметров затраты/прибыль, производилось манипулирование изменение значением объема издержек и последующим подбором параметра изменения объема продаж. В таком случае изменение исходного соотношения затраты/прибыль фиксирует уровень изменения объема затрат и производит подбор параметра изменения объема продаж, для получения такого же уровня прибыли. Для этого после второго элемента Case Is следуют три строки кода VBA, скопированные из подпрограммы УменьшениеЗатрат;

Без включения режима - изменяется соотношение исходных параметров затраты/прибыль без всякого подбора параметров.

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

Приближение созданной модели к пользовательскому интерфейсу

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

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

Защита приложения от неосторожных действий пользователя

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

Защита рабочего листа

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

Рис. 12.11. Диалоговое окно Защитить лист

Рис. 12. 12. Предупреждение о том, что невозможно изменение параметров листа

Следовательно, перед защитой листа необходимо указать ячейки, которые можно изменять. В нашем приложении это ячейки I5, I10, I15, F19 и диапазон С5:D21. Выделите эти ячейки и сняв защиту листа выполните команду Формат/Ячейки в диалоговом окне Формат ячеек перейдите на вкладку Защита, в которой отметьте опции Защищаемая ячейка и Скрыть формулы, как показано на рис. 12.13. и после этого защитите лист.

Рис. 12. 13. Диалоговое окно Формат ячеек вкладка Защита с отмеченными опциями

Дополнительная защита

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

Создание удобного интерфейса

Обратите внимание на внешний вид созданной модели на рис. 12.5. Около 30% пространства на экране монитора занимает ненужная информация. Это панели инструментов и меню, строка формул и строка состояния, полосы прокрутки, ярлык с именем листа и заголовки строк и столбцов. В этом приложении они не нужны, но из-за них уменьшен масштаб рабочего листа для того, чтобы вся рабочая область была видна на экране. Удалив их можно увеличить масштаб и изменить сам внешний вид приложения. Для автоматизации процесса удаления ненужных атрибутов и последующего их восстановления запишите два макроса СозданиеПриложения (рис. 12.14.) и ЗакрытьПриложение (рис. 12.15.).

Рис. 12.14. Подпрограмма СозданиеПриложения

Рис. 12.15. Подпрограмма ЗакрытьПриложения

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

выполните команды Вид/Панели инструментов/ Стандартная и Вид/Панели инструментов/ Форматирование;
выполните команды Вид/Строка формул и Вид/Строка состояния.

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

При выполнении макроса, ему каждый раз придется выполнять все операции, да и код VBA не совсем читаем. Этот недостаток устраняет инструкция With, которая выполняет последовательность инструкций над одиночным объектом или определяемым пользователем типом данных. Синтаксис инструкции:

With объект
[инструкции]
End With

Синтаксис инструкции With содержит следующие элементы:

объект - обязательный аргумент. Это имя объекта или определяемого пользователем типа.
инструкции - необязательный аргумент. Это одна или несколько инструкций, выполняемых над объектом.

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

Применение инструкция With можете наблюдать в отредактированной подпрограмме на рис. 12.14., которая дополнена строка кода VBA и еще одной инструкцией With:

Caption = "Модель влияния издержек и объема продаж на прибыль"

Эта строка устанавливает свойство объекта Windows, воздействующее на все приложение, коим и является Excel. Еще раз посмотрите на рис. 12.5.. В заголовке приложения Excel находится текстовая строка Microsoft Excel - Сравнение. Свойство Caption заменяет текст Microsoft Excel на текст Модель влияния издержек и объема продаж на прибыль.

Объект CommandBars позволяет отображать панели инструментов и команд с помощью свойства Visible, которому могут присваиваться значения True (панель видима) или False (невидима). Свойство DisplayStatusBar управляет отображением строки состояния, а свойство DisplayFormulaBar строки формул при присвоении им значений True или False.

Дополнительная инструкция With интересна с позиции замены строки меню на пустую строку (рис. 12.14.). Для этого используется метод Add. Синтаксис метода следующий

CommandBars.Add(name, position, menuBar, temporariy)

где,

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

выполните команду Сервис/Параметры и на вкладке Вид снимите галочки со всех опций в области Параметры окна (рис. 12.16.);
нажатием на кнопку восстановления размеров рабочего листа установите максимальный размер окна;
выберите масштаб, при котором рабочая область таблицы расположена на всем экране монитора.

В результате записи макроса свойствам DisplayGridlines, DisplayHeadings, DisplayOutline, DisplayZeros, DisplayHorizontalScrollBar, DisplayVerticalScrollBar и DisplayWorkbookTabs будет присвоено значение False. При необходимости подберите размер масштаба (Zoom) под ваш монитор.

Рис. 12.16. Диалоговое окно Параметры вкладка Вид

Окно рабочей книги WindowState может иметь размеры:

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

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

Последняя строка относится к свойствам объекта Windows, воздействующим на всю рабочую книгу. Еще раз посмотрите на рис. 12.5.. В заголовке приложения Excel находится текст Microsoft Excel - Сравнение. Вот именно свойство Caption и устанавливает отображение текста имени рабочей книги. При выполнении подпрограммы создания приложения этот текст удаляется.

Последняя строка кода подпрограммы СозданиеПриложения (рис. 12.14.) удаляет с помощью метода Protect меню управления рабочей книги, расположенное в левом верхнем углу окна в виде маленького значка Excel.

Внешний вид созданного приложения показан на рис. 12.17.

Рис. 12.17. Внешний вид созданного приложения

Для записи подпрограммы ЗакрытьПриложение (рис. 12.15.) можно скопировать подпрограмму, либо записать последовательность действий и произвести редактирование. Остановимся только на восстановлении свойства Caption. Для возвращения исходного текста Microsoft Excel ему присваивается значение Empty, а для текста имени рабочего листа значение ActiveWorkbook.Name.

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

Итоги

В жизни довольно часто встречаются вещи, которые воспринимаются практически дословно, без проверок их на достоверность. В настоящее время, в эпоху попыток повального увлечения управлением затратами, к таким вещам относится аксиома: прибыль, полученная за счет увеличения продаж на 33% равноценна уменьшению уровня издержек на 5%. Почему и как, как правило, задумывается мало кто. Поэтому эта глава посвящена материализации подтверждения этой теории в виде созданной простой таблицы, в которой для проверки этой аксиомы требовалось проделывать немало движений. Да и сама таблица, при ручном вводе и подборе параметров, не позволяла, получить полноценное представление о многомерности этой аксиомы, которую насквозь пронизывают влияющие на друг друга параметры: рентабельность, как таковая - увеличение объема продаж - уменьшение уровня издержек - полученная дополнительная прибыль.

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

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

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

Оглавление

Следующая глава

Версия для печати

Автоматизация и моделирование бизнес-процессов в Excel

Глава 12 Модель по управлению затратами Создание предпосылок для создания модели управления затратами

Глава 12
Модель по управлению затратами Создание предпосылок для создания модели управления затратами