На этом шаге мы рассмотрим некоторые приемы, используемые для создания этой модели.
Для управления создаваемой моделью внедрите четыре элемента управления (рисунок 1): одну кнопку и три полосы прокрутки.
Рис.1. Таблица для расчета показателей сравнения сокращения затрат и увеличения объема продаж с позиции их влияния на величину прибыли с внедренными элементами управления
Восстановление исходного состояния
Для того чтобы всегда была возможность вернуть таблицу в исходное состояние с формулами и значениями, приведенными на рисунке 2, запишите макрос ИсходноеСостояние, производящий ввод этих формул и значений.
Рис.2. Таблица для расчета показателей сравнения сокращения затрат и увеличения объема продаж с позиции их влияния на величину прибыли с формулами
Для записи макроса последовательно выделите каждую ячейку с созданными формулами и введенными значениями, нажимая каждый раз после выделении ячейки на клавиши F2 (режим редактирования) и Enter (подтверждение ввода данных в ячейку). Дополнительно запишите очистку содержимого ячейки F19. После записи макроса отредактируйте его код VBA (рисунок 3).
Рис.3. Макрос восстановления исходных формул
Назначьте макросу кнопку Исходное состояние.
Режим уменьшения объема затрат
При выборе элемента управления по изменению уровня затрат предварительно предъявите к нему следующие требования:
- изменение содержимого ячейки С14 с шагом 1% и 10%;
- начальное (минимальное) значение 0%;
- максимальное значение 250%;
- быстрое перемещение в начальное или конечное значение, а также в любую точку выбранного диапазона;
- автоматический подбор параметра (значения) в ячейке С9, показывающий на сколько процентов необходимо увеличить уровень объема продаж, чтобы достичь такого же уровня прибыли при любом исходном соотношении продажи/затраты от нуля до 100%;
- формирование текста - в состоянии какого режима расчета находится созданная модель.
Наиболее подходящий элемент управления для выполнения поставленной задачи - Полоса прокрутки. Первые четыре пункта требований выполняемы при помощи диалогового окна Формат элемента управления, во вкладке Элемент управления которого задайте шаг изменения, минимальное и максимальное значение, а также в поле Помещать результат в ячейку введите адрес ячейки I15. Все остальное поручите макросу УменьшениеЗатрат (рис. 12.7.).
Рис.4. Подпрограмма УменьшениеЗатрат, переключающая модель в режим изменения уровня издержек и подбора требуемого объема продаж для достижения того же результата
Перед записью макроса введите формулы в ячейки С14, С10 и С9:
=I15/100 =C5*(1+D20) =(C10-C5)/C5
Смысл введенных формул и текста будет раскрыт далее. После записи макроса, заключающемся во вводе формул в этих ячейках, отредактируйте код VBA, как это показано на рисунке 4, и дополните подпрограмму инструкцией 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 будет видно значение уровня, на который необходимо увеличить объем продаж, чтобы достичь такого же увеличения прибыли.
Как видите с помощью небольшой подпрограммы и элемента управления достигнут эффект, позволяющий избежать ввода данных с клавиатуры и не прибегать к процедуре подбора параметра с помощью диалогового окна Подбор параметра.
На следующем шаге мы закончим изучение этого вопроса.
