Использование функций excel для прогнозирования экономических процессов реферат

Обновлено: 05.07.2024

Ехсеl можно использовать как для решения простых задач учета, так и для составления различных бланков, деловой графики и даже полного баланса Фирмы. Например, на предприятии с помощью Ехсе1 можно облегчать решение таких задач, как обработка заказов и планирование производства, расчет налогов и заработной платы, учет кадров и издержек управление сбытом и имуществом и многих других.

Содержание

ВВЕДЕНИЕ 3
ГЛАВА 1 ФУНКЦИИ EXCEL 4
ГЛАВА 2 ФИНАНСОВЫЕ ФУНКЦИИ EXCEL 6
2.1 Понятие финансовых функций 6
2.2 Функции вычисления эффективных и номинальных процентных ставок 7
2.3 Функции для расчетов при условии постоянства процентной ставки и периодических платежей 8
2.4 Функции для вычисления чистой приведенной стоимости и внутренней ставки доходности 10
2.5 Функции для расчета амортизации 11
2.6 Функции для работы с ценными бумагами, купонами и чеками 13
2.7 Функции преобразования числовых значений 15
ГЛАВА 3 ПРИМЕР ПРАКТИЧЕСКОГО ИСПОЛЬЗОВАНИЯ ФИНАНСОВЫХ ФУНКЦИЙ 16
ЗАКЛЮЧЕНИЕ 17
СПИСОК ИСПОЛЬЗОВАННОЙ ЛИТЕРАТУРЫ 18

Прикрепленные файлы: 1 файл

информационные технологии.docx

БЕЛОРУССКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ

фИНАНСОВЫЕ ФУНКЦИИ MICROSOFT EXCEL

ВВЕДЕНИЕ

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

Широкое распространение программ обработки электронных таблиц во многом объясняется универсальными возможностями их применения, поскольку без вычислений, в широком смысле этого слова, не обойтись в самых различных сферах нашей жизни.

Ехсеl можно использовать как для решения простых задач учета, так и для составления различных бланков, деловой графики и даже полного баланса Фирмы. Например, на предприятии с помощью Ехсе1 можно облегчать решение таких задач, как обработка заказов и планирование производства, расчет налогов и заработной платы, учет кадров и издержек управление сбытом и имуществом и многих других.

ГЛАВА 1
ФУНКЦИИ EXCEL

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

С помощью функций даты и времени можно решить практически любые задачи, связанные с учетом даты или времени (например, определить возраст, вычислить стаж работы, определить число рабочих дней на любом промежутке времени).

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

В Excel широко представлены математические функции. Например, можно выполнять различные операции с матрицами: умножать, находить обратную, транспонировать.

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

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

Значения, возвращаемые функциями в качестве ответа, называются результатами.

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

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

Функции Excel – это специальные, заранее созданные формулы для сложных вычислений, в которые пользователь должен ввести только аргументы.

Каждая встроенная функция Excel имеет название или уникальное имя. В формулах функция выглядит следующим образом: Название функции (Аргументы), где Название функции – это уникальное имя функции, а Аргументы – это список аргументов функции.

Ввести функцию в формулу можно несколькими способами, но, независимо от способа ввода функций, необходимо:

• правильно ввести имя функции;
• правильно задать аргументы функции.

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

При работе с функциями главная задача пользователя – правильно задать ее аргументы. Непосредственно процесс получения результата скрыт от глаз пользователя.

ГЛАВА 2
ФИНАНСОВЫЕ ФУНКЦИИ EXCEL

2.1 Понятие финансовых функций

Финансовые функции – это совокупность необходимых процессов, циклов и подразделений, которые взятые вместе:

• способствуют получению выручки на всех стадиях с помощью проведения финансового анализа;
• управляют расходами с помощью анализа необходимости закупок, утверждения всех расходов (включая капиталовложения) в масштабе всего предприятия;
• следят за имеющимися финансовыми средствами и обязательствами;
• управляют поступлениями и расходованиями денежных средств, включая оплату налогов;
• ведут поиск оптимальных источников и условий финансирования;
• способствуют проведению бартерных операций;
• осуществляет финансовый анализ проектов на всех этапах;
• выполняют работу по составлению бюджета, планированию и прогнозированию как для предприятия, так и его подразделений;
• учитывают и регистрируют каждую операцию, проводимую предприятием;
• способствуют распределению заработанного между сотрудниками и акционерами.

Основная роль финансовой функции состоит в оптимизации использования предприятием своих ресурсов.

Программа Excel предлагает более пятидесяти финансовых функций для самых разнообразных финансовых расчётов. Эти функции, как никакие другие, требуют понимания того, что они делают, а также знания соответствующей терминологии.

Финансовые вычисления необходимы для успешного проведения любой коммерческой сделки. В комплексе с современными методами анализа и моделирования финансовых ситуаций финансовые вычисления перерастают во влиятельное направление организации и управления предпринимательской деятельностью – финансовый менеджмент.

Для применения финансовых функций следует использовать мастер функций с помощью команды меню ВСТАВКА – ФУНКЦИИ или кнопки fx в строке формул Excel.

По типу решаемых задач все финансовые функции Excel можно разделить на условные группы:

1. функции для анализа аннуитетов и инвестиционных проектов (аннуитет – это ряд постоянных денежных выплат в течение длительного периода);
2. функции для анализа ценных бумаг;
3. функции для расчета амортизационных платежей;
4. вспомогательные функции.

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

Технология работы с финансовыми функциями на рабочих листах Excel в целом не отличается от работы с другими функциями:

• подготовка исходных значений основных аргументов функции;
• для расчета финансовой функции курсор устанавливается в нужную ячейку и вызывается с панели задач Диспетчер функций;
• из появившегося списка выбираем в разделе финансовых функций необходимую;
• вводим аргументы функций;
• получаем результат.

2.2 Функции вычисления эффективных и номинальных процентных ставок

Функции этого раздела взаимообратные: одна вычисляет эффективную процентную ставку по номинальной процентной ставке, а другая – номинальную по эффективной.

Возвращает номинальную ставку, если заданы эффективная ставка и число периодов, за которые начисляются сложные проценты.

• Аргумент ставка – положительное действительное число, задающее значение эффективной процентной ставки;
• Аргумент кол_периодов – положительное целое число, задающее количество периодов;
• Функцию нельзя использовать в формулах массивов.

Возвращает эффективную (фактическую) процентную ставку, если заданы номинальная процентная ставка и количество периодов, за которые начисляются сложные проценты.

• Аргумент ставка – положительное действительное число, задающее значение номинальной процентной ставки;
• Аргумент кол_периодов – положительное целое число, задающее количество периодов;
• Функцию нельзя использовать в формулах массивов.

2.3 Функции для расчетов при условии постоянства процентной ставки и периодических платежей

Аргумент ставка – действительное число, задающее величину процентной ставки за один период.

Аргумент кол_периодов – положительное целое число, задающее количество периодов.

Аргумент кпер – действительное число, задающее количество периодов.

Аргумент плт – действительное число, задающее величину платежа.

Аргумент пс – действительное число, задающее приведенную стоимость.

Аргумент тип – принимает значение 0 или 1 и определяет момент выплаты.

Аргумент бс – действительное число, задающее будущую стоимость или остаток средств после последней выплаты.

Аргумент предложение – действительное число, задающее предполагаемую величину ставки.

Возвращает будущую стоимость инвестиции на основе периодических равных по величине платежей и постоянной процентной ставки.

Возвращает приведенную стоимость инвестиции при условии периодических равных по величине платежей и постоянной процентной ставки.

Возвращает сумму периодического платежа при условии постоянства сумм платежей и постоянства процентной ставки.

Функция возвращает процентную ставку за один период.

Функция возвращает общее количество периодов выплаты для инвестиции на основе периодических постоянных выплат и постоянной процентной ставки.

При выполнении регулярных выплат, например для погашения кредита на основе фиксированной процентной ставки, сумма выплат состоит из основной части, идущей на погашение кредита, и начисленных процентов. Функция ОСПЛТ вычисляет основную часть выплат за один период.

Прогнозирование имеет две стороны конкретизации: предсказательную (описательную); предуказательную (предписательную). Предсказание означает описание возможных или желательных перспектив, состояний, решений проблем будущего. Предуказание означает решение этих проблем путем использования информации о будущем в целенаправленной деятельности. Таким образом, в прогнозировании различают два аспекта: теоретико-познавательный и управленческий.

Оглавление

1 Основные понятия прогнозирование деятельности предприятия 3
2 Программное обеспечение для прогнозирования деятельности предприятия 6
3 Технология прогнозирования деятельности предприятия с помощью Microsoft Excel 9
Список использованных источников 13

Файлы: 1 файл

Технология прогнозирования деятельности предприятия.doc

Технология прогнозирования деятельности предприятия (Прогнозирование с помощью Microsoft Excel)

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

Прогнозирование имеет две стороны конкретизации: предсказательную (описательную); предуказательную (предписательную). Предсказание означает описание возможных или желательных перспектив, состояний, решений проблем будущего. Предуказание означает решение этих проблем путем использования информации о будущем в целенаправленной деятельности. Таким образом, в прогнозировании различают два аспекта: теоретико-познавательный и управленческий.

В основе экономического прогнозирования лежит предположение о том, что будущее состояние экономики в значительной мере предопределяется ее прошлым и настоящим состояниями. Будущее несет в себе и элементы неопределенности. Это объясняется следующими моментами:

1. наличием не одного, а множества вариантов возможного развития;
2. зависимостью действий экономических законов в будущем не только от прошлого и настоящего состояний экономики, но и от управленческих решений, которые еще только должны быть приняты и реализованы;
3. неполнотой степени познания экономических законов, дефицитом и недостаточной надежностью информации.

Единство определенности (детерминированности) и неопределенности будущего – решающая предпосылка экономического прогнозирования. Если бы будущее было полностью определенным, то тогда бы не было потребности в прогнозировании. При неопределенности будущего сама возможность экономического прогнозирования исключается.

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

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

В процессе экономического прогнозирования используются как общие научные методы и подходы к исследованию, так и специфические методы, свойственные социально-экономическому прогнозированию. В числе общих методов можно выделить следующие:

1. интуитивные методы;
2. формализованные методы.

Классификация данных методов представлена на рис. 1.


Рис. 1. Методы прогнозирования


Интуитивные методы применяются, когда невозможно учесть все факторы, влияющие на объект прогнозирования. Это может произойти из-за сложности объекта или большого количества влияющих факторов. Второе название интуитивных методов прогнозирования – экспертные оценки. В свою очередь экспертные оценки могут быть индивидуальными или
коллективными.

Существует несколько видов индивидуальных экспертных оценок основные из них это:

Основными методами коллективных экспертных оценок можно назвать следующие:

Формализованные методы прогнозирования основаны на математических и статистических методах. Они считаются более точными для простых объектов прогнозирования. Они делятся на:

1. методы моделирования;
2. методы экстраполяции.

Сущностью метода моделирования является создание математической модели объекта прогнозирования с исходными и интересующими нас параметрами. В результате обработки исходных параметров модель выдает прогнозируемые показатели.

За основу метода экстраполяции взята инертность экономических процессов. Прогнозы, основанные на методе экстраполяции, строятся так: по статистическим данным объекта прогнозирования определяется динамика его развития и применяется к текущим показателям. Метод экстраполяции широко применяется при краткосрочном прогнозировании.

Для прогнозирования деятельности предприятия можно использовать следущие программные продукты (ПП):

– MS Excel содержит ряд родственных статистических функций для экстраполяционного анализа. К ним относят функции ТЕНДЕНЦИЯ, РОСТ, ЛИНЕЙН, ЛГРФПРИБЛ.

Инструментальные средства MS Excel для работы с трендами можно разделить на вспомогательные, промежуточные и основные.

Вспомогательные инструменты ускоряют построение числовых рядов периодов времени. Сюда входят опции Правка, Заполнить, Прогрессия и диалоговое окно Прогрессия, а также возможность растягивания двухклеточного ряда до необходимого диапазона.

Промежуточные инструменты строят XY-графики зависимости показателя от времени. В MS Excel инструментарий расчета и моделирования трендов до получения таких графиков заблокирован.

• Forecast Expert – ПП позволяет проанализировать имеющиеся данные и построить прогноз с указанием интервала на произвольный период времени. При этом ПП позволяет отслеживать соотношения между полученными значениями и некоторым рядом пороговых значений. Программа автоматически выбирает тот тип модели, который наиболее соответствует характеру процесса.

Система предназначена для:

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

2. Прогнозирование потоков денежных средств, доходов и расходов компании, для определения вероятности кассовых разрывов.

3. Для составления плана производства продукции и ее реализации.

• Statistica – Система для статистического анализа данных, включающая широкий набор аналитических процедур и методов: более 10000 различных типов графиков, описательные и внутригрупповые статистики, разведочный анализ данных, корреляции, быстрые основные статистики и блоковые статистики, интерактивный вероятностный калькулятор, T-критерии (и другие критерии групповых различий), таблицы частот, сопряженности, флагов и заголовков, анализ многомерных откликов, множественная регрессия, непараметрические статистики, общая модель дисперсионного и ковариационного анализа, подгонка распределений. Приведено описание только базового блока.

Также существуют дополнительные блоки: Линейные / нелинейные модели, Многомерные разведочные технологии, Анализ мощности, Нейронные сети, Data Mining, Карты контроля качества, Анализ Процессов, Планирование экспериментов и др.)

• Project Expert – дает возможность проанализировать несколько вариантов достижения целей развития предприятия и выбрать из них оптимальный. При этом можно оценить запас прочности бизнеса как производную риска изменения важнейших факторов, влияющих на реализацию проекта. Программа также позволяет оценить, как исполнение бизнес-плана повлияет на эффективность деятельности предприятия, рассчитать срок окупаемости проекта, спрогнозировать общие показатели эффективности для группы инвестиционных проектов, финансируемых из общего бюджета.

Project Expert позволяет создать безупречный бизнес-план предприятия, соответствующий международным стандартам (МСФО), подготовить предложения для региональной инвестиционной программы и/или стратегического инвестора, определив для каждого из участников общий экономический эффект от реализации инвестиционного проекта и эффективность инвестиций в него.

Project Expert позволяет проанализировать планируемую структуру затрат и прибыльность отдельных подразделений и видов продукции, определить минимальный объем выпуска продукции и предельные издержки, подобрать производственную программу и оборудование, схемы закупок и варианты сбыта.

Project Expert поможет проконтролировать выполнение бизнес-плана предприятия, сравнивая в ходе реализации его плановые и фактические показатели.

Project Expert позволяет гибко учитывать изменения в экономическом окружении и оперативно отражать изменения. Программа рекомендована к использованию Минэкономики России и структурами регионального уровня как стандартный инструмент для разработки бизнес-планов предприятий. В основу Project Expert положена методика UNIDO по оценке инвестиционных проектов и методика финансового анализа, определенная международными стандартами IAS .

Благодаря возможностям динамического обмена данными с Excel , передачи отчетов в Word и сохранения их в формате HTML , Project Expert может использоваться и как самостоятельная аналитическая программа, и как составная часть информационно-аналитической системы предприятия.

Система выпускается в нескольких версиях: от Standard – для небольших предприятий – до Professional и PIC - Holding – для крупных корпораций и холдингов – и существует в локальном и сетевом вариантах. Среди пользователей Project Expert свыше 4500 организации.

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

Современное общество постоянно испытывает необходимость в прогнозировании. Например, чтобы выработать правильную политику, члены правительства должны прогнозировать уровни безработицы, инфляции, промышленного производства, подоходного налога отдельных лиц и корпораций. Чтобы определить потребности в оборудовании и персонале, директора авиакомпаний должны правильно предсказать объем авиаперевозок.

Существуют два общепринятых подхода к прогнозированию: качественный и количественный. Методы качественного прогнозирования (qualitative forecasting methods) особенно важны, если исследователю недоступны количественные данные. Как правило, эти методы носят весьма субъективный характер.




Прогнозирование – это очень важный элемент практически любой сферы деятельности, начиная от экономики и заканчивая инженерией. Существует большое количество программного обеспечения, специализирующегося именно на этом направлении. К сожалению, далеко не все пользователи знают, что обычный табличный процессор Excel имеет в своем арсенале инструменты для выполнения прогнозирования, которые по своей эффективности мало чем уступают профессиональным программам. Давайте выясним, что это за инструменты, и как сделать прогноз на практике.

Процедура прогнозирования

Целью любого прогнозирования является выявление текущей тенденции, и определение предполагаемого результата в отношении изучаемого объекта на определенный момент времени в будущем.

Способ 1: линия тренда

Одним из самых популярных видов графического прогнозирования в Экселе является экстраполяция выполненная построением линии тренда.

Попробуем предсказать сумму прибыли предприятия через 3 года на основе данных по этому показателю за предыдущие 12 лет.







• Линейная;
• Логарифмическая;
• Экспоненциальная;
• Степенная;
• Полиномиальная;
• Линейная фильтрация.

Давайте для начала выберем линейную аппроксимацию.










Способ 2: оператор ПРЕДСКАЗ

Экстраполяцию для табличных данных можно произвести через стандартную функцию Эксель ПРЕДСКАЗ. Этот аргумент относится к категории статистических инструментов и имеет следующий синтаксис:

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

При вычислении данным способом используется метод линейной регрессии.

Давайте разберем нюансы применения оператора ПРЕДСКАЗ на конкретном примере. Возьмем всю ту же таблицу. Нам нужно будет узнать прогноз прибыли на 2018 год.
















Но не стоит забывать, что, как и при построении линии тренда, отрезок времени до прогнозируемого периода не должен превышать 30% от всего срока, за который накапливалась база данных.

Способ 3: оператор ТЕНДЕНЦИЯ

Для прогнозирования можно использовать ещё одну функцию – ТЕНДЕНЦИЯ. Она также относится к категории статистических операторов. Её синтаксис во многом напоминает синтаксис инструмента ПРЕДСКАЗ и выглядит следующим образом:

=ТЕНДЕНЦИЯ(Известные значения_y;известные значения_x; новые_значения_x;[конст])

Данный оператор наиболее эффективно используется при наличии линейной зависимости функции.

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










Способ 4: оператор РОСТ

Ещё одной функцией, с помощью которой можно производить прогнозирование в Экселе, является оператор РОСТ. Он тоже относится к статистической группе инструментов, но, в отличие от предыдущих, при расчете применяет не метод линейной зависимости, а экспоненциальной. Синтаксис этого инструмента выглядит таким образом:

=РОСТ(Известные значения_y;известные значения_x; новые_значения_x;[конст])

Как видим, аргументы у данной функции в точности повторяют аргументы оператора ТЕНДЕНЦИЯ, так что второй раз на их описании останавливаться не будем, а сразу перейдем к применению этого инструмента на практике.










Способ 5: оператор ЛИНЕЙН

Оператор ЛИНЕЙН при вычислении использует метод линейного приближения. Его не стоит путать с методом линейной зависимости, используемым инструментом ТЕНДЕНЦИЯ. Его синтаксис имеет такой вид:

=ЛИНЕЙН(Известные значения_y;известные значения_x; новые_значения_x;[конст];[статистика])

Последние два аргумента являются необязательными. С первыми же двумя мы знакомы по предыдущим способам. Но вы, наверное, заметили, что в этой функции отсутствует аргумент, указывающий на новые значения. Дело в том, что данный инструмент определяет только изменение величины выручки за единицу периода, который в нашем случае равен одному году, а вот общий итог нам предстоит подсчитать отдельно, прибавив к последнему фактическому значению прибыли результат вычисления оператора ЛИНЕЙН, умноженный на количество лет.













Как видим, прогнозируемая величина прибыли, рассчитанная методом линейного приближения, в 2019 году составит 4614,9 тыс. рублей.

Способ 6: оператор ЛГРФПРИБЛ

Последний инструмент, который мы рассмотрим, будет ЛГРФПРИБЛ. Этот оператор производит расчеты на основе метода экспоненциального приближения. Его синтаксис имеет следующую структуру:

= ЛГРФПРИБЛ (Известные значения_y;известные значения_x; новые_значения_x;[конст];[статистика])

Как видим, все аргументы полностью повторяют соответствующие элементы предыдущей функции. Алгоритм расчета прогноза немного изменится. Функция рассчитает экспоненциальный тренд, который покажет, во сколько раз поменяется сумма выручки за один период, то есть, за год. Нам нужно будет найти разницу в прибыли между последним фактическим периодом и первым плановым, умножить её на число плановых периодов (3) и прибавить к результату сумму последнего фактического периода.













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

Мы выяснили, какими способами можно произвести прогнозирование в программе Эксель. Графическим путем это можно сделать через применение линии тренда, а аналитическим – используя целый ряд встроенных статистических функций. В результате обработки идентичных данных этими операторами может получиться разный итог. Но это не удивительно, так как все они используют разные методы расчета. Если колебание небольшое, то все эти варианты, применимые к конкретному случаю, можно считать относительно достоверными.




Мы рады, что смогли помочь Вам в решении проблемы.


Отблагодарите автора, поделитесь статьей в социальных сетях.





Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.





Научитесь использовать все прикладные инструменты из функционала MS Excel.

Постановка задачи

Исходные данные

Для начала, давайте определимся, какие у нас есть исходные данные и что нам нужно получить на выходе. Фактически, все что у нас есть, это некоторые исторические данные. Если мы говорим о прогнозировании продаж, то историческими данными будут продажи за предыдущие периоды.

Примечание. Собранные в разные моменты времени значения одной и той же величины образуют временной ряд. Каждое значение такого временного ряда называется измерением. Например: данные о продажах за последние 5 лет по месяцам — временной ряд; продажи за январь прошлого года — измерение.

Составляющие прогноза

Следующий шаг: давайте определимся, что нам нужно учесть при построении прогноза. Когда мы исследуем наши данные, нам необходимо учесть следующие факторы:

• Изменение нашей пронозируемой величины (например, продаж) подчиняется некоторому закону. Другими словами, в временном ряде можно проследить некую тенденцию. В математике такая тенденция называется трендом.
• Изменение значений в временном ряде может зависить от промежутка времени. Другими словами, при построении модели необходимо будет учесть коэффициент сезонности. Например, продажи арбузов в январе и августе не могут быть одинаковыми, т.к. это сезонный продукт и летом продажи значительно выше.
• Изменение значений в временном ряде периодически повторяется, т.е. наблюдается некоторая цикличность.

Эти три пункта в совокупность образуют регулярную составляющую временного ряда.

Примечание. Не обязательно все три элемента регулярной составляющей должны присутствовать в временном ряде.

Однако, помимо регулярной составляющей, в временном ряде присутствует еще некоторое случайное отклонение. Интуитивно это понятно — продажи могут зависеть от многих факторов, некоторые из которых могут быть случайными.

Вывод. Чтобы комплексно описать временной ряд, необходимо учесть 2 главных компонента: регулярную составляющую (тренд + сезонность + цикличность) и случайную составляющую.

Виды моделей

Следующий вопрос, на который нужно ответить при построении прогноза: “А какие модели временного ряда бывают?”

Обычно выделяют два основных вида:

• Аддитивная модель: Уровень временного ряда = Тренд + Сезонность + Случайные отклонения
• Мультипликативная модель: Уровень временного ряда = Тренд X Сезонность X Случайные отклонения

Иногда также выделают смешанную модель в отдельную группу:

• Смешанная модель: Уровень временного ряда = Тренд X Сезонность + Случайные отклонения

Классический вариант такой:
— Аддитивная модель используется, если амплитуда колебаний более-менее постоянная;
— Мультипликативная – если амплитуда колебаний зависит от значения сезонной компоненты.




Решение задачи с помощью Excel

Итак, необходимые теоретические знания мы с вами получили, пришло время применить их на практике. Мы будем с вами использовать классическую аддитивную модель для построения прогноза. Однако, мы построим с вами два прогноза:

1. с использованием линейного тренда
2. с использованием полиномиального тренда

Во всех руководствах, как правило, разбирается только линейный тренд, поэтому полиномиальная модель будет крайне полезна для вас и вашей работы!




Научитесь использовать все прикладные инструменты из функционала MS Excel.

Модель с линейным трендом

Пусть у нас есть исходная информация по продажам за 2 года:




Учитывая, что мы используем линейный тренд, то нам необходимо найти коэффициенты уравнения

• y — значения продаж
• x — номер периода
• a — коэффициент наклона прямой тренда
• b — свободный член тренда

Рассчитать коэффициенты данного уравнения можно с помощью формулы массива и функции ЛИНЕЙН. Нам необходимо будет сделать следующую последовательность действий:

1. Выделяем две ячейки рядом
2. Ставим курсор в поле формул и вводим формулу =ЛИНЕЙН(C4:C27;B4:B27)
3. Нажимаем Ctrl+Shift+Enter, чтобы активировать формулу массива

На выходе мы получили 2 числа: первое — коэффициент a, второе — свободный член b.




Теперь нам нужно рассчитать для каждого периода значение линейного тренда. Сделать это крайне просто — достаточно в полученное уравнение подставить известные номера периодов. Например, в нашем случае, мы прописываем формулу =B4*$F$4+$G$4 в ячейке I4 и протягиваем ее вниз по всем периодам.




Нам осталось рассчитать коэффициент сезонности для каждого периода. Учитывая, что у нас есть исторические данные за два года, разумно будет учесть это при расчете. Можем сделать следующим образом: в ячейке J4 прописываем формулу =(C4+C16)/СРЗНАЧ($C$4:$C$27)/2 и протягиваем вниз на 12 месяцев (т.е. до J15).




Что нам это дало? Мы посчитали, сколько суммарно продавалось каждый январь/каждый февраль и так далее, а потом разделили это на среднее значение продаж за все два периода.

То есть мы выяснили, как продажи двух январей отклонялись от средних продаж за два года, как продажи двух февралей отклонялись и так далее. Это и дает нам коэффициент сезонности. В конце формулы делим на 2, т.к. в расчете фигурировало 2 периода.

Примечание. Рассчитали только 12 коэффициентов, т.к. один коэффициент учитывает продажи сразу за 2 аналогичных периода.

Итак, теперь мы на финишной прямой. Нам осталось рассчитать тренд для будущих периодов и учесть коэффициент сезонности для них. Давайте амбициозно построим прогноз на год вперед.

Сначала создаем столбец, в котором прописываем номера будущих периодов. В нашем случае нумерация начинается с 25 периода.

Далее, для расчета значения тренда просто прописываем уже известную нам формулу =L4*$F$4+$G$4 и протягиваем вниз на все 12 прогнозируемых периодов.

И последний штрих — умножаем полученное значение на коэффициент сезонности. Вуаля, это и есть итоговый ответ в данной модели!




Модель с полиномиальным трендом

Конструкция, которую мы только что с вами построили, достаточно проста. Но у нее есть один большой минус — далеко не всегда она дает достоверные результаты.

Посмотрите сами, какая модель более точно аппроксимирует наши точки — линейный тренд (прямая зеленая линия) или полиномиальный тренд (красная кривая)? Ответ очевиден. Поэтому сейчас мы с вами и разберем, как построить полиномиальную модель в Excel.




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

Для начала давайте определимся, чем полиномиальный тренд отличается от обычного линейного. Правильно — формой уравнения. У линейного тренда мы разбирали обычный график прямой:




У полиномиального тренда же уравнение выглядит иначе:

где конечная степень определяется степенью полинома.

Т.е. для полинома 4 степени необходимо найти коэффициенты уравнения:

Согласитесь, выглядит немного страшно. Однако, ничего страшного нет, и мы с легкостью можем решить эту задачку с помощью уже известных нам методов.

Кстати говоря, мы можем легко сами себя проверить. Давайте построим график наших продаж и добавим к нему полиномиальный тренд.

Теперь вы наглядно можете видеть, как рассчитанный тренд аппроксимирует исходные данные и как выглядит само уравнение. Можно сравнить уравнение на графике с вашими коэффициентами. Сходится? Значит сделали все верно!

Помимо всего прочего, вы можете сразу оценить точность аппроксимации (не полностью, но хотя бы первично). Это делается с помощью коэффициента R^2. Тут у вас снова есть два пути:

Заключение

Мы с вами подробно разобрали вопрос прогнозирования — изучили необходимые термины и виды моделей, построили аддитивную модель в Excel с использованием линейного и полиномиального тренда, а также научились отображать результаты своих вычислений на графиках. Все это позволит вам эффективно внедрять полученные знания на работе, усложнять существующие модели и уточнять прогнозы. Чем большим количеством методов и инструментов вы будете владеть, тем выше будет ваш профессиональный уровень и статус на рынке труда.

Автор: Алексанян Андрон, эксперт SF Education




Научитесь использовать все прикладные инструменты из функционала MS Excel.

Читайте также:

  
• Право фрг модель германской правовой группы реферат
  
• Реферат история развития фармакологии в россии
  
• Внутренние и внешние факторы риска реферат
  
• Олимпийские чемпионы по лыжным гонкам из россии реферат
  
• Реферат параметры потребителей и источников электроэнергии