Диссертация (1138535), страница 25
Текст из файла (страница 25)
рисунок Б.1). Подмодели для второго периода планирования создаются путем копирования подмодели для первого периода (см. рисунки Б.2 –Б.4).Рисунок Б.1 – Табличная модели и исходные данные рассматриваемой задачи(период 1)Рисунок Б.2 – Табличная модели и исходные данные рассматриваемой задачи(период 2, сценарий 1)164Рисунок Б.3 – Табличная модели и исходные данные рассматриваемой задачи(период 2, сценарий 2)Рисунок Б.4 – Табличная модели и исходные данные рассматриваемой задачи(период 2, сценарий 3)Подмодель для первого периода планирования (см. рисунок Б.1) содержитследующую информацию:− в диапазоне ячеек C3:E5 – значения закупочной цены трех видов товаров утрех поставщиков Pi,j;− в ячейке F3 – формула для расчета затрат на приобретение товаров=СУММПРОИЗВ(C3:E5;C6:E8);− в ячейке G3 – правая часть бюджетных ограничений Bt (в рассматриваемомпримере Bt = 100000 руб.);− в диапазоне ячеек C6:E8 – переменные Xi,j,t (количество единиц товаров,закупленных у поставщиков);− в диапазоне ячеек F6:F8 – переменные Yj,t;165− в диапазоне ячеек C9:E9 – формулы для расчета суммарного количества jго товара, закупленного у поставщиков, вида =СУММПРОИЗВ(C6:C8;$F6:$F8);− в диапазоне ячеек C10:E10 – формулы для расчета вспомогательной переменной Rj,t вида =C9-C11;− в диапазоне ячеек C11:E11 – величина продаж товаров/спроса в течениепериода Dj,t;− в диапазоне ячеек C12:E12 – величина общего запаса j-го товара на началопериода ST j,t-1;− в ячейке F12 – формула для расчета величины суммарного общего запасавсех товаров на начало периода ∑ST j,t-1 =СУММ(C12:E12);− в диапазоне ячеек C13:E13 – формулы для расчета издержек на хранениеединицы товара за период Hj вида =0,1*C3 (издержки на хранение единицы j-готовара за период принимаются в размере 10% от цены товара);− в диапазоне ячеек C14:E14 – формулы для расчета величины общего запасаj-го товара на конец периода ST j,t вида =C9-C11+C12;− в ячейке F14 – формула для расчета величины суммарного общего запасавсех товаров на конец периода ∑ST j,t =СУММ(C14:E14);− в диапазоне ячеек C15:E15 – величина максимального желательного запасаj-го товара Smaxj;− в диапазоне ячеек C16:E16 – формулы для расчета величины издержек, связанные с дефицитом товара за период Fj вида =МИН(C3:C5)*1,5*(C15-C14);− в ячейке C17 – формула для расчета общих затрат на закупки с учетом издержек на хранение товаров и штрафов за дефицит товаров за первый периодTC1=СУММПРОИЗВ(C6:E8;C3:E5)+СУММПРОИЗВ(F6:F8;G6:G8)+СУММПРОИЗВ (C13:E13;C14:E14)+СУММ(C16:E16).Подмодели для второго периода планирования, соответствующие сценариямнизкого, среднего и высокого спроса, создаются путем копирования подмодели дляпервого периода планирования.166После создания табличной модели необходимо открыть программу PremiumSolver Pro и в окне Solver Option and Model Specification указать целевую ячейку,диапазоны изменяемых ячеек, а также ограничения, как показано на рисунке Б.5.Рисунок Б.5 – Окно Solver Option and Model Specification программы PremiumSolver ProЗатем необходимо запустить отладчик Analyze without Solving, который проверяет корректность модели и автоматически определяет метод ее решения.
Результаты представлены в нижней части окна Solver Option and Model Specificationна закладке Model Diagnosis (рисунок Б.6).167Рисунок Б.6 – Закладка Model Diagnosis окна Solver Option and Model Specificationпрограммы Premium Solver ProОптимизация запускается кнопкой Solve Complete Problem or run a Simulationв правом верхнем углу окна Solver Option and Model Specification программы Premium Solver Pro (рисунок Б.7).
После завершения работы программы PremiumSolver Pro на закладку Output выводятся общие результаты работы программы инайденное оптимальное значение целевой функции. Значения переменных Xi,j,t, Yj,tи другие расчетные параметры выводятся непосредственно на рабочий лист Excel.168Рисунок Б.7 – Закладка Output окна Solver Option and Model Specification программы Premium Solver Pro169Приложение ВРезультат решения задачи об определении оптимального размера партии поставки и выбора поставщиков в условияхредкого спроса в ExcelТаблица В.1 – Результаты численного решения задачи (период 1)№Наименование показателяГазоваяколонкаOasis OR20SГазоваяколонкаBOSCHTherm4000 WR15-2ГазоваяколонкаEdisson F20 GD(Костер)Затраты наприобретение,руб.Бюджетныеограничения,руб.30037,33100000Закупочная цена товара у поставщика X, P1,j, руб.Закупочная цена товара у поставщика Y, P2,j, руб.6419,826740,8115753,5415003,387119,006795,413Закупочная цена товара у поставщика Z, P3,j, руб.7061,8016503,716471,824Кол.товаров, закупленных у поставщика X, X1,j,t, ед.0000120005Кол.товаров, закупленных у поставщика Y, X2,j,t, ед.00001000067Кол.товаров, закупленных у поставщика Z, X3,j,t, ед.∑ кол.товаров, закупленных у поставщиков, ед.111111180008Вспомогательные переменные, Rj,t, ед.0109Величина продаж товаров в течение периода, Dj,t, ед.101Сумма, ∑ST jt-11113641,981500,34647,1813 Общий запас на конец периода, ST j,t,ед.12114 Максимальный желательный запас, Smaxj, ед.43416 Издержки, связанные с дефицитом товара/период, Fj, руб.28889,1822505,0629123,1817 Общие затраты на закупки за период, TC1, руб.122844,6011 Общий запас на начало периода, ST j,t-1, ед.12 Издержки на хранение ед.
товара/период, Hj, руб.Сумма, ∑ST jt416912170Таблица В.2 – Результаты численного решения задачи при низком уровне спроса (период 2, сценарий 1)№Наименование показателяГазоваяколонкаOasis OR20SГазоваяколонкаBOSCHTherm4000 WR15-2ГазоваяколонкаEdisson F20 GD(Костер)Затраты наприобретение,руб.Бюджетныеограничения,руб.84151,62100000Закупочная цена товара у поставщика X, P1,j, руб.Закупочная цена товара у поставщика Y, P2,j, руб.6419,826740,8115753,5415003,387119,006795,413Закупочная цена товара у поставщика Z, P3,j, руб.7061,8016503,716471,824Кол.товаров, закупленных у поставщика X, X1,j,t, ед.0000120005Кол.товаров, закупленных у поставщика Y, X2,j,t, ед.42411000067Кол.товаров, закупленных у поставщика Z, X3,j,t, ед.∑ кол.товаров, закупленных у поставщиков, ед.040204080008Вспомогательные переменные, Rj,t, ед.3139Величина продаж товаров в течение периода, Dj,t, ед.111Сумма, ∑ST jt-11214641,981500,34647,1813 Общий запас на конец периода, ST j,t,ед.43414 Максимальный желательный запас, Smaxj, ед.43416 Издержки, связанные с дефицитом товара/период, Fj, руб.00011 Общий запас на начало периода, ST j,t-1, ед.12 Издержки на хранение ед.
товара/период, Hj, руб.17 Общие затраты на закупки за период, TC2, руб.103809,2917012Сумма, ∑ST jt11171Таблица В.3 – Результаты численного решения задачи при среднем уровне спроса (период 2, сценарий 2)№ Наименование показателяГазоваяколонкаOasis OR20SГазоваяколонкаBOSCHTherm4000 WR15-2ГазоваяколонкаEdisson F20 GD(Костер)Затраты наприобретение,руб.Бюджетныеограничения,руб.97687,84100000Закупочная цена товара у поставщика X, P1,j, руб.Закупочная цена товара у поставщика Y, P2,j, руб.6419,826740,8115753,5415003,387119,006795,413Закупочная цена товара у поставщика Z, P3,j, руб.7061,8016503,716471,824Кол.товаров, закупленных у поставщика X, X1,j,t, ед.0000120005Кол.товаров, закупленных у поставщика Y, X2,j,t, ед.52511000067Кол.товаров, закупленных у поставщика Z, X3,j,t, ед.∑ кол.товаров, закупленных у поставщиков, ед.050205080008Вспомогательные переменные, Rj,t, ед.3039Величина продаж товаров в течение периода, Dj,t, ед.222Сумма, ∑ST jt-11214641,981500,34647,1813 Общий запас на конец периода, ST j,t,ед.42414 Максимальный желательный запас, Smaxj, ед.4340,0022505,060,0011 Общий запас на начало периода, ST j,t-1, ед.12 Издержки на хранение ед.
товара/период, Hj, руб.16 Издержки, связанные с дефицитом товара/период, Fj, руб.17 Общие затраты на закупки за период, TC2, руб.138350,23Сумма, ∑ST jt1017112172Таблица В.4 – Результаты численного решения задачи при высоком уровне спроса (период 2, сценарий 3)№ Наименование показателяГазоваяколонкаOasis OR20SГазоваяколонкаBOSCHTherm4000 WR15-2ГазоваяколонкаEdisson F20 GD(Костер)Затраты наприобретение,руб.Бюджетныеограничения,руб.99155,00100000Закупочная цена товара у поставщика X, P1,j, руб.Закупочная цена товара у поставщика Y, P2,j, руб.6419,826740,8115753,5415003,387119,006795,413Закупочная цена товара у поставщика Z, P3,j, руб.7061,8016503,716471,824Кол.товаров, закупленных у поставщика X, X1,j,t, ед.0000120005Кол.товаров, закупленных у поставщика Y, X2,j,t, ед.43411000067Кол.товаров, закупленных у поставщика Z, X3,j,t, ед.∑ кол.товаров, закупленных у поставщиков, ед.040304080008Вспомогательные переменные, Rj,t, ед.1019Величина продаж товаров в течение периода, Dj,t, ед.333Сумма, ∑ST jt-11214641,981500,34647,1812 Общий запас на конец периода, ST j,t,ед.22213 Максимальный желательный запас, Smaxj, ед.43414 Издержки, связанные с дефицитом товара/период, Fj, руб.19259,4522505,0619415,4515 Общие затраты на закупки за период, TC2, руб.175913,9710 Общий запас на начало периода, ST j,t-1, ед.11 Издержки на хранение ед.
товара/период, Hj, руб.Общие затраты на закупки за все периоды, ∑TC, руб.235440,26Сумма, ∑ST jt617212173Приложение ГРезультат решения задачи об определении оптимальной СУЗ в условиях редкогоспроса в ExcelТаблица Г.1 – Существующая и оптимальная СУЗ для товарной позиции №2№ неделиСпрос,ед./неделя123456789101112131415161718192021222324252627282930313233343536210000001131123201043121130121023140Существующая стратегия УЗЗапас Запас СредПонананийставка,начало козапасед.ненецза недели,неделю,ед.дели,едед.1513141513121312121212121212121212121212121212121212111211101110797676562018191518151715131413131313121312121212810857201920151917181716171615161512141212121211121191098988886763518171815171315131313Оптимальная стратегия УЗЗапас Запас СредПоставка,нананийед.начало козапасненецза недели,неделю,ед.дели,едед.4234656455555555555555555554543474644347674645413534477747676666246354767464543476746357774767645434777475652465645135554174Продолжение таблицы Г.1№ неделиСпрос,ед./неделя37383940414243444546474849505152Итого522321320000100068Существующая стратегия УЗЗапас Запас СредПонана ко- ний за- ставка,началнецпас заед.о нененедели,дели,делю,ед.ед.ед138118676451916181516141514131413101210898888888888888787777777771175Оптимальная стратегия УЗЗапас Запас СредПоставка,нана ко- ний заед.началнецпас зао нененедели,дели,делю,ед.ед.ед50342342014134534432363547564555555555555545444444444572Таблица Г.2 – Существующая и оптимальная СУЗ для товарной позиции №3№ неделиСпрос,ед./неделя123456789101112131010100100001Существующая стратегия УЗЗапас Запас СредПонананийставка,начало козапасед.ненецза недели,неделю,ед.дели,едед.109101099998988887877777776766616161610161616161616161516Оптимальная стратегия УЗЗапас Запас СредПоставка,нананийед.начало козапасненецза недели,неделю,ед.дели,едед.43447774767666656555555545444444444444434175Продолжение таблицы Г.2№ неделиСпрос,ед./неделя141516171819202122232425262728293031323334353637383940414243444546474849505152Итого03012003021004001001020040010020004002038Существующая стратегия УЗЗапас Запас СредПонана ко- ний за- ставка,началнецпас заед.о нененедели, дели,делю,ед.ед.ед151515151214121212121112119109999999686666454343333331391110999999989888888878777756555555513111111101111111110111010101010101089888888888846444444423222840Оптимальная стратегия УЗЗапас Запас СредПоставка,нана ко- ний заед.началнецпас зао нененедели, дели,делю,ед.ед.ед77747464444347564555555524666464543477747777357774777767666666656555534777477773577747777676666666454444444444024444444423222540176Таблица Г.3 – Существующая и оптимальная СУЗ для товарной позиции №4№ не- Спрос,дели ед./неделя123456789101112131415161718192021222324252627282930313233343536373839404142200002100223300021301110300022130222140510Существующая стратегия УЗОптимальная стратегия УЗЗапас наЗапас на СредПоЗапас Запас СредПоначалоконец ненийставка,нана конийставка,недели,дели, ед.запасед.началонецзапасед.ед.за нененеза неделю,дели,дели, делю,едед.ед.ед2018192042341818186664181818666181818666181818666181617645161516434151515777415151577715131475613111253411810746485741355555545555555555555345343237674171416156351414147774141314767131213656121112545111111444118104138885554888555888555867534645756419181915545181517413151515555415131453413111231211910534498932323192115624419191966641914176141413145454131313444177Продолжение таблицы Г.3№ неделиСпрос,ед./неделя43444546474849505152Итого200012200261Существующая стратегия УЗЗапас наЗапасСредПоначалона коний за- ставканедели,нец непас за, ед.ед.дели,неделю,ед.ед13111111111086661111111110866641211111111976651265Оптимальная стратегия УЗЗапасЗапасСредПонана конийставка,начало нец не- запасед.недели,дели,за неед.ед.делю,ед42366646666666565347564555555534564Таблица Г.4 – Существующая и оптимальная СУЗ для товарной позиции №5№ неделиСпрос,ед./неделя123456789101112131415161718192021210000001131123201043Существующая стратегия УЗЗапас ЗапасСредПонана ко- ний за- ставка,началонецпас заед.нененедели,дели,делю,ед.ед.ед15131415131212,5121212121212121212121212121212121212121111,5111010,5252223,515222121,5212020,5201819181516,5151314131313131212,512121212810856,5Оптимальная стратегия УЗЗапас ЗапасСредПонана ко- ний заставка,началонецпас заед.нененедели,дели,делю,ед.ед.ед4234655,54555555555555555555544,5433,5745,54433,5766,54645412,553447774766,5666624634,54178Продолжение таблицы Г.4№ неделиСпрос,ед./неделя22232425262728293031323334353637383940414243444546474849505152Итого121130121023140522321320000100068Существующая стратегия УЗЗапас ЗапасСредПонана ко- ний за- ставка,началонецпас заед.нененедели,дели,делю,ед.ед.ед201919,515191718171616,5161515,5151213,5121212121111,511910988,5888867634,5322,51713151513131313810,5867645412,516141515141313,5131011,51089888888888888877,577777777711,519275Оптимальная стратегия УЗЗапас ЗапасСредПонана ко- ний заставка,началонецпас заед.нененедели,дели,делю,ед.ед.ед766,54645433,5766,54634,57774766,5645433,57774756523,5655,545135554502,542346454412,55344322,5634,547564555555555555544,54444444444,7307772179Таблица Г.5 – Существующая и оптимальная СУЗ для товарной позиции №6№неделиСпрос,ед./неделя123456789101112131415161718192021222324252627282930313233343536373839404142220011021130121211221310302010000100010122Существующая стратегия УЗЗапас Запас СредПонана конийставка,началонецзапасед.ненеза недели,дели, делю,ед.ед.ед15131415131112111111111111111010,51099,5999978766,5655,5201718,515171717171616,5161415141313,5131112111010,51099,5978756544,5191617,515161515,5151515151213,51212121210111010101099,5999999999999988,588888823232315232222,5222222222121,5211920191718Оптимальная стратегия УЗЗапас Запас СредПонана конийставка,началонецзапасед.ненеза недели,дели, делю,ед.ед.ед42346454444444433,5766,54666645433,5766,54634,57774766,5645433,57564544,5433,57564534766,54634,5766,54666634,57774756555544,5444444444444433,57774777777766,5666655,55347564180Продолжение таблицы Г.5№ не- Спрос,дели ед./неделя43444546474849505152Итого000213202053Существующая стратегия УЗЗапасЗапас СредПонана ко- ний за- ставка,началонецпас заед.недели,ненеед.дели,делю,ед.ед171717171717171717171516151414,5141112,51191099997877712,451960Оптимальная стратегия УЗЗапасЗапас СредПонана ко- ний за- ставка,началонецпас заед.нененедели,дели,делю,ед.ед.ед555555555534766,54634,5756455553477745,1634660Таблица Г.6 – Существующая и оптимальная СУЗ для товарной позиции №7№ неделиСпрос,ед./неделя123456789101112131415161718192021011010001001020021001Существующая стратегия УЗЗапасЗапасСредПонана ко- ний за- ставка,началонецпас заед.нененедели,дели,делю,ед.ед.ед101010101099,5988,5888877,5777777777766,566616161610161515,5151515151314131313131313131112111010,51010101010101099,5Оптимальная стратегия УЗЗапасЗапасСредПонана ко- ний за- ставка,началонецпас заед.нененедели,дели,делю,ед.ед.ед4444433,5766,54666655,5555555555544,5444444433,57774756555555534766,54666666655,5181Продолжение таблицы Г.6№ неделиСпрос,ед./неделя22232425262728293031323334353637383940414243444546474849505152Итого100301110413010212021012013512252Существующая стратегия УЗЗапас Запас на СредПонаконецний за- ставка,начал недели,пас заед.о неед.недели,делю,ед.ед988,5888888856,5555151414,510141313,5131212,512121212810877,5745,5444433,513131310131112111010,51089888867655,515151510151414,5141213121212121111,51189,5181315,510131212,5121011108910,057607Оптимальная стратегия УЗЗапасЗапасСредПонана конийставка,начало нец не- запасед.недели, дели,за неед.ед.делю,ед544,5444444412,55554544,5433,5766,54666624655,54523,56664655,5555534766,54645444423655,54555544,54236664655,5523,5613,54544,5442364544,7884566182Таблица Г.7 – Существующая и оптимальная СУЗ для товарной позиции №8№ неделиСпрос,ед./неделя123456789101112131415161718192021222324252627282930313233343536373839404142100200000000200100100000000000000000300001Существующая стратегия УЗЗапас Запас на СредПонаконец ненийставка,начало дели, ед.
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
