Пример 2. Зависимость спроса от цены

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

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

Таблица 1.1

Зависимость спроса от цены

Для решения задачи средствами MS Excel, прежде всего, занесём данные в ячейки электронной таблицы и построим график зависимости (рис. 1.9). Для построения нужного графика в диалоговом окне нужно выбрать Тип: Точечная и Вид без соединительных линий.

Рис. 1.9. Построение графика зависимости спроса от цены товара

Если мы выделим построенный график, то в строке команд появится новое меню – Диаграмма. Выберем в этом меню команду Добавить линию тренда. В появившемся диалоговом окне на вкладке Тип выберем тип линии тренда Линейная, а на вкладке Параметры отметим опции Показывать уравнения на диаграмме и поместить на диаграмму величину достоверности аппрокси­ма­ции (R^2) (рис. 1.10) (ОК). В результате получим график, представленный на рис. 1.11. На нём проведена прямая, аппроксими­рующая табличные данные. Об­щий вид уравнения прямой:

y

Рис. 1.10. Заполнение диалогового окна ко­манды: Добавить линию тренда

Рис. 1.11. Линейная аппроксимация зависимости спроса от цены товара

где a и b – параметры, подбираемые таким образом, чтобы аппроксимация была наилучшей для данного вида функции. Критерием оценки точности аппроксимации является величина R². Чем ближе она к единице, тем лучше аппроксимация. Из графика видно, что для наилучшей линейной аппроксимации параметры a и b равны: 14.101 и 73.136 соответственно. Аналогичным образом можно провести аппроксимацию с использованием степенной и экспоненциальной функций (рис. 1.12). Из рисунков видно, что наилучшей является аппроксимация экспоненциальной функцией. Её общий вид:

y = a exp(b  x). (1.4)

Наилучшее согласие между аналитической зависимостью и наблю­даемыми значениями достигается при а=687.33 и b=-1.0511 (рис. 1.12).

Рис. 1.12. Аппроксимация зависимости спроса от цены товара степенной и экспоненциальной функциями

Теперь ответим на вопрос, при какой цене достигается максимальный доход. Для этого составим модель, представленную на рис. 1.13. Здесь число в ячейке В10 определяется по формуле

=Константаexp(ЦенаПоказатель), (1.5)

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

=СпросЦена. (1.6)

Содержимое ячейки В12 вычисляется как

=СпросСтоимость, (1.7)

и прибыль в ячейке В13 определяется формулой

=Доход-ПолнаяСтоимость. (1.8)

Рис. 1.13. Модель для расчёта оптимальной цены

Чтобы найти оптимальную цену, надо выполнить команду: Поиск решения меню Сервис и в появившемся диалоговом окне в графе Установить целевую ячейку записать адрес ячейки В13, установить Равной: максимальному значению и в графу Изменяя ячейки внести адрес ячейки В7 (рис. 1.14) (ОК). В результате расчета получим, что максимальная прибыль 21.44 тыс. р. достигается при цене 3.25 тыс. р. за установку одного окна. На этом задачу можно считать решённой. В заключение стоит лишь сказать несколько слов об ограничениях модели. Как правило, установка окон связана с их покупкой в той же компании, которая производит установку. Учёт этой взаимосвязи не должен привести к принципиальным изменениям модели. Изменится лишь значение цен и затрат. Далее, на зависимость спроса от цены должны налагаться сезонные колебания спроса и, возможно, другие факторы (действия конкурентов, инфляция и т.д.). Кроме того, на спрос может оказывать влияние цена установки окон в предшествующий период. Эти моменты, как и фактор случайности, не учтены в модели из соображений её наглядности.

Рис. 1.14. Заполнение диалогового окна команды: Поиск решения