Лабораторная работа ’1 3 курс 6 семестр Подбор параметра Задача оптимизации затрат Задача 1 С помощь
Работа добавлена на сайт samzan.net:
Лабораторная работа №1
3 курс 6 семестр
Подбор параметра
Задача оптимизации затрат
Задача 1 С помощью подбора параметров можно решать задачи оптимального распределения ресурсов следующего плана. Требуется закупить составляющие (конфета карамельная, конфета шоколадная, упаковка печенья и мармелада) для комплектования подарочных наборов так, чтобы цена набора не превышала 100 руб. При этом известны соотношения цен относительно одной из компонент (здесь карамели): цена шоколадной конфеты в 2.5 раза выше цены карамели, цена печенья на 10 руб. больше карамели и цена мармелада в 8.5 раза выше цены карамели. В наборе должно быть 5-10 конфет карамель, 4-6 шоколадных конфет, 1-2 упаковки печенья и 1 упаковка мармелада. Рассчитать закупочные цены для разных комплектов.
Ниже показаны расчеты закупочных цен для максимального и минимального комплектов:
В ячейки В4:В6 запишите формулы: =B3*2.5, =B3+10 и =B3*8.5. В колонки D3:D6 поставьте формулы вычисления сумм по строкам, а в D7 вычислите общую сумму: =СУММ(D3:D6). В итоге целевая функция – стоимость набора D7 есть C3*x+C4*2.5*x+C5*(x+10)+C6*8.5*x, где x=B3 цена одной компоненты – конфеты карамель. Параметры инструмента Подбор параметра заполните так:
Инструмент Подбор Параметра из меню Сервис позволяет найти значение аргумента, удовлетворяющее желаемому значению функции. С его помощью можно получить результаты , которые трудно или невозможно получить непосредственно.
Задача 2 Предположим необходимо распределить премию среди сотрудников. Каждому сотруднику начисляется определенный процент от начисленного. Требуется определить какой процент премии необходимо установить, чтобы уложиться в премиальный фонд 15000р.
Первоначально установим премию равной 10% и выполним необходимые вычисления
Сделайте активной ячейку D17
Воспользуйтесь командой меню Сервис - Подбор Параметра
В поле ввода Значение введите с клавиатуры сумму премиального фонда
Установите курсор в поле ввода Изменяя значение ячейки и щелкните по ячейки B2
После нажатия кнопки OK отобразиться окно Результат подбора параметра, где отображаются ожидаемые результаты операции.
Если согласны с результатом , нажмите кнопку OK
Конечно, с помощью этого средства можно решать более интересные и сложные задачи.
Подбор параметра и таблицы подстановки
В паре с инструментом Подбор параметра обычно применяют инструмент Таблица подстановки, который позволяет расширить количество одновременно рассчитываемых вариантов решений. Таблицы подстановки создаются на основе одной или двух изменяемых параметров.
Рассмотрим пример. Фирма производит изделия и продает их по цене 90руб. Ежемесячные постоянные затраты составляют 5000руб., переменные затраты на единицу изделия – 30руб. Необходимо определить точку безубыточности, т.е. вычислить количество изделий, при котором прибыль равна 0. Кроме того, требуется определить изменение прибыли для 10 следующих значений количества с шагом 5, а также прибыль при этих значениях количества для цен 80, 85, 95 и 100руб.
Решение. Введите в Excel исходные данные, приведенные ниже:
В точке безубыточности валовая прибыль равна валовым затратам, т.е. (В3*В4)-(В1+В2*В4)=0. Вызовите Подбор параметра, заполните параметры и нажмите ОК – в ячейке В4 будет вычислено значение 83.33 (рис. 17).
На следующем шаге рассчитаем 10 значений прибыли для следующих значений количества с шагом 5. Используем для этого таблицу подстановки с одним изменяемым параметром. Подготовьте исходные данные (рис. 2.35): в ячейки C4:C13 запишите значения количества с шагом 5, а в колонке справа в строке выше (ячейка D3) - формулу из ячейки В7.
Примените инструмент ^ Таблица подстановки к подготовленным данным: выделите диапазон C3:D13 (рис. 2.36), вызовите пункт ДанныеТаблица подстановки…, укажите изменяемую ячейку ($B$4) и порядок расположения исходных данных (в строках) в окне запроса – рис. 2.36. После нажатия ОК в ячейках D4:D13 будут рассчитаны значения прибыли.
На последнем шаге рассчитаем значения прибыли для тех же значений количества при ценах 80, 85, 95 и 100руб. Используем для этого таблицу подстановки с двумя изменяемыми параметрами. Результат показан на рис. 2.37.
Для его получения подготовьте исходные данные: в ячейки F4:F13 запишите значения количества, в строке G3:J3 запишите значения цен, на пересечении строки и столбца с данными в ячейке F3 запишите формулу из ячейки В7. Примените инструмент ^ Таблица подстановки к подготовленным данным: выделите диапазон F3:F13 (рис. 2.37), вызовите пункт ДанныеТаблица подстановки…, укажите изменяемую ячейку по строкам ($B$4) и по столбцам ($B$3) в окне запроса – рис. 2.38. После нажатия ОК в ячейках G4:J13 будут рассчитаны значения прибыли.
Рис. 2.35
Рис. 2.36
Рис. 2.37
Рис. 2.38
Самостоятельная работа
Сформировать рекламный бюджет на предъявленную сумму от заказчика (сумма произвольная от 1млн рублей и выше и расходы в % соотношении по статьям) Решить в Excel/ Сдать в электронном виде педагогу на следующем занятии.
2. Сексуальное учение Белой тигрицы Секреты даосских наставниц Эта книга повествует о древнем китайск
3. ТЕМА 12 ЗАБОРГОВАНІСТЬ У СИСТЕМІ МІЖНАРОДНИХ ФІНАНСІВ Світовий борг та світова економіка
4. Українська держава в період руїни (1657-1676 рр)
5. КОНТРОЛЬНАЯ РАБОТА ПО ДИСЦИПЛИНЕ ldquo;УГОЛОВНОЕ ПРАВОrdquo; Направление Юриспруденция Бакалавриат ч
6. техникалы~ факультетіні~ деканы А
7. Израиль
8. Дух русского народа
9. КСОШ ’4 им. Б.Б. Городовикова В настоящее время главной задачей образовательных учреждений России являе
10. Права жінки в шаріаті
11. Горэлектросети Тюрин А
12. Статья 1 Предмет регулирования настоящего Федерального закона Предметом правового регулирован
13. Учет кассовых операций в бухгалтерии предприятия
14. О нормативных правовых актах содержащих государственные нормативные требования охраны труда
15. Мислення
16. СанктПетербургский государственный инженерноэкономический университет Кафедра бухгалтерско
17. Задание 1 Поездка главной целью которой является потребность в решении профессиональных задач относится-
18. Ройса; Почему Наполеон проиграл в шахматы первому роботу 26 марта 2007 002
19. Учебное пособие- Коррупция и ее общественная опасность
20. Машенька г. Старый Оскол Раннее детство ~ начало жизни