Информационные системы маркетинга лекция 7 09 04

Информационные системы маркетинга, лекция № 7, 09. 04. 2012 Примеры решения различных маркетинговых задач в Excel 1. Функции для расчета амортизационных отчислений 2. Решение оптимизационных задач

Информационные системы маркетинга, лекция № 7, 09. 04. 2012 Функции для расчета амортизационных отчислений Экономический смысл расчета амортизационных отчислений Компания покупает оборудование, предполагая в будущем получить доход от его использования. Амортизация позволяет сбалансировать затраты, которые имели место при покупке основного средства, с последующими доходами. Для определения амортизации актива необходимо знать: • первоначальную стоимость актива, • срок его эксплуатации, • остаточную стоимость актива. Величина амортизации определяется по одному из общепринятых методов начисления амортизации.

Информационные системы маркетинга, лекция № 7, 09. 04. 2012 В Excel имеется 7 функций для вычисления величины амортизации: • АПЛ (SLN в Calc) вычисляет величину амортизации по линейному методу; • АСЧ (SYD) использует метод «суммы (годовых) чисел» для вычисления суммы амортизации; • ДДОБ (DDB) и ПУО (VDB) используют метод ускоренного начисления амортизации; • ФУО (DB) вычисляет величину амортизации методом фиксированного уменьшения остатка; • АМОРУВ (AMORLINC) и АМОРУМ (AMORDEGRC) вычисляют величину амортизации по французской системе бухгалтерского учета.

Информационные системы маркетинга, лекция № 7, 09. 04. 2012 АПЛ возвращает величину амортизации актива за один период, рассчитанную линейным методом. Линейный метод еще называют методом равномерного начисления износа. Он заключается в том, что из суммы стоимости актива вычитается его остаточная стоимость, полученное значение делится на количество лет эксплуатации. Таким образом, величина амортизации на протяжении всего периода эксплуатации актива постоянна. Функция АПЛ имеет следующий синтаксис: АПЛ (нач_стоимость; ост_стоимость; время_эксплуатации), где: • нач_стоимость — начальная стоимость актива; • ост_стоимость — остаточная стоимость в конце амортизации (иногда называется ликвидной стоимостью имущества); • время_эксплуатации — срок полезной службы актива — количество периодов (как правило, лет), за которые собственность амортизируется (иногда называется периодом амортизации). В ранних версиях Excel АПЛ имела другое название — AMP

Информационные системы маркетинга, лекция № 7, 09. 04. 2012 Пример функции АПЛ в Excel

Информационные системы маркетинга, лекция № 7, 09. 04. 2012 Пример функции SLN в Calc

Информационные системы маркетинга, лекция № 7, 09. 04. 2012 Функция АСЧ (нач_стоимость; ост_стоимость; срок_эксплуатации; период) возвращает величину амортизации актива за данный период, рассчитанную методом «суммы (годовых) чисел» . В этом методе суммируются порядковые номера лет, в течение которых актив находится в эксплуатации. Допустим, период эксплуатации актива составляет 5 лет, тогда сумма всех лет эксплуатации актива равна 15 (или 1+2+3+4+5). Затем разница между первоначальной и остаточной стоимостью актива делится на это число и умножается на количество оставшихся лет периода эксплуатации. Например, в первый год это будет число 5, во второй - 4 и так далее.

Информационные системы маркетинга, лекция № 7, 09. 04. 2012 Аргументы функции АСЧ: нач_стоимость — начальная стоимость имущества; ост_стоимость — остаточная стоимость в конце периода амортизации; срок_эксплуатации — количество периодов, за которые собственность амортизируется (иногда называется периодом амортизации); период — период, для которого рассчитывается величина амортизации; должен быть измерен в тех же единицах, что и предыдущий аргумент. В более ранних версиях Excel АСЧ имела другое название — АМГД. В Calc для этих целей определена функция SYD(Стоимость; Ликв_Стоим; Время_эксплуатации; Период).

Информационные системы маркетинга, лекция № 7, 09. 04. 2012 Пример функции АСЧ в Excel

Информационные системы маркетинга, лекция № 7, 09. 04. 2012 Пример функции SYD в Calc

Информационные системы маркетинга, лекция № 7, 09. 04. 2012 Функция ДДОБ возвращает величину амортизации актива за данный период, рассчитанную по методу двойного уменьшаемого остатка. При начислении амортизации по этому методу коэффициент, который использовался для определения амортизации по линейному методу, удваивается, а затем умножается на первоначальную стоимость. После этого из полученного произведения вычитается сумма всех предыдущих значений амортизации. Для тех же целей в Calc определена функция DDB(Стоимость; Ликв_Стоим; Время_эксплуатации; Период; [Коэффициент]). В справочной системе расчетный метод назван поиному — метод «суммы чисел» .

Информационные системы маркетинга, лекция № 7, 09. 04. 2012 Синтаксис функции ДДОБ (нач_стоимость; ост_стоимость; время_эксплуатации; период; [коэффициент]). Все 5 аргументов должны быть положительны: нач_стоимость — начальная стоимость имущества; ост_стоимость — остаточная стоимость в конце периода амортизации; время_эксплуатации — количество периодов, за которые собственность амортизируется; период — период, для которого требуется вычислить амортизацию; должен быть измерен в тех же единицах, что и время_эксплуатации; коэффициент — это норма снижения балансовой стоимости (амортизации); если коэффициент опущен, то предполагается, что он равен 2 (метод двукратного учета амортизации, при котором амортизация максимальна в первый период и затем уменьшается в последующие).

Информационные системы маркетинга, лекция № 7, 09. 04. 2012 Пример функции ДДОБ в Excel

Информационные системы маркетинга, лекция № 7, 09. 04. 2012 Пример функции DDB в Calc

Информационные системы маркетинга, лекция № 7, 09. 04. 2012 Функция ПУО возвращает величину амортизации актива за данный период, используя метод двойного процента со снижающегося остатка. Синтаксис имеет следующий вид: ПУО (нач_стоимость; ост_стоимость; время_эксплуатации; нач_период; кон_период; [коэффициент]; [без_переключения]), где аргументы: • нач_стоимость — начальная стоимость имущества; • ост_стоимость — остаточная стоимость в конце срока полезного использования; • время_эксплуатации —полный срок службы актива; • нач_период — начальный период, для которого вычисляется амортизация (должен быть задан в тех же единицах, что и срок_эксплуатации); • кон_период — конечный период, для которого вычисляется амортизация (должен быть задан в тех же единицах, что и срок_эксплуатации), так, для первого года эксплуатации актива аргумент нач_период = 0, а аргумент кон_период = 1; • коэффициент — это процентная ставка снижающегося остатка; • без_переключения — логическое значение, определяющее, следует ли использовать прямую амортизацию, когда амортизация превышает вычисленную величину амортизации.

Информационные системы маркетинга, лекция № 7, 09. 04. 2012 Пример функции ПУО в Excel

Информационные системы маркетинга, лекция № 7, 09. 04. 2012 Пример функции VDB в Calc

Информационные системы маркетинга, лекция № 7, 09. 04. 2012 Функция ФУО (нач_стоимость; ост_стоимость; время_эксплуатации; период; месяц) возвращает величину амортизации актива для заданного периода, рассчитанную методом фиксированного уменьшения остатка или методом уменьшающегося баланса. Амортизация по данному методу начисляется каждый год на основе фиксированной процентной ставки по формуле: ставка = 1 – ((остаточная_стоимость/ первоначальная_стоимость)^(1/время _эксплуатации)) При вычислении амортизации по методу фиксированного уменьшения остатка используется значение суммарной амортизации за предшествующие периоды. Амортизация за текущий период вычисляется как разность между первоначальной стоимостью актива и суммарной амортизацией за предшествующие периоды, умноженная на фиксированную процентную ставку.

Информационные системы маркетинга, лекция № 7, 09. 04. 2012 Аргументы функции ФУО: нач_стоимость — начальная стоимость имущества; ост_стоимость — остаточная стоимость имущества в конце периода амортизации; время_эксплуатации — количество периодов, за которые собственность амортизируется, т. е. период амортизации; период — период, для которого требуется вычислить амортизацию, должен быть измерен в тех же единицах, что и время_эксплуатации; месяц — число месяцев в первом году. Аргумент месяц необязателен, и его следует задавать только в том случае, когда покупка актива была совершена не в начале года, а позже. По умолчанию значение этого аргумента принимается равным 12. В более ранних версиях Excel ФУО имела другое название — ДОБ. В Calc подобный метод расчета реализует функция DB(Стоимость; Ликв_Стоим; Время_эксплуатации; Период; [Месяц]). В справочной системе некорректно указано, что возвращается «снижение стоимости актива для определенного периода по методу двукратного снижения балансовой стоимости» .

Информационные системы маркетинга, лекция № 7, 09. 04. 2012 Пример функции ФУО в Excel

Информационные системы маркетинга, лекция № 7, 09. 04. 2012 Пример функции ФУО в Excel

Информационные системы маркетинга, лекция № 7, 09. 04. 2012 Пример функции DB в Calc

Информационные системы маркетинга, лекция № 7, 09. 04. 2012 Использование ФУО для вычисления амортизации актива (неполный первый год)

Информационные системы маркетинга, лекция № 7, 09. 04. 2012 Функция АМОРУВ вычисляет величину амортизации актива для заданного периода по французской системе бухгалтерского учета. Если имущество приобретается в середине бухгалтерского периода, то учитывается пропорционально распределенная амортизация. Синтаксис имеет следующий вид: АМОРУВ (стоимость; дата_приобр; первый_период; остаточная_стоимость; период; ставка; [базис]), где аргументы: стоимость — стоимость имущества; дата_приобретения — дата приобретения имущества; первый_период — дата окончания первого период; остаточная_стоимость — остаточная стоимость имущества в конце периода; период — период; ставка — процентная ставка амортизации; базис — используемый способ вычисления дат.

Информационные системы маркетинга, лекция № 7, 09. 04. 2012 Если аргумент базис равен: 0 или отсутствует, то задействован американский метод вычисления года (NASD), подразумевающий 12 месяцев по 30 дней в каждом; 1, то используется точное число дней в месяцах и точное число дней в году; 2, то используется точное число дней в месяцах, а число дней в году принимается за 360; 3, то предпосылки аналогичны предыдущему, но число дней считается равным 365; 4, то используется европейский метод с 12 месяцев по 30 дней в каждом. Соответствующая функция в Calc — AMORLINC(Стоимость; Дата приобретения; Первый период; Остаточная стоимость; Период; Ставка; [Базис])

Информационные системы маркетинга, лекция № 7, 09. 04. 2012 Пример использования функции АМОРУВ в Excel

Информационные системы маркетинга, лекция № 7, 09. 04. 2012 Пример использования функции AMORLINC в Calc

Информационные системы маркетинга, лекция № 7, 09. 04. 2012 Функция АМОРУМ возвращает величину амортизации актива для заданного периода, однако, в отличие от АМОРУВ, используемый в вычислениях коэффициент амортизации изменяется в зависимости от периода амортизации. Синтаксис АМОРУМ выглядит так: АМОРУМ (стоимость; дата_приобр; первый_период; остаточная_стоимость; период; ставка; [базис]), где аргументы функции: • стоимость — затраты на приобретение имущества; • дата_приобретения — дата приобретения имущества; • первый_период — дата окончания первого периода; • остаточная_стоимость — остаточная стоимость имущества в конце периода амортизации; • период — период; • ставка — процентная ставка амортизации; • базис — используемый способ вычисления дат. Парная функция в Calc именуется AMORDEGRC (Стоимость; Дата приобретения; Первый период; Остаточная стоимость; Период; Ставка; [Базис])

Информационные системы маркетинга, лекция № 7, 09. 04. 2012 Пример функции АМОРУМ в Excel

Информационные системы маркетинга, лекция № 7, 09. 04. 2012 Пример функции AMORDEGRC в Calc

Информационные системы маркетинга, лекция № 7, 09. 04. 2012 Решение оптимизационных задач Достаточно часто в практике приходится сталкиваться с оптимизационными задачами при ограничениях на какие-либо ресурсы. Примерами таких задач служат задачи • оптимизации ассортимента продукции, • задачи составления графиков занятости сотрудников организации, транспортные задачи, • задачи о назначениях (например, выбора продавцов продукции по сегментам рынка). Для решения данной группы задач может быть использована надстройка «Поиск решения» , которая доступна из меню Сервис. Если эта надстройка не была инсталлирована, то ее установка происходит после выполнения последовательности команд Сервис+ Надстройки+ «Поиск решения» + ОК.

Информационные системы маркетинга, лекция № 7, 09. 04. 2012 Задачи, которые рационально решать с помощью надстройки «Поиск решения» , имеют следующие свойства: • имеется единственная максимизируемая или минимизируемая цель (минимизация затрат на доставку, максимизация доходов за счет оптимизации средств на разные инвестиционные цели и т. п. ); • имеются ограничения, выражающиеся, как правило, в виде неравенств (например, объем используемого сырья не должен превышать объем имеющегося сырья на складе); • имеется набор входных значений-переменных, прямо или косвенно влияющих на ограничения и на оптимизируемые величины.

Информационные системы маркетинга, лекция № 7, 09. 04. 2012 Пример 1. При разработке годового финансового плана деятельности фирмы необходимо определить расходы на рекламу для получения наибольшей прибыли. Цена изделия составляет 40 ден. ед. , себестоимость 25 ден. ед. Коэффициент сезонного изменения объема продаж составляет для 1– 4 кварталов 0. 9, 1. 1, 0. 8 и 1. 2 соответственно. Затраты на торговый персонал составят 8000 ден. ед. Целью задачи является планирование оптимальных объемов затрат на рекламу для увеличения объема продаж и получения наибольшей прибыли.

Информационные системы маркетинга, лекция № 7, 09. 04. 2012 Плановая таблица с итоговыми значениями по 1 -му кварталу

Информационные системы маркетинга, лекция № 7, 09. 04. 2012 Увеличение затрат на рекламу будет постоянно увеличивать объем продаж. Но изменение прибыли, вероятно, будет иметь максимум, поскольку рост рекламных издержек будет сокращать прибыль. Исследуем эту зависимость, растиражировав необходимым образом формулы в таблице.

Информационные системы маркетинга, лекция № 7, 09. 04. 2012 Проанализировав влияние факторов на отклик (критериальные показатели), перейдем в надстройку «Поиск решения» .

Информационные системы маркетинга, лекция № 7, 09. 04. 2012 Результаты поиска решения в Excel

Информационные системы маркетинга, лекция № 7, 09. 04. 2012 В соответствии с найденным решением, затратив 17 093 ден. ед. на рекламу в 1 -ом квартале, можно получить наибольшую прибыль, которая составит 15 093 ден. ед.

Информационные системы маркетинга, лекция № 7, 09. 04. 2012 Таблица с поквартальными и годовыми значениями

Информационные системы маркетинга, лекция № 7, 09. 04. 2012 Поиск решения с варьированием нескольких ячеек в Excel

Информационные системы маркетинга, лекция № 7, 09. 04. 2012 Параметры поиска решения в Excel

Информационные системы маркетинга, лекция № 7, 09. 04. 2012 Поставленная задача относится к нелинейным задачам оптимизации средней степени сложности. Нелинейность уравнения связана с операцией возведения в степень в формуле строки «Число продаж» . Для того чтобы корректно учесть этот факт, в окне «Параметры поиска решения» следует выбрать нелинейную задачу и метод поиска Ньютона или градиентный. Инструмент «Поиск решения» реализован и в Calc, но работа с его текущей версией связана с многочисленными трудностями и не может быть рекомендована для решения нелинейных задач оптимизации.

Информационные системы маркетинга, лекция № 7, 09. 04. 2012 Таблица с оптимальным бюджетом

Информационные системы маркетинга, лекция № 7, 09. 04. 2012 Предположим, что расходы на рекламу за год не должны превышать 40000 ден. ед. и добавим в рассмотренную задачу соответствующее ограничение.

Информационные системы маркетинга, лекция № 7, 09. 04. 2012 Оптимизация прибыли при ограничении на рекламный бюджет Оптимизация прибыли при повышении рекламного бюджета на 10 000

Информационные системы маркетинга, лекция № 7, 09. 04. 2012 Пример 2. Оптимальные смеси компонентов. Некоторый технологический процесс требует использования сырья с определенным содержанием примесей: примеси Ф не более 0. 03%, примеси П не более 3. 25%. Состав сырья нормируется сортами А, В, С, однако ни один из сортов не соответствует требованиям по содержанию обеих примесей одновременно. Следовательно, нужно закупать все три сорта в определенной пропорции и смешивать их для обеспечения допустимых долей примесей. Требуется найти эти пропорции, в которых следует закупать сырье разных сортов. Каждый сорт характеризуется не только долями примесей, но и стоимостью, поэтому требуется минимизировать общую стоимость приобретаемого сырья (целевая функция).

Информационные системы маркетинга, лекция № 7, 09. 04. 2012 Исходные данные

Информационные системы маркетинга, лекция № 7, 09. 04. 2012

Информационные системы маркетинга, лекция № 7, 09. 04. 2012

Информационные системы маркетинга, лекция № 7, 09. 04. 2012 Пример 3. Необходимо определить оптимальный объем перевозок товаров с 3 производственных площадок на 5 региональных складов с учетом ряда ограничений. Товары могут доставляться с любого производства на любой склад, однако, очевидно, что стоимость доставки на большее расстояние будет большей (хотя и необязательно пропорционально расстоянию). Цель решения задачи — уменьшение совокупных транспортных расходов.

Информационные системы маркетинга, лекция № 7, 09. 04. 2012 Оформление шаблона для примера 3

Информационные системы маркетинга, лекция № 7, 09. 04. 2012 Ячейки В 15 C 3: G 5 Показатель Общая стоимость перевозок Объемы перевозок от каждого из производств к каждому складу Суммарный объем перевозок C 7: G 7 на каждый из складов Потребности складов C 9: G 9 В 11: В 13 Объемы производства каждым из заводов C 11: G 13 Стоимости перевозок C 15: G 15 Суммарные стоимости перевозок по каждому складу B 15 Суммарные логистические издержки Формула и комментарий =СУММ(C 15: G 15) изменяемые ячейки С 7 =СУММ(С 3: С 5) и далее аналогично исходные данные Стоимость перевозки товарной единицы в пределах логистического звена С 15 =СУММПРОИЗВ(C 3: C 5; C 11: C 13) и далее аналогично =СУММ(C 15: G 15) Структура рабочего листа для примера 3

Информационные системы маркетинга, лекция № 7, 09. 04. 2012 Ограничение Примечания В 3: В 5<=В 11: В 13 Количества перевезенных грузов не могут превышать производственных возможностей C 7: G 7>=C 9: G 9 Количество доставляемых грузов не должно быть меньше потребностей складов, т. е. общее производство должно быть не меньше интегральной потребности C 3: G 5>=0 n Объем перевозок не может быть отрицательным Ограничения на перемещения товаров в примере 3

Информационные системы маркетинга, лекция № 7, 09. 04. 2012 Ограничения для примера 3

Информационные системы маркетинга, лекция № 7, 09. 04. 2012 Решение для примера 3

Информационные системы маркетинга, лекция № 7, 09. 04. 2012 Пример 4. Построение графика занятости. Предприятие торговли обслуживается семью группами сотрудников (группы обозначены А, Б. . . Ж). Признак разделения на группы — разные выходные дни. Выходных дней для каждой группы — не менее двух, выходные дни должны следовать подряд. Каждый сотрудник входит только в одну группу. На основе статистических данных о среднем количестве посетителей в зависимости от дня недели известна потребность в сотрудниках в каждый из дней. Существенно, что эта потребность различна. Все сотрудники имеют одинаковый размер понедельной оплаты, не зависящий от графика работы. Необходимо подобрать такую численность сотрудников в каждой группе, чтобы добиться минимизации суммарных затрат на оплату труда при выполнении требований по числу сотрудников на каждый день.

Информационные системы маркетинга, лекция № 7, 09. 04. 2012 Оформление шаблона для примера 4

Информационные системы маркетинга, лекция № 7, 09. 04. 2012 Ячейки C 15 Показатель Общая недельная зарплата C 14 Дневная зарплата 1 работника Выходные дни Число сотрудников в каждой из групп График работы групп B 4: B 10 C 4: C 10 D 4: J 10 D 11: J 11 D 12: J 12 С 12 Количество сотрудников, работающих в каждый из дней Требуемое количество сотрудников в каждый из дней недели Общее число сотрудников Формула и примечание Минимизируемая величина (=C 14*СУММ(D 11: J 11)) Параметр Перечисление аббревиатур выходных дней Изменяемые данные Кодирование выходных дней (1 — группа работает, 0 — день отдыха). Используются формулы вида =ЕСЛИ( ЕОШИБКА( НАЙТИ(D$3; $B 4)); 1; 0). =СУММПРОИЗВ($C$4: $C$10; D 4: D 10) Исходные данные — значения на базе накопленного опыта работы =СУММ(С 4: С 10) Описание шаблона для примера 4

Информационные системы маркетинга, лекция № 7, 09. 04. 2012 Целевой функцией является общая недельная зарплата (С 15). Ограничения на перемещения товаров в примере 4 Ограничение Примечания C 4: C 10>=0 Число сотрудников в группе неотрицательно, однако может быть равным нулю — это означает, что можно организовать меньшее количество групп C 4: C 10=Целое Число сотрудников должно быть целым D 11: J 11>=D 12: J 12 Число занятых сотрудников не должно быть меньше ежедневной потребности

Информационные системы маркетинга, лекция № 7, 09. 04. 2012 Ограничения для примера 4

Информационные системы маркетинга, лекция № 7, 09. 04. 2012 Решение для примера 33