Оптимальное планирование закупок при случайном спросе на товары

 

      Третий, наиболее информативный, способ заключается  в использовании инструмента  РЕГРЕССИЯ в Excel-расширении АНАЛИЗ ДАННЫХ. Расчеты данным способом представлены в Приложении 1. 
 
 

3.2. Расчет параметров модели дисперсии ошибки прогноза

    Для интервального прогноза необходимо вычислить дисперсию ошибки независимой переменной , временная модель которой определяется выражением

                            , (8)    где    -  корреляционный момент коэффициентов модели,

   Входящие в формулу (8) дисперсии ошибок коэффициентов определяются по формулам: 

,                ,                ( 9) 

      Расчеты, связанные с прогнозированием и  определением доверительных границ, представлены на примере товара 21 из группы  в табл.11.

Таблица 11

Расчетная форма для прогнозирования продаж товара Т21

								Доверительные границы
	ti,	Qi,	Q(t)=	[Qi-Q(t)]2	t2	(t-tср)2	σQ(t)
	мес.	ед.	a+b∙t				(СКО)	Qв	Qн
Исходные данные	1	330	331,34	1,79	1	9	1,22	337,18	325,50
	2	328	327,68	0,10	4	4	0,96	332,26	323,10
	3	327	324,02	8,89	9	1	0,76	327,64	320,39
	4	320	320,36	0,13	16	0	0,68	323,60	317,12
	5	315	316,70	2,88	25	1	0,76	320,32	313,07
	6	312	313,04	1,07	36	4	0,96	317,62	308,45
	7	310,5	309,38	1,27	49	9	1,22	315,22	303,53
Прогноз	8		305,71		64		1,52	312,96	298,47
	9		302,05		81		1,83	310,78	293,33
	10		298,39		100		2,15	308,64	288,14
	11		294,73		121		2,47	306,53	282,94
	12		291,07		144		2,80	304,43	277,71
Сумма:	78			16,1339286	650	28

 

3.3. Определение доверительных границ прогноза продаж товаров группы А

 Доверительные границы для прогнозируемых объемов  продаж товаров определяются по формуле 

                                  ,                          

где t_β - статистика Стьюдента (табл.10) 

 Результаты  расчета верхней и нижней границ продаж для товара Т21 при заданной доверительной вероятности β представлены в табл.11 и в виде графика на рис.12.Для остальных товаров из группы А расчетные таблицы и графики представлены в Приложении 2. 

 

Рис. 12. График результатов прогнозирования продаж товара Т21 

    В заключение в точке упреждения t_пр=t₇+Δt=9 для всех товаров группы определим характеристики распределения объемов продаж в 9-ом месяце: Форма представления окончательных результатов приведена в табл.12. 

Таблица 12

     Результирующая  форма для параметров распределения  спроса

Параметр  нормального распределения	Обозначение	Товары  группы А
		Т21	Т3	Т22	Т4	Т10	Т19	Т2	Т18	Т25	Т20
Среднее, ед		302,05	308,75	284,30	143,68	222,25	273,79	215,93	243,27	149,23	298,45
С.К.О., ед.	σQ	1,83	6,31	2,35	4,05	4,82	2,35	7,98	3,03	3,22	2,51

 

4. ОПРЕДЕЛЕНИЕ ОПТИМАЛЬНЫХ РАЗМЕРОВ ЗАКАЗОВ ТОВАРОВ 

4.1. Расчет параметров модели хозяйственного риска

Модель  хозяйственного риска представляет собой минимизируемую функцию общих потерь компании 

, (10)

где: Q₀ – искомый оптимальный объем заказа, определяемый в интервале доверительных границ { Q_в ≥Q₀≥ Q_н }; 

f(Q) – распределение спроса;

(11)

C₁, C₂ – потери при заказе единицы товара

                 C₁ = C_х + 0.01γ ∙C_и ,          С₂ = С_п – С_и; 

γ  - банковская ставка, % (табл.__). Дополнительным ограничением является условие непревышения бюджета группы А

S₀ = Q_0∙C_и ≤ B_A 

Рассчитаем  параметры модели хозяйственного риска  для товара Т21.

C_х, C_и, С_п даны по условию (табл.2,4).

Для товара Т21 известны следующие данные

  

        302,05 (см.табл.12)

σ_Q =1,83 (см.табл.12)

γ =6% (см.табл.3)

C_х =0,1, C_и =17,95, С_п=23 (см.табл.2,4)

Найдем  потери при заказе единицы товара 21:

C₁ = C_х + 0.01γ ∙C_и =0,1+0,01*6*17,95=1,177

С₂ = С_п – С_и=23-17,95= 5,05 

4.2. Расчет оптимальных заказов

      Поскольку средства Excel не позволяют вычислять интегралы напрямую, следует заменить их суммами с конечными пределами (Q_н и Q_в)

   ,

где DQ - интервал дискретизации диапазона изменения спроса.

Для расчётов интервал дискретизации принят равным ΔQ =1.

      Расчёт  составляющих общих издержек произведен во всём диапазоне спроса (на всём интервале) и приведен в табл.13. Для расчета f(Q)использовались встраиваемые функции Excel.

Оптимальный заказ Q₀ найден с помощью инструмента ПОИСК РЕШЕНИЯ в Excel.Учтены ограничения S₀ = Q_0∙C_и ≤ B_A  и   Q_в ³ Q₀ ³ Q_н.

Таблица 13

Расчетная форма для определения издержек для товара Т21

Расчёт  издержек				Штрафные  функции
Q(t)	f(Q)	C1(Q0-Q)(fQ)	C2(Q-Q0)(fQ)	C1 (Q0-Q)/103	C2(Q-Q0)/103
290	7,91116E-11	1,3036E-09	-5,59318E-09	0,016477973	-0,070699883
291	2,50969E-09	3,84007E-08	-1,64761E-07	0,015300973	-0,065649883
292	5,90269E-08	8,33694E-07	-3,57702E-06	0,014123973	-0,060599883
293	1,02927E-06	1,3326E-05	-5,7176E-05	0,012946973	-0,055549883
294	1,33064E-05	0,000156616	-0,000671974	0,011769973	-0,050499883
295	0,000127539	0,00135102	-0,005796644	0,010592973	-0,045449883
296	0,000906309	0,008533784	-0,03661479	0,009415973	-0,040399883
297	0,004774849	0,039339848	-0,168790343	0,008238973	-0,035349883
298	0,018650626	0,131710211	-0,565111779	0,007061973	-0,030299883
299	0,054010453	0,317850043	-1,36375762	0,005884973	-0,025249883
300	0,11596124	0,545942355	-2,342403476	0,004707973	-0,020199883
301	0,184585889	0,651767741	-2,796454622	0,003530973	-0,015149883
302	0,217838562	0,512786037	-2,200143997	0,002353973	-0,010099883
303	0,190599451	0,224330357	-0,962504932	0,001176973	-0,005049883
304	0,123640042	-3,37063E-06	1,44619E-05	-2,72616E-08	1,16968E-07
305	0,059463061	-0,069989644	0,300295415	-0,001177027	0,005050117
306	0,021202448	-0,04991114	0,214147202	-0,002354027	0,010100117
307	0,005604997	-0,019791396	0,084916354	-0,003531027	0,015150117
308	0,001098539	-0,00517195	0,022190608	-0,004708027	0,020200117
309	0,000159627	-0,000939408	0,004030596	-0,005885027	0,025250117
Q(t)	f(Q)	C1(Q0-Q)(fQ)	C2(Q-Q0)(fQ)	C1 (Q0-Q)/103	C2(Q-Q0)/103
310	1,71968E-05	-0,000121444	0,000521064	-0,007062027	0,030300117
311	1,37353E-06	-1,13166E-05	4,85545E-05	-0,008239027	0,035350117
312	8,13357E-08	-7,65859E-07	3,28597E-06	-0,009416027	0,040400117
313	3,57087E-09	-3,78264E-08	1,62297E-07	-0,010593027	0,045450117
314	1,1623E-10	-1,36803E-09	5,86963E-09	-0,011770027	0,050500117
315	2,80487E-12	-3,63147E-11	1,55811E-10	-0,012947027	0,055550117
316	5,01831E-14	-7,08787E-13	3,0411E-12	-0,014124027	0,060600117
317	6,65661E-16	-1,01853E-14	4,37007E-14	-0,015301027	0,065650117
318	6,54635E-18	-1,07871E-16	4,62827E-16	-0,016478027	0,070700117
319	4,77305E-20	-8,42683E-19	3,61559E-18	-0,017655027	0,075750117
320	2,58014E-22	-4,85893E-21	2,08476E-20	-0,018832027	0,080800117
321	1,03405E-24	-2,06903E-23	8,87732E-23	-0,020009027	0,085850117
322	3,07249E-27	-6,50938E-26	2,79289E-25	-0,021186027	0,090900117
323	6,76845E-30	-1,51363E-28	6,49433E-28	-0,022363027	0,095950117
324	1,10545E-32	-2,60223E-31	1,11651E-30	-0,023540027	0,101000117
325	1,33856E-35	-3,30853E-34	1,41955E-33	-0,024717027	0,106050117
326	1,20168E-38	-3,11165E-37	1,33507E-36	-0,025894027	0,111100117
Сумма ЕСЛИ >0		2,433782211	0,626167711	3,059949921

 

Построим  графики распределения спроса f(Q) и штрафных функций S₁(Q)=С₁(Q₀-Q), S₂(Q)=С₂(Q-Q₀) для товара Т21(рис.13). 

Рис. 13 Результаты поиска оптимального заказа на  товар Т21

Для остальных  товаров из группы А расчетные формы для определения издержек и графики поиска оптимального заказа представлены в Приложении 3. 
 

4.3. Определение оптимальных бюджетов товаров 

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

                                             S_0i = Q_0i∙C_иi.       (12)                              

где i=1, 2 …. m, где m – количество наименований товаров в группе А.

     Результаты  расчета оптимальных бюджетов приведены в табл.14.

Таблица 14

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

Товар	Оптимальный заказ Q0	Удельные	Оптимальный (желаемый)
		издержки  Cи	бюджет  S0
Т21	304,00	17,95	5456,80
Т3	312,00	31	9672,00
Т22	286,00	21,05	6020,30
Т4	147,00	26	3822,00
Т10	226,00	18,1	4090,60
Т19	276,00	9,1	2511,60
Т2	221,00	21	4641,00
Т18	247,00	4	988,00
Т25	154,00	5,1	785,40
Т20	301,00	3,05	918,05
Сумма:			38905,75