Главная страница Случайная страница
КАТЕГОРИИ:
АвтомобилиАстрономияБиологияГеографияДом и садДругие языкиДругоеИнформатикаИсторияКультураЛитератураЛогикаМатематикаМедицинаМеталлургияМеханикаОбразованиеОхрана трудаПедагогикаПолитикаПравоПсихологияРелигияРиторикаСоциологияСпортСтроительствоТехнологияТуризмФизикаФилософияФинансыХимияЧерчениеЭкологияЭкономикаЭлектроника
Пример 22.
|
|
Рассмотрим более сложный пример типичной модели сбыта, отражающей увеличение числа продаж от заданной величины (обусловленной, например, затратами на персонал) при увеличении затрат на рекламу и уменьшении прибыли. Поиск решения может помочь определить необходимость увеличения рекламного бюджета или его перераспределения с учетом сезонной поправки. Суть в том, что перераспределив затраты на рекламу (увеличив в начале периода и уменьшив в конце), можно увеличить прибыль предприятия при тех же затратах. В табл.6.35. приведена исходная модель сбыта.
Таблица 6.35
Исходная модель сбыта для примера 22
| A | B | C | D | E | F | |
| Месяц | 1 квартал | 2 квартал | 3 квартал | 4 квартал | Всего | |
| Сезонность | 0, 9 | 1, 1 | 0, 8 | 1, 2 | ||
| Число продаж | 3 592 | 4 390 | 3 192 | 4 789 | 15 962 | |
| Выручка от реализации | 143 662р. | 175 587р. | 127 700р. | 191 549р. | 638 498р. | |
| Затраты на сбыт | 89 789 | 109 742 | 79 812 | 119 718 | 399 061 | |
| Валовая прибыль | 53 873 | 65 845 | 47 887 | 71 831 | 239 437 | |
| Торговый персонал | 8 000 | 8 000 | 9 000 | 9 000 | 34 000 | |
| Реклама | 10 000 | 10 000 | 10 000 | 10 000 | 40 000 | |
| Косвенные затраты | 21 549 | 26 338 | 19 155 | 28 732 | 95 775 | |
| Суммарные затраты | 39 549 | 44 338 | 38 155 | 47 732 | 169 775 | |
| Произв. прибыль | 14 324р. | 21 507р. | 9 732р. | 24 099р. | 69 662р. | |
| Норма прибыли | 10% | 12% | 8% | 13% | 11% | |
| Цена изделия | 40р. | |||||
| Затраты на изделие | 25р. |
Пояснения к содержимому ячеек табл.6.35 приведены в табл.6.36.
Таблица 6.36
Пояснения к расчетам для примера 22
| Строка | Содержимое | Пояснение |
| Фиксированное знач. | Сезонная поправка: во 2 и 4 кварталах уровень продаж выше, чем в 1 и 3. | |
| =35*B3*(B11+3000)^0.5 | Ожидаемое число продаж по кварталам: в строке 3 - сезонная поправка; в строке 11 отражены затраты на рекламу. | |
| =B5*$B$18 | Выручка от реализации: произведение числа продаж(5 строка) на цену изделия (ячейка B18). | |
| =B5*$B$19 | Затраты на сбыт: произведение числа продаж (5 строка) и затрат на изделие (ячейка B19). | |
| =B6-B7 | Валовая прибыль: разность выручки от реализации (строка 6) и затрат на сбыт (строка 7). | |
| Фиксированное знач. | Расходы на торговый персонал. | |
| Фиксированное знач. | Средства на рекламу (около 6, 3% от продаж). | |
| =0.15*B6 | Косвенные затраты в фонд корпорации: 15% выручки от реализации (строка 6) | |
| =СУММ(B10: B12) | Суммарные расходы: затраты на персонал (10 строка), рекламу (11 строка) и косвенные затраты (12 строка). | |
| =B8-B13 | Производственная прибыль: валовая прибыль (8 строка) за вычетом суммарных затрат (13 строка). | |
| =B15/B6 | Норма прибыли: отношение прибыли (15 строка) и выручки от реализации (6 строка). | |
| Фиксированное знач. | Цена изделия | |
| Фиксированное знач. | Затраты на изделие |
Рассмотрим несколько заданий примера 22 для реализации проблем прогнозирования и поддержки принятия решения.
Задание 1. Нахождение значения, при котором заданная величина максимальна.
Пусть, например, требуется определить расходы на рекламу для получения наибольшей прибыли в первом квартале. Необходимо добиться наибольшей прибыли, изменяя затраты на рекламу. Для этого выполнить команду «Поиск решения». Задайте B15 в качестве результирующей ячейки (прибыль за первый квартал). Выберите поиск наибольшего значения и укажите в качестве изменяемой ячейки B11 (расходы на рекламу в первом квартале). Запустите процесс поиска решения. Сохраните вариант решения и восстановите исходные значения.
Надо иметь ввиду, что при выполнении команды «Сохранить» меню «Файл» последние заданные параметры задачи будут сохранены вместе с листом Excel. Однако для листа Excel может быть определено несколько задач (как в данном случае), если сохранять их по отдельности с помощью команды «Сохранить модель...» в диалоговом окне «Параметры» окна «Поиск решения». При этом каждая модель задачи определяется ячейками и ограничениями, заданными в этом диалоговом окне.
При сохранении модели предлагается выбрать интервал, включающий активную ячейку, используемую для сохранения модели. В интервал входят ячейки ограничений и три дополнительные ячейки. Убедитесь в том, что этот интервал на листе Excel не содержит данных.
Задание 2. Нахождение значения за счет изменения нескольких величин.
Имеется возможность поиска наибольшего или наименьшего значения для заданной величины, одновременно изменяя несколько других величин. Например, можно определить бюджет на рекламу в каждом квартале, соответствующий наибольшей годовой прибыли. Поскольку задаваемая в 3 строке сезонная поправка входит в расчет числа продаж (строка 5) в качестве сомножителя, целесообразно увеличить затраты на рекламу в 4 квартале, когда прибыль от продаж наибольшая и уменьшить, соответственно, в 3 квартале. «Поиск решения» позволит найти наилучшее распределение затрат на рекламу по кварталам.
Выполните команду «Сервис/Поиск решения…». Задайте F15 (общая прибыль за год) в качестве результирующей ячейки. Выберите поиск наибольшего значения и задайте в качестве изменяемых ячеек B11: E11 (расходы на рекламу в каждом квартале). Запустите процесс поиска решения. Сохраните результаты и восстановите исходные значения ячеек.
Рассмотренное задание является нелинейной задачей оптимизации средней степени сложности; то есть поиск значения уравнения с четырьмя неизвестными в ячейках с B11 по E11. Нелинейность уравнения связана с операцией возведения в степень в формуле строки 5.
Наиболее близкие к жизни модели учитывают ограничения, накладываемые на те или иные величины. Эти ограничения могут относиться к ячейкам результата, ячейкам изменяемых данных или другим величинам, используемых в формулах для этих ячеек.
Задание 3.Добавление ограничения.
Итак, бюджет покрывает расходы на рекламу и обеспечивает получение прибыли, однако наблюдается тенденция к уменьшению эффективности вложений. Поскольку нет гарантии, что данная модель зависимости прибыли от затрат на рекламу будет работать в следующем году (учитывая существенное увеличение затрат), целесообразно ввести ограничение расходов, связанных с рекламой.
Предположим, расходы на рекламу за четыре квартала не должны превышать 40000 р. Добавим в рассмотренную задачу соответствующее ограничение.
«Поиск решения» позволяет экспериментировать с различными параметрами задачи, для определения наилучшего варианта решения. Например, изменив ограничения, можно оценить изменение результата. На листе примера измените ограничения на рекламный бюджет с 40000 р. до 50000р. и посмотрите, как изменится при этом общая прибыль.
Сделайте выводы по всем заданиям примера 22 с точки зрения обеспечения общей прибыли различными приемами.