Валентин Арьков - Бизнес-аналитика. Сводные таблицы. Часть 2. Учебное пособие

Задание. Запишите в отчёте ответ на следующий вопрос. Какой вид корреляции между спросом и предложением заложен в нашей модели и насколько это соответствует действительности?

Ко всем значениям спроса и предложения мы добавим случайную составляющую. Это будет разброс вокруг цены и количества товара в каждой покупке. Случайный разброс составит плюс-минус указанное количество процентов. В нулевом варианте это плюс-минус 20%.

Случайную составляющую будем моделировать как числа с нормальным распределением. Значит, разброс в 2 процента составит три сигмы. Находим сигму:

сигма = 20% / 3 = 0,2 / 3 = 0,066667.

Задание. Определите величину сигмы для своего варианта.

У нас всё готово для моделирования исходных данных. Как и в предыдущей работе, мы создаём таблицу транзакций. Во всех вариантах у нас будет 10000 записей, то есть строк.

Процедура имитационного моделирования тоже была подробно описана в предыдущей работе. Напомним, что для каждого запуска генератора случайных чисел нужно устанавливать новое начальное значение. Оно вводится в окне с загадочным названием

Random Seed – Случайное рассеивание.

Создаём таблицу транзакций в привычном порядке.

Вначале генерируем случайные даты. Мы выбрали номера дней начала и конца интервала по времени. Вызываем генератор:

Data – Analysis – Data Analysis – Random Number Generation.

Задаём параметры генератора (рис. 5.1).

Рис. 5.1. Настройки генератора

Округляем случайные числа, копируем в буфер и вставляем как значения. Задаём формат ячеек в виде даты. Вся процедура тоже должна быть знакома по предыдущей работе.

Задание. Сгенерируйте столбец дат.

Следующий этап – товары. Начинаем с идентификатора товара. Целое число от 1 до 6. Не забываем установить новое состояние генератора случайных чисел.

Задание. Сгенерируйте столбец идентификаторов товаров.

Далее используем функцию

VLOOKUP

ВПР.

Подставляем категорию, название и цену из справочника товаров.

Задание. Подставьте данные из справочника товаров.

Мы подставили постоянные цены на товары. Добавим к ценам сезонные колебания (рис. 5.2). Это элемент мультипликативной модели. Мы умножаем постоянное значение цены на сезонную составляющую.

Рис. 5.2. Сезонные колебания цен

Задание. Сгенерируйте сезонность цен.

Затем генерируем количество товара с помощью мультипликативной модели:

y (t) = T * S * E.

Рис. 5.2. Мультипликативная модель

Чтобы не запутаться в формулах, создадим вспомогательные столбцы для тренда, сезонности и случайности. Затем соберём из них количество товара и округлим до граммов.

Задание. Сгенерируйте количество товара.

Проверим, что получилось в результате моделирования. Построим диаграмму разброса «Дата – Количество» (рис. 5.3). Тренд – общая тенденция от 2 до 4. Сезонность присутствует. Случайный разброс увеличивается вместе с ростом среднего значения. Пока грубых ошибок не обнаружено. Вычисляем стоимость в рублях.

Убираем всё лишнее. Для этого копируем нужные колонки и вставляем как значения. Удаляем вспомогательные столбцы.

Рис. 5.3. Данные по количеству

Задание. Сгенерируйте данные по товарам.

Следующий этап – ФО, регионы, города.

Начинаем с идентификатора города. В нулевом варианте это должно быть целое число от 1 до 12.

Генерируем равномерно распределённые числа. Опять с новым начальным состоянием. Округляем. Копируем и вставляем как значения.

Затем подставляем данные по городам из справочника городов. Копируем и вставляем как значения.

Удаляем вспомогательные столбцы.

Задание. Сгенерируйте данные по городам.

5.4. Таблица транзакций