Условният анализът (“what-if”) в Excel е процес на промяна на стойностите в
определени клетки, за да се види как тези промени влияят върху крайните
резултати от изчисленията (припомнете си Упражнение 1 – в него можехте да
променяте сумата на заема и/или срока на погасяване, така че да получите
различна месечна вноска)
Условният анализ включва 3 основни техники:
– Data tables (Таблица с данни) – представлява диапазон от клетки, които
показват как промяната на определени стойности във вашите формули засяга
резултатите от формулите.
– Goal seek (Търсене на цел) – Ако знаете желания резултат от формула, но не и
входящите данни, от които се нуждае формулата, за да се получи този резултат,
можете да използвате функцията „Търсене на цел“.
– Scenarios (Сценарии) – Можете да използвате сценарии ако искате да създадете
бюджет, но не сте сигурни в приходите си. Със сценария можете да дефинирате
различни стойности за приходите и след това да превключвате между сценариите
и да извършите анализи след промяна на входните стойности.
Задача 1
Искате да изтеглите кредит, като съобразявайки вашите доходи в момента не
можете да си позволите месечна
вноска по-голяма от 150 лв.
Получили сте следната примерна
оферта от банка (виж фигурата).
Очевидно е, че за вас не е възможно
да изтеглите този заем за този
период от време. Максималният
срок, който ви се струва разумен е
5 години (60 месеца). Ако банката
няма да прави скорошни промени в лихвените проценти, изчислете какъв е
максималния размер на заема който можете да изтеглите за срок от 60 месеца и
месечна вноска от 150 лв.
Указания за изпълнение:
За да се включат модулите за условен анализ, трябва да се използва Tools Add
Ins Променете стойността на клетка В4 на 60. Изберете Tools Goal Seek. В
Set cell (Целева клетка) посочете клетката съдържаща размера на месечната
вноска (В6). В клетката To value (Търсена стойност) напишете исканата от вас
сума на месечната вноска (150).
Щракнете върху клетката,
съдържаща сумата на заема в
полето By changing cell (Клетка,
която ще се променя). По този
начин, Goal Seek ще търси
максималния размер на заема при
указаните условия, при който месечната вноска ще бъде 150 лв. Резултатът от изчислението се показва по
следния начин.
В първоначалната таблица се появява максимално
допустимия размер на заема при зададените условия –
7406,50 лв.
Задача 2. Самостоятелна работа
Въпреки това, може да се окаже, че тези средства не са ви
достатъчни. Нуждаете се от минимум
10000 лв. Последният вариант е
удължаване на срока на кредита. За какъв
период от време трябва да изтеглите заема
ако искате месечната вноска да остане със
същия размер – 150 лв.? Променете
размера на заема на 10000 лв. Задайте
условията така, както е показано на
фигурата. Крайният резултат трябва да е
88,2 месеца, т.е. около 7 години и 4 месеца.
Задача 3. Самостоятелна работа
Клиент на вашата фирма иска
да закупи 1200 броя от
продукта, който
произвеждате. Ако получите
поръчката ще имате фиксирани финансови икономически разходи (които не зависят от обема на производството) за производството в размер на 1500 лв. и променливи разходи за единица продукт 52 лв. Вашето желание е да постигнете 15% печалба.
Каква трябва да бъде цената, на която да продавате? Използвайки следната таблица, задайте правилните формули в клетките и използвайте функцията Goal
Seek, за да достигнете до решението:
Разходи общо = Постоянни разходи + Брой * Променливи разходи
Приходи общо = Продажна цена * Брой на продаваните продукти
Процент на печалбата = (Приходи общо – Разходи общо) / Разходи общо
ЗАДАЧА 4.
Искате да внесете определена сума пари на срочен банков депозит. За да вземете
решение каква сума да инвестирате и за какъв срок от време, трябва да видите
каква би била потенциалната възвращаемост от всички възможни варианти. Най
лесния начин за визуално представяне на подобна информация е инструмента
Data tables (Таблица с данни). Въведете в нов документ на Excel следната функцията за бъдеща стойност FV.
Указания за изпълнение:
1) В клетка В5 напишете 100. 2) Маркирайте клетки от В5 до В14. 3) Изберете
последователно Edit Fill Series. 4) В клетката Step Value напишете 100 и
натиснете ОК. 5) В клетката С4 напишете 12. 6) Маркирайте клетки от С4 до G4.
7) Изберете последователно Edit Fill Series. 8) В клетката Step Value
напишете 12 и натиснете ОК. 9) Маркирайте клетки от B4 до G14. 10) От менюто
Data изберете Table. 11) В клетката Row input cell щракнете върху В3. 12) В
клетката Column input cell щракнете върху В1 и потвърдете с ОК 13)
Форматирайте клетките от B5 до G14 като валута в лв. Крайният резултат трябва
да ви показва доходността на различните вложения за различните периоди от
време.
102,32 лв 12 24 36 48 60
100,00 лв 102,32 лв 104,70 лв 107,14 лв 109,63 лв 112,17 лв
200,00 лв 204,65 лв 209,41 лв 214,27 лв 219,25 лв 224,35 лв
300,00 лв 306,97 лв 314,11 лв 321,41 лв 328,88 лв 336,52 лв
400,00 лв 409,30 лв 418,81 лв 428,55 лв 438,51 лв 448,70 лв
500,00 лв 511,62 лв 523,51 лв 535,68 лв 548,13 лв 560,87 лв
Задача 5. Самостоятелна работа
(Източник: Wayne Winston –
Microsoft Excel Data Analysis
and Business Modeling,
Microsoft Press, 2004):
Голяма Интернет компания
обмисля закупуването на
свой конкурент. Приходите
на компанията са 100,000,000
годишно, а разходите към
момента са 150,000,000.
Прогнозите на купувача сочат, че приходите ще се покачват с 25% всяка година,
докато разходите ще нарастват едва с 5%. Разбира се, прогнозите могат да се
окажат и грешни и затова е по-добре да бъдат разгледани множество варианти
при различни възможни ситуации за това кога фирмата ще спре да трупа загуби и
ще излезе на печалба (приходи = разходи). Искаме да разгледаме вариантите, в които приходите се повишават от 10 до 50%, разходите от 2 до 20%. Използвайте
данните от файла E6-sales.xls
В клетките В2 и В3 са зададени сегашните нива на приходите и разходите, а в
клетки С2 и С3 – тяхното вероятно нарастване. По-надолу в таблицата (редове от
5 до 7) са зададени вероятните стойности на приходите и разходите за следващите
10 години (Използвана е следната формула: Приходи * (1 + Нарастване).
Необходимо е в ред 8 да се напише функция, която да определи в коя година
приходите се изравняват с разходите (т.нар. критична точка). Това става със
следната формула в клетка D8 (под първата година):
=IF(AND(C6D7);D5;0)
Тя проверява разликата между приходите и разходите в две съседни години и
следи през коя година приходите ще станат по-големи от разходите. След това
изписва номера на година, в която това се случва. Допълнителната функция AND
показва, че за да има положителен резултат, трябва да са изпълнени едновременно
и двете зададени условия (Припомнете си Упражнение 1, в което функцията OR
изискваше изпълнение на само едно от зададените условия).
В клетка С10 трябва да се пренесе номера на годината, в която се достига до
критичната точка. Това става със следната формула:
=IF(SUM(D8:M8)>0;SUM(D8:M8);“Няма КТ“)
Тази формула има следния смисъл: Ако сумата от ред 8 е по-голяма от 0, това
означава че е достигната критичната точка и тя е през този период, показан от
сумата. Ако през тези 10 години не е достигната критична точка, то формулата ще
изпише Няма КТ.
Следващата стъпка е да се нанесат възможните проценти за повишаване на
разходите (от 0,10 до 0,50) в клетки С11 до С51, както и процента на увеличение
на разходите (от 0,02 до 0,20) в клетки D10 до V10.
Маркирате всички клетки от С10 до V51 и от менюто Data избирате Table. В
полето Row input cell щракнете върху С3, а в клетката Column input cell изберете
С2. В таблицата трябва да се покаже информация за годината на настъпване на
критичните точки при различните комбинации от приходи и разходи. Част от
таблицата е показана на фигурата:
3 0,02 0,03 0,04 0,05 0,06 0,07 0,08 0,09 0,1
0,1 6 7 8 9 Няма КТ Няма КТ Няма КТ Няма КТ Няма КТ
0,11 5 6 7 8 9 Няма КТ Няма КТ Няма КТ Няма КТ
0,12 5 5 6 7 8 9 Няма КТ Няма КТ Няма КТ
0,13 4 5 5 6 7 8 9 Няма КТ Няма КТ
0,14 4 4 5 5 6 7 8 10 Няма КТ
0,15 4 4 5 5 5 6 7 8 10
ЗАДАЧА 6:
Използвайте модула за разработване на сценарии в Excel (Scenario), за да
направите прогноза за продажбите на безалкохолни напитки на известен
производител. В конкретната задача чрез използването на сценарии може да
разгледат различни варианти на продажбите и паралелно да се направи анализ на
резултатите. Много често сценариите се използват за изчисляване на
песимистични, реалистични и оптимистични варианти на продажбите.
Етикети: Ipotpal, системи, технологии
ipotpal 