Динамічні діаграми у фінансовому моделюванні

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

Побудова діаграми на основі даних, керованих формулою

Завантажте файл 0901.xlsx . Відкрийте його та виберіть вкладку з позначкою 9-23, щоб спробувати самі.

Зміна спадного списку.

Якщо ви приховаєте дані у вихідній таблиці, вони не відображатимуться на діаграмі. Перевірте це, приховавши один із стовпців на аркуші «Фінанси» та перевірте, чи місяць зник на графіку. Ви можете змінити параметри в розділі «Вибір джерела даних», щоб воно відображало приховані клітинки.

Зв’язування заголовків діаграм із формулами

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

У комірці A1 цієї моделі змініть заголовок на такий: =«Стратегічний прогноз витрат на п’ять років для колл-центру – «&F1.

Амперсанд (&) служить сполучником, який об’єднує текст і значення формул разом.

Замість амперсанда ви також можете використовувати функцію CONCATENATE, яка працює дуже подібно, об’єднуючи одиничні клітинки, або функція TEXTJOIN є новим доповненням до Excel 2016, яка об’єднує велику кількість даних.

Коли формула в клітинці A1 працює, вам потрібно зв’язати заголовок діаграми з клітинкою A1.

Клацніть на заголовку діаграми.

Ця частина може бути складною. Переконайтеся, що ви вибрали лише назву діаграми.

Клацніть рядок формул.

Введіть = і клацніть клітинку A1.

Натисніть Enter.

Заголовок діаграми змінюється, щоб показати, що знаходиться в клітинці A1.

Зв’язування заголовків діаграм із формулами.

Ви не можете вставити будь-які формули в діаграму. З нею можна зв’язати лише одну клітинку. Усі обчислення потрібно виконати в одній клітинці, а потім зв’язати з заголовком, як показано.

Створення динамічного тексту

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

Створення кластеризованої стовпчастої діаграми.

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

Виділіть дані, що показують облікові, фактичні та бюджетні значення в стовпцях B, C і D відповідно.

Виберіть перший параметр 2-D стовпця в розділі «Діаграми» вкладки «Вставка» на стрічці, щоб створити кластеризовану стовпчасту діаграму.

У клітинці A1 створіть заголовок з динамічною датою.

Зв’яжіть назву діаграми з формулою в клітинці A1.

Відредагуйте діаграму, щоб заголовки були вирівняні по горизонталі, і змініть кольори.

Ця діаграма буде виглядати набагато краще, якщо її відсортувати таким чином, щоб більші стовпчики були зліва.

Виділіть усі дані, включаючи заголовки, і натисніть кнопку Сортувати (у розділі Сортування та фільтр на вкладці Дані на стрічці).

З'явиться діалогове вікно Сортування.

Сортувати за фактичним від найбільшого до найменшого.

Сортування даних діаграми.

Під час сортування дуже легко зіпсувати формули, тому переконайтеся, що ви виділили всі стовпці від стовпців A до G, перш ніж застосовувати сортування.

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

У клітинці A15 створіть формулу, яка автоматично розраховує, скільки позицій перевищить бюджет.

Ви можете зробити це за допомогою формули =COUNTA(G3:G12)-COUNT(G3:G12), яка обчислює кількість непустих клітинок у стовпці G.

Ви можете помітити, що два рядки перевищують бюджет, тому перетворіть його на динамічний текст за формулою =COUNTA(G3:G12)-COUNT(G3:G12)&” Items over Budget”.

Завершена діаграма з динамічним текстовим полем.

Вставте текстове поле в діаграму, натиснувши кнопку «Текстове поле» у групі «Текст» на вкладці «Вставка» на стрічці.

Клацніть на діаграмі один раз.

З’явиться текстове поле.

Обережно виділіть зовнішню частину текстового поля за допомогою миші, як ви робили в останньому розділі під час зв’язування заголовків діаграм.

Тепер перейдіть до рядка формул і введіть =.

Клацніть клітинку A15 і натисніть Enter.

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

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