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

⇐ ПредыдущаяСтр 2 из 3Следующая ⇒

Для управления создаваемой моделью внедрите четыре элемента управления (рис. 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. Подпрограмма ИзменитьПропорцииЗатратыПрибыль, переключающая модель в режим изменения параметра затраты/прибыль в исходных данных и поддерживающий при этом автоматический подбор параметров как при изменении объема продаж, так и при изменении уровня затрат, в зависимости от выбранного режима

Дата добавления: 2018-06-01; просмотров: 203; Мы поможем в написании вашей работы!

Поделиться с друзьями:

⇐ Предыдущая 123 Следующая ⇒

Мы поможем в написании ваших работ!