Решение экономических задач оптимизационными методами

метки: Задача, Таблица, Данные, Решение, Анализ, Основа, Расчет, Информация

Министерство образования и науки Российской Федерации

Федеральное агентство по образованию Ярославский государственный университет им. П.Г. Демидова

Кафедра мировой экономики и статистики

Рекомендовано

Научно-методическим советом университета

для студентов специальностей Бухгалтерский учет,

анализ и аудит и Мировая экономика

Ярославль 2006 УДК 311 ББК У.В611я73-4 З 58

Рекомендовано

Редакционно-издательским советом университета

в качестве учебного издания. План 2006 года

Рецензент

кафедра мировой экономики и статистики Ярославского

государственного университета им. П.Г. Демидова.

Зеткина, О.В. Решение экономических задач оптиЗ 58 мизационными методами: метод. указания/ О.В. Зеткина;

Яросл. гос. ун-т. – Ярославль: ЯрГУ, 2006. – 67 с.

Методические указания предназначены для проведения

практических занятий. Они написаны для оказания помощи

в решении наиболее распространенных задач, возникающих

в бухгалтерской и экономической деятельности. Для фор мирования практических навыков у студентов по обработке

экономической информации к задачам прилагаются файлы,

содержащие решение в электронном виде.

Методические указания рекомендуются для студентов,

обучающихся по специальностям 060500 – «Бухгалтерский

учет, анализ и аудит», 060600 – «Мировая экономика»

(дисциплина «Статистическая обработка учетно-аналити ческой информации», блок ОПД), очная форма обучения.

УДК 311

ББК У.В611я73-4

© Ярославский государственный университет

им. П.Г. Демидова, 2006

© О.В. Зеткина, 2006

Оглавление

Введение…………………………………………………………………………………… 5 Тема 1. Решение экономических задач с помощью функций….. 7

1.1. Обработка экономической информации с помощью

функций …………………………………………………………………. 7

1.2. Структуризация и консолидация данных……………….. 13

Задачи для самостоятельного решения по теме 1 ……….. 15 Тема 2. Работа со списками и формами для составления

Задача: Решение задач онлайн по макроэкономике

... Список использованной литературы: 1. Агапова Т.А. Макроэкономика: Учебник / Т.А. Агапова, С.Ф. Серегина; ... и студенты-заочники входят в состав рабочей силы: 13 ∙ 100% Уровень безработицы = 285 = 4,6% Задача № ... вызванное повышением мировой ставки процента. Решение: Повышением мировой ставки процента непосредственно ... Компания Германии экспортировала автомобили в США по цене $10000. Валютный курс составлял ...

аналитических отчетов ……………………………………………… 17

2.1. Использование диапазонов для анализа

статистической информации ………………………………. 17

2.2. Применение фильтров для оперативного получения

экономических данных ………………………………………….. 20

2.3. Автоматизация обработки экономической

информации с помощью сводных таблиц ………………. 20

Задачи для самостоятельного решения по теме 2 ……….. 27 Тема 3. Оптимизация экономической деятельности …………….. 27

3.1. Обработка экономических данных на основе

проведения XYZ- и АВС-анализа ……………………………. 27

3.2. Операционно-стоимостной анализ процесса …………. 31

Задачи для самостоятельного решения по теме 3 ……….. 31 Тема 4. Автоматизация бухгалтерских расчетов…………………… 34

4.1. Ввод и поиск информации с помощью функций Excel 34

4.2. Расчеты по единому социальному налогу (ЕСН) …….. 37

4.3. Определение количественного и качественного

состава сотрудников …………………………………………… 42

4.4. Расчет премии сотрудников на основе

соответствующих коэффициентов ……………………… 43

Задачи для самостоятельного решения по теме 4 ……….. 45 Тема 5. Оптимизационные задачи в экономике ……………………. 45

5.1. Определение задачи оптимизации …………………………. 45

5.2. Разработка компьютерной модели для решения

задач оптимизации ………………………………………………. 52 Тема 6. Некоторые приемы решения задач оптимизации …….. 54

6.1. Задачи оптимизации без ограничений ……………………. 54

6.2. Решение задач оптимизации со многими

неизвестными ………………………………………………………. 56

6.3. Примеры типовых задач оптимизации ………………….. 60

Задачи для самостоятельного решения по темам 5 – 6 .. 63 Литература …………………………………………………………………………….. 66

Введение

Математические расчеты занимают существенную долю времени и сил любого бухгалтера и экономиста, поэтому их работа тесно связана с использованием современных информационных технологий. Сегодня от студентов требуется значительная подготовка в области практического применения статистических и математических методов для принятия практических решений в прогнозировании деятельности предприятия, банковском деле, бизнесе. Современная действительность требует от экономистов владения соответствующими основными компьютерными программами. Если профессиональная программа автоматизации бухгалтерских расчетов помогает выстроить итоговый результаты работы в виде бухгалтерского баланса, то офисная программа MS Excel окажется не менее эффективным помощником в решении ряда других, часто необходимых на практике математических задач и расчетов, таких как подбор параметров и обратный пересчет данных. Например, требуется разделить общую сумму заработка или премии, заданные определенным значением числа, между работниками с учетом их трудового вклада. Или распределить общезаводские затраты по подразделениям, службам и объектам. Подобные задачи можно решать оптимизационными методами, используя специальные инструменты табличного процессора MS Excel, в котором существует ряд специализированных функций и процедур, включающих операции подбора параметров, поиска решений и консолидации данных. Программа электронных таблиц Excel, содержащая встроенные средства для решения задач оптимизации является далеко не универсальной в силу их большого разнообразия. Однако существуют типовые классы задач оптимизации, которые могут быть успешно решены с помощью Excel. Повышение эффективности работы экономиста может происходить за счет определенных методов, применение которых возможно благодаря инструментам Excel. Методические указания созданы с целью обеспечения методической поддержки практических занятий, проводимых преподавателями кафедры мировой экономики и статистики экономического факультета ЯрГУ им. П.Г. Демидова. Пособие может оказать практическую помощь в решении наиболее распространенных задач по дисциплине «Статистическая обработка учетно-аналитической информации» для студентов всех форм обучения. Методические указания включают разделы, содержащие следующие направления обработки статистических данных: • применение математических и логических функций при анализе различных направлений финансово-хозяйственной деятельности предприятия; • использование фильтров для оперативного получения первичных и обобщенных сведений; • графическое представление необходимой информации; • обобщение первичной информации по различным направлениям исследования; • решение конкретных задач оптимизации с помощью инструментов Подбор параметра и Поиск решения. Каждый раздел содержит теоретические основы по теме, постановку и решение наиболее актуальных экономических задач с помощью информационных технологий. Для выработки практических навыков по обработке экономической информации к рассмотренным задачам прилагаются файлы, содержащие решение в электронном виде.

Анализ экономических задач оптимизации

... Оптимизация Сервис Поиск решения Рис. 2.3 Диалоговое окно надстройки Поиск решения Рис. 2.4 Решение задачи ... максимальную выручку.Исходя из условий задачи целевая функция принимает вид: Табличная модель. Рис. ... Задача №2 Оптимальная организация рекламной компании Постановка задачи.На рекламу выделено 80000 руб. Предприятие рекламирует свою деятельность, используя четыре источника массовой информации: ...

Тема 1. Решение экономических задач

с помощью функций

1.1. Обработка экономической информации

с помощью функций При работе с функциями требуется выполнить 2 действия: 1) вызвать нужную функцию 2) задать ее аргументы Функции представлены в библиотеке и для их выбора существует 2 пути вызова Мастера функций. Первый способ вызова функции: 1) установить курсор на ячейку, в которой должен оказаться результат выполнения функции; 2) одновременно нажать клавиши Shift+F3 – в ячейке появится знак «=», а в верхней части экрана откроется меню Мастера функций Второй способ: 1) установить курсор на нужную ячейку, в меню задать Вставка – Функция; 2) откроется экран Мастера функций, в котором осуществляется выбор нужной функции. Различают 2 вида структуры функции: с аргументом и без аргумента. Структура функции с аргументом: ИМЯ_ФУНКЦИИ(аргумент1, аргумент2, …) Если функция не имеет аргументов, то при ее вызове все равно должны быть использованы круглые скобки. Например, функция ТДАТА, возвращающая значение текущей даты и времени. Для вызова этой функции в ячейке, в которой должна появиться текущая дата, следует записать =ТДАТА().

Организация вычислений в среде MS Excel. Формулы и функции

... (амперсант) служит для объединения последовательностей символов в одну последовательность. Результатом выполнения выражения “Результата” & “ расчета” будет “результаты расчета”. Функции MS Excel, Мастером функций В общем виде функцию можно представить как имя ( арг1, ...

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

К числу некоторых полезных в работе экономиста функций можно отнести следующие:

1. ЕСЛИ(условие; значение если условие истинно, значение если условие ложно) – используется проверки выполнения условия;

2. СУММЕСЛИ(диапазон; критерий; диапазон суммирования) – используется для суммирования ячеек по какому-либо условию;

3. СЧЕТЕСЛИ(диапазон; критерий) – подсчитывает количество непустых ячеек в указанном диапазоне;

4. СРЗНАЧ(диапазон) – вычисляет среднее значение в указанных ячейках;

5. МАКС(диапазон), МИН(диапазон) – находит максимальное или минимальное значение в указанном диапазоне ячеек;

6. БДСУММ(база данных; поле; критерий) – задает посредством критерия подмножество строк диапазона ячеек для сложения:

• база данных – диапазон ячеек, составляющих базу; первая строка диапазона содержит заголовки каждого столбца;

• поле – столбец, значения которого должна просуммировать функция;

• критерий – указывает на диапазон ячеек, которые должна обработать функция; первая строка диапазона критериев должна содержать один или несколько заголовков столбцов.

Примечание. Так как вычисления выполнены в MS Excel, при решении задачи сохраняется наименование строк и столбцов, традиционное для данного табличного процессора.

Задача 1. Оптимизация покупок (файл задача1.xls).

Цена единицы товара находится в следующей зависимости от величины покупаемой партии: за 500 и менее единиц товара платят по $3; за 501 – 2 000 единиц платят $2,7; за 1 201 до 1 200 единиц платят $2,3. Выразить приведенную зависимость с помощью формулы. Исходные данные для решения задачи представим в таблице 1.

Таблица 1

Исходные данные

А В С D

Граница объема

1 покупки, ед. Цена

2 диапазон 1 500 $ 3,00 цена 1

3 диапазон 2 1 200 $ 2,70 цена 2

4 диапазон 3 2 000 $ 2,30 цена 3

5 > 2 000 $ 2,00 цена 4

Формулы расчета стоимости 2 способами приведены в столбцах С и D таблицы 2. Для вычислений используются адреса ячеек таблицы 1.

Интернет-магазин по продаже спортивных товаров

... продажи в предстоящем периоде и создание заявок на закупки у поставщиков. Когда возникают спорные вопросы по поводу товаров, работы менеджеров, интернет-магазина ... формы. В исследовательской части проведена оптимизация разработанной базы данных. Организационно-экономический раздел содержит ... Техническое задание Нормативные ссылки В дипломном проектировании использованы следующие стандарты: 1. ...

Таблица 2

Расчет стоимости покупки

А В С D

заказан- формула

стои- формула расчета стоимости на 8 ное коли- расчета

мость основе логической функции

чество стоимости

=ЕСЛИ(A9<=B2;C2*A9;ЕСЛИ(A9<=B3

;C3*A9;ЕСЛИ(A9<=B3;C3*A9;C5*A9)) 9 450 $1 350,00 =A9*C2 )

=ЕСЛИ(A10<=B3;C3*A10;ЕСЛИ(A10<

=B4;C4*A10;ЕСЛИ(A10<=B4;C4*A10; 10 900 $2 430,00 =A10*C3 C6*A10)))

=ЕСЛИ(A11<=B4;C4*A11;ЕСЛИ(A11<

=B5;C5*A11;ЕСЛИ(A11<=B5;C5*A11; 11 1 450 $3 335,00 =A11*C4 C7*A11)))

=ЕСЛИ(A12>B5;C5*A12;ЕСЛИ(A12<=

B6;C6*A12;ЕСЛИ(A12<=B6;C6*A12;C8 12 2 100 $4 200,00 =A12*C5 *A12)))

Задача 2. Планирование продаж (файл задача2.xls).

Исходные данные приводятся в таблице 3 (файл задача2.xls Лист «Данные»): • продажи закупок за месяц; • остатки на начало и конец месяца; • число рабочих дней за период анализа продаж, в течение которых товар имелся в наличии или продавался.

Таблица 3

Исходные данные о продажах фирмы «Весна»

за период с 1.02.06 по 28.02.06

A B C D E F 2 Период анализа продаж, Период закупки,

дней 20 дней 21 3 Остаток

Остаток Дней Заку4 Продано, на

на нача- про- плено,

шт. конец, 5 ло, шт. дажи шт.

Номенклатура шт. 6 200/12 Печенье с абрикосом 1 000 750 15 100 350 7 200/12 Печенье с клубникой 350 450 15 200 100 8 200/12 Печенье с клюквой 600 550 15 300 350 9 200/12 Печенье с лимоном 500 700 15 400 200 10 300/8 Пряники с абрикосом 1 900 2 100 20 500 300 11 300/8 Пряники с апельсином 1 500 1 400 20 600 700 12 300/8 Пряники с брусникой 3 000 2 800 20 700 900 13 300/8 Пряники с вишней 1 700 1 300 20 800 1 200 14 300/8 Пряники с клубникой 3 000 4 000 20 1 200 200 15 350/8 Пряники с клюквой 200 500 15 400 100 16 350/8 Пряники с лимоном 300 350 10 500 450 17 350/8 Пряники с малиной 80 100 7 0 0 18 200/8 Ушки с сахаром 350 350 5 0 0 19 200/8 Ушки с корицей 1 100 1 050 20 300 350 20 200/8 Ушки с маком 1 000 950 20 400 450 21 250/10 Ушки с корицей 900 830 20 500 570 22 250/10 Ушки с маком 1 070 1 100 20 600 570 23 4 кг Ушки с сахаром 1 250 1 200 20 500 550 24 4 кг Ушки с арахисом 200 370 10 0 0 25 4 кг Ушки с корицей 350 200 10 0 0 26 4 кг Ушки с маком 200 380 10 0 0 27 350 г/12 Торт Снежана 950 1 100 20 500 350 28 350 г/12 Торт Фея 500 560 15 0 0 29 350 г/12 Торт Ярославна 100 200 8 0 0

На основе приведенных данных требуется найти (файл задача2.xls Лист «Оптимизация продаж»): 1. Средние продажи за 1 день – скорость продажи за день, получаемая как отношение общего объема отгрузки за период анализа продаж к числу дней продажи. 2. Необходимый объем закупки – количество товара, которое следует приобретать для продаж в течение периода планирования закупок. Рассчитывается как Планируемый объем продаж – Остаток на конец.

3. Планируемый объем продаж – количество товара, которое планируется продать в указанном периоде закупки. Предполагается, что в течение этого периода товар будет продаваться по рабочим дням со скоростью, равной средней скорости продаж за период анализа продаж и на момент начала продаж имеется остаток, указанный в колонке Остаток за месяц.

Прикладные задачи по математике (с экономическим содержанием)

... денег школам-интернатам города и области, если в городе 6 интернатов, а в области – 8 школ-интернатов? Задача . Школа получила 10000 руб. ... Сколько хлеба получится из 27 кг муки? При решении задачи на выпечку хлеба многие дети не знали, что ... получили задание – измерить рост своих близких и записать соответствующие данные. На уроках чертим отрезки заданной длины. Повторяем понятия “горизонтальные ...

4. Резерв продаж за период анализа продаж – количество товара, которое дополнительно можно было бы продать при его постоянном наличии на складе. Этот показатель позволяет оценить потери, вызванные отсутствием на складе пользующегося спросом товара.

В таблице 4 приведен фрагмент вычислений на основе исходных данных таблицы 3.

Таблица 4

Вычисление экономических показателей

G H I J

Средняя Планируе Резерв продаж за 5 продажа в Необходимый объем закупки мый объем

февраль

день продаж 6 =C6/B$2 =ЕСЛИ(I6-F6>0;I6-F6;0) =G6*E$2 =(C6*B$2)/D6-C6 7 =C7/B$2 =ЕСЛИ(I7-F7>0;I7-F7;0) =G7*E$2 =(C7*B$2)/D7-C7 8 =C8/B$2 =ЕСЛИ(I8-F8>0;I8-F8;0) =G8*E$2 =(C8*B$2)/D8-C8 9 =C9/B$2 =ЕСЛИ(I9-F9>0;I9-F9;0) =G9*E$2 =(C9*B$2)/D9-C9 10 =C10/B$2 =ЕСЛИ(I10-F10>0;I10-F10;0) =G10*E$2 =(C10*B$2)/D10-C10 11 =C11/B$2 =ЕСЛИ(I11-F11>0;I11-F11;0) =G11*E$2 =(C11*B$2)/D11-C11 12 =C12/B$2 =ЕСЛИ(I12-F12>0;I12-F12;0) =G12*E$2 =(C12*B$2)/D12-C12 13 =C13/B$2 =ЕСЛИ(I13-F13>0;I13-F13;0) =G13*E$2 =(C13*B$2)/D13-C13 14 =C14/B$2 =ЕСЛИ(I14-F14>0;I14-F14;0) =G14*E$2 =(C14*B$2)/D14-C14 15 =C15/B$2 =ЕСЛИ(I15-F15>0;I15-F15;0) =G15*E$2 =(C15*B$2)/D15-C15 16 =C16/B$2 =ЕСЛИ(I16-F16>0;I16-F16;0) =G16*E$2 =(C16*B$2)/D16-C16 17 =C17/B$2 =ЕСЛИ(I17-F17>0;I17-F17;0) =G17*E$2 =(C17*B$2)/D17-C17 18 =C18/B$2 =ЕСЛИ(I18-F18>0;I18-F18;0) =G18*E$2 =(C18*B$2)/D18-C18 19 =C19/B$2 =ЕСЛИ(I19-F19>0;I19-F19;0) =G19*E$2 =(C19*B$2)/D19-C19 20 =C20/B$2 =ЕСЛИ(I20-F20>0;I20-F20;0) =G20*E$2 =(C20*B$2)/D20-C20 21 =C21/B$2 =ЕСЛИ(I21-F21>0;I21-F21;0) =G21*E$2 =(C21*B$2)/D21-C21 22 =C22/B$2 =ЕСЛИ(I22-F22>0;I22-F22;0) =G22*E$2 =(C22*B$2)/D22-C22 23 =C23/B$2 =ЕСЛИ(I23-F23>0;I23-F23;0) =G23*E$2 =(C23*B$2)/D23-C23 24 =C24/B$2 =ЕСЛИ(I24-F24>0;I24-F24;0) =G24*E$2 =(C24*B$2)/D24-C24 25 =C25/B$2 =ЕСЛИ(I25-F25>0;I25-F25;0) =G25*E$2 =(C25*B$2)/D25-C25 26 =C26/B$2 =ЕСЛИ(I26-F26>0;I26-F26;0) =G26*E$2 =(C26*B$2)/D26-C26 27 =C27/B$2 =ЕСЛИ(I27-F27>0;I27-F27;0) =G27*E$2 =(C27*B$2)/D27-C27 28 =C28/B$2 =ЕСЛИ(I28-F28>0;I28-F28;0) =G28*E$2 =(C28*B$2)/D28-C28 29 =C29/B$2 =ЕСЛИ(I29-F29>0;I29-F29;0) =G29*E$2 =(C29*B$2)/D29-C29

Таблица 5

Анализ продаж фирмы «Весна»

за период с 1.02.06 по 28.02.06, шт.

A G H I J

Плани Средняя Необходи- Резерв

руемый 5 Номенклатура продажа мый объем продаж за

объем

в день закупки февраль

продаж 6 200/12 Печенье с абрикосом 37,5 438 788 250 7 200/12 Печенье с клубникой 22,5 373 473 150 8 200/12 Печенье с клюквой 27,5 228 578 183 9 200/12 Печенье с лимоном 35 535 735 233 10 300/8 Пряники с абрикосом 105 1905 2 205 0 11 300/8 Пряники с апельсином 70 770 1 470 0 12 300/8 Пряники с брусникой 140 2040 2 940 0 13 300/8 Пряники с вишней 65 165 1 365 0 14 300/8 Пряники с клубникой 200 4000 4 200 0 15 350/8 Пряники с клюквой 25 425 525 167 16 350/8 Пряники с лимоном 17,5 0 368 350 17 350/8 Пряники с малиной 5 105 105 186 18 200/8 Ушки с сахаром 17,5 368 368 1 050 19 200/8 Ушки с корицей 52,5 753 1 103 0 20 200/8 Ушки с маком 47,5 548 998 0 21 250/10 Ушки с корицей 41,5 302 872 0 22 250/10 Ушки с маком 55 585 1 155 0 23 4 кг Ушки с сахаром 60 710 1 260 0 24 4 кг Ушки с арахисом 18,5 389 389 370 25 4 кг Ушки с корицей 10 210 210 200 26 4 кг Ушки с маком 19 399 399 380 27 350 г/12 Торт Снежана 55 805 1 155 0 28 350 г/12 Торт Фея 28 588 588 187 29 350 г/12 Торт Ярославна 10 210 210 300

Использование информатики для решения экономических задач

... 4 Решить задачу линейного программирования. Отчет долженсодержать следующие разделы: Условие задачи Формализация задачи Графическое решение задачи Распечатку решения задачи с помощью пакета Microsoft Excel Экономический вывод 1. ... периода). Результаты решения задачи представлены в таблице 1. Динамикароста стоимости выплат показана на рисунке 1. Таблица 2 содержит расчетныеформулы к решению задачи в ...

Нередко на практике экономист осуществляет анализ продаж с целью выявления так называемых «узких» мест финансовохозяйственной деятельности организации. Для этой цели применяются функции СУММЕСЛИ(диапазон; критерий; диапазон суммирования) и СЧЕТЕСЛИ(диапазон; критерий); СРЗНАЧ(диапазон); МАКС(диапазон), МИН(диапазон).

На основе представленных выше данных по организации можно провести анализ торговой деятельности (файл задача2.xls Лист «Расчет резервов»).

Таблица 6

Сводный анализ продаж фирмы «Весна»

(адреса ячеек по таблице 3) Итоги по продуктам Функции для вычислений Всего, шт. убыточных товаров (<300) =СЧЕТЕСЛИ($C$6:$C$29;»<300″) 3 без остатков на конец =СЧЕТЕСЛИ($F$6:$F$29;»=0″) 7 товар дополнительно закупался =СЧЕТЕСЛИ($E$6:$E$29;»>0″) 17 максимально продано =МАКС($C$6:$C$29) 4 000 среднемесячная продажа =СРЗНАЧ($C$6:$C$29) 970

Анализ проводится на основе исходных данных таблицы 3. Убыточными условно считаем те продукты, которых продано менее чем 300 шт.

1.2. Структуризация и консолидация данных Для повышения наглядности и читаемости экономической информации, представленной в виде таблиц, имеется средство, называемое структурой таблицы. Структура позволяет быстро найти в громоздких таблицах интересующие нас данные. Структурирование таблицы – это изменение степени детализации данных при помощи создания структуры. Благодаря структуре часть данных можно скрыть, но в любой момент представляется возможным вывести их на экран. Для автоматического создания структуры требуется составить какой-либо список. При структурировании будут использоваться названия полей и записей списка. Чтобы создать структуру надо выбрать команду меню Данные – Группа и структура – Создание структуры. Для создания структуры вручную можно выбрать базу данных, команду меню Данные – Группа и структура – Группировать (разгруппировать).

Кнопки уровня детализации предназначены для изменения степени подробности при выводе списка на экран. Если структура построена по столбцам, то, нажав кнопку уровня детализации 1 для столбцов, мы свернем структуру по столбцам. Чтобы развернуть структуру, нужно нажать кнопку уровня детализации 2. Сложные структуры могут иметь много уровней детализации. Нажав «-», мы свернем группу, «+» – развернем. Консолидацией называется объединение из нескольких диапазонов данных. Например, необходимо просуммировать данные из одной ячейки на разных листах книги. Применяют следующий способ: объединяют ячейки с помощью формул, используя в формулах ссылки на ячейки. При этом указывается имя листа и адрес ячейки через знак «!».

Задача 3. Группировка и структуризация данных (файл задача3.xls Лист «Структура»).

Задачи на издержки производства с решением по экономике

... 914 000 = 16% . Таким образом, мы составили структуру затрат в таблице. Задачи на издержки производства с решением по экономике образовательные – систематизировать теоретические знания по теме: “Центральные и вписанные углы” ... |EP|=|31% / -20%| = 1.55 – спрос эластичности. Начертите график спроса на бутерброды при данных ценах и количествах продаж. Определите, при какой цене торговая выручка окажется ...

Выполните группировку продукции по 4 наименованиям: «Печенье», «Пряники», «Ушки», «Торт». Рассчитайте итоговые суммы по каждой номенклатуре.

Таблица 7

Список товаров фирмы «Весна»

за период с 1.02.06 по 28.02.06

A B C D E F

Остаток Про- Дней Закуп- Остаток 5 Номенклатура на нача- дано, про- лено, на конец,

ло, шт. шт. дажи шт. шт 6 Печенье 2 450 2 450 60 1 000 1 000 7 200/12 Печенье с абрикосом 1 000 750 15 100 350 8 200/12 Печенье с клубникой 350 450 15 200 100 9 200/12 Печенье с клюквой 600 550 15 300 350 10 200/12 Печенье с лимоном 500 700 15 400 200 11 Пряники 11 680 12 550 132 4 700 3 850 12 Мелкая фасовка 13 300/8 Пряники с абрикосом 1 900 2 100 20 500 300 14 300/8 Пряники с апельсином 1 500 1 400 20 600 700 15 300/8 Пряники с брусникой 3 000 2 800 20 700 900 16 300/8 Пряники с вишней 1 700 1 300 20 800 1 200 17 300/8 Пряники с клубникой 3 000 4 000 20 1 200 200 18 Крупная фасовка 19 350/8 Пряники с клюквой 200 500 15 400 100 20 350/8 Пряники с лимоном 300 350 10 500 450 21 350/8 Пряники с малиной 80 100 7 0 0 22 Ушки 6 420 6 430 135 2 300 2 490 23 Мелкая фасовка 24 200/8 Ушки с сахаром 350 350 5 0 0 25 200/8 Ушки с корицей 1 100 1 050 20 300 350 26 200/8 Ушки с маком 1 000 950 20 400 450 27 Средняя фасовка 28 250/10 Ушки с корицей 900 830 20 500 570 29 250/10 Ушки с маком 1 070 1 100 20 600 570 30 Крупная фасовка 31 4 кг Ушки с сахаром 1 250 1 200 20 500 550 32 4 кг Ушки с арахисом 200 370 10 0 0 33 4 кг Ушки с корицей 350 200 10 0 0 34 4 кг Ушки с маком 200 380 10 0 0 35 Торт 1 550 1 860 43 500 350 36 350 г/12 Торт Снежана 950 1 100 20 500 350 37 350 г/12 Торт Фея 500 560 15 0 0 38 350 г/12 Торт Ярославна 100 200 8 0 0

Таблица 8

Структуризация номенклатуры товаров фирмы «Весна»

за период с 1.02.06 по 28.02.06

A B C D E F

Остаток Остаток

Прода- Дней Закупле5 Номенклатура на нача- на ко но, шт. продажи но, шт.

ло, шт. нец, шт. 6 Печенье 2 450 2 450 60 1 000 1 000 11 Пряники 11 680 12 550 132 4 700 3 850 12 Мелкая фасовка 18 Крупная фасов ка 22 Ушки 6 420 6 430 135 2 300 2 490 23 Мелкая фасовка 27 Средняя фасовка 30 Крупная фасовка 35 Торт 1 550 1 860 43 500 350

Задачи для самостоятельного решения

по теме 1 Задача 4 (файл задача4.xls).

Пропускная способность рейса авиакомпании 250 человек. На рейс продано 270 билетов по $300. Стоимость билетов не возмещается. Переменные издержки (в основном это затраты на питание и топливо) на транспортировку одного пассажира составляют $30. Если на рейс зарегистрировалось больше 250 человек, это означает, что билетов продано больше, чем имеется посадочных мест, и авиакомпания обязана выплатить компенсацию в размере $350 каждому пассажиру, которому не хватило места. Вычислите прибыль авиакомпании на основании числа клиентов, зарегистрировавшихся на рейс.

Задача 5 (файл задача5.xls).

Крупная фармацевтическая компания пытается определить правильную производственную мощность предприятия для нового лекарства. Единицу годовой мощности можно создать с затратами в $10. Каждая единица лекарства продается по $12, включая переменные издержки $2. Лекарство будет продаваться в течение 10 лет. Вычислить прибыль компании за 10 лет при заданном годовом уровне производственной мощности и ежегодном спросе на лекарство. Предполагается одинаковый спрос на лекарство в каждом году.

Задача: Задачи к экзамену по эконометрике в Excel

... форму модели. Рассчитать смоделированные уровни вклада и расходов на бытовые нужды. Задача №3 В таблице представлены данные о цене однокомнатных квартир на вторичном рынке жилья в областном ... на вопрос: целесообразно ли включение в модель фактора Z? При решении использовать средства Excel. Задача №4 Имеются: данные об объемах продаж (в тыс. руб.), совершенных 12 менеджерами ...

Задача 6 (файл задача6.xls).

Аналитики фармацевтической компании полагают, что в 2007 году будет продано 10 000 единиц нового лекарства. Они ожидают появления двух конкурентов на рынке. В год появления первого конкурента компания потеряет, как предполагается , 30% занятой доли рынка, в год появления второго – 15% доли рынка. Размер рынка увеличивается на 10% в год. Учитывая появление конкурентов, разработайте электронную таблицу, вычисляющую ежегодные продажи для 2007 – 2015 годов.

Задача 7 (файл задача7.xls).

Магазин одежды заказал 100 000 костюмов. Затраты на производство одного костюма составляют $22. Магазин планирует продавать костюмы до 31 августа по цене $40, а затем снизить цену до $30. Вычислить прибыль магазина от данного заказа.

Тема 2. Работа со списками и формами для составления аналитических отчетов

Повысить скорость работы экономиста поможет использование отдельных инструментов Excel, таких как • диапазоны; • наложение фильтров; • составление сводных таблиц. Ввод информации в базы данных может осуществляться путем набора или выбором из списков. Списки на основе введенных заранее данных широко используются для ускорения обработки большого объема информации. Списки должны находиться на том же рабочем листе, на котором осуществляется ввод данных. Для создания списка необходимо: 1. Правой кнопкой сделать Выбор из раскрывающегося списка: появятся все данные, стоящие в этом столбце выше. 2. Выбрать нужный элемент.

2.1. Использование диапазонов для анализа

статистической информации На практике гораздо удобнее работать не с адресами, а с конкретными названиями, которые определяются именами. При работе с электронными таблицами очень часто возникает необходимость сослаться не на одну ячейку, а на целую группу – строку, столбец или даже на несколько подряд идущих строк и столбцов одновременно. Такая группа называется диапазоном ячеек. Например, если выделить прямоугольную область из трех столбцов и двух строк, к примеру, с А1 по С2, то она будет представлять собой диапазон, обозначающийся как А1:С2. Непосредственно в момент выделения в поле имени ячейки показывается, сколько строк (R, Rows) и столбцов (С, Columns) выделено. Диапазон – это одна или группа связанных ячеек, в которую можно включать столбцы, строки, комбинации столбцов и строк. Диапазоны удобны для применения, поэтому используются для решения различных задач. Имена диапазонов появляются в поле Имя в верхней части листа слева от строки формул. После присвоения имени, по крайней мере, одному диапазону можно щелкнуть на стрелке поля Имя для выбора диапазона из списка. Для быстрого перемещения к указанному диапазону использовать команду Перейти. Три способа создания имен диапазонов: 1. Ввести имя диапазона в поле Имя. 2. Выбрать команды Имя – Создать в меню Вставка. 3. Выбрать команды Имя – Присвоить в меню Вставка. Правила для имени диапазона обобщены в таблице 9: 1. Имя диапазона должно начинаться с буквы или символа подчеркивания (_).

Дальше можно использовать любые символы, включая знаки препинания, кроме дефиса (-) или пробела. 2. В имени диапазона могут использоваться как строчные, так и прописные буквы. 3. В имени диапазона нельзя использовать пробелы. Вместо них символ подчеркивания (_) или точка.

Таблица 9

Правила построения имени диапазона Недопустимые имена Допустимые имена Объем продаж Объем_ продаж Итого за год Итого_ за_ год 2005 Год_2005 №п/п _№п/п Квартал 1 Квартал1

Особого внимания заслуживает работа с диапазонами, относящимися к разным листам. Присвоенные имена диапазонов могут использоваться в любом месте Книги. Чтобы не путаться, с какого именно листа взят диапазон, в случае, если у вас несколько диапазонов с таким именем, ему присваивают сложное имя со знаком «!» в качестве разделителя. Например, Лист3!Всего_продаж.

Задача 8. Создание диапазонов (файл задача8.xls).

Каждой ячейке в диапазоне В6:В55 присвоить аббревиатуру названия штата. Последовательно выполните решение задачи 3 способами.

Создание диапазонов Способ 1. Отобразив строку формул, увидим поле Имя. 1. Выделить диапазон ячеек и щелкнуть поле Имя. 2. Ввести желаемое Имя и нажать Enter. 3. Щелкнув стрелку справа от поля Имя, открыть список имен диапазонов. Способ 2. Отобразив строку формул, увидим поле Имя. 1. Выделить диапазон А6:В55. 2. Выберите из меню Вставка команды Имя – Создать. 3. Пометьте вариант в столбце слева. Щелкните стрелку в поле Имя, чтобы проверить названия диапазонов. Способ 3. Для этого несколько изменим задание. Ячейкам В6:В9 присвоим название А. 1. Выберите из меню Вставка команды Имя – Присвоить. 2. В диалоговом окне задать название в поле Имя, адреса ячеек в поле Формула. 3. Щелкнуть кнопку Добавить.

Удаление диапазонов 1. Открыть в меню Вставка команду Имя – Присвоить. 2. В диалоговом окне Присвоение имени выбрать имя удаляемого диапазона. 3. Выбрать команду Удалить.

Задача 9 (файл задача9.xls).

На основе исходных данных просуммировать продажи в штатах, начинающихся с А, С, М.

Задача 10 (файл задача10.xls).

Показаны ежемесячные прибыли по акциям двух компаний. Присвойте имена диапазонам, содержащим месячные прибыли компаний и вычислите среднемесячную прибыль.

Задача 11 (файл задача11.xls).

Вычислить среднюю прибыль по акциям, выполнив следующие действия. 1. Выделив диапазон ячеек В7:D81 и выбрав в меню Вставка команды Имя – Создать, создаем имена в верхней строке диапазона. Проверить, соответственно какому диапазону данных присвоены имена Акции, Векселя, Облигации. 2. Для ячейки В86 введем в строке формул =СРЗНАЧ(в скобках вместо аргумента функции нажмем F3 – откроется диалоговое окно Вставка имени, выделим любой из объектов) и нажмем ОК. 3. Удалим ненужные имена диапазонов.

Задача 12 (файл задача1.xls).

Выполнить моделирование скидки в зависимости от количества приобретенного товара на основе условия задачи 1. Для этого используйте в формулах имена диапазонов, представленных в столбцах А, D.

2.2. Применение фильтров для оперативного

получения экономических данных Повышение эффективности работы бухгалтера может происходить за счет следующих определенных методов, применение которых возможно благодаря инструментам Excel. Наиболее популярными являются следующие направления: • использование фильтров для оперативного получения данных; • графическое представление информации. Фильтр служит для ускорения поиска информации в базе данных. Для активизации фильтра используют меню Данные – Фильтр – Автофильтр. Выбирается поле и тип применяемого фильтра для отбора данных.

2.3. Автоматизация обработки экономической информации с помощью сводных таблиц Сводные таблицы широко используются в работе экономиста для анализа внешних и внутренних исходных данных. Они позволяют обобщить экономические данные, составить их группировку и представить различные их объединения по нескольким критериям. В результате, громоздкие списки представляются в удобном виде. Отчеты сводных таблиц допускают различные варианты форматирования, сортировки, группирования экономических данных. Исходной информацией для сводных таблиц могут быть не только списки MS Excel, но и внешние базы данных. Сводные таблицы строятся на основе базы данных, компонентами которой являются: • поля – столбцы; • записи – строки; • таблицы – совокупность всей информации, содержащейся в базе данных. Для построения базы данных требуется: 1. Открыть лист и щелкнуть в месте размещения первого столбца. 2. Ввести название первого поля. Нажать клавишу Тав и ввести названия следующих полей. Порядок создания формы базы данных: 1. Выделить любую ячейку в строке под названием полей, затем открыть меню Данные и выбрать Форма. Появится диалоговое окно ввода данных. 2. Ввести данные для каждого поля. 3. Чтобы начать новую запись, нажать кнопку Добавить. 4. Вводимые записи сразу заносятся в электронную таблицу. Сводная таблица имеет следующую структуру: 1. Поле строки – поле сводной таблицы, которое суммирует данные по строкам. 2. Поле столбца – поле сводной таблицы, которое суммирует данные по столбцам. 3. Поле страницы. 4. Область данных – центральная часть сводной таблицы, где фактически и появляются данные. 5. Список – таблица базы данных, которая является основой для построения сводной таблицы. 6. Функции итогов – вычисления, которые производятся в области данных сводной таблицы. 7. Управление сводной таблицей – после определения полей, используемых в сводной таблице, названия строк и столбцов становятся кнопками. Для работы со сводными таблицами используют Мастер сводных таблиц, который активизируется через меню Данные – Сводная таблица. На основе исходной таблицы 10 построена нижеприведенная сводная таблица 11.

Таблица 10

Структура дохода и расхода семьи, руб.

Дата Доход Расход Кто Откуда/Куда На что Что именно 01.02.2002 2 000,00 Отец Альфа Зарплата Январь 2002 02.02.2002 1 600,00 Мать Сигма Зарплата Январь 2002 03.02.2002 936,00 Отец Одежда Верхняя Пальто 04.02.2002 200,00 Ребенок Питание Фрукты Дыня 05.02.2002 1 600,00 Отец Гонорар Университет Январь 2002 06.02.2002 308,00 Мать Обувь Зимняя Туфли 07.02.2002 40,00 Ребенок Питание Фрукты Арбуз 08.02.2002 88,00 Мать Одежда Нижняя Рубашка 09.02.2002 1 200,00 Мать Бета Зарплата Январь 2002 10.02.2002 200,00 Мать Обувь Летняя Туфли 11.02.2002 3 108,00 Отец Гонорар Институт Январь 2002 12.02.2002 132,00 Общее Оплата жилья Газ Январь 2002 13.02.2002 176,00 Общее Оплата жилья Свет Январь 2002 14.02.2002 220,00 Общее Оплата жилья Вода Январь 2002 15.02.2002 600,00 Общее Машина Запчасти Колесо 16.02.2002 60,00 Общее Машина Стоянка За январь 17.02.2002 68,00 Общее Машина Штрафы Перекресток 18.02.2002 80,00 Общее Машина Бензин На дачу 19.02.2002 40,00 Отец Машина Бензин На работу 20.02.2002 1 380,00 Отец Гонорар Университет Февраль 2002 21.02.2002 100,00 Мать Машина Бензин К подруге 22.02.2002 120,00 Общее Питание Мясо На неделю 23.02.2002 160,00 Мать Питание Обеды На неделю 24.02.2002 120,00 Отец Питание Обеды На неделю 25.02.2002 396,00 Ребенок Одежда Демисезонная Куртка 26.02.2002 444,00 Отец Обувь Летняя На работу

Таблица 11 Сводная таблица по расходам на питание членов семьи, руб. Откуда/Куда Питание Сумма по полю Расход Кто

Общий На что Общее Ребенок Отец Мать итог Мясо 120 120 Обеды 120 160 280 Фрукты 240 240 Общий итог 120 240 120 160 640 Порядок построения сводной таблицы: 1. Выделите диапазон ячеек (исходные данные) для сводной таблицы. 2. Выберите меню Данные – Сводная таблица. 3. В первом окне выберите пункт в списке или в базе данных Microsoft Excel и нажмите кнопку Далее> 4. Во втором окне можно скорректировать диапазон ячеек с исходными данными. Можно указать данные из другого файла. Нажмите кнопку Далее>

а. Если вы хотите использовать в качестве данных для свод ной таблицы другую таблицу, то следует нажать кнопку Обзор, и будет открыт диалог для выбора файла.

б. Если вы создаете сводную таблицу из нескольких листов Рабочей книги и выбрали в первом диалоге (п.1) положение пе реключателя В нескольких диапазонах данных, то второй диа лог Мастера сводных таблиц будет иметь несколько иной вид. 5. В следующем окне непосредственно создается сводная таблица – на макет просто перетаскиваются названия столбцов, перечисленные в отдельном окне – Панели инструментов Сводные таблицы. Поочередно перетащите каждую кнопку (название столбца) на макет. Также возможно перетаскивание кнопок и с макета на панель инструментов. 6. В заключительном диалоге мастера сводных таблиц определяют ее расположение: на существующем или ином листе.

Задача 13. Повышение качества изготавливаемой продукции (файл задача13.xls).

Компания производит микрочипы. При этом обнаружено 5 видов дефектов, помеченных номерами 1 – 5. Чипы выпускаются 2 операторами А и В на четырех машинах (1 – 4), известен день изготовления чипа. Подразумевается, что каждый оператор и машина выпускают одинаковое количество чипов. На основе представленных данных составьте план действий для повышения качества продукции. Для этого, используя сводные таблицы, требуется «рассортировать» дефекты по: • видам; • дням недели; • виду машины и оператора. Исходные данные для решения задачи приведены в электронном виде. На основе обработки статистических данных представлены их группировки по направлениям исследования.

Таблица 12

Группировка дефектов по направлениям

величи по дням величина, по на, недели % видам %

по

маши- величина, Понедельник 16,36 1 39,39 нам % Вторник 18,18 2 26,06 1 36,97

по опе- величина, Среда 29,09 3 19,39 2 20,61 раторам % Четверг 16,36 4 12,73 3 22,42 A 57,58 Пятница 20,00 5 2,42 4 20,00 B 42,42 Итого 100,00 Итого 100,00 Итого 100,00 Итого 100,00

Таблица 13

Сводная таблица по операторам, машинам и видам

Дефекты Виды дефектов

оператор машина 1 2 3 4 5 Итого

A 1 24 14 12 10 1 61

2 12 11 7 3 1 34

A итого 36 25 19 13 2 95

B 3 16 10 6 4 1 37

4 13 8 7 4 1 33

B итого 29 18 13 8 2 70

Итого 65 43 32 21 4 165

Таблица 14

Обобщенная таблица дефектов по дням недели, %

Дефект Виды дефектов

День недели 1 2 3 4 5

Понедельник 15,38 13,95 18,75 14,29 50,00

Вторник 23,08 11,63 21,88 9,52 25,00

Среда 23,08 48,84 15,63 33,33 0,00

Четверг 18,46 13,95 15,63 14,29 25,00

Пятница 20,00 11,63 28,13 28,57 0,00

Итого 100,00 100,00 100,00 100,00 100,00

На основе сводных таблиц сформулируем следующую программу повышения качества. Так как дефект 1 является наиболее общим, то его требуется устранить. Оператор А допускает больше дефектов, чем В, но более глубокий анализ показывает, что, возможно, причиной этого является лишь тот факт, что используется машина 1, которая является наихудшей. В среду возникает наибольшее количество дефектов, преимущественно 2-го типа. Следовательно, при анализе «узких» мест в работе предприятия следует уделить внимание причинам возникновения дефектов 1-го типа, особенностям работы машины 1 и определить, почему возникают проблемы в работе по средам.

Таблица 15

Обобщенная таблица по видам дефектов

на каждый день недели

Дефект

День недели 1 2 3 4 5 Итого

Понедельник 10 6 6 3 2 27

Вторник 15 5 7 2 1 30

Среда 15 21 5 7 0 48

Четверг 12 6 5 3 1 27

Пятница 13 5 9 6 0 33

Итого 65 43 32 21 4 165

Задача 14. Анализ распределения студентов по направлениям подготовки (файл задача14.xls).

Подготовка студентов колледжа осуществляется по двум направлениям – английский язык и точные науки. Выяснить, не существует ли дискриминации женщин при предоставлении студентам права выбора направления подготовки. Для этого исследуйте информацию по следующим составляющим: 1) пол; 2) направление подготовки; 3) учитывается ли желание студента (да или нет).

Исходные данные для решения задачи приведены в электронном виде. Обобщенные сведения представлены в таблицах 17, 18.

Таблица 17

Данные о согласии студентов

на выбор направления подготовки, %

Желание

Пол Нет Да Итог

Жен. 48,00 52,00 100,00

Муж. 47,0 53,00 100,00

Среднее

значение 47,50 52,50 100,00

Таблица 18

Выбор направления подготовки, %

Программа Пол Нет Да Итог

Жен. 40,00 60,00 100,00

Англ. Муж. 20,00 80,00 100,00

Точные Жен. 80,00 20,00 100,00

науки Муж. 58,57 41,43 100,00

Обнаружено, что примерно одинаковый процент мужчин и женщин согласны с их выбором поля деятельности. Однако по каждому направлению меньшее число женщин согласно обучаться по желанию, что показывает возможную дискриминацию. Причиной того, что общий процент согласившихся женщин приблизительно одинаков с мужчинами, является то, что большинство женщин выбрали более «легкое» направление (английский язык).

Задачи для самостоятельного решения

по теме 2 Задача 15 (файл задача15.xls).

Требуется оценить качество обслуживания пациентов с сердечными приступами в клиниках г. Москвы (М) и г. Ярославля (Яр).

В таблице исходных данных имеются следующие сведения: • клиника, в которой находился каждый пациент; • степень опасности (высокая или низкая); • итог лечения: выздоровел пациент или нет.

Задача 16 (файл задача16.xls).

За 1985 – 1992 годы предоставлены данные о ежемесячных процентных ставках выплат по облигациям, деньги по которым выплачиваются в течение одного года после их приобретения. Считается, что процентная ставка более изменчива, то есть имеет большую тенденцию к изменению, когда она высока. Проверьте это утверждение на основе составления сводной таблицы и расчета стандартного отклонения.

Тема 3. Оптимизация экономической

деятельности

3.1. Обработка экономических данных на основе

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

Сущность XYZ-анализа состоит в том, что в соответствии с заданными критериями каждому покупателю присваивается определенный статус – разовый покупатель, постоянный, потерянный. В то же время постоянный покупатель делится на классы XYZ в соответствии со степенью его стабильности. Критерием для оценки стабильности покупателей могут служить количество продаж, сумма прибыли, сумма отгрузки, интервал между выпиской расходных документов.

При использовании информационных технологий в проведении XYZ-анализа основной задачей является автоматическое отслеживание перехода покупателей из одной категории в другую.

Для практических вычислений большее распространение получил АВС-анализ, суть которого состоит в разбиении всех товаров (клиентов) по важности на 3 класса (А, В и С) по некоторому показателю – объему продаж, прибыли и т.д. АВС-анализ строится на принципе, сформулированном Вильгельмом Парето, называемом закон Парето. Данный принцип больше известен как «принцип 20 на 80» и гласит, что, как правило, 80% всех товаров (клиентов) дают не более 20% оборота. С помощью АВС-анализа клиентов можно разделить по степени их значимости: высокой важности (А класс), средней важности (В класс) и низкой важности (С класс).

Критерии, по которым определяется степень важности клиентов, настраиваются самим пользователем, это может быть, например, сумма выручки или прибыли.

Проведение такого анализа обеспечивает индивидуальный подход к каждому клиенту. Изучение торговой организации показывает: тому, кто является важным и стабильным для нас, можно отгружать товар в кредит, а нестабильному – только на условии полной предоплаты. Отслеживание взаиморасчетов с контрагентами ведется на уровне договоров. При этом предусмотрены различные схемы ведения договоров покупателей. Начиная от наиболее обобщенного – по долгосрочным договорам – до отслеживания оплаты конкретной накладной в случае ведения взаиморасчетов по конкретным документам. При ведении взаиморасчетов с контрагентами по заказам покупателей можно автоматически отслеживать процент предоплаты по заказу и применять различные стратегии работы (с контролем максимально возможной суммы кредита по заказу), в том числе отгрузку товаров только при получении от покупателя 100-процентной предоплаты. Рассмотрим на практике АВС-анализ торговой организации. В связи с этим выделим классы: 1) А – товары (клиенты), дающие 80% прибыли (их 20%); 2) С – 80% товаров (клиентов), с наименьшей важностью, доля участия которых в обороте фирмы невелика; 3) В – все остальные товары (клиенты), занимающие промежуточное положение.

Задача 17. Обработка данных торговой компании (файл задача17.xls).

Таблица 19

Исходные данные по отгрузке торговой компании «Весна»

с 01.02.06 по 28.02.06

А В 2 Номенклатура Отгрузка, руб. 3 Датское печенье Queen’s 1 573 000 4 Кексы, рулеты Дан Кейк 848 926 5 Конфеты Ferrero 532 361 6 Кофе Lavazza 401 378 7 Круассаны Bauli 35 001 8 Мини круассаны Bauli 17 099 9 Печенье Bauli 4 008 10 Печенье Hellema 117 834 11 Печенье и вафли Destrooper 741 962 12 Печенье и вафли Freitag 212 052 13 Печенье отеч. 60 719 14 Пирожные Freddi 40 134 15 Пряники отеч. 1 040 824 16 Рулеты Freddi 80 134 17 Слойки, ушки 23 550 18 Сухарики Finn Crisp 45 445 19 Торты Bauli 50 810 20 Торты отеч. 40 530 21 Ушки отеч. 1 050 199 22 Хлебцы 74 747 23 Хлебцы Finn Crisp 70 682 24 Чипсы, снеки 656 097 25 Шоколад Lindt 951 952 26 Шоколадная паста 65 516 27 Шоколадные конфеты Bauli 600 28 Шоколадные конфеты Hanssens 90 014 29 Шоколадные конфеты Pernigotti 324 837

Необходимо определить суммы (и процент) отгрузки, которые дают 80% товаров с наименьшей важностью (класс С) и 20% товаров с наибольшей важностью. Определим сумму отгрузки, которую дают 80% товаров с наименьшей важностью (класс С).

Проведем АВС-анализ по следующей схеме: 1. В ячейку В31 введите формулу =СУММ(В3:В29) – общая сумма отгрузки, которая равна 9 150 411 руб. 2. Установите фильтр на данные. В строке 2 появятся кнопки списков. 3. В списке Отгрузка выберите пункт (Первые 10…) и в появившемся окне установите условие: 80 наименьших % от количества элементов. После нажатия ОК получите 80% списка товаров, дающих минимальные объемы. 4. Вычислите сумму, которую дает их отгрузка, она равна 2 943 548 руб. (32%) от общего объема. Для суммирования используйте функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ(номер функции; ссылка1; ссылка2;…), которая возвращает результат промежуточного вычисления в список или базу данных. 5. Аналогично оцените объем продаж 20% товаров наибольшей важности. 6. Получите итоговую таблицу следующего вида:

Таблица 20

Результаты проведения АВС-анализа Продукты, дающие максимальную Продукты, дающие минимальную

выручку выручку Наименование продукта Выручка Наименование продукта Выручка

7. Используя функцию БДСУММ(база данных; поле; критерий), просуммируйте числа в столбце списка или базы данных, которые удовлетворяют заданным условиям: отгрузка составляет от 15 тыс. до 50 тыс. рублей. 3.2. Операционно-стоимостной анализ процесса

Один из способов расчета и оптимизации расходов состоит в проведении операционно-стоимостного анализа процесса, приня того в системах моделирования и анализа бизнеса. Механизм процесса заключается в следующем:

1) весь процесс разбивают на функции-операции;

2) каждой функции-операции приписывают ресурсы, оцени вают стоимость единицы ресурса;

3) вычисляют стоимость однократного выполнения каждой функции-операции;

4) выявляют самые дорогие функции-операции и снижают их стоимость;

5) параллельно вычисляют коэффициенты участия каждой функции-операции в процессе, оценивают стоимость процесса в сумме всех заказов и пр.

Решение задачи проводится с помощью таблиц и графиков.

Например, требуется провести операционно-стоимостной анализ оформления и доставки товара в магазин. Необходимые данные представлены в таблице 21. Для увеличения наглядности анализ может проводиться на основе графика (рис. 1).

На основе проведенного анализа заметим, что наиболее дорогостоящими операциями являются оплата услуг торгового представителя по оформлению заказа и его вводу в базу данных. Экономия может быть достигнута за счет автоматизации процесса и осуществления процесса оформления через локальную и глобальную сети.

Задачи для самостоятельного решения

по теме 3 Задача 18 (файл задача18.xls).

Ознакомиться со структурой, созданной в файле. Добавить дополнительную группировку.

Задача 19 (файл задача19.xls).

Выполнить суммирование данных на Листе 3 по соответствующим полям с Листа 1 и Листа 2.

Таблица 21 Операционно-стоимостной анализ процесса оформления, дос тавки заказа

Стоимость

Человеческий ресурс Материальный ресурс

функции № Функция (одно Стои- Стои- Расп/п Расход, Назва- процесса кратного

Название мость, мость, ход,

мин ние выполне руб./мин. руб./ ед. ед.

ния)

Согласно заказа

в магазине,

Торговый пред- в т.ч. по теле1 ставитель 2,5 60 Бланк 1 2 фону 152

Ручной ввод

и корректиров Торговый пред- ка заказа в базу 2 ставитель 2,5 60 данных 150

Финансовый Контроль взаи3 директор 1,8 10 морасчетов 18

Выписка, пе Лист чать накладных

бумаги и счетов4 Оператор 1,4 20 А4 0,3 8 фактур 30,4

Подбор и копи рование серти Лист фикатов, сор бумаги тировка доку5 Оператор 1,4 30 А4 0,3 20 ментов 48

Передача паке та документов 6 Курьер 1,3 60 на склад 78

Сбор заказа 7 Кладовщик 1,3 30 на складе 39

Сортировка за Заведующий каза по адресу 8 складом 1,8 10 доставки 18

Загрузка в ав9 Грузчик 1,2 15 тотранспорт 18

Доставка, вы грузка товара

в магазине,

Водитель- оформление 10 экспедитор 2 60 Бензин 10 3 документов 150

Возврат доку ментов

Водитель- и некондиции 11 экспедитор 2 30 Бензин 10 3 на склад 90

Передача паке та документов 12 Курьер 1,3 60 в бухгалтерию 78

СУММА = 869,4 33

Тема 4. Автоматизация

бухгалтерских расчетов

4.1. Ввод и поиск информации

с помощью функций Excel В работе экономиста зачастую возникает необходимость автоматического заполнения полей таблицы на основе существующей первичной документации. Рациональным решением данной проблемы может служить применение функций просмотра, позволяющих анализировать значения диапазонов электронной таблицы. Excel позволяет проводить как вертикальный, так и горизонтальный просмотр с помощью функций ВПР и ГПР соответственно. Данные функции имеют следующий синтаксис: ВПР(искомое значение;таблица;номер столбца;[интервальный просмотр]) ГПР(искомое значение;таблица;номер строки;[интервальный просмотр]) • искомое значение – то, что мы ищем в первом столбце диапазона таблицы; • таблица – диапазон, включающий всю просматриваемую таблицу; диапазон включает первый столбец, в котором пытаемся найти соответствие искомому значению, и любые другие столбцы, в которых хотим найти результаты вычисления по формуле; • номер столбца (строки) – указывается столбец (строка) в диапазоне, из которого возвращается значение функции просмотра; • интервальный просмотр – дополнительный аргумент для поиска.

Задача 20. Определение налоговых ставок в зависимости от доходов (файл задача20.xls).

Диапазон D6:E9 в таблице ниже содержит цены на продукцию в соответствии с датой покупки и обозначен Lookup (Просмотр).

Таблица 22

Исходные данные

D E

Строки/ Столбцы Доход, руб. Налоговая ставка,%

6 0 0,15

7 10 000 0,3

8 30 000 0,34

9 100 000 0,4 Результат работы функции просмотра представлен в таблице 23. Так как номер столбца в формуле равен 2, ответ всегда возвращается из второго столбца диапазона таблицы.

Таблица 23

Вычисления с помощью функции ВПР

D E F E F Столбцы

ИстиСтроки/

До- Ложь

на Формула расчета по

ход, Формула расчета по ЛОЖЬ

ИСТИНА

руб. Ставка 13 -1 000 #Н/Д #Н/Д =ВПР(D13;Lookup;2) =ВПР(D13;Lookup;2;ЛОЖЬ) 14 30 000 0,34 0,34 =ВПР(D14;Lookup;2) =ВПР(D14;Lookup;2;ЛОЖЬ) 15 29 000 0,3 #Н/Д =ВПР(D15;Lookup;2) =ВПР(D15;Lookup;2;ЛОЖЬ) 16 98 000 0,34 #Н/Д =ВПР(D16;Lookup;2) =ВПР(D16;Lookup;2;ЛОЖЬ) 17 104 000 0,4 #Н/Д =ВПР(D17;Lookup;2) =ВПР(D17;Lookup;2;ЛОЖЬ)

Задача 21. Определение цены продукта по его идентификатору (файл задача21.xls).

Диапазон H11:I15 в таблице ниже содержит идентификатор продукта и его цену и обозначен Lookup2 (Просмотр2).

Таблица 24

Исходные данные

H I

Строки/ столбцы Идентификатор продукта Цена

11 A134 $3,50

12 B242 $4,20

13 X212 $4,80

14 C413 $5,00

15 B2211 $5,20 Результат работы функции просмотра представлен в таблице 25. Так как номер столбца в формуле равен 2, ответ всегда возвращается из второго столбца диапазона таблицы.

Таблица 25

Вычисления с помощью функции ВПР

H I I Строки/

Идентификатор Столбцы Цена Формула определения цены

продукта

18 B2211 $ 3,5 =ВПР(H18;Lookup2;2;ИСТИНА)

19 B2211 $ 5,2 =ВПР(H19;Lookup2;2;ЛОЖЬ)

Задача 22. Определение цены продукта по дате продажи (файл задача22.xls).

Предположим, что цена продукта изменяется со временем. Требуется рассчитать цену продукта по дате продажи. Диапазон в таблице 26 содержит цены на продукцию в соответствии с датой покупки и обозначен lookup.

Таблица 26

Исходные цены с помощью функции ГПР, дол.

01.01.2005 01.05.2005 01.08.2005

98 105 112 В столбце В с 8 по12 строках осуществляется поиск значений даты и их сравнение с датой в таблице с исходными данными. В столбце С фиксируется верная цена для любой даты продажи.

Таблица 27

Формирование прайс-листа Строки/ Столбцы В С С

7 Дата Цена, дол. Формулы для вычислений

8 04.01.2005 98 =ГПР(B8;lookup;2;ИСТИНА)

9 10.05.2005 105 =ГПР(B9;lookup;2;ИСТИНА)

10 12.09.2005 112 =ГПР(B10;lookup;2;ИСТИНА)

11 01.05.2005 105 =ГПР(B11;lookup;2;ИСТИНА)

12 01.05.2006 112 =ГПР(B12;lookup;2;ИСТИНА) 4.2. Расчеты по единому социальному налогу

(ЕСН) Все расчеты с персоналом организации по заработной плате неразрывно связаны с расчетами по единому социальному налогу (ЕСН), налогу на доходы физических лиц (НДФЛ) и отчислениями в Пенсионный фонд РФ (ПФР).

При автоматизации расчетов при начислении ЕСН и ПФР необходимо учитывать как требования действующего налогового и пенсионного законодательств, так и положения учетной политики организации. Исходными сведениями для автоматизации расчетов будут являться: • размер налогооблагаемой базы; • процент отчислений в соответствующие фонды; • регрессивные ставки; • возраст работника: старше или моложе 1967 года рождения. Напомним, что, если организация осуществляет свою деятельность в рамках специальных налоговых режимов – «Упрощенная система налогообложения (УСН)» и «Единый налог на вмененный доход (ЕНВД)», – она не является плательщиком ЕСН в полном объеме. Если организация работает в общем режиме, то необходимо начислять ЕСН, взносы в Пенсионный фонд РФ, а также, в соответствии с особенностями вида деятельности организации, начислять взносы в Фонд социального страхования (ФСС), связанные с производственным травматизмом. Это же касается и работодателя – индивидуального предпринимателя. С 1.01.05 г. максимальная ставка ЕСН составляет 26% вместо действовавшей ранее ставки 35,6%. Был также изменен и порядок распределения ЕСН между федеральными бюджетами и фондами: • 20% в федеральный бюджет (до 01.01.05 – 28%); • 3,2% в Фонд обязательного страхования Российской Федерации (до 01.01.05 – 4%); • 0,8% в федеральный Фонд обязательного медицинского страхования (до 01.01.05 – 0,2 %); • 2% в территориальный Фонд обязательного медицинского страхования (до 01.01.05 – 3,4%).

Дополнительно при проведении начислений необходимо учитывать взносы в ФСС, связанные с профессиональным травматизмом. При этом следует учесть, что с 2005 года было отменено условие применения регрессивной шкалы (уровень заработной платы в текущем году не должен быть менее 2 500 рублей в месяц).

Все организации-работодатели с января 2005 года обязаны применять регрессивные ставки в случае, если налоговая база на каждого работника превысит установленные пределы. С 1.01.05 г. изменена и сама регрессивная шкала ставки налога. Так, для организаций, осуществляющих выплаты физическим лицам, устанавливается следующая шкала налоговых ставок при величине выплат: • до 280 000 руб. в год – 26%; • от 280 000 руб. до 600 000 руб. в год – 72 800 руб. + 10% с суммы, превышающей 280 000 руб.; • свыше 600 000 руб. в год – 104 800 руб. + 2% с суммы, превышающей 600 000 руб.

При начислении заработной платы задача бухгалтера заключается в проведении начислений в вышеперечисленные фонды и в удержании налога на доходы физических лиц.

20% в федеральный бюджет

0,8% в федеральный 2% в территориальный

Фонд ОМС ЕСН Фонд ОМС

3,2% в ФСС РФ

Рис. 2. Порядок распределения ЕСН

между федеральными бюджетами и фондами Задача 23. Расчет ЕСН (файл задача 23.xls).

В январе 2005 года сотруднику фирмы «Аленка» Сидоровой Т.П. начислена заработная плата в размере 20 000 руб. Налоговая база в год при указанной заработной плате не превышает установленного предела для применения регрессии (280 000 руб.).

ЕСН должен быть рассчитан таким образом: • в ФСС РФ: 20 000 руб.* 3,2% = 640 руб.; • в федеральный Фонд ОМС: 20 000 руб.* 0,8% = 160 руб.; • в территориальный фонд ОМС: 20 000 руб.* 2% = 400 руб.; • в федеральный бюджет: 20 000 руб.* 20 % = 4 000 руб. При применении налогового вычета с учетом тарифа страховых взносов на обязательное медицинское страхование 14% (20 000 руб. * 14% = 2 800) сумма ЕСС к уплате в федеральный бюджет составит 1 200 руб.: 4 000 – 2 800 = 1 200 руб. В данном примере сумма начисленных страховых взносов (налоговый вычет) 2800 руб. не превышает начисленную за тот же период сумму налога, подлежащую к уплате в федеральный бюджет (4 000 руб.).

Таблица 28

Расчет ЕСН и распределение между фондами, руб. столбцы

A B C D E F G H Строки/

Ставки отчислений, %

Сведения о работнике 1 3,20 0,80 2 20 к уплате

Начислено

Год в ФБ 2 ФИО рож- ФСС ФФОМС ТФОМС ФБ

дения 3 Сидорова Т.П. 1966 20 000,00 640 160 400 4 000 1 200 4 Карпова Н.Б. 1954 16 000,00 512 128 320 3 200 960 5 Иваненко Л.А. 1973 18 000,00 576 144 360 3 600 1 080 6 Иванов И.П. 1990 2 000,00 64 16 40 400 120 7 Вьюнов К.Т. 1985 3 000,00 96 24 60 600 180 8 Лещева Т.В. 1958 3 500,00 112 28 70 700 210 9 Петренко О.В. 1968 6 250,00 200 50 125 1 250 375 19 Сидоренко А.И. 1968 3 000,00 96 24 60 600 180

Таблица 29 Формулы расчета ЕСН и распределение между фондами

относительно адресов таблицы

Начис

лено,

руб. Федеральный

ФИО ФСС ФФОМС ТФОМС бюджет Сидорова Т.П. 20 000 =C3*$D$1 =C3*$E$1 =C3*$F$1 =C3*$G$1 Карпова Н.Б. 16 000 =C4*$D$1 =C4*$E$1 =C4*$F$1 =C4*$G$1 Иваненко Л.А. 18 000 =C5*$D$1 =C5*$E$1 =C5*$F$1 =C5*$G$1 Иванов И.П. 2 000 =C6*$D$1 =C6*$E$1 =C6*$F$1 =C6*$G$1 Вьюнов К.Т. 3 000 =C7*$D$1 =C7*$E$1 =C7*$F$1 =C7*$G$1 Лещева Т.В. 3 500 =C8*$D$1 =C8*$E$1 =C8*$F$1 =C8*$G$1 Петренко О.В. 6 250 =C9*$D$1 =C9*$E$1 =C9*$F$1 =C9*$G$1 Сидоренко А.И. 3 000 =C10*$D$1 =C10*$E$1 =C10*$F$1 =C10*$G$1

Помимо общих положений при автоматизации расчетов по ЕСН важны сведения о возрасте работника. Для работников 1967 года рождения и моложе предусмотрено начисление как страховой части трудовой пенсии (8%), так и накопительной (6%).

Предлагается 2 способа выполнения расчетов: 1. В общей таблице с последующим отбором необходимых сотрудников 2. В отдельных таблицах по каждой из 2-х групп: до 1967 года рождения и старше. Экономисту может требоваться как итоговая информация отчислений в ПФР, так и в разрезе каждой группы. Поэтому при автоматизированных расчетах требуется продумать общую схему расчета суммарных платежей, представленных в последнем столбце таблицы 30. Для ускорения внесения поправок в формулы в случае изменения процентных ставок отчислений, следует использовать абсолютную адресацию при введении констант в формулы (признаком абсолютной адресации является знак $, который появляется при нажатии на адрес ячейки клавиши F4).

Проведенные вычисления выполнены с использованием логической функции ЕСЛИ(условие; если истинно; если ложно).

Таблица 30

Расчет отчислений в ПФР, руб. Стро ки/ст

A B C D E F олб цы

Ставки, % 1 Сведения о работнике

14 8 6 Итого в ПФР

ПФР- ПФР 2 ФИО Начислено ПФР-накоп.

страх. страх. 3 Сидорова Т.П. 20 000,00 2 800 0 0 2 800 4 Карпова Н.Б. 16 000,00 2 240 0 0 2 240 5 Иваненко Л.А. 18 000,00 0 1 440 1 080 2 520 6 Иванов И.П. 2 000,00 0 160 120 280 7 Вьюнов К.Т. 3 000,00 0 240 180 420 8 Лещева Т.В. 3 500,00 490 0 0 490 9 Петренко О.В. 6 250,00 0 500 375 875 10 Сидоренко А.И. 3 000,00 0 240 180 420

Таблица 31

Формулы расчета отчислений в ПФР

(относительно адресов ячеек таблицы 30) Ставки, % 0,14 0,08 0,06

Итого в ФИО ПФР-страх. ПФР-страх. ПФР-накоп. к уплате в ФБ

ПФР

=ЕСЛИ($B3>=

=ЕСЛИ($B3< 1967;$C3*$I$1 =ЕСЛИ($B3>=19 =СУММ Сидорова Т.П. 1967;$C3*$H$1;0) ;0) 67;$C3*$J$1;0) (H3;I3;J3) =G3-(C3*$L$1)

=ЕСЛИ($B4>=

=ЕСЛИ($B4< 1967;$C4*$I$1 =ЕСЛИ($B4>=19 =СУММ Карпова Н.Б. 1967;$C4*$H$1;0) ;0) 67;$C4*$J$1;0) (H4;I4;J4) =G4-(C4*$L$1)

=ЕСЛИ($B5>=

=ЕСЛИ($B5< =ЕСЛИ($B5>=19 =СУММ Иваненко Л.А. 1967;$C5*$I$1; =G5-(C5*$L$1)

1967;$C5*$H$1;0) 67;$C5*$J$1;0) (H5;I5;J5)

0)

=ЕСЛИ($B6>=

=ЕСЛИ($B6< =ЕСЛИ($B6>=19 =СУММ Иванов И.П. 1967;$C6*$I$1; =G6-(C6*$L$1)

1967;$C6*$H$1;0) 67;$C6*$J$1;0) (H6;I6;J6)

0)

=ЕСЛИ($B7>=

=ЕСЛИ($B7< =ЕСЛИ($B7>=19 =СУММ(H Вьюнов К.Т. 1967;$C7*$I$1; =G7-(C7*$L$1)

1967;$C7*$H$1;0) 67;$C7*$J$1;0) 7;I7;J7)

0)

=ЕСЛИ($B8>=

=ЕСЛИ($B8< =ЕСЛИ($B8>=19 =СУММ(H Лещева Т.В. 1967;$C8*$I$1; =G8-(C8*$L$1)

1967;$C8*$H$1;0) 67;$C8*$J$1;0) 8;I8;J8)

0)

=ЕСЛИ($B9>=

=ЕСЛИ($B9< =ЕСЛИ($B9>=19 =СУММ(H Петренко О.В. 1967;$C9*$I$1; =G9-(C9*$L$1)

1967;$C9*$H$1;0) 67;$C9*$J$1;0) 9;I9;J9)

0)

=ЕСЛИ($B10>

=ЕСЛИ($B10<1967 =ЕСЛИ($B10>=1 =СУММ(H =G10Сидоренко А.И. =1967;$C10*$I

;$C10*$H$1;0) 967;$C10*$J$1;0) 10;I10;J10) (C10*$L$1)

$1;0) Для повышения скорости обработки записей рекомендуется сотрудников до и после 1967 года рождения распределить по разным листам. Тогда в формулы расчета отчислений требуется ввести название листа, содержащего данные о ФИО, годе рождения и начислениях сотруднику. Допустим, этот лист называется «Состав отдела». Тогда в формулах расчетов перед каждой составляющей будет идти запись следующего вида:

Состав отдела!адрес используемой ячейки Список сотрудников в поле ФИО и Год рождения также переносятся из исходной таблицы на листе Состав отдела, чтобы происходили автоматические изменения в прикрепленных таблицах к исходной.

4.3. Определение количественного и

качественного состава сотрудников Для анализа кадрового состава предприятия нередко требуется определить количественный состав специалистов по отделам.

Задача 24. Анализ кадровой структуры предприятия (файл задача 24.xls).

Таблица 32

Исходные данные о сотрудниках

фирмы «Аленка» Столб A B C D E F цы/ Год Стро- Отдел Должность Таб. номер Оклад, руб. рождения ки ФИО 2 Контроля Начальник Сидорова Т.П. 2345 10 500,00 1966 3 Реализации Аудитор Карпова Н.Б. 6789 7 000,00 1954 4 Реализации Менеджер Иваненко Л.А. 3456 5 000,00 1973 5 Реализации Менеджер Иванов И.П. 123 2 000,00 1990 6 Реализации Аудитор Вьюнов К.Т. 1234 3 000,00 1985 7 Снабжения Начальник Лещева Т.В. 9012 3 500,00 1958 8 Снабжения Инженер Петренко О.В. 7890 6 250,00 1968 9 Контроля Аудитор Сидоренко А.И. 8901 3 000,00 1968

Проведенные вычисления выполнены с использованием функции СЧЕТЕСЛИ(диапазон;условие).

Таблица 33

Состав сотрудников

фирмы «Аленка» по отделам

Численность, Отдел чел. Формулы для расчета Контроля 2 =СЧЁТЕСЛИ($A$2:$A$9;»Контроля») Реализации 4 =СЧЁТЕСЛИ($A$2:$A$9;»Реализации») Снабжения 2 =СЧЁТЕСЛИ($A$2:$A$9;»Снабжения»)

Таблица 34 Состав сотрудников фирмы «Аленка» по должностям

Численность, Должность чел. Формулы для расчета Аудитор 3 =СЧЁТЕСЛИ($B$2:$B$9;»Аудитор») Инженер 1 =СЧЁТЕСЛИ($B$2:$B$9;»Инженер») Секретарь 0 =СЧЁТЕСЛИ($B$2:$B$9;»Секретарь») Менеджер 2 =СЧЁТЕСЛИ($B$2:$B$9;»Менеджер») Начальник 2 =СЧЁТЕСЛИ($B$2:$B$9;»Начальник»)

4.4. Расчет премии сотрудников на основе

соответствующих коэффициентов Расчет премии проводится в таблице 35 по установленным коэффициентам по каждой должности, согласно штатному расписанию.

Таблица 35

Коэффициенты для начисления премии

Должность Коэффициент

Аудитор 0,75

Инженер 0,5

Секретарь 0,2

Менеджер 0,4

Начальник 0,7

Таблица 36

Расчет премии сотрудников фирмы «Аленка»

(адресация в формулах согласно

таблице 32 с исходными данными)

Оклад, Общий ФИО руб. Расчет премии итог

=ЕСЛИ(B2=»Начальник»;E2*0,7;ЕСЛИ(B2=»Аудитор

«;E2*0,75;ЕСЛИ(B2=»Менеджер»;E2*0,4;ЕСЛИ(B2=» Сидорова Т.П. 10 500 Секретарь»;E2*0,2;ЕСЛИ(B2=»Инженер»;E2*0,4;0))))) =B42+C42

=ЕСЛИ(B3=»Начальник»;E3*0,7;ЕСЛИ(B3=»Аудитор

«;E3*0,75;ЕСЛИ(B3=»Менеджер»;E3*0,4;ЕСЛИ(B3=» Карпова Н.Б. 7 000 Секретарь»;E3*0,2;ЕСЛИ(B3=»Инженер»;E3*0,4;0))))) =B43+C43

=ЕСЛИ(B4=»Начальник»;E4*0,7;ЕСЛИ(B4=»Аудитор

«;E4*0,75;ЕСЛИ(B4=»Менеджер»;E4*0,4;ЕСЛИ(B4=» Иваненко Л.А. 5 000 Секретарь»;E4*0,2;ЕСЛИ(B4=»Инженер»;E4*0,4;0))))) =B44+C44

=ЕСЛИ(B5=»Начальник»;E5*0,7;ЕСЛИ(B5=»Аудитор

«;E5*0,75;ЕСЛИ(B5=»Менеджер»;E5*0,4;ЕСЛИ(B5=» Иванов И.П. 2 000 Секретарь»;E5*0,2;ЕСЛИ(B5=»Инженер»;E5*0,4;0))))) =B45+C45

=ЕСЛИ(B6=»Начальник»;E6*0,7;ЕСЛИ(B6=»Аудитор

«;E6*0,75;ЕСЛИ(B6=»Менеджер»;E6*0,4;ЕСЛИ(B6=» Вьюнов К.Т. 3 000 Секретарь»;E6*0,2;ЕСЛИ(B6=»Инженер»;E6*0,4;0))))) =B46+C46

=ЕСЛИ(B7=»Начальник»;E7*0,7;ЕСЛИ(B7=»Аудитор

«;E7*0,75;ЕСЛИ(B7=»Менеджер»;E7*0,4;ЕСЛИ(B7=» Лещева Т.В. 3 500 Секретарь»;E7*0,2;ЕСЛИ(B7=»Инженер»;E7*0,4;0))))) =B47+C47

=ЕСЛИ(B8=»Начальник»;E8*0,7;ЕСЛИ(B8=»Аудитор

«;E8*0,75;ЕСЛИ(B8=»Менеджер»;E8*0,4;ЕСЛИ(B8=» Петренко О.В. 6 250 Секретарь»;E8*0,2;ЕСЛИ(B8=»Инженер»;E8*0,4;0))))) =B48+C48

=ЕСЛИ(B9=»Начальник»;E9*0,7;ЕСЛИ(B9=»Аудитор

«;E9*0,75;ЕСЛИ(B9=»Менеджер»;E9*0,4;ЕСЛИ(B9=» Сидоренко А.И. 3 000 Секретарь»;E9*0,2;ЕСЛИ(B9=»Инженер»;E9*0,4;0))))) =B49+C49

Таблица 37 Итоги по премированию сотрудников фирмы «Аленка», руб.

Расчет

ФИО Оклад премии Общий итог

Сидорова Т.П. 10 500,00 7 350 17 850,00

Карпова Н.Б. 7 000,00 5 250 12 250,00

Иваненко Л.А. 5 000,00 2 000 7 000,00

Иванов И.П. 2 000,00 800 2 800,00

Вьюнов К.Т. 3 000,00 2 250 5 250,00

Лещева Т.В. 3 500,00 2 450 5 950,00

Петренко О.В. 6 250,00 2 500 8 750,00

Сидоренко А.И. 3 000,00 2 250 5 250,00

Задачи для самостоятельного решения

по теме 4 Задача 25 (файл задача 25.xls).

В файле записаны идентификаторы сотрудников, их зарплаты и стаж. Напишите формулу, которая по заданному идентификатору показывает заработную плату сотрудника.

Задача 26 (файл задача 26.xls).

Вы собираетесь рекламировать продукты Microsoft во время спортивных передач. Чем больше рекламного времени вы покупаете, тем ниже его цена (см. таблицу).

Таблица 38

Цена рекламного времени

Количество Цена за показ

показов

1–5 $12 000

6 – 10 $11 000

11 – 20 $10 000

Более 20 $9 000

Создайте формулу, которая выводит общую стоимость покупки любого количества показов.

Тема 5. Оптимизационные задачи

в экономике

5.1. Определение задачи оптимизации Нередко у экономиста возникает необходимость решения задач, когда задается некоторый итоговый (целевой) показатель и надо на базе имеющихся ресурсов предприятия получить требуемую величину целевого показателя, например, прибыли, выручки, объема выпуска и т.д. При этом, исходя из ограниченности имеющихся в распоряжении ресурсов и знания определенных экономических закономерностей, выбирается тот фактор, который либо является наиболее важным по влиянию на искомый целевой показатель, либо более доступен изменению. В качестве такого показателя может выступать цена, себестоимость единицы продукции, величина затрат и т.п. Для автоматизированного решения подобных задач используется инструмент Excel «Подбор параметра», который служит для анализа информации и построения решений, соответствующих требуемым ограничениям. При этом существует, как правило, возможность изменения только одного ресурса, выступающего в модели в качестве компоненты. Так как Excel работает с адресами ячеек, содержащих информацию, то ячейка, содержащая значение искомого (целевого) показателя, называется целевой. К задачам оптимизации относятся задачи, в которых требуется максимизировать или минимизировать значение некоторой функции и найти соответствующие значения переменных, от которых она зависит. В классическом случае, когда целевая функция дифференцируема, был разработан метод, связанный с нахождением нулей первой производной целевой функции и проверки критериев экстремума. Этот метод, получивший название аналитического способа решения задач оптимизации, в ряде случаев подходит и для решения экономических задач. Целевой, или критериальной, функцией задачи оптимизации называется произвольная оценочная функция, предназначенная для количественного сравнения альтернатив с целью выбора наилучшей. Целевая функция определяется как некоторая математическая функция, функционал или оператор, что в общем случае записывается в виде:

f(х1, х2, …, хn), f: D(f)→R1

В зависимости от свойств функции f(х1, х2, …, хn) рассматриваемые в задачах оптимизации делятся на следующие основные типы целевых функций: • линейные функции f(х1, х2, …, хn), являющиеся линейными относительно всех своих переменных; • нелинейные функции f(х1, х2, …, хn), являющиеся нелинейными относительно некоторых своих переменных. В последнем случае иногда дополнительно рассматривают:

□ выпуклые (квадратичные), в которых функция функции f(х1, х2, …, хn) является выпуклой (соответственно, квадратич ной) относительно своих переменных;

□ невыпуклые, в которых функция функции f(х1, х2, …, хn) не является выпуклой относительно своих переменных.

В зависимости от количества целевых функций рассматриваются два основных типа задач оптимизации:

• однокритериальные задачи оптимизации, в математических моделях которых присутствует единственная целевая функция;

• многокритериальные задачи оптимизации, в математических моделях которых присутствуют несколько целевых функций.

В контексте математической модели задач оптимизации требование нахождения наилучшего решения конкретизируется в требование максимизации или минимизации целевой функции. Данное требование записывают символически в виде:

f(х1, х2, …, хn)→max или f(х1, х2, …, хn)→min

При этом максимум (минимум) целевой функции находится на множестве допустимых значений переменных. Решением задачи оптимизации является некоторый допустимый набор значений переменных, который доставляет максимальное или минимальное значение целевой функции. С учетом введенных обозначений общая математическая модель однокритериальной задачи оптимизации может быть записана символически в следующем виде:

f(х1, х2, …, хn)→max или f(х1, х2, …, хn)→min

m

D(f)={( х1, х2, …, хn)│ Λ g k ( x1 , x 2 ,…, x n ) ≤ 0 },

k =1

где m – число ограничений. Здесь через (х1, х2, …, хn) обозначено множество допустимых альтернатив, которое формируется посредством сужения исходного множества альтернатив D с помощью совокупности ограничений, записанных в произвольной форме в виде: gk (х1, х2, …, хn) ≤ 0 или gk (х1, х2, …, хn) = 0 В качестве исходного множества альтернатив D выступает одно из рассмотренных ранее множеств: множество действительных чисел R1 , множество целых чисел Z1 или множество из двух чисел: 0 и 1. Выбор этого множества определяется типом переменных, которые используются в постановке соответствующей задачи оптимизации. В случае n = 2 соответствующие задачи оптимизации называются двухкритериальными, при n = 3 – трехкритериальными и т.д. Натуральное число m определяет общее количество ограничений задачи оптимизации. В математических моделях типовых задач оптимизации явно указывают исходные множества альтернатив для точной спецификации типа переменных. Рассмотренные свойства базовых компонентов математической модели задач оптимизации позволяют выполнить общую классификацию этих задач, знание которой необходимо для правильного анализа и выбора метода для решения конкретных задач оптимизации. В общем случае процесс постановки и решения задач оптимизации может быть представлен в форме взаимосвязанных этапов, на каждом из которых выполняются определенные действия, направленные на построение и последующее использование информационно-логических моделей систем (см. рис. 3).

Характерной особенностью данного процесса является его циклический, или итеративный характер, который отражает современные требования к анализу и проектированию сложных систем. Отдельными этапами процесса постановки и решения задач оптимизации являются: 1. Анализ проблемной ситуации. 2. Построение математической модели. 3. Анализ модели. 4. Выбор метода и средства решения. 5. Выполнение численных расчетов. 6. Анализ результатов расчетов. 7. Применение результатов расчетов. 8. Коррекция и доработка модели.

Анализ проблемной ситуации

Построение экономико-математической модели

Анализ модели Коррекция и доработка модели

Выбор метода и средства решения

Метод решения задачи не существует

Метод решения задачи

существует 2

Выполнение численных расчетов

Анализ результатов расчетов

Результаты не удовлетво ряют требованиям

Применение результатов расчетов

Исходная проблема не решена

Исходная проблема решена 4

Рис. 3. Общая схема процесса постановки и решения задач оптимизации Условные обозначения к схеме: 1 – Логическая проверка возможности построения модели. 2 – Проверка существования решения задачи. 3 – Соответствуют ли результаты расчетов требованиям. 4 – Проверка возможности применения результатов расчетов для решения исходной проблемы. Конкретное содержание этапов зависит от специфических особенностей решаемых задач оптимизации в той или иной проблемной области. При этом каждый новый цикл процесса постановки и решения задачи инициируется этапом анализа проблемной ситуации, в чем проявляется реализация требования проблемно-ориентированного подхода к построению и использованию информационно-логических моделей систем для решения задач оптимизации. Одним из основных принципов системного моделирования является проблемная ориентация процессов построения и использования моделей. Другими словами, та или иная модель конкретной системы строится в контексте решения некоторой проблемы или достижения некоторой цели. Главное назначение первого этапа – логическое осмысление конкретной проблемы в контексте методологии системного моделирования. При этом выполняется анализ всех доступных ресурсов (материальных, финансовых, информационных и других), необходимых для построения модели, ее использования и реализации полученных результатов с целью решения имеющейся проблемы. В случае отсутствия требуемых ресурсов на данном этапе может быть принято решение либо о сужении (уменьшении масштаба) решаемой проблемы, либо вообще об отказе от использования средств системного моделирования. На этом этапе также выполняется анализ требований, предъявляемых в той или иной форме к результату решения проблемы. Первоначальный анализ решаемой проблемы и соответствующей проблемной области является наименее формализуемым с точки зрения использования известных аналитических подходов и средств. Поэтому на данном этапе рекомендуется применять так называемые эвристические, или неформальные, методы системного анализа, к которым относятся: • построение логических сценариев или повествовательных историй на естественном языке для анализа возможных способов и альтернативных путей решения проблемы; • мозговая атака или штурм для генерации новых идей и нестандартных подходов к решению проблемы; • морфологический и концептуальный анализ для достижения требуемой полноты рассмотрения исходной проблемы; • построение и анализ дерева целей и задач, которые позволяют разбить исходную проблему на ряд более частных или более простых подпроблем.

Общая классификация задач оптимизации представлена в таблице 39.

Таблица 39

Классификация задач оптимизации Характеристика Характеристи- Характеристи- Класс задач оптимизации переменных ка ограничений ка целевой

функции Непрерывные Линейные Одна, линейная Линейное программирова ние Непрерывные Нелинейные Одна, Нелинейное программиро или линейные нелинейная вание Целочисленные Линейные Одна, линейная Целочисленное программи рование Целочисленные Нелинейные Одна, нелиней- Целочисленное нелинейное

или линейные ная программирование Булевы Линейные Одна, линейная Булево программирование Булевы Нелинейные Одна, Булево нелинейное про или линейные нелинейная граммирование Непрерывные Линейные Несколько, Многокритериальное ли линейные нейное программирование Непрерывные Нелинейные Несколько, Многокритериальное нели или линейные нелинейные нейное программирование Целочисленные Линейные Несколько, Многокритериальное цело линейные численное программирова ние Целочисленные Нелинейные Несколько, Многокритериальное цело или линейные нелинейные численное нелинейное про граммирование Булевы Линейные Несколько, Многокритериальное буле линейные во программирование Булевы Нелинейные Несколько, Многокритериальное буле или линейные нелинейные во нелинейное программи рование

При решении задач оптимизации необходимо найти наилучшее решение из всех допустимых. Формализация оценочной функции в форме целевой функции математической модели и ограничивающих условий в форме ограничений позволяет также дать строгое определение понятию «наилучшее решение». Таковым является оптимальное решение. В общем случае под оптимальным решением однокритериальной задачи оптимизации в математической постановке понимается такой набор значений переменных х1, х2, …, хn, ∈ D(f), которые доставляют максимум (минимум) целевой функции f(х1, х2, …, хn) среди всех допустимых решений множества D(f).

Другими словами, характерным признаком оптимального решения задачи оптимизации является выполнение следующего условия:

∀x1 , x2 ,…, xn ∈ D( f ) f ( x1* , x2* ,…, xn* ) ≥ f ( x1 , x2 ,…, xn ) или

∀x1 , x2 ,…, xn ∈ D( f ) f ( x1* , x2* ,…, xn* ) ≤ f ( x1 , x2 ,…, xn ) .

При этом первое условие должно выполняться для задач максимизации, а второе – для задач минимизации. Говоря о решении той или иной задачи оптимизации, всегда понимают нахождение ее оптимального решения, которое соответствует понятию наилучшего решения в содержательной постановке. Обобщая задачи максимизации и минимизации, часто говорят о нахождении экстремума задачи оптимизации, а саму теорию решения задач оптимизации называют теорией решения экстремальных задач.

5.2. Разработка компьютерной модели для

решения задач оптимизации Изо всех методов решения задач оптимизации с конечным множеством допустимых альтернатив имеется один, представляющий собой универсальное средство решения задач любого класса. Это метод полного перебора и сравнения альтернативных возможностей с целью выбора наилучшего решения. Однако выполнение полного перебора при большом числе вариантов может потребовать значительного времени и других ресурсов. Поэтому разработчики соответствующих компьютерных программных пакетов, как правило, используют приближенные методы поиска оптимальных решений. Значит, у нас нет достаточных оснований считать полученное с помощью компьютерной программы решение действительно наилучшим изо всех возможных, оно лишь будет близким к оптимальному. Одним из главных этапов решения задач оптимизации является построение модели. Модель – это специально подобранный объект, который имеет с реальным объектом некоторые общие свойства, интересующие исследователя. Модели бывают натуральные и знаковые. Натуральная модель – это реальный (физический, биологический, химический и другой) объект, характеристики которого изменяются по тем же законам, по которым изменяются показатели экономической системы. Знаковая модель состоит из графических объектов (схемы, графики, символы, формулы и т.д.), связанных определенными правилами и преобразованиями. Математическая (знаковая) модель составляется на языке математики с использованием математических законов и правил. Цели и задачи построения компьютерной модели: • исследование и изучение на моделях экономических процессов и законов; • предсказание последствий принимаемых решений; • автоматизация расчетов в проектировании, прогнозировании, планировании, управлении, подготовке решений. Моделируемые цели и критерии субъектов экономики (например, экономистов или менеджеров): • максимизация прибыли, рентабельности; • снижение затрат; • минимизация налогов; • обеспечение устойчивости в нестабильной среде и другие. Разработка модели решения проблемы включает следующие этапы: 1. Определение объекта моделирования. 2. Изучение внешней среды объекта. 3. Характеристика системы управления объектом. 4. Детализация описания подсистем и элементов модели. В общем случае формально-логическая модель системы разрабатывается для получения некоторой новой информации о системеоригинале с целью решения исходной проблемы. При решении задач оптимизации для этой цели строится некоторая экономикоматематическая модель, анализ которой предполагает установление характерных свойств отдельных элементов этой модели. Такими элементами являются: переменные, ограничения, целевая функция модели и множество допустимых наборов значений переменных.

Тема 6. Некоторые приемы

решения задач оптимизации

6.1. Задачи оптимизации без ограничений В MS Excel задачи оптимизации без ограничений решаются с помощью инструмента Подбор параметра. Запуск осуществляется через меню Сервис команду Подбор параметра.

Задача 27. Расчет прибыли от продаж (файл задача27.xls).

Задано количество выпускаемых изделий и затраты, на основе чего рассчитываются доход, себестоимость и прибыль. На основе данных, представленных в таблице 40, требуется получить прибыль 50 000, изменяя цену единицы. Примечание. Так как для реализации решения используется рабочий лист Excel, то сохраняются обозначения столбцов и адреса ячеек программы. В столбце А приведены количественные значения показателей, в столбце С заданы формулы для расчета.

Таблица 40

Исходные данные Столбцы/

А В С Строки 1 1 000 Количество Формулы для расчета

значений

2 200 000 Доход (=А1*А8)

3 130 000 Себестоимость (=А1*А9)

4 50 000 Другие затраты

6 20 000 Прибыль (=А2-А3-А4)

8 200 Цена единицы

9 130 Себестоимость единицы

Порядок выполнения задания: 1. Встать на целевую ячейку. 2. Выбрать меню Сервис-Подбор параметра и ввести значение ожидаемой прибыли 50 000. 3. Для ввода адреса изменяемой ячейки перейти в нижнее поле и встать в нужную ячейку (здесь А8) – адрес будет записан автоматически.

Задача 28. Формирование подарочных наборов исходя из количества каждой номенклатуры (файл задача28.xls).

Закупить составляющие (конфета карамельная, конфета шоколадная, упаковка печенья и мармелада) для комплектования подарочных наборов так, чтобы цена набора не превышала 100 руб. Рассчитать закупочные цены для разных комплектов. Известны соотношения цен одной из компонент (здесь карамели): а) цена шоколадной конфеты в 2,5 раза выше цены карамели; б) цена печенья на 10 руб. больше цены карамели; в) цена мармелада в 8,5 раза выше цены карамели. В наборе должно быть: а) 5 – 10 карамелей; б) 4 – 6 шоколадных конфет; в) 1 – 2 упаковки печенья; г) 1 упаковка мармелада. При решении задачи будем ориентироваться на два количественных набора: максимальный и минимальный. Необходимые данные приведены в таблице 41.

Таблица 41

Исходные данные 1 A B C D 2 Составляющие Цена, руб. В наборе, шт. Сумма 3 Конфета карамель 2,25 10 22,50 4 Конфета шоколадная 5,625 6 33,75 5 Печенье 12,25 2 24,50 6 Мармелад 19,125 1 19,13 7 Стоимость набора = 100 Порядок выполнения задания: 1. Выбрав в качестве целевой ячейки D7, выполните подбор параметра. 2. Установить в ячейке D7 значение 100, изменяя значение ячейки В3.

Результаты решения приведены в таблицах ниже:

Таблица 42

Максимальный набор

Составляющие Цена, руб. Количество, шт. Сумма, руб.

Конфета карамель 2,2535211 10 22,54

Конфета шоколад ная 5,6338028 6 33,80

Печенье 12,253521 2 24,51

Мармелад 19,15493 1 19,15

Стоимость набора = 100

Таблица 43

Минимальный набор

Количество, Составляющие Цена, руб. Сумма, руб.

шт. Конфета карамель 3,6734694 5 18,37 Конфета шоколадная 9,1836735 4 36,73 Печенье 13,673469 1 13,67 Мармелад 31,22449 1 31,22 Стоимость набора = 100

6.2. Решение задач оптимизации

со многими неизвестными

Если целевая функция и ограничения линейны, то решение задачи состоит в нахождении множества чисел (х1, х2, …, хn), минимизирующих (максимизирующих) линейную целевую функцию f(х1, х2, …, хn) = с1х1 + с2х2 + …+ сnхn при m

Задача 29. Максимизация стоимости производства (файл задача29.xls).

Ателье шьет комбинезоны трех типов: К1, К2, К3 и использует ткани четырех типов Т1, Т2, Т3, Т4. Нормы расхода ткани каждого типа на каждый комбинезон и объем дневных затрат приведены ниже. Стоимость пошива комбинезона типа К1 равна 100 руб., К2 – 120 руб., К3 – 110 руб. Дневной запас тканей в ателье: Т1 – 50 м, Т2 – 80 м, Т3 – 25 м, Т4 – 60 м. Требуется найти ежедневный объем выпуска комбинезонов каждого типа для максимальной стоимости производства.

Ограничения

Нормы расхода, м Расход неравенства

К1 К2 К3 в день, м

1 2 1 50 0

2 1,5 3 80 0

0,5 1 0,5 25 0

3 1 0,5 60 0

Целевая

х1 х2 х3 функция

0 0 0 0

Рис. 4. Схема расположения исходных данных на рабочем листе Excel

Ограничения

Нормы расхода, м Расход в день, неравенства

К1 К2 К3 м

1 2 1 50 49

2 1,5 3 80 80

0,5 1 0,5 25 24,5

3 1 0,5 60 57

х1 х2 х3

13 12 12 4 060

Рис. 5. Схема расположения полученного решения на рабочем листе Excel

Для проведения расчетов полезно сохранять и при необходимости вызывать варианты вычислений. Для этого используют сценарии – наборы значений, которые сохраняются с некоторым именем и могут подставляться на листе:

Сервис – Сценарии…

Имеется также возможность вывода Отчета сценариев, по которому можно проследить зависимости между данными.

6.3. Примеры типовых задач оптимизации Понятие типовой задачи оптимизации является в некоторой степени условным и определяется исключительно соображениями удобства и анализа классификации соответствующих задач. Рассмотрим 3 вида типовых задач: решаемых в быту, в сфере производства и услуг.

А. Задачи индивидуального планирования.

Задача 30. Задача об оптимальной диете (файл задача30.xls).

Имеется конечное число видов продуктов питания, в которых содержится конечное число типов питательных веществ, например, белки, жиры, углеводы:

Таблица 44

Исходные данные, г/кг

Продукты/ Питательные Огурвещества Хлеб Мясо Сыр Банан цы Помидоры Виноград Белки 61 220 230 15 8 11 6 Жиры 12 172 290 1 1 2 2 Углеводы 420 0 0 212 26 38 155

В каждом виде продуктов питания содержится известное количество питательных веществ каждого из типов. Задана минимальная суточная потребность человека в каждом из видов питательных веществ:

Таблица 45

Калорийность каждого продукта, ккал/кг Продукты/ Питательные Огурвещества Хлеб Мясо Сыр Банан цы Помидоры Виноград Калорийность 2 060 2 430 3 600 890 140 230 650

Требуется определить такой состав рациона питания, чтобы каждое питательное вещество содержалось в нем в необходимом количестве, обеспечивающем суточную потребность человека, и при этом суммарная калорийность рациона была минимальной. Оценочная функция – суммарная калорийность рациона. Ограничения – минимальная суточная потребность человека в каждом из видов питательных веществ: белках – 100, жирах – 70, углеводах – 400 г. Б. Задачи, решаемые в сфере услуг.

Задача 31. Формирование экскурсионных пакетов (файл задача31.xls).

Ежедневно направляется в отели Анталии – 30 человек, Кемера – 20, Мармариса – 16. Считается, что все они должны побывать на трех предлагаемых экскурсиях: рафтинг, яхт-тур, джип-сафари. Существуют ограничения на количество человек в туре: рафтинг – 25, яхт-тур – 20, джип-сафари – 30. Количество туристов из каждого отеля на каждую экскурсию должно быть не менее 5. Стоимость тура для каждого туриста зависит от места проживания и представлена в таблице 46:

Таблица 46

Зависимость стоимости тура от отеля, дол.

Яхт- Джип Отель Рафтинг тур сафари

Анталия 55 20 35

Кемер 65 35 20

Мармарис 60 25 25 Требуется минимизировать стоимость экскурсионной программы для всех туристов. С учетом данных таблицы целевая функция будет иметь вид: 55х1 + 20х2 + 35х3 + 65х4 + 35х5 + 20х6 + 60х7 + 24х8 + 25х9.

В. Задачи производственного планирования.

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

Задача 32. Производство красок (файл задача32.xls).

Производственное предприятие выпускает 2 вида краски, одна предназначена для внутренних работ, другая – для наружных. Для производства этих видов краски используется 3 типа исходных красителей и химических веществ – индиго, железный купорос и свежегашеная известь.

Таблица 47 Расход красителей для получения каждого вида краски, кг

Красители/ Внутренние

Виды красок работы Наружные работы

Индиго 0,1 0,2

Железный купорос 0,2 0,1

Известь 0,15 0,05

Стоимость каждого вида краски измеряется в руб.\кг. Запасы исходных красителей на складе предприятия ограничены следующими значениями: индиго – 10, железный купорос – 7, известь – 5 кг. Стоимость каждого вида краски для оптовых покупателей для внутренних работ – 250, для наружных – 230 руб. Выполнить постановку задачи, введя систему необходимых ограничений.

Задачи для самостоятельного решения

по темам 5 – 6 1. Задача о производстве клея. Производственное предприятие выпускает 3 вида клея. Для производства клея используется 4 типа химических веществ: крахмал, желатин, квасцы и мел. Расход этих веществ для получения 1 кг каждого вида клея и их запас на складе предприятия представлены в таблице:

Таблица 48

Расход веществ для получения каждого вида клея, кг Химические Вид клея вещества Клей 1 Клей 2 Клей 3 Клей 4 Крахмал 0,4 0,3 0,2 20 Желатин 0,2 0,3 0,4 35 Квасцы 0,005 0,07 0,1 7 Мел 0,01 0,05 0,15 10

Стоимость каждого вида клея для оптовых покупателей 380 руб./кг, 430 руб./кг, 460 руб./кг. Требуется определить оптимальный объем выпуска клея каждого вида, обеспечивающий максимум общей стоимости готовой продукции.

2. Задача о диете.

Имеется конечное число видов продуктов питания, в которых содержится конечное число типов питательных веществ, например, белки, жиры, углеводы. В каждом виде продуктов питания содержится известное количество питательных веществ каждого из типов.

Таблица 49

Питательные вещества по каждому продукту, г/кг

Пита- Продукты тельные Грейп- Сар- Карто вещества Ананас Арбуз Мясо Хлеб

фрут дельки фель Белки 4 7 9 122 114 68 20 Жиры 2 2 2 109 182 13 4 Углеводы 115 88 65 0 15 407 163

Задана минимальная суточная потребность человека в каждом из видов питательных веществ:

Таблица 50

Калорийность каждого продукта, ккал/кг

Продукты Питательные

Грейп- Сар- Карто вещества Ананас Арбуз Мясо Хлеб

фрут дельки фель Калорийность 470 380 350 1 460 2 150 2 070 800

Требуется определить такой состав рациона питания, чтобы каждое питательное вещество содержалось в нем в необходимом количестве, обеспечивающем суточную потребность человека, и при этом суммарная калорийность рациона была минимальной. Предлагается решить задачу, добавив дополнительные ограничения на потребление определенных продуктов, например, мясных или хлеба. Целевая функция – суммарная калорийность рациона. Ограничения – минимальная суточная потребность человека в каждом из видов питательных веществ: белках – 100, жирах – 70, углеводах – 400 г.

Литература

1. Коцюбинский, А.О. Excel для бухгалтера в примерах / А.О. Коцюбинский, С.В. Грошев. – М.: Вершина, 2004. – 240 с.

2. Куправа, Т.А. Excel. Практическое руководство / Т.А. Куправа. – М.: Диалог-МИФИ, 2004. – 240 с.

3. Леоненков, А.В. Решение задач оптимизации в среде MS Excel / А.В. Леоненков. – СПб.: БХВ-Петербург, 2005. – 704 с. 4. Никольская, Ю.П. Excel в помощь бухгалтеру и экономисту / Ю.П. Никольская, А.А. Спиридонов. – М.: Вершина, 2006. – 256 с. 5. Винстон, У.Л. Microsoft Excel: анализ данных и построение бизнес моделей / У.Л. Винстон; пер. с англ. – М.: Русская Редакция, 2005. – 576 с.

Учебное издание

Зеткина Оксана Валерьевна

Решение экономических задач оптимизационными методами

Методические рекомендации

Редактор, корректор О.Н. Скибинская

Компьютерная верстка Е.Л. Шелеховой

Подписано в печать 16.10.2006 г. Формат 60х84/16. Бумага тип. Усл. печ. л. 3,95. Уч.-изд. л. 2,49.

Тираж 100 экз. Заказ

Оригинал-макет подготовлен в редакционно-издательском отделе ЯрГУ.

Отпечатано на ризографе.

Ярославский государственный университет.

150000 Ярославль, ул. Советская, 14. 68

140 стоимость функции

100

60

20

0 Ручной Подбор, Передача Сортиров. Возврат Передача

Согласно Контроль Выписка, Сбор Загрузка Доставка,

ввод, кор- копир., пакета заказа по док-тов и пакета

заказа в взаимо- печать заказа на в авто- выгрузка

ректир. сорт. док- док-тов адресу некондици док-тов

магазине расчетов док-тов складе транспорт товара

заказа тов на склад доставки и на склад бухгалт. стоимость функции 152 150 18 30,4 48 78 39 18 18 150 90 78

Рис. 1. Операционно-стоимостной анализ процесса оформления, доставки заказа

О.В. Зеткина

Решение экономических задач оптимизационными методами