Прогнозирование итогов на следующий год при помощи сводной таблицы Excel 2010
Предположим, что каждый склад компании предоставил начальный прогноз продаж на следующий год. Поскольку эти показатели рассчитываются на основе исходных данных и обязательно будут изменяться в течение последующих двух недель, для создания затребованного отчета с прогнозами следует использовать сводную таблицу.
Ваша задача состоит в использовании предоставленных складами данных для создания итогового отчета по созданию сайтов, выводящего следующие данные:
- прогноз общего объема дохода по рынкам сбыта;
- общий (процентный) рост объема продаж за последний год;
- общая чистая прибыль по рынкам сбыта.
Создайте исходную сводную таблицу (рис. 5.13), которая включает поля Доход за прошлый год и Прогноз на следующий год для каждого региона и рынка сбыта. Как показано на рис. 5.13, добавив поля Прогноз на следующий год в область данных, вы выполняете первое требование — вывести прогноз общего объема продаж по рынкам сбыта.
Рис. 5.13. Исходная сводная таблица довольно проста, однако она обеспечивает данные для первого требования — вывести прогноз общего дохода от продаж по рынкам сбыта
Далее требуется определить процентный рост продаж за последний год. Чтобы получить эти данные, вам нужно добавить вычисляемое поле с использованием следующей формулы: (Прогноз на следующий год / Доход за прошлый год) - 1
Чтобы выполнить поставленную задачу, следуйте таким инструкциям.
- В диалоговом окне Вставка вычисляемого поля введите имя нового поля Процент роста (рис. 5.14).
- Удалите нуль в поле Формула.
- Введите открывающую скобку (.
- Дважды щелкните на поле Прогноз на следующий год.
- Введите знак косой черты /.
- Дважды щелкните на поле Доход за прошлый год.
- Введите закрывающую скобку ).
- Введите знак деления /.
- Введите 1 (единица).
Рис. 5.14. Назовите новое поле процент роста
В вычислениях полей можно применять любые константы. Константа — это статическое значение, которое не изменяется со временем. Например, в качестве константы может использоваться число 1. Значение Доход за прошлый год или Прогноз на следующий год может изменяться от даты к дате, тогда как число 1 не изменяется ни при каких обстоятельствах.
На этом этапе ваше диалоговое окно должно выглядеть примерно так, как показано на рис. 5.15.
Рис. 5.15. Всего лишь несколько раз щелкнув кнопкой мыши, вы создали формулу процентного роста продаж
Щелкните на кнопке ОК для добавления определенного выше поля. Вам нужно отформатировать новые созданные поля так, чтобы правильно представить данные в них. После изменения формата данных на процентный вы получите в сводной таблице оптимально представленные данные (рис. 5.16). Используя новое вычисляемое поле, можно увидеть, что для трех рынков сбыта необходимо заново построить прогнозы для отображения положительного роста за последний год.
Рис. 5.16. Для правильного отображения данных требуется выполнить дополнительное форматирование данных поля процент роста
Итак, после вывода прогноза общего объема продаж по регионам и рынкам, а также общего процентного роста за последний год, пришло время выполнить последнее требование: вывести общую маржинальную прибыль по рынкам. Чтобы извлечь эти данные, нужно добавить вычисляемое поле, в котором используется следующая формула: Прогноз на следующий год + Переменные издержки будущего года
Изучение рис. 5.17 показывает, что поле Переменные издержки будущего года находится вне отчета сводной таблицы. Разве такое допустимо? Конечно! Ведь в вычисляемом поле могут использоваться скрытые поля сводной таблицы.
Рис. 5.17. Выделенная в вычисляемом поле информация указывает на то, что в отрасли существует три проблемных рынка сбыта
Чтобы создать новое поле, выполните следующие действия.
- Откройте диалоговое окно Вставка вычисляемого поля (Insert Calculation Field) и присвойте новому вычисляемому полю имя Маржинальная прибыль.
- Удалите нуль в поле Формула.
- Дважды щелкните на поле Прогноз на следующий год.
- Введите знак плюс +.
- Дважды щелкните на поле Переменные издержки будущего года.
На этом этапе ваше диалоговое окно должно выглядеть так, как показано на рис. 5.18.
Рис. 5.18. Всего лишь несколько раз щелкнув кнопкой мыши, вы создали формулу маржинальной прибыли
После создания поля, в котором подсчитывается маржинальная прибыль, ваш отчет сводной таблицы должен выглядеть примерно так, как показано на рис. 5.19. Этот отчет полностью готов для просмотра.
Рис. 5.19. Благодаря вычисляемому полю, маржинальная прибыль теперь представляется отдельным полем данных в отчете сводной таблицы
Теперь, после создания наглядного отчета сводной таблицы, вы можете быстро проанализировать имеющиеся данные и получить новые прогнозы, постоянно обновляя исходные данные имеющихся полей.