Введение.
Цель контрольной работы: Получение практических навыков автоматизации задач анализа и оптимального планирования ресурсов.
Задача контрольной работы: Определить оптимальный план поставки товара на оптовые базы при минимальных суммарных транспортных издержках.
Решение.
Исходные параметры модели
n – 4 – количество предприятий-представителей.
m – 5 – количество потребителей – оптовых баз.
a¡ - ежемесячные объемы предложения продукции 
(i=1.4) [шт]
– спрос на продукцию (емкость оптовых баз) 
(i=1,5) [шт]
- затраты на производство и поставку продукции от представителей на оптовые базы потребителя [руб/шт.]
больше 
(суммарное предложение больше суммарного спроса)
или меньше (суммарное предложение меньше суммарного спроса)
или равно (суммарное предложение равно суммарному спросу, сбалансированная модель).
Исходные параметры модели
– количество продукции, перевозимой из пункта отправления 
и пункт назначения 
[шт]
- целевая функция – транспортные расходы на перевозку всей продукции [руб.]
Экономико-математическая модель 
f(X)= 
cij xij min,
xij = ai, i = 1,m,
xij = bj, j = 1,n,
xij ≥ 0, j = 1,n.
Таблица №1. Параметры перевозок от представителей к оптовым базам.
Пояснительная записка
Основная часть
Описываем экономико-математическую модель транспортной задачи.
Исходные параметры модели
n – 4 – количество предприятий-представителей.
m – 5 – количество потребителей – оптовых баз. a¡ - ежемесячные объемы предложения продукции (i=1.4) [шт]
– спрос на продукцию (емкость оптовых баз) (i=1,5) [шт]
- затраты на производство и поставку продукции от представителей на оптовые базы потребителя [руб/шт.]
больше
Искомые параметры модели
– количество продукции, перевозимой из пункта отправления и пункт назначения [шт]
транспортные расходы на перевозку всей продукции [руб.]
Создаем экранную форму, вводим условия задачи, исходные данные и зависимости.
Экранная форма
| Объект математической модели | Выражение в Excel |
| Формула в целевой ячейке Н15 | =СУММПРОИЗВ(С3:G6$C12:G15) |
| Ограничения по строкам в ячейках Н3; Н4; Н5; Н6. | = СУММ(C3:G3) =СУММ(C4:G4) =СУММ(C5:G5) =СУММ(C6:G6) |
| Ограничения по столбцам С7; D7; E7; F7; G7. | =СУММ(C3:C6) =СУМ(D3:D6) =СУММ(E3:E6) =СУММ(F3:F6) =СУММ(G3:G6) |
| Суммарные запасы и потребности в ячейках J8; I9. | =СУММ(J3:J7) =СУММ(C9:H9) |
| Проверка баланса в ячейке J9 | =ЕСЛИ (I9=J8;”БАЛАНС”;ЕСЛИ;I9>J8;”ПРЕДЛОЖЕНИЕ БОЛЬШЕ”;”ЗАПАСЫБОЛЬШЕ”)) |
Задание.
На пять оптовых баз поставляется продукция от четырех представителей. Ежемесячное предложение продукции представителями (шт.), емкость оптовых баз (шт.) и тарифы (руб./шт.) за доставку продукции от представителей к оптовым базам приведены в таблице №1. Решить задачу определения транспортного плана поставки данного товара на оптовые базы так, чтобы суммарные транспортные издержки были минимальны.
Параметры перевозок от представителей к оптовым базам.
| Тарифы,руб/шт. | Оптовая база 1 | Оптовая база2 | Оптовая база 3 | Оптовая база 4 | Оптовая база 5 | Предложение, шт. |
| Представитель 1 | ||||||
| Представитель 2 | ||||||
| Представитель3 | ||||||
| Представитель4 | ||||||
| Емкость шт. |
Решаем задачу с помощью инструмента «Писк решений».
Установить курсор в поле «Установить целевую ячейку»
Ввести адрес целевой ячейки (нажали левой клавишей мыши на целевую ячейку в экранной форме).
Ввели направление оптимизации ЦФ (щелкнув 1 раз левой клавиши мыши по секторной кнопке «минимальному значению»
В окне «Поиск решения» в поле «изменения ячейки» впишем адреса
Необходимые адреса можно вносить в поле «изменяя ячейки» и автоматически путем выделения мышью соответствующих ячеек переменных непосредственно в экранной форме.
Ввести ограничения модели. В нашем случае на значения переменных накладывается граничное условие равенства.
Нажали кнопку «параметры» и заполнили некоторые поля окна «параметры поиска решения»
После запуска решения появилось «поиск решения». На данном сообщении показано что поиск не может найти подходящего решения.
После того как мы нажали кнопку выполнить, мы получаем ответ на данную задачу.
Заключение
Результат решения: Емкость оптовых баз больше предложений поставщиков. В данных условиях невозможно определить оптимальный план поставки товара на оптовые базы.
Вывод: Чтобы удовлетворить потребности оптовых баз необходимо привлечь дополнительных поставщиков с предприятий-представителей для того, чтобы разделить транспортные издержки и сделать их минимальными. Либо совсем отказаться от собственных поставок и воспользоваться услугами сторонних транспортных компаний.
Список литературы.
1. Должнеков В.А., Колесников Ю.В. Microsoft Excel 2003. – СПб.:ИНV Спб,2006
2. Мак-Федрис П\ Формулы и функции в MicrosostExcel2003. – М.:Вильямс,2006
3. Информационные системы в экономике: практикум / кол. Авторов по общ.ред.П.В.Аникина. – М.: КНОРУС,2008