Розробка фінансових показників бюджетів за допомогою MS Excel

Размещено на http://www.allbest.ru/

Розробка фінансових показників бюджетів за допомогою MS Excel

Підхід до прогнозування із застосуванням даних про обсяг виробництва й реалізацію ґрунтується на тому, що господарська діяльність підприємства часто прямо пов'язана з обсягом реалізації продукції. Збільшення обсягу реалізації повинне привести до появи додаткових засобів на банківському рахунку, зростання суми змінних витрат, адміністративних витрат і тому подібних витрат. Але при цьому, обсяг реалізації є основою для більшості інших показників. Метод прогнозування на основі ступеня обсягу реалізації включає кілька етапів:

аналіз традиційно сформованих тенденцій зміни ринку та взаємопов'язаних фінансових показників. Наприклад, варто враховувати, що протягом декількох років змінні витрати становили в середньому 6% від загального обсягу реалізації;

складання прогнозу щодо вихідного показника, тобто обсягу реалізації, на наступний плановий період, наприклад, на рік;

підрахунок майбутніх витрат і доходів на підставі їхнього співвідношення з обсягом реалізації протягом останнього року. Якщо наступному ви плануєте збільшити обсяг реалізації на 5%, то варто запланувати таке саме підвищення змінних витрат.

планування фінансової діяльності підприємства за даними про планований обсяг реалізації й витрат.

На перших етапах розглядають існуючі фінансові звіти підприємства на предмет наявного тренду, а також структури й взаємозв'язки показників. Але якщо базова лінія коротка, скажемо, становить 3-5 років, то можна одержати дані про тенденції, які, незважаючи на свою зовнішню правдивість, можуть повести в хибному напрямі. У цьому випадку варто постаратися одержати додаткові дані, наприклад такі:

Чи підтверджується припущення, що підприємство буде давати результати, які відслідковувалися дотепер?

Як ідуть справи у ваших конкурентів: вони збільшують або скорочують обсяги? Чи намагаються вони перетягти ваших клієнтів?

Чи не відбулося яких-небудь технічних змін, що прискорюють або гальмують розвиток вашого бізнесу?

Яке поточне положення ваших клієнтів? Чи будуть вони купувати вашу продукцію, і в яких обсягах?

Чи маєте ви намір розпочати випуск нових видів продукції або припинити випуск старої?

Які тенденції розвитку ринку збуту вашої продукції? Падає або підвищується попит?

Відповіді на подібні питання в сукупності з фактичними результатами попередніх років дадуть більше підстав для відповідного прогнозу.

Для прогнозування доходів та інших кількісних характеристик господарської діяльності підприємства можна використати ефективні способи досліджень зміни багатьох змінних величин за допомогою MS Excel. Базова лінія являє собою числове вираження результатів спостережень, проведених протягом тривалого періоду. З погляду прогнозування, існує чотири найважливіших характеристики базових ліній.

Базова лінія містить у собі результати спостережень, починаючи з самих ранніх результатів і закінчуючи останніми.

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

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

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

Якщо базова лінія відповідає усім цим чотирьом вимогам, то є набагато більше шансів скласти досить точний прогноз. Крім даних про саму базову лінію, необхідно вибрати метод складання прогнозу. MS Еxcel дає можливість використати три основних підходи до цього процесу: застосування ковзного середнього, регресії й згладжування. Іноді використають також метод Бокса -Дженкинса.

Метод ковзного середнього застосовувати досить недоречно.

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

1.Виділити дані своєї базової лінії.

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

3.На першому кроці роботи засобу Майстер діаграм перевірити правильність посилань на осередки базової лінії й клацнути на кнопці Далі.

4.На другом}' кроці вибрати тип діаграми Графік і клацнути на кнопці Далі.

5.На третьому кроці вибрати вид графіка, що включає як лінії, так і маркери, наприклад, вид 1, 4 або 5. Клацнути на кнопці Далі.

6.На четвертом}' кроці встановити перемикач у стовпцях опції «Виражених даних». Установити параметри опцій «Вважати стовп, мітками осі х» або «Вважати ряд. мітками легенди». Клацнути на кнопці Далі.

На останньому кроці роботи Майстра визначити назви діаграми та осей, а також необхідність відображення легенди у відповідних полях діалогового вікна. Клацнути на кнопці Готово.

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

Виділити ряд Дані діаграми, потім вибрати команд}^- Вставка > Лінія тренда.

У вікні, що з'явився діалогом, Лінія тренда клацнути на корінці вкладки Тип.

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

11.Клацніть на кнопці ОК

прогнозування фінансовий показник еxcel

Названі дії дадуть на графіку лінію тренда ковзного середнього, разом з фактичними даними спостережень (рис.5).

Методи регресії оцінюють взаємозв'язок між фактичними даними спостережень й інших параметрів, які найчастіше є показниками того, коли були зроблені ці спостереження. При цьому регресії в MS Excel можуть бути виконані як лінійні прогнози за допомогою функції ТЕНДЕНЦІЯ, так і нелінійні, завдяки функції РІСТ. Функція Тенденція обчислює прогнози, засновані на лінійному зв'язку між результатом спостереження й годиною, у яку це спостереження було зафіксовано. Якщо цей взаємозв'язок має лінійний характер, то лінія на графіку буде або прямою, або злегка нахиленою в одну чи іншу сторону, або горизонтальною. Це буде кращою підказкою про те, що взаємозв'язок є лінійний, і тому в цьому випадку функція ТЕНДЕНЦІЯ - самий зручний спосіб регресивного аналізу. Для виконання регресивного прогнозу за функціями ТЕНДЕНЦІЯ або РІСТ спостережувані результати внесені, наприклад, в осередки А1:АІ0, а дні місяця розташовані в осередках В1:В10 виділяють осередок С1:С10 і вводять формули масиву:

=ТЕНДЕНЦІЯ (А1:А10; В1:В10) або =РІСТ (А1:А10; В1:В10).

Необхідно пам'ятати, що для уведення формули масиву необхідно нажати комбінацію клавіш <Ctrl+Shift+Enter>. Розглядаючи даний метод прогнозування, варто звернути особливу увагу на наступні моменти. Кожен результат в осередках С1:С10 виходить на основі однієї й тієї ж формули масиву, усередині якої «заховані» більше складні вирази. У цьому випадку формула має такий вигляд: СІ: = 9,13-0,61*1; С2: = 9,13+0,61*2; СЗ: = 9,13+0,61 *3. Значення 9,13 являє собою довжину відрізка, що відтинає на осі ординат лінією прогнозу, тобто значення прогнозу в початковий момент. Значення 0,61 дорівнює кутовому коефіцієнту лінії прогнозу, ін-акше кажучи, значення прогнозу змінюються в результаті зміни дат проведення спостережень. Оскільки всі значення прогнозу складаються із тих самих показників відрізка, що відтинаються на осі ординат, і кутового коефіцієнта, прогноз не відбиває змін, що відбуваються, у тимчасовому ряді. Розглянуті функції РІСТ і ТЕНДЕНЦІЯ дають можливість одночасного прогнозування даних для декількох нових тимчасових значень (табл. 10). Наприклад, уводячи в осередки В11:В24, а потім виділяючи осередки С11.С24 й увівши за допомогою формули масиву наступне: =РІСТ(А1:А10;В1:В10;В11:В24) Excel поверне в осередки СІ 1: С24 прогноз на тимчасові моменти з 11 по 24. Даний прогноз буде базуватися на зв'язку між даними спостережень базової лінії діапазону А1: А10 і тимчасовими моментами базової лінії з 1 по 10, зазначеними в осередках В1 :В 10. Взаємозв'язок є лінійної, і тому в цьому випадку функція ТЕНДЕНЦІЯ - самий зручний спосіб регресивного аналізу (табл. 10).

Однак, якщо лінія різко згинається в одному з напрямків, то це означає, що взаємозв'язок показників носити нелінійний характер. Існує велика кількість типів даних, які змінюються в часі нелінійним способом. Прикладами таких даних є обсяг продажів нової продукції, приріст населення, виплати по основному кредиту, коефіцієнт питомого прибутку та ін. У випадку нелінійного взаємозв'язку функція Excel РІСТ допомагає одержати точнішу картину явища, ніж функція ТЕНДЕНЦІЯ для цілей контролінгу (Рис.6).

Прогнозування при нелінійних взаємозв'язках функції

Метод згладжування - це спосіб, що забезпечує швидке реагування розроблювального прогнозу на всі події, що відбуваються протягом періоду довжини базової лінії. Методи, засновані на регресії, такі як функції ТЕНДЕНЦІЯ й РІСТ, застосовують до всіх точок прогнозу ту саму формулу. Із цих дій досягнення швидкої реакції на зрушення в рівні базової лінії значно утрудняються. Згладжування являє собою простий спосіб обійти дану проблему. Основна ідея методу згладжування полягає в том}', що кожен новий прогноз, виходить за допомогою переміщення попереднього прогнозу в напрямку, який дає б кращі результати в порівнянні зі старим прогнозом. Таким чином, константа згладжування є величиною, що само коригується. Інакше кажучи, кожен новий прогноз являє собою суму попереднього прогнозу й поправочного коефіцієнта, що пересуває новий прогноз у напрямку, що робить попередній результат більш точним. Іноді, у якийсь часовий момент рівень ряду різко збільшується. Це явище відомо за назвою викиду функції. І згладжування в цьому випадку буває дуже корисним. Методи прогнозування за назвою «згладжування» ураховують ефекти викиду функції набагато краще, ніж способи, що використають регресивний аналіз. MS Excel підтримує один з таких методів за допомогою засобу Експонентне згладжування в надбудові Пакет аналізу. Активізувати засіб Експонентне згладжування можна, вибравши команду Сервіз > Аналіз даних після завантаження надбудови Пакет аналізу. Для безпосереднього виконання прогнозу вводять дані за період разом з викидом функції в осередки А1:А10 робочого аркуша, і тільки потім активізують засіб Excel Експонентне згладжування (рис.7) Щоб скласти прогноз на період, що випливає за останнім показником базової лінії, у діалоговому вікні вводять у текстовому полі Вхідний інтервал експонентне згладжування на один рядок більше, ніж необхідно. Установити прапорець опції Мітки, осередок В2 застосувати як параметр Вихідний інтервал, а значення 0,7 - як параметр Фактор загасання. Складання прогнозів, що згладжують, для контролінга особливо ефективно для тенденцій сезонності в реалізації товарів і послуг.

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

Размещено на Allbest.ru