Моделирование в Excel. Социально-экономический профиль

Практическое задание на тему Компьютерное моделирование

1. Линейное планирование

Надстройка Поиск решения позволяет отыскать оптимальное решение из множества возможных.

Задача: Дано:

предприятие производит продукцию трех видов

используются комплектующие детали 5 наименований

количество комплектующих ограничено и известно;

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

известна прибыль от производства продукции каждого вида.

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

Данные задачи:

Математическая модель задачи:

Сумма прибыли должна быть максимальна, т.е.

1. 2Х₁+3Х₂+5Х₃ = MAX

II. Количество деталей ограничено:

2. 2Х₁+2Х₂+Х₃<=10

3. Х₁+2Х₂<=7

4. 4Х₁+Х₂+Х₃<=12

5. 3Х₁+Х₂<=15

6. Х₁+2Х₂+3Х₃<=20

Значения Х₁, Х₂, Х₃ >0;

Значения Х₁, Х₂, Х₃ - целое

Рис 1. Ввод данных в таблицу

Выполнение:

1. Ввести данные в таблицу (Рис. 1), где в ячейки А9:А13 ввести формулы для ограничения 2-6.

2. Выделить ячейку В4, задать командуСервисПоиск решения. В окне Поиск решения (Рис.2) в пункте Установить целевую ячейку должен быть адрес В4, включен переключатель Равен максимальному значению

Рис.2. Установка параметров в окне Поиск решений

3.Чтобы ввести адреса ячеек в пункт Изменяя значения, щелкнуть красную стрелочку, выделить блок ячеек В1:В3, снова щелкнуть красную стрелочку.

4. Чтобы ввести адреса ячеек в пунктОграничения, выполните следующий ряд действий:

1 .щелкнуть кнопку Добавить, откроется окноДобавление ограничений (Рис. 3)

2. щелкнуть красную стрелку в пунктеСсылка на ячейку;

3. выделить ячейку с формулой для ограничения, щелкнуть красную стрелку;

4. выбрать нужное условие (больше, меньше и т.д.);

5. щелкнуть красную стрелку в пункте Ограничения

6. выделить ячейку с граничным значением, щелкнуть красную стрелку;

7. щелкнуть кнопку Добавить.

Повторить все действия 1-7 столько раз, сколько ограничений следует ввести по условию задачи (пункты 2, 3, 4) в Математической модели задачи.

После ввода всех ограничений, щелкнуть кнопку Выполнить в окне Поиск решений.

2. Имитационное моделирование в программе Excel

Метод сценариев. Пример детерминированной модели

При составлении бизнес плана необходимо знать точку безубыточности производства товара, которая определяется величиной постоянных затрат на производстве Р, ценой реализации товара С и величиной переменных затрат на сырье и т.п. К. Точка безубыточности Х=Р*(С-К).

В таблице показаны варианты параметров затрат на производстве, выбрать наилучший.

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

Решение:

1. Ввести данные в таблицу

2. Задать команду Сервис, Сценарии, Добавить.

3. Ввести имя сценария (№ варианта), в качестве изменяемых ячеек указать ячейки со значениями переменных расходов В4,В7,В8, щелкнуть ОК

4. Ввести значения для 2 варианта

5. Повторить действия 2-4 для 3-го вариантов.

6. Щелкнуть кнопку Отчет, тип отчета – структура. Указать ячейку результата В18.

7. Просмотреть отчет и сделать вывод.

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

3. Моделирование с применением функций случайных чисел

Пример: Покупатель приобретает товар: №1 с вероятностью 0,7

№2 0,3.

Количество товара – равномерно распределенная случайная величина на отрезке :

для базы №1 [1000; 2000]

№2 [800; 1500]

Цена товара - равномерно распределенная случайная величина на отрезке [8; 10].

Произвести 100 имитаций для определения стоимости приобретенного товара.

Получить оценки математического ожидания стоимости товара и среднего квадратичного отклонения.

Определить, сколько раз цена товара была больше 13000.

Решение:

Для генерирования случайных значений цены воспользуемся функцией генерации случайных чисел СЛЧИС(), которая возвращает случайное число из равномерно распределенных чисел на отрезке [0;1].

Для определения количества приобретенного товара воспользуемся функцией генерации случайного целого числа СЛУЧМЕЖДУ(), которая возвращает случайное число, заключенное на заданном отрезке.

Подготовить следующую таблицу c введенным текстом и исходными значениями.

2. Введите формулы в ячейки:

Если функций СЛУЧ() и СЛУЧМЕЖДУ() нет в списке функций, включите их через командуСервис, Надстройки, Пакет анализа

3.Скопируйте формулы из строки 9 вниз до конца таблицы.

4. Построить гистограмму стоимости приобретенного товара.

Для этого – команда Сервис, Анализ данных, инструмент Гистограмма. Команду Анализ данных следует ввести через Сервис, Надстройки. ВвестиВходной интервал $E$9:$E$108,Выходной интервал - $G$9, Установить флажок Вывод графика.

5. Измените исходные значения, посмотрите результат

4. Статистическое моделирование:

Пример: Задача на очередь массового обслуживания.

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

Максимальный интервал между приходом покупателей [0,5] мин

Максимальное время обслуживания покупателей [0,10] мин.

Определить, Какое среднее время приходится стоять в очереди?

Решение:

Подготовить таблицу:

Промежуток между приходами покупателей определяется по формуле: =СЛУЧМЕЖДУ(0;8)

Длительность обслуживания: =СЛУЧМЕЖДУ(0;10)

Время прихода определяется как время прихода предыдущего покупателя плюс промежуток между приходами, т.е. D5+B6

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

Время конца обслуживания: Время начала обслуживания плюс длительность обслуживания, т.е. E6+C6

Время проведенное в магазине: Время конца обслуживания минус время прихода

2. введите в строку 5 нулевые значения, в строку 6 следующие формулы:

3.Подсчитайте среднее время ожидания в очереди и среднее время ожидания покупателя.

Постройте гистограмму распределения времени ожидания в очереди и время ожидания покупателя.

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

Адрес публикации: https://www.prodlenka.org/metodicheskie-razrabotki/266598-modelirovanie-v-excel-socialno-jekonomicheski