Excel: план, факт, прогноз

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

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

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

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

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

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

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

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

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

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

Виды моделей

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

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

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

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

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

С моделями мы определились, но теперь возникает еще один вопрос: «А когда какую модель лучше использовать?»

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

Как посчитать процент выполнения плана в Excel

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

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

Обратите внимание, что в последнем столбце где указан результат выполнения плана в процентах изменен формат ячеек на «процентный». А формулы в этом столбце весьма простые – значение столбца «Продано» делиться на значение в столбце «План» =C2/B2

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

Зачем нужно рассчитывать

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

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

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

Зачем нужно рассчитывать

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

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

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

Экономико-статистические методы

Экономико-статистические методы включают широкий спектр различных методов. Не все они получили широкое распространение в практике . Наиболее популярными являются:

Метод экстраполяции – определяет объем продаж как функцию времени (или иного фактора). Как обычно в таких случаях необходим учет динамического (временного) ряда показателя

• Метод скользящей средней величины продаж. Планируемый объем продаж определяется делением фактического объема продаж на продолжительность анализируемого периода. Скользящая средняя величина пересчитывается по мере получения новых значений;
• Метод доли рынка предприятия – в основе данные о среднегодовых темпах роста емкости целевого рынка в прошедшем периоде и планируемой доли предприятия в общем объеме продаж

Планирование сбыта может происходить на основе показателей прибыли и рентабельности, необходимых фирме. Такой расчёт использует понятие «точка безубыточности», которая характеризует пограничный, т.е. нулевой по прибыльности объём работы.

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

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

Как построить линию тренда в MS Excel

Щелкните правой кнопкой мыши по одному из «синих» столбцов, и в контекстном меню выберите пункт «Добавить линию тренда» .

На листе диаграммы теперь отображается пунктирная линия тренда. Как видите, она не совпадает на 100% со значениями диаграммы — построенная по средневзвешенным значениям, она лишь в общих чертах повторяет её направление. Однако это не мешает нам видеть устойчивый рост числа посещений сайта — на общем результате не сказывается даже «летняя» просадка.

Линия тренда для столбца «Посетители»

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

Ещё одна линия тренда позволяет прояснить ситуацию

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

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

Полезный сигнал и шум

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

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

Функции листа прогноза

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

1. Средняя (AVERAGE) – функция, которая вычисляет среднее значение числовых данных. Она может быть полезна при анализе большого объема данных и вычислении среднего значения данных за определенный период времени.
2. Минимум (MIN) и Максимум (MAX) – функции, которые находят наименьшее и наибольшее значения в диапазоне данных соответственно. Использование этих функций позволяет легко определить минимальное и максимальное значение данных.
3. Процент (PERCENT) – функция, которая вычисляет процент от численного значения. Она может быть полезна при рассчете процентного соотношения конкретных данных относительно общего объема данных.
4. Сумма (SUM) – функция, которая суммирует числовые значения в диапазоне данных. Это может быть полезно для выявления общей суммы данных за определенный период.
5. Стандартное отклонение (STDEV) – функция, которая вычисляет стандартное отклонение числовых данных. Она позволяет определить, насколько сильно значения данных расходятся вокруг среднего значения.

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

Возможности программы Excel

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

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

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

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

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

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

Расчет отклонения от плана в excel

Формула отклонения факта от плана такая:

Отклонение от плана = (Факт / План — 1) * 100%

Воспользуемся тем же примером и посчитаем отклонение фактических показателей от плана продаж. 

Отклонение от плана “сок вишневый” = (118 / 110 — 1) * 100% = 7,27%
Отклонение от плана “сок яблочный” = (133 / 150 — 1) * 100% = -11,33%

В первом случае отклонение от плана положительное (больше нуля), следовательно, план перевыполнен. А в случае я яблочным соком отклонение отрицательное, поэтому можем сделать вывод, что план по продажам яблочного сока не выполнен, и до выполнения плана не хватило 11,3%

Теперь сделаем расчет отклонения факта продаж и от плана в excel.

Как видите, мы снова не умножаем на 100%, а применяем процентный формат ячейки.

Прогнозирование объема продаж продукции на 3,6,9 месяцев

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

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

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

Форматирование прогноза

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

• Выберите удобный шрифт и размер текста. Не забывайте о том, что читабельность прогноза зависит от выбранного шрифта и его размера. Рекомендуется использовать стандартные шрифты, такие как Arial или Calibri, и установить размер шрифта на 10-12 пунктов.

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

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

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

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

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

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

Расчет отклонения от плана в excel

Формула отклонения факта от плана такая:

Отклонение от плана = (Факт / План — 1) * 100%

Воспользуемся тем же примером и посчитаем отклонение фактических показателей от плана продаж. 

Отклонение от плана “сок вишневый” = (118 / 110 — 1) * 100% = 7,27%
Отклонение от плана “сок яблочный” = (133 / 150 — 1) * 100% = -11,33%

В первом случае отклонение от плана положительное (больше нуля), следовательно, план перевыполнен. А в случае я яблочным соком отклонение отрицательное, поэтому можем сделать вывод, что план по продажам яблочного сока не выполнен, и до выполнения плана не хватило 11,3%

Теперь сделаем расчет отклонения факта продаж и от плана в excel.

Как видите, мы снова не умножаем на 100%, а применяем процентный формат ячейки.

Основы листа прогноза

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

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

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

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

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

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

Метод экспоненциального сглаживания

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

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

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

Для использования метода экспоненциального сглаживания в Excel существует специальная функция «СГЛАЖ.», которая позволяет быстро и удобно вычислить прогноз на основе заданных данных и параметра сглаживания.

Временные ряды в Excel

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

Сделаем анализ временных рядов в Excel. Пример: торговая сеть анализирует данные о продажах товаров магазинами, находящимися в городах с населением менее 50 000 человек. Период – 2012-2015 гг. Задача – выявить основную тенденцию развития.

Внесем данные о реализации в таблицу Excel:

На вкладке «Данные» нажимаем кнопку «Анализ данных». Если она не видна, заходим в меню. «Параметры Excel» – «Надстройки». Внизу нажимаем «Перейти» к «Надстройкам Excel» и выбираем «Пакет анализа».

Подключение настройки «Анализ данных» детально описано здесь.

Нужная кнопка появится на ленте.

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

Заполняем диалоговое окно. Входной интервал – диапазон со значениями продаж. Фактор затухания – коэффициент экспоненциального сглаживания (по умолчанию – 0,3). Выходной интервал – ссылка на верхнюю левую ячейку выходного диапазона. Сюда программа поместит сглаженные уровни и размер определит самостоятельно. Ставим галочки «Вывод графика», «Стандартные погрешности».

Закрываем диалоговое окно нажатием ОК. Результаты анализа:

Для расчета стандартных погрешностей Excel использует формулу: =КОРЕНЬ(СУММКВРАЗН(‘диапазон фактических значений’; ‘диапазон прогнозных значений’)/ ‘размер окна сглаживания’). Например, =КОРЕНЬ(СУММКВРАЗН(C3:C5;D3:D5)/3).



Быстрый прогноз функцией ПРЕДСКАЗ (FORECAST)

Умение строить прогнозы, предсказывая (хотя бы примерно!) будущее развитие событий — неотъемлемая и очень важная часть любого современного бизнеса. Само-собой, это отдельная весьма сложная наука с кучей методов и подходов, но часто для грубой повседневной оценки ситуации достаточно простых техник. Одна из них — это функция ПРЕДСКАЗ (FORECAST) , которая умеет считать прогноз по линейному тренду.

Принцип работы этой функции несложен: мы предполагаем, что исходные данные можно интерполировать (сгладить) некой прямой с классическим линейным уравнением y=kx+b:

Построив эту прямую и продлив ее вправо за пределы известного временного диапазона — получим искомый прогноз.

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

Excel позволяет легко построить линию тренда прямо на диаграмме щелчком правой по ряду — Добавить линию тренда (Add Trendline), но часто для расчетов нам нужна не линия, а числовые значения прогноза, которые ей соответствуют. Вот, как раз, их и вычисляет функция ПРЕДСКАЗ (FORECAST) .

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

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

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

Например, если у вас есть таблица с данными о продажах за последние 12 месяцев, вы можете использовать функцию TREND для построения трендовой линии и прогнозирования будущих продаж. Функция TREND принимает в качестве аргументов массивы значений x и y, где x — это диапазон значений времени, а y — это диапазон значений продаж. Эта функция возвращает массив прогнозируемых значений, основываясь на линейной трендовой прямой.

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

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

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

Определение цели и временного периода прогноза

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

Временной период прогноза зависит от цели и может быть короткосрочным, среднесрочным или долгосрочным. Короткосрочный прогноз обычно охватывает период до 1 года, среднесрочный – от 1 года до 5 лет, а долгосрочный – более 5 лет.

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

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

Формулы, используемые при прогнозировании

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

Таблицы могут содержать следующие столбцы, три из которых являются вычисляемыми:

столбец статистических значений времени (ваш ряд данных, содержащий значения времени);

столбец статистических значений (ряд данных, содержащий соответствующие значения);

столбец прогнозируемых значений (вычисленных с помощью функции ПРЕДСКАЗ.ЕTS);

два столбца, представляющие доверительный интервал (вычисленные с помощью функции ПРЕДСКАЗ.ЕTS.ДОВИНТЕРВАЛ). Эти столбцы отображаются только в том случае, если в разделе ” Параметры ” установлен флажок ” доверительный интервал “.

Расчет выполнения плана в excel

Выполнение плана рассчитывается, как правило, в процентах. Другими словами, понятие “выполнение плана” означает, сколько процентов фактические показатели составляют относительно плановых.

Формула выполнения плана в процентах выглядит так:

Выполнение плана = Факт / План *100%

Рассмотрим пример расчета выполнения плана продаж.

У торгового представителя установлен план продаж:

• сок вишневый — 110 упаковок
• сок яблочный — 150 упаковок

Фактически в течение месяца он продал:

• сок вишневый — 118  упаковок
• сок яблочный — 133 упаковки

Рассчитаем процент выполнения плана продаж:

Выполнение “сок вишневый” = 118 / 110 * 100% = 107,27%
Выполнение “сок яблочный” = 133 / 150 * 100% = 88,67%

Рассчитаем выполнение плана продаж в excel по той же формуле:

Обратите внимание, что в excel не нужно умножать на 100%. Вместо этого выбираем процентный формат ячейки (как показано на картинке или через правую кнопку мыши — Формат ячеек)

Таким образом, видим, что план продаж вишневого сока перевыполнен (т.к. он больше 100%), а во втором случае — не выполнен.

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

Как посчитать процент выполнения плана в Excel

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

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

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

Обратите внимание, что в последнем столбце где указан результат выполнения плана в процентах изменен формат ячеек на «процентный». А формулы в этом столбце весьма простые – значение столбца «Продано» делиться на значение в столбце «План» =C2/B2. Мало что можно сказать о данной формуле

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

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

Итоги

Такой отчет можно составить, не обладая даже должными навыками по аналитике. Так вы можете понять, есть ли какие-то риски.

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