иллюстрации экономического смысла IRR(ВСД) был рассчитан показатель чистой приведенной прибыли (NPV)
Исходя из данных о покупке и продаже товаров я оприделила, что моя прибиль составила 22118,00грн., то есть я подняла исходно затраченную сумму на 41,14%
3. Анализ товарооборота
Определение суммарных объемов продаж, поступления товаров и остатков.
Первый этап анализа товарооборота — определение суммарных объемов поступления (рис. 2) и реализации товаров(рис. 3). Для этого были использованы сводные таблицы (меню Данные4Сводная таблица…). Исходной информацией служат листы «Приход товара (подробно)» и «Продажа товара (подробно)»
Анализ структуры
Анализ выполняется на основании продаж (лист «Продажа товара (подробно))». Определить итоговые объемы удобно мастером сводных таблиц или итогами. Структура продаж проиллюстрирована на следующей диаграмме (рис. 6)
Рис. 6
Для обработки данных нужно проконтролировать их правильность (лучше посмотреть, куда прыгаешь до прыжка, а не сразу после!).
Были осуществлены следующие методы контроля:
1. сопоставить суммарные объемы продаж таблицах Приход товара (подробно), Продажа товара (подробно), Приход товара, Продажа товара и База, основной инструмент – автофильтр и выборочное суммирование;
2. проанализировать количество цен, соответствующих каждому виду ТМЦ, основной инструмент – сводные таблицы и формула СЧЕТЕСЛИ().
3. убедиться, что в базе данных нет документов, содержащих строки с различными датами.. Иными словами, все сроки, образующие документ, должны содержать одинаковое значение даты.
Для графического определения внутренней ставки дохода по отчету о показателе NPV я построила семейство диаграмм, как показано на рис. 7
Рис. 7
Корреляционный анализ динамики продаж
Выполнение данного этапа началось с формирования сводной таблицы по динамике продаж в количественном выражении. В данной таблице определены суммы по полю количества продаж из таблицы «Продажа товара (подробно)» по каждой дате реализации. В приведенном примере таблицы (рис. 8) указаны лишь несколько наименований продукции
Дата |
Наименование |
|||||||||||||||||||
"Кагор" десертное |
"Крымское шампанское" полусладкое |
"Мускат игристый" белый |
"Мускат игристый" розовый |
"Рандеву" полусладкое |
"Советское шампанское" полусладкое |
"Советское шампанское" сладкое |
"Хрещатик" полусладкое |
Бальзам |
Вино "Золотое" полусладкое |
Вино "Золотое" полусухое |
Вино "Киянь" полусладкое |
Вино "Лебедь" полусладкое |
Вино "Монастырская изба" полусухое |
Водка "Medoff" |
Водка "Nemiroff" |
Водка "Медова з перцем" |
Коньяк "Ариана" |
Коньяк "Каховка" |
Коньяк "Таврия" |
|
02.08.2006 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
3 |
45 |
16 |
20 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
03.08.2006 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
5 |
0 |
0 |
5 |
5 |
50 |
50 |
0 |
0 |
0 |
0 |
5 |
04.08.2006 |
0 |
0 |
0 |
0 |
20 |
30 |
0 |
0 |
0 |
0 |
0 |
5 |
0 |
0 |
0 |
30 |
0 |
18 |
0 |
10 |
05.08.2006 |
49 |
0 |
0 |
0 |
28 |
0 |
0 |
10 |
0 |
0 |
0 |
0 |
50 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
06.08.2006 |
0 |
59 |
0 |
58 |
46 |
0 |
0 |
0 |
40 |
0 |
0 |
47 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
07.08.2006 |
0 |
0 |
0 |
58 |
0 |
0 |
0 |
20 |
0 |
0 |
18 |
0 |
0 |
0 |
50 |
0 |
0 |
0 |
0 |
0 |
08.08.2006 |
38 |
0 |
60 |
0 |
25 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
15 |
0 |
0 |
0 |
70 |
20 |
0 |
0 |
09.08.2006 |
0 |
0 |
0 |
0 |
0 |
80 |
45 |
0 |
0 |
0 |
0 |
40 |
0 |
0 |
0 |
0 |
0 |
0 |
20 |
30 |
10.08.2006 |
0 |
0 |
0 |
0 |
50 |
0 |
0 |
0 |
0 |
20 |
0 |
0 |
0 |
70 |
50 |
0 |
0 |
0 |
20 |
0 |
11.08.2006 |
40 |
10 |
0 |
0 |
0 |
40 |
0 |
10 |
0 |
0 |
0 |
0 |
20 |
0 |
0 |
0 |
0 |
0 |
0 |
15 |
12.08.2006 |
7 |
10 |
0 |
40 |
0 |
0 |
0 |
0 |
20 |
0 |
0 |
0 |
15 |
20 |
0 |
0 |
0 |
0 |
0 |
0 |
13.08.2006 |
0 |
30 |
0 |
0 |
0 |
0 |
0 |
8 |
20 |
0 |
0 |
0 |
0 |
100 |
0 |
0 |
0 |
30 |
0 |
0 |
14.08.2006 |
0 |
8 |
50 |
0 |
20 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
5 |
0 |
0 |
15.08.2006 |
0 |
0 |
70 |
50 |
0 |
0 |
0 |
0 |
20 |
0 |
20 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
16.08.2006 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
10 |
0 |
10 |
20 |
5 |
0 |
0 |
5 |
0 |
0 |
0 |
10 |
17.08.2006 |
0 |
30 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
30 |
0 |
5 |
0 |
0 |
25 |
0 |
0 |
15 |
10 |
18.08.2006 |
0 |
0 |
0 |
0 |
30 |
48 |
0 |
0 |
0 |
0 |
10 |
0 |
0 |
48 |
30 |
0 |
60 |
0 |
0 |
0 |
19.08.2006 |
49 |
0 |
50 |
50 |
0 |
0 |
0 |
40 |
0 |
40 |
0 |
0 |
0 |
0 |
40 |
0 |
0 |
0 |
0 |
0 |
20.08.2006 |
0 |
0 |
10 |
20 |
0 |
0 |
0 |
5 |
50 |
0 |
0 |
0 |
10 |
50 |
0 |
0 |
0 |
0 |
0 |
0 |
21.08.2006 |
50 |
0 |
0 |
0 |
40 |
0 |
40 |
0 |
35 |
0 |
0 |
0 |
0 |
10 |
0 |
0 |
40 |
0 |
0 |
0 |
22.08.2006 |
0 |
47 |
0 |
0 |
0 |
0 |
0 |
10 |
2 |
0 |
0 |
0 |
0 |
0 |
47 |
0 |
30 |
10 |
0 |
0 |
23.08.2006 |
0 |
21 |
0 |
60 |
0 |
15 |
0 |
0 |
0 |
0 |
0 |
48 |
15 |
0 |
0 |
0 |
0 |
10 |
0 |
0 |
24.08.2006 |
0 |
37 |
0 |
37 |
0 |
40 |
0 |
0 |
37 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
25.08.2006 |
0 |
0 |
0 |
45 |
30 |
0 |
0 |
0 |
10 |
0 |
40 |
40 |
30 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
26.08.2006 |
0 |
0 |
20 |
0 |
20 |
60 |
0 |
0 |
48 |
0 |
8 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
20 |
0 |
27.08.2006 |
0 |
0 |
17 |
0 |
0 |
40 |
0 |
10 |
0 |
0 |
35 |
0 |
0 |
0 |
0 |
0 |
0 |
0 |
10 |
0 |
28.08.2006 |
0 |
0 |
0 |
0 |
0 |
0 |
20 |
0 |
0 |
0 |
1 |
0 |
0 |
0 |
0 |
40 |
40 |
20 |
15 |
0 |
29.08.2006 |
0 |
20 |
0 |
0 |
0 |
0 |
16 |
0 |
0 |
0 |
0 |
0 |
0 |
15 |
0 |
0 |
20 |
0 |
20 |
15 |
30.08.2006 |
0 |
16 |
0 |
58 |
0 |
0 |
0 |
15 |
0 |
40 |
0 |
0 |
0 |
30 |
38 |
0 |
0 |
0 |
0 |
0 |
31.08.2006 |
20 |
30 |
50 |
20 |
35 |
60 |
75 |
3 |
4 |
30 |
113 |
26 |
17 |
5 |
5 |
5 |
5 |
40 |
0 |
0 |
Рис.8 Динамика продаж
По результатам корреляционного анализа была подготовлена таблица .
Также необходимо определить зависимость объемов продаж между различными видами товаров. При построении таблицы мастером сводных таблиц следует наиболее продаваемый ТМЦ разместить в области строк, остальные — в области данных и рассчитать по ним количество значений. Это покажет, сколько раз продавался тот или иной ТМЦ в паре с наиболее «ходовым». Если указать операцию суммирования, отчет покажет, в каком объеме продавался тот или иной товар паре с наиболее «ходовым».
"Мускат игристый" розовый |
Данные |
Итог |
7 |
Количество по полю "Кагор" десертное |
|
Количество по полю "Крымское шампанское" полусладкое |
||
Количество по полю "Мускат игристый" белый |
||
Количество по полю "Рандеву" полусладкое |
||
Количество по полю "Советское шампанское" полусладкое |
1 |
|
Количество по полю "Советское шампанское" сладкое |
||
Количество по полю "Хрещатик" полусладкое |
||
Количество по полю Бальзам |
||
Количество по полю Вино "Золотое" полусладкое |
||
Количество по полю Вино "Золотое" полусухое |
||
Количество по полю Вино "Киянь" полусладкое |
||
Количество по полю Вино "Лебедь" полусладкое |
||
Количество по полю Вино "Монастырская изба" полусухое |
||
Количество по полю Водка "Medoff" |
||
Количество по полю Водка "Nemiroff" |
||
Количество по полю Водка "Медова з перцем" |
||
Количество по полю Коньяк "Ариана" |
||
Количество по полю Коньяк "Каховка" |
||
Количество по полю Коньяк "Таврия" |
||
18 |
Количество по полю "Кагор" десертное |
|
Количество по полю "Крымское шампанское" полусладкое |
||
Количество по полю "Мускат игристый" белый |
||
Количество по полю "Рандеву" полусладкое |
||
Количество по полю "Советское шампанское" полусладкое |
||
Количество по полю "Советское шампанское" сладкое |
||
Количество по полю "Хрещатик" полусладкое |
||
Количество по полю Бальзам |
||
Количество по полю Вино "Золотое" полусладкое |
||
Количество по полю Вино "Золотое" полусухое |
||
Количество по полю Вино "Киянь" полусладкое |
||
Количество по полю Вино "Лебедь" полусладкое |
||
Количество по полю Вино "Монастырская изба" полусухое |
||
Количество по полю Водка "Medoff" |
1 |
|
Количество по полю Водка "Nemiroff" |
||
Количество по полю Водка "Медова з перцем" |
||
Количество по полю Коньяк "Ариана" |
||
Количество по полю Коньяк "Каховка" |
||
Количество по полю Коньяк "Таврия" |
||
20 |
Количество по полю "Кагор" десертное |
1 |
Количество по полю "Крымское шампанское" полусладкое |
||
Количество по полю "Мускат игристый" белый |
2 |
|
Количество по полю "Рандеву" полусладкое |
||
Количество по полю "Советское шампанское" полусладкое |
||
Количество по полю "Советское шампанское" сладкое |
||
Количество по полю "Хрещатик" полусладкое |
||
Количество по полю Бальзам |
||
Количество по полю Вино "Золотое" полусладкое |
||
Количество по полю Вино "Золотое" полусухое |
1 |
|
Количество по полю Вино "Киянь" полусладкое |
||
Количество по полю Вино "Лебедь" полусладкое |
||
Количество по полю Вино "Монастырская изба" полусухое |
||
Количество по полю Водка "Medoff" |
||
Количество по полю Водка "Nemiroff" |
||
Количество по полю Водка "Медова з перцем" |
||
Количество по полю Коньяк "Ариана" |
||
Количество по полю Коньяк "Каховка" |
||
Количество по полю Коньяк "Таврия" |
||
30 |
Количество по полю "Кагор" десертное |
|
Количество по полю "Крымское шампанское" полусладкое |
||
Количество по полю "Мускат игристый" белый |
1 |
|
Количество по полю "Рандеву" полусладкое |
||
Количество по полю "Советское шампанское" полусладкое |
||
Количество по полю "Советское шампанское" сладкое |
||
Количество по полю "Хрещатик" полусладкое |
||
Количество по полю Бальзам |
||
Количество по полю Вино "Золотое" полусладкое |
||
Количество по полю Вино "Золотое" полусухое |
||
Количество по полю Вино "Киянь" полусладкое |
||
Количество по полю Вино "Лебедь" полусладкое |
||
Количество по полю Вино "Монастырская изба" полусухое |
||
Количество по полю Водка "Medoff" |
||
Количество по полю Водка "Nemiroff" |
||
Количество по полю Водка "Медова з перцем" |
||
Количество по полю Коньяк "Ариана" |
||
Количество по полю Коньяк "Каховка" |
||
Количество по полю Коньяк "Таврия" |
||
40 |
Количество по полю "Кагор" десертное |
|
Количество по полю "Крымское шампанское" полусладкое |
||
Количество по полю "Мускат игристый" белый |
||
Количество по полю "Рандеву" полусладкое |
||
Количество по полю "Советское шампанское" полусладкое |
||
Количество по полю "Советское шампанское" сладкое |
||
Количество по полю "Хрещатик" полусладкое |
||
Количество по полю Бальзам |
||
Количество по полю Вино "Золотое" полусладкое |
||
Количество по полю Вино "Золотое" полусухое |
1 |
|
Количество по полю Вино "Киянь" полусладкое |
||
Количество по полю Вино "Лебедь" полусладкое |
1 |
|
Количество по полю Вино "Монастырская изба" полусухое |
||
Количество по полю Водка "Medoff" |
||
Количество по полю Водка "Nemiroff" |
||
Количество по полю Водка "Медова з перцем" |
||
Количество по полю Коньяк "Ариана" |
||
Количество по полю Коньяк "Каховка" |
||
Количество по полю Коньяк "Таврия" |
||
45 |
Количество по полю "Кагор" десертное |
|
Количество по полю "Крымское шампанское" полусладкое |
||
Количество по полю "Мускат игристый" белый |
||
Количество по полю "Рандеву" полусладкое |
||
Количество по полю "Советское шампанское" полусладкое |
||
Количество по полю "Советское шампанское" сладкое |
||
Количество по полю "Хрещатик" полусладкое |
||
Количество по полю Бальзам |
1 |
|
Количество по полю Вино "Золотое" полусладкое |
||
Количество по полю Вино "Золотое" полусухое |
||
Количество по полю Вино "Киянь" полусладкое |
||
Количество по полю Вино "Лебедь" полусладкое |
||
Количество по полю Вино "Монастырская изба" полусухое |
||
Количество по полю Водка "Medoff" |
||
Количество по полю Водка "Nemiroff" |
||
Количество по полю Водка "Медова з перцем" |
||
Количество по полю Коньяк "Ариана" |
||
Количество по полю Коньяк "Каховка" |
||
Количество по полю Коньяк "Таврия" |
||
50 |
Количество по полю "Кагор" десертное |
|
Количество по полю "Крымское шампанское" полусладкое |
||
Количество по полю "Мускат игристый" белый |
||
Количество по полю "Рандеву" полусладкое |
||
Количество по полю "Советское шампанское" полусладкое |
||
Количество по полю "Советское шампанское" сладкое |
||
Количество по полю "Хрещатик" полусладкое |
1 |
|
Количество по полю Бальзам |
||
Количество по полю Вино "Золотое" полусладкое |
||
Количество по полю Вино "Золотое" полусухое |
||
Количество по полю Вино "Киянь" полусладкое |
||
Количество по полю Вино "Лебедь" полусладкое |
||
Количество по полю Вино "Монастырская изба" полусухое |
||
Количество по полю Водка "Medoff" |
||
Количество по полю Водка "Nemiroff" |
||
Количество по полю Водка "Медова з перцем" |
||
Количество по полю Коньяк "Ариана" |
||
Количество по полю Коньяк "Каховка" |
||
Количество по полю Коньяк "Таврия" |
||
58 |
Количество по полю "Кагор" десертное |
|
Количество по полю "Крымское шампанское" полусладкое |
1 |
|
Количество по полю "Мускат игристый" белый |
||
Количество по полю "Рандеву" полусладкое |
||
Количество по полю "Советское шампанское" полусладкое |
||
Количество по полю "Советское шампанское" сладкое |
||
Количество по полю "Хрещатик" полусладкое |
1 |
|
Количество по полю Бальзам |
||
Количество по полю Вино "Золотое" полусладкое |
||
Количество по полю Вино "Золотое" полусухое |
||
Количество по полю Вино "Киянь" полусладкое |
||
Количество по полю Вино "Лебедь" полусладкое |
||
Количество по полю Вино "Монастырская изба" полусухое |
||
Количество по полю Водка "Medoff" |
||
Количество по полю Водка "Nemiroff" |
||
Количество по полю Водка "Медова з перцем" |
||
Количество по полю Коньяк "Ариана" |
||
Количество по полю Коньяк "Каховка" |
||
Количество по полю Коньяк "Таврия" |
||
60 |
Количество по полю "Кагор" десертное |
|
Количество по полю "Крымское шампанское" полусладкое |
||
Количество по полю "Мускат игристый" белый |
||
Количество по полю "Рандеву" полусладкое |
||
Количество по полю "Советское шампанское" полусладкое |
||
Количество по полю "Советское шампанское" сладкое |
||
Количество по полю "Хрещатик" полусладкое |
||
Количество по полю Бальзам |
||
Количество по полю Вино "Золотое" полусладкое |
||
Количество по полю Вино "Золотое" полусухое |
||
Количество по полю Вино "Киянь" полусладкое |
1 |
|
Количество по полю Вино "Лебедь" полусладкое |
||
Количество по полю Вино "Монастырская изба" полусухое |
||
Количество по полю Водка "Medoff" |
||
Количество по полю Водка "Nemiroff" |
||
Количество по полю Водка "Медова з перцем" |
||
Количество по полю Коньяк "Ариана" |
||
Количество по полю Коньяк "Каховка" |
||
Количество по полю Коньяк "Таврия" |
||
(пусто) |
Количество по полю "Кагор" десертное |
7 |
Количество по полю "Крымское шампанское" полусладкое |
12 |
|
Количество по полю "Мускат игристый" белый |
7 |
|
Количество по полю "Рандеву" полусладкое |
14 |
|
Количество по полю "Советское шампанское" полусладкое |
11 |
|
Количество по полю "Советское шампанское" сладкое |
5 |
|
Количество по полю "Хрещатик" полусладкое |
10 |
|
Количество по полю Бальзам |
14 |
|
Количество по полю Вино "Золотое" полусладкое |
6 |
|
Количество по полю Вино "Золотое" полусухое |
11 |
|
Количество по полю Вино "Киянь" полусладкое |
8 |
|
Количество по полю Вино "Лебедь" полусладкое |
11 |
|
Количество по полю Вино "Монастырская изба" полусухое |
12 |
|
Количество по полю Водка "Medoff" |
8 |
|
Количество по полю Водка "Nemiroff" |
5 |
|
Количество по полю Водка "Медова з перцем" |
7 |
|
Количество по полю Коньяк "Ариана" |
8 |
|
Количество по полю Коньяк "Каховка" |
7 |
|
Количество по полю Коньяк "Таврия" |
7 |
|
Итог Количество по полю "Кагор" десертное |
8 |
|
Итог Количество по полю "Крымское шампанское" полусладкое |
13 |
|
Итог Количество по полю "Мускат игристый" белый |
10 |
|
Итог Количество по полю "Рандеву" полусладкое |
14 |
|
Итог Количество по полю "Советское шампанское" полусладкое |
12 |
|
Итог Количество по полю "Советское шампанское" сладкое |
5 |
|
Итог Количество по полю "Хрещатик" полусладкое |
12 |
|
Итог Количество по полю Бальзам |
15 |
|
Итог Количество по полю Вино "Золотое" полусладкое |
6 |
|
Итог Количество по полю Вино "Золотое" полусухое |
13 |
|
Итог Количество по полю Вино "Киянь" полусладкое |
9 |
|
Итог Количество по полю Вино "Лебедь" полусладкое |
12 |
|
Итог Количество по полю Вино "Монастырская изба" полусухое |
12 |
|
Итог Количество по полю Водка "Medoff" |
9 |
|
Итог Количество по полю Водка "Nemiroff" |
5 |
|
Итог Количество по полю Водка "Медова з перцем" |
7 |
|
Итог Количество по полю Коньяк "Ариана" |
8 |
|
Итог Количество по полю Коньяк "Каховка" |
7 |
|
Итог Количество по полю Коньяк "Таврия" |
7 |
Рис. 9 Данные о взаимопродажах
Регрессионный анализ товарооборота
Основная задача этого этапа — получение аналитической зависимости между продажами отдельных видов ТМЦ. Рекомендуем такую схему работы.
В качестве исходных данных используем таблицу динамики продаж. По структуре продаж выбираем ТМЦ (Т.М. "Смайлик" драже "Арахис в какао" (новинка)), для которого будет выполняться прогноз объемов реализации.
По данным корреляционного анализа определяем ТМЦ, объемы реализации которых взаимосвязаны с товаром А.
Преобразуем таблицу так, чтобы ТМЦ "Смайлик" располагался в левом столбце (это будет независимая переменная Y), а остальные ТМЦ располагались в смежных колонках справа (это зависимые переменные Х). Пример таблицы показан на рис. 10 (колонки А-E).
Дата |
"Мускат игристый" розовый |
Бальзам |
"Рандеву" полусладкое |
"Крымское шампанское" полусладкое |
02.08.2006 |
0 |
45 |
0 |
0 |
03.08.2006 |
0 |
5 |
0 |
0 |
04.08.2006 |
0 |
0 |
20 |
0 |
05.08.2006 |
0 |
0 |
28 |
0 |
06.08.2006 |
58 |
40 |
46 |
59 |
07.08.2006 |
58 |
0 |
0 |
0 |
08.08.2006 |
0 |
0 |
25 |
0 |
09.08.2006 |
0 |
0 |
0 |
0 |
10.08.2006 |
0 |
0 |
50 |
0 |
11.08.2006 |
0 |
0 |
0 |
10 |
12.08.2006 |
40 |
20 |
0 |
10 |
13.08.2006 |
0 |
20 |
0 |
30 |
14.08.2006 |
0 |
0 |
20 |
8 |
15.08.2006 |
50 |
20 |
0 |
0 |
16.08.2006 |
0 |
10 |
0 |
0 |
17.08.2006 |
0 |
0 |
0 |
30 |
18.08.2006 |
0 |
0 |
30 |
0 |
19.08.2006 |
50 |
0 |
0 |
0 |
20.08.2006 |
20 |
50 |
0 |
0 |
21.08.2006 |
0 |
35 |
40 |
0 |
22.08.2006 |
0 |
2 |
0 |
47 |
23.08.2006 |
60 |
0 |
0 |
21 |
24.08.2006 |
37 |
37 |
0 |
37 |
25.08.2006 |
45 |
10 |
30 |
0 |
26.08.2006 |
0 |
48 |
20 |
0 |
27.08.2006 |
0 |
0 |
0 |
0 |
28.08.2006 |
0 |
0 |
0 |
0 |
29.08.2006 |
0 |
0 |
0 |
20 |
30.08.2006 |
58 |
0 |
0 |
16 |
31.08.2006 |
20 |
4 |
35 |
30 |
Рис. 10
Формулы Excel для прогнозируемого объема продаж ТМЦ «Смайлик»
Уважаемый посетитель!
Чтобы распечатать файл, скачайте его (в формате Word).
Ссылка на скачивание - внизу страницы.