Прогнозування підсумків на наступний рік за допомогою зведеної таблиці Excel 2010

Припустимо, що кожен склад компанії надав початковий прогноз продажів на наступний рік. Оскільки ці показники розраховуються на основі вихідних даних і обов’язково будуть змінюватися протягом наступних двох тижнів, для створення затребуваного звіту з прогнозами слід використовувати зведену таблицю.
Ваше завдання полягає в використанні наданих складами даних для створення підсумкового звіту по створенню сайтів, що виводить наступні дані:

  • прогноз загального обсягу доходу по ринках збуту;
  • загальний (процентний) зростання обсягу продажів за останній рік;
  • загальний чистий прибуток по ринках збуту.

Створіть вихідну зведену таблицю (рис. 5.13), яка включає поля Дохід за минулий рік і Прогноз на наступний рік для кожного регіону і ринку збуту. Як показано на рис. 5.13, додавши поля Прогноз на наступний рік в область даних, ви виконуєте перша вимога – вивести прогноз загального обсягу продажів по ринках збуту.

Мал. 5.13. Вихідна зведена таблиця досить проста, однак вона забезпечує дані для першої вимоги – вивести прогноз загального доходу від продажів по ринках збуту

Далі потрібно визначити відсоткове зростання продажів за останній рік. Щоб отримати ці дані, вам потрібно додати обчислюване поле з використанням такої формули: (Прогноз на наступний рік / Дохід за минулий рік) - 1

Щоб виконати поставлене завдання, дотримуйтесь таких інструкцій.

  1. У діалоговому вікні Вставка обчислюваного поля введіть ім’я нового поля Відсоток зростання (рис. 5.14).
  2. Видаліть нуль в поле Формула.
  3. Введіть відкриває дужку (.
  4. Двічі клацніть на поле Прогноз на наступний рік.
  5. Введіть знак косою риси /.
  6. Двічі клацніть на поле Дохід за минулий рік.
  7. Введіть дужку).
  8. Введіть знак ділення /.
  9. Введіть 1 (одиниця).

Мал. 5.14. Назвіть нове поле відсоток зростання

В обчисленнях полів можна застосовувати будь-які константи. Константа – це статична значення, яке не змінюється з часом. Наприклад, в якості константи може використовуватися число 1. Значення Дохід за минулий рік або Прогноз на наступний рік може змінюватися від дати до дати, тоді як число 1 не змінюється ні за яких обставин.

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

Мал. 5.15. Всього лише кілька разів клацнувши кнопкою миші, ви створили формулу процентного зростання продажів

Клацніть на кнопці ОК для додавання певної вище поля. Вам потрібно відформатувати нові створені поля так, щоб правильно уявити дані в них. Після зміни формату даних на процентний ви отримаєте в зведеній таблиці оптимально представлені дані (рис. 5.16). Використовуючи нове обчислюване поле, можна побачити, що для трьох ринків збуту необхідно заново побудувати прогнози для відображення позитивного зростання за останній рік.

Мал. 5.16. Для правильного відображення даних потрібно виконати додаткове форматування даних поля відсоток зростання

Отже, після виведення прогнозу загального обсягу продажів по регіонах і ринкам, а також загального процентного зростання за останній рік, прийшов час виконати останню вимогу: вивести загальну маржинальний прибуток по ринках. Щоб витягти ці дані, потрібно додати обчислюване поле, в якому використовується наступна формула: Прогноз на наступний рік + Змінні витрати майбутнього року

Вивчення рис. 5.17 показує, що поле Змінні витрати майбутнього року знаходиться поза звіту зведеної таблиці. Хіба таке допустимо? Звичайно! Адже в обчислюваному поле можуть використовуватися приховані поля зведеної таблиці.

Мал. 5.17. Виділена в обчислюваному поле інформація вказує на те, що в галузі існує три проблемних ринку збуту

Щоб створити нове поле, виконайте наступні дії.

  1. Відкрийте діалогове вікно Вставка обчислюваного поля (Insert Calculation Field) і надайте новому обчислюваному полю ім’я Маржинальний прибуток.
  2. Видаліть нуль в поле Формула.
  3. Двічі клацніть на поле Прогноз на наступний рік.
  4. Введіть знак плюс +.
  5. Двічі клацніть на поле Змінні витрати майбутнього року.

На цьому етапі ваше діалогове вікно повинно виглядати так, як показано на рис. 5.18.

Мал. 5.18. Всього лише кілька разів клацнувши кнопкою миші, ви створили формулу маржинальної прибутку

Мал. 5.19. Завдяки обчислюваному полю, маржинальний прибуток тепер представляється окремим полем даних в звіті зведеної таблиці

Тепер, після створення наочного звіту зведеної таблиці, ви можете швидко проаналізувати наявні дані і отримати нові прогнози, постійно оновлюючи вихідні дані наявних полів.

Ссылка на основную публикацию