Практическая часть Постановка задачи

По данным таблицы 4 рассчитать прибыль от продажи изделий за квартал, итоговую прибыль и выполнить графический анализ вклада в общую прибыль предприятия каждого изделия (с учетом коэффициента реализации, равного 0,2).

Таблица 4 – Исходные данные задачи «Реализация изделий»

Наименование	Выпуск, шт.			Цена, р.
	Январь	Февраль	Март
Изделие 9/001	700	650	666	299,00
Изделие 9/002	310	217	222	199,00
Изделие 9/003	122	129	156	150,00

Ход работы

1. Открываем MS Excel и сохраняем пустую рабочую книгу в свою папку под именем "laba_5.xls".

2. Разбиваем рабочую книгу на отдельные листы с необходимыми именами; у нас листы называются "Исходные данные" и "Расчет".

3. Заносим исходные данные на лист "Исходные данные" и решаем вопрос о типе данных, их формате и расположении (см. рисунок 17). Оформление таблицы производим, используя пункт меню Формат/Формат ячеек, вкладки Число и Выравнивание. Для ввода наименований изделий и названий месяцев используем автозаполнение.

Рисунок 17 – Рабочая книга "laba_5.xls". Лист "Исходные данные"

4. Скопируем таблицу на лист "Расчет" и добавим столбцы "Всего", "Выручка" и "Прибыль" (см. рисунок 18).

Рисунок 18 – Рабочая книга "laba_5.xls". Лист "Расчет"

5. Данные в этих столбцах рассчитаем по формулам:

Всего=Сумма(Январь,Февраль,Март);

Выручка=Всего*Цена;

Прибыль= Выручка *Коэффициент реализации.

Значение итоговой прибыли по всем изделиям рассчитывается в ячейке H8 с помощью операции автосуммирования значений в ячейках H5:H7 или при использовании функции =СУММ(H5:H7). Формулы для расчетов представлены на рисунке 19.

Рисунок 19 – Расчетные формулы

6. Для проведения графического анализа, показывающего вклад изделий в суммарную прибыль предприятия, построим диаграмму, пользуясь услугами Мастера диаграмм. Для нашего примера на рисунке 20 построена круговая диаграмма, на которой изображена процентная доля вклада каждого изделия в общую сумму прибыли.

Рисунок 20 – Диаграмма "Прибыль от реализации изделий"

На основании графического анализа формулируем вывод о том, что наибольший вклад (74%)в общую прибыль предприятия вносит изделие 9/001; наименьший вклад (8%) – изделие 9/003; средний вклад (18%) – изделие 9/002.

Варианты заданий для самостоятельной работы

Задача 1. «Меховое ателье»

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

Исходные данные: наименования изделий; количество пошитых изделий за каждый квартал; отпускная цена; норма чистой прибыли (коэффициент реализации).

Задача 2. «Книжная база»

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

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

Задача 3. «Склад»

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

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

Задача 4. «Банк»

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

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

Практическая работа 6

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

Цель работы

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

Теоретическое введение

В электронных таблицах (ЭТ), помимо базовых операций с данными, можно также эффективно обрабатывать и исследовать результаты, то есть решать задачи оптимизации и анализа решений.

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

В табличном процессоре MS Excel используют два способа оптимизации и анализа данных:

• решение экономических задач типа "что надо, чтобы" методом "Подбор параметра";

• анализ экономических решений по типу "что будет, если" с помощью технологии "Поиск решения".

Оптимизация данных методом "Подбор параметра" применяется, как правило, для решения задач, в которых исследуется выходное значение, наиболее значимое для нас (целевая функция), за счет изменения определенного параметра. В этом случае на изменяемый параметр не вводятся ограничения, а величина оптимального значения устанавливается постановщиком задачи. Таким способом можно, например, "улучшить" прибыль до желаемого фиксированного значения за счет изменения объема выпуска или цены продажи до неограниченной величины. Как следствие, оптимизация по технологии "Подбор параметра" ограничена и далека от реальности.

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