Расчет и построение кумулятивной кривой прибыльно-сти товаров
Кумулятивная кривая (диаграмма Парето) строится по вектору накопленной прибыли от продажи товаров, рассчитываемому по-сле ранжирования (в %) по формуле
k
Wk =∑K j
j =1
для всех k=1,2,…. N.
Результаты сортировки и расчета кумулятивного ряда пред-ставляются в форме табл.2.
Таблица 2. Расчетная форма для деления товаров на группы
| № | Товар | Продажи по месяцам | Si | Kj, | Wk | ||||||||||||||||||
| Q1j | Q2j | Q3j | Q4j | Q5j | Q6j | Q7j | % | ||||||||||||||||
| Т21 | |||||||||||||||||||||||
| Т12 | |||||||||||||||||||||||
| … | …. | …. | …. | …. | …. | …. | …. | …. | …. | …. | |||||||||||||
| Т2 | |||||||||||||||||||||||
| Сумма: | ∑= | ||||||||||||||||||||||
3.5. Деление товаров на категории и определение доли товаров группы А
Деление товаров на категории выполняется аналитически и графически. Аналитический расчет заключается в следующем.
1) Все товары с прибыльностью выше средней относятся к катего-рии А. То есть, по табл.2 все товары с Kj ≥ Kср следует отнести к категории А и по той же таблице найти долю этой группы в об-щей прибыли WA.
2) Подсчитывается количество товаров группы А - NA и соответст-вующая этой группе товаров прибыль в % по кумулятивному ряду WA. С учетом этих данных находится средняя прибыль по оставшейся номенклатуре товаров
KBС =100−−WA .
N N A
3) К группе товаров категории В следует отнести те товары, для которых выполняется правило
Kср > Kj ≥ KBС .
Остальные товары составляют группу С.
Для графического способа деления товаров на группы АВС необходимо средствами Excel построить график накопленной при-были (кумулятивная кривая) W(k), соединив точки плавной вы-пуклой кривой (рис.1). Затем нужно соединить прямой линией на-чальную и конечную точки (0-N). Касательная к выпуклой кривой накопленной прибыли W(k), параллельная прямой (0-N), отсекает слева от точки касания группу товаров категории А. Если далее со-единить прямой точку касания с конечной точкой и провести па-раллельно ей новую касательную к кумулятивной кривой прибы-ли, то новая точка касания разделит оставшиеся товары на группы В (слева)и С (справа).
Рис.1. Диаграмма Парето: АВС анализ номенклатуры товаров
3.6. Расчет бюджета товаров группы А
Бюджет товаров группы А (наиболее прибыльные товары) определяется через заданный в исходных данных общий бюджет В0 в условных единицах(см.табл.П4)по формуле
| BA = | γ AB0 | 1000 , | ( 5) | |
где γА – доля затрат в %, приходящаяся на группу товаров катего-рии А. Эта доля находится из табл.2 по формуле
| γ | A | = | SA | 100 , | ( 6) | |
| N | ||||||
| ∑S j |
j =1
в которой SA – это суммарные затраты на товары группы А по табл.2.
Примечание: бюджет ВA в формуле(5)умножается на1000,так как в табл.П4 общий бюджет задан в тыс.ед.
Все последующие расчеты выполняются только для товаров группы А!
4. ПРОГНОЗИРОВАНИЕ ПРОДАЖ ТОВАРОВ ГРУППЫ А
Прогнозирование продаж выполняется для всех товаров группы А и осуществляется по временным рядам Qi (i=1,2,.. n) табл.2 (в данном случае n=7). Горизонт прогнозирования t и до-верительная вероятность прогноза β входят в состав исходных данных, выбираемых из табл.П4. Для прогнозирования использу-ется линейная модель тренда с доверительными границами для за-висимой переменной Q.
4.1. Расчет коэффициентов линейной модели тренда для товаров группы А
Параметры модели прогнозирования
Q = a + b·t ( 7)
определяются методом наименьших квадратов (МНК). Для их рас-чета можно применить разные способы.
Первый, самый простой, заключается в формировании в среде Excel точечного графика Q(t) с последующим построением линии
тренда (выделить мышью график и нажать правую кнопку ). Второй способ состоит в использовании встроенных функций
Excel (табл.3).
Таблица 3 Встроенные функции Excel, используемые для прогнозирования
| Параметры | Обозначение | Функция | |
| Среднее значение | tср | =СРЗНАЧ(t) | |
| аргумента t (мес.) | |||
| Коэффициент | r | =КОРРЕЛ(Q,t) | |
| корреляции | |||
| Коэффициенты | a | =ОТРЕЗОК(Q,t) | |
| линейной модели | |||
| b | =НАКЛОН(Q,t) | ||
| тренда | |||
| Статистика Стьюдента | tβ | =СТЬЮДРАСПОБР(β,n-2) | |
Третий, наиболее информативный, способ заключается в ис-пользовании инструмента РЕГРЕССИЯ в Excel-расширении АНАЛИЗ ДАННЫХ (рис.2). Перечень и интерпретация необходи-мых для дальнейших расчетов данных из листинга регрессионно-го анализа (табл.5) приведен в табл.4.
Рис.2. Интерфейс инструмента «Регрессия»: Y – зависимая пере-менная (Q); X – независимая переменная (t).
Таблица 4 Используемые для расчетов данные регрессионного анализа
| Текст в листинге | Обозначение | Параметры | ||||||||
| Стандартная ошибка | σ0 | Стандартная ошибка мо- | ||||||||
| дели | ||||||||||
| Множественный R | r | Коэффициент корреляции | ||||||||
| Коэффициенты: | a | Коэффициенты линейной | ||||||||
| Y-пересечение | модели тренда | |||||||||
| Коэффициенты: | b | |||||||||
| Переменная Х1 | ||||||||||
| Стандартная ошибка: | σa | Стандартные ошибки ко- | ||||||||
| Y-пересечение | эффициентов модели | |||||||||
| Стандартная ошибка: | σb | |||||||||
| Переменная Х1 | ||||||||||
| Таблица 5 | ||||||||||
| Пример листинга регрессионного анализа в Excel | ||||||||||
| ВЫВОД ИТОГОВ | ||||||||||
| Регрессионная статистика | ||||||||||
| Множественный R | 0.947587188 | |||||||||
| R-квадрат | 0.897921478 | |||||||||
| Нормированный R-квадрат | 0.877505774 | |||||||||
| Стандартная ошибка | 6.155137227 | |||||||||
| Наблюдения | ||||||||||
| Дисперсионный анализ | ||||||||||
| df | SS | MS | F | |||||||
| Регрессия | 1666.285714 | 1666.285714 | 43.9819005 | |||||||
| Остаток | 189.4285714 | 37.88571429 | ||||||||
| Итого | 1855.714286 | |||||||||
| Коэффициенты | Стандартная | t-статистика | P- | |||||||
| ошибка | Значение | |||||||||
| Y-пересечение | 145.7142857 | 5.202040416 | 28.01098686 | 1.0858E-06 | ||||||
| Переменная X 1 | 7.714285714 | 1.163211599 | 6.631885136 | 0.0011742 |