Analiza What-If în Excel: Goal Seek, Scenario Manager și Data Tables

Îi oferi trei lucruri:

• Set cell: celula care conține formula ta (outputul)
• To value: rezultatul țintă dorit
• By changing cell: celula de intrare pe care Excel are voie s-o ajusteze

Să vedem în practică. 

Conduci o mică afacere cu caiete. Venitul tău actual este $25,000, dar ținta e $50,000. Prețul e fix la $25, deci trebuie să afli câte unități trebuie să vinzi. 

Iată cum faci:

1. Mergi la Data > What-If Analysis > Goal Seek

2. În Set cell, dă click pe celula B4 (celula cu formula veniturilor)

3. În To value, tastează 50000

4. La By changing cell, selectează B2 (Units Sold)

5. Click pe OK

Goal Seek ajustează B2 de la 1.000 la 2.000 de unități — numărul exact necesar pentru a atinge venituri de 50.000 $ la 25 $ pe unitate.

Folosirea Goal Seek pentru a atinge un rezultat specific. Imagine de autor.

Cum folosești Scenario Manager în Excel

Scenario Manager este un instrument de Analiză What-If care îți permite să salvezi și să comuți între mai multe seturi de valori de intrare în aceeași foaie. Fiecare versiune salvată se numește scenariu. 

Poți crea câte ai nevoie și trece instant între ele ca să compari rezultatele. 

Este util când vrei să planifici rezultate în cel mai bun, cel mai rău și cel mai probabil caz, fără să păstrezi trei fișiere separate. 

Îi oferi trei lucruri:

• Nume scenariu: o etichetă pentru fiecare set de ipoteze
• Changing cells: celulele de intrare care diferă între scenarii
• Values: numerele specifice pentru fiecare scenariu

Creează un scenariu

Să spunem că planifici vânzările trimestriale pentru afacerea ta cu caiete.

Presupune că profitul actual este $15,000. Vrei să modelezi trei scenarii: cel mai bun, cel mai rău și cel așteptat. 

Iată cum creezi un scenariu:

1. Mergi la Data > What-If Analysis > Scenario Manager

2. În fereastra Scenario Manager, dă click pe Add

3. La Scenario name, tastează Best Case.

4. La Changing cells, selectează B2:B4.

5. Va apărea o fereastră Scenario Value. În câmpurile afișate, introdu aceste valori:

• B2 = 2000, 

• B3 = $30

• B4= $10

6. Click pe OK. 

Repetă pașii pentru celelalte două scenarii folosind valorile de mai jos:

Creează un scenariu cu trei cazuri. Imagine de autor.

Pentru a comuta între scenarii, selectează unul din listă și apasă Show. Excel înlocuiește instant valorile, iar profitul se actualizează.

Click pe Show pentru a vedea valorile fiecărui scenariu. Imagine de autor.

Editează scenarii

Selectează orice scenariu din listă și apasă Edit. Îl poți redenumi, schimba ce celule acoperă sau actualiza valorile. Nu se schimbă nimic în foaia ta până când nu apeși Show

Generează rapoarte de scenarii

Scenario Manager poate crea și un raport care compară toate scenariile salvate, unul lângă altul. 

Pentru a genera un raport de scenarii:

1. În Scenario Manager, dă click pe Summary

2. În Scenario Summary, alege Scenario Summary

3. La Result cells, selectează B7 (celula profitului)

4. Click pe OK

Excel generează un tabel rezumat într-o foaie nouă, care afișează toate cele trei scenarii unul lângă altul

Generează un raport rezumat de scenarii. Imagine de autor.

Cum folosești Data Tables în Excel

Data Tables automatizează calculele repetate pe un interval de valori de intrare. În loc să schimbi manual un input, să notezi rezultatul și să repeți, definești intervalul o singură dată, iar Excel completează toate rezultatele dintr-odată.

Îi oferi trei lucruri:

• Input range: valorile pe care vrei să le testezi
• Row/column input cell: celula din formula ta pe care aceste valori o înlocuiesc
• Formula cell: outputul în jurul căruia este construit tabelul

Data Tables cu o singură variabilă

Un Data Table cu o singură variabilă testează cum schimbarea unui input afectează rezultatul unei formule.

De exemplu, ai o formulă pentru rata lunară a unui împrumut și vrei să vezi cum se modifică plata lunară pentru dobânzi între 5% și 10%.

Pentru a crea un Data Table cu o singură variabilă:

1. Configurează setul de date. Un detaliu important: în setul meu de date, în celula E1, direct deasupra primei dobânzi, nu lângă antetul coloanei, introduc =B5. Dacă E1 rămâne goală sau referința formulei merge într-o altă celulă, tabelul de date va rula, dar va întoarce doar zerouri în loc de valori ale plăților.

Creează un Data Table cu o singură variabilă. Imagine de autor.

2. Selectează apoi intervalul complet D1:E9 (inclusiv E1)

3. Mergi la Data > What-If Analysis > Data Table

4. Lasă necompletată Row input cell

5. La Column input cell, selectează B4 (rata dobânzii)

6. Click pe OK.

Excel completează tabelul cu rezultatele calculate pentru fiecare valoare de intrare. Observă că plata nu crește uniform. Saltul de la 3% la 4% este 49 $, dar de la 9% la 10% este 61 $. Schimbările de dobândă lovesc mai tare cu cât sunt mai ridicate.

Rezultatele calculate pentru fiecare valoare de intrare. Imagine de autor.

Data Tables cu două variabile

Un Data Table cu două variabile testează cum schimbarea simultană a două inputuri afectează rezultatul unei formule.

De exemplu, un model de preț poate depinde atât de prețul produsului, cât și de unitățile vândute. Un Data Table cu două variabile poate arăta profitul pentru diferite prețuri și volume de vânzări într-o singură vedere.

Mai întâi, configurează setul de date. 

Uită-te la setul de date pe care îl folosesc:

• În celula D1, introdu =B5. Aceasta este referința formulei și trebuie să fie în colțul din stânga sus al grilei, nu lângă o etichetă

• Pe rândul 1, începând cu E1, listează valorile pentru unități vândute pe care vrei să le testezi: 500, 1000, 1500, 2000

• Pe coloana D, începând cu D2, listează prețurile pe care vrei să le testezi: $20, $25, $30, $35

Configurează un set de date pentru un Data Table cu două variabile. Imagine de autor.

Pentru a crea un Data Table cu două variabile:

1. Selectează intervalul complet D1:H5.

2. Mergi la Data > What-If Analysis > Data Table

3. La Row input cell, selectează B3 (unități vândute)

4. La Column input cell, selectează B2 (preț pe unitate)

5. Click pe OK.

Excel completează profitul pentru fiecare combinație de preț și unități vândute

Rezultatele calculate pentru valorile de intrare. Imagine de autor.

Data Tables cu două variabile sunt deosebit de utile când o singură ipoteză nu spune toată povestea. Prețul poate arăta bine singur, dar profitul depinde și de câte unități cumpără clienții de fapt. Acea a doua variabilă face modelul mai util.

Goal Seek vs. Scenario Manager vs. Data Tables

Goal Seek, Scenario Manager și Data Tables fac parte din trusa de Analiză What-If din Excel, dar rezolvă probleme diferite. 

Iată o scurtă comparație între ele 

Analiza What-If vs. Solver

Analiza What-If și Solver ne ajută ambele să explorăm rezultate diferite în Excel, dar rezolvă tipuri diferite de probleme.

• Analiza What-If testează ipoteze. Schimbi unul sau mai multe inputuri și vezi cum afectează rezultatul. Scopul este să înțelegi posibile rezultate, nu să-l găsești pe cel mai bun.
• Solver funcționează diferit. Îi dai un obiectiv, cum ar fi maximizarea profitului sau minimizarea costului, iar el caută combinația de inputuri care îl îndeplinește cel mai bine. Nu testează o ipoteză. Rezolvă pentru cea optimă. 

De exemplu, un solicitant de împrumut ar putea folosi Goal Seek pentru a determina suma împrumutului care duce la o rată lunară de 500 $. Dar o companie de producție ar putea folosi Solver pentru a determina mixul de producție care generează cel mai mare profit, rămânând în limitele de muncă și materiale. 

Notă: Solver nu face parte din instrumentele standard de Analiză What-If. Este un add-in separat. În majoritatea versiunilor Excel, trebuie să-l activezi manual din Excel Options ca să apară pe fila Data.

Cele mai bune practici pentru Analiza What-If

Iată câteva bune practici de urmat când lucrezi cu Analiza What-If: 

1. Ține ipotezele separat de formule

Stochează valorile de intrare în celule dedicate și referă acele celule în formule. Evită să inserezi direct numere în calcule. 

De exemplu, această formulă e greu de actualizat:

=(50*1000)-10000

Dar această variantă e mult mai ușor de întreținut:

=(B2*B3)-B4

Când se schimbă ipotezele, actualizezi doar celulele de intrare, nu formule peste tot în registru.

2. Etichetează clar scenariile

Numele scenariilor ar trebui să spună imediat ce reprezintă. „Scenariul 1”, „Scenariul 2” și „Scenariul 3” nu-ți spun nimic peste șase luni. Denumește-le după ceea ce reprezintă, de exemplu „Best Case: creștere 15%”. 

3. Validează inputurile

Analiza What-If e la fel de fiabilă ca inputurile testate. Înainte de analiză, verifică dacă:

• Procentele sunt introduse corect
• Datele folosesc un format consecvent
• Unitățile de măsură sunt consecvente
• Valorile de intrare se încadrează în intervale realiste

O prognoză de venit bazată pe o creștere a vânzărilor de 500% ar putea fi corectă tehnic, dar inutilă pentru luarea deciziilor.

4. Documentează ipotezele

Un model care ți se pare logic azi nu va fi neapărat logic pentru tine sau altcineva peste trei luni. 

O notă scurtă lângă fiecare ipoteză, chiar și doar „bazat pe rezultatele T3”, economisește multe presupuneri mai târziu. Documentarea ajută pe alții să înțeleagă de unde vin numerele și face actualizările viitoare mult mai ușoare.

Probleme frecvente cu Analiza What-If

Cele mai multe probleme cu Analiza What-If vin din configurarea foii, nu din instrumente. Dacă Goal Seek, Scenario Manager sau Data Tables dau rezultate neașteptate, verifică câteva zone comune cu probleme; de obicei asta rezolvă situația.

1. Formulele nu se actualizează 

Schimbi un input și nu se întâmplă nimic. De obicei, asta înseamnă că modul de calcul al Excel a fost trecut pe manual pe parcurs. 

Soluție: mergi la Formulas > Calculation Options și asigură-te că e setat pe Automatic. Dacă deja este, apasă F9 pentru a forța un recalcul.

2. Referințe de celule incorecte

Instrumentele de Analiză What-If depind de referințe de celule corecte. Dacă sunt selectate celulele greșite, rezultatele vor fi incorecte chiar dacă Excel finalizează calculul cu succes. 

De exemplu, Goal Seek poate schimba celula de intrare greșită. Un Data Table poate referi celula greșită pentru inputul de rând sau coloană. Scenario Manager poate stoca valori pentru celulele greșite.

Soluție: Revizuiește celulele de intrare înainte de analiză, verifică dublu ce e selectat în fiecare fereastră a instrumentelor și folosește etichete descriptive, ca să fie evident ce reprezintă fiecare celulă. 

3. Neînțelegerea limitărilor Goal Seek

Mulți utilizatori se așteaptă ca Goal Seek să rezolve probleme cu multiple variabile care se schimbă. Nu poate. Goal Seek modifică doar o celulă de intrare pe rând pentru a atinge o valoare țintă. 

De exemplu, Goal Seek poate determina volumul de vânzări necesar pentru un profit de 15.000 $. Nu poate ajusta simultan volumul, prețul și costurile pentru a atinge acea țintă.

Soluție: Folosește Goal Seek când doar o variabilă trebuie schimbată. Folosește Scenario Manager sau Solver când sunt implicate mai multe variabile. 

4. Modele de scenarii prea complexe

Tehnic, Scenario Manager permite până la 32 de celule în schimbare per scenariu, dar faptul că poți nu înseamnă că și ar trebui. 

Peste patru-cinci variabile, scenariile devin greu de comparat și și mai greu de explicat altcuiva. Dacă modelul are nevoie de atâtea piese în mișcare, probabil e timpul să-l restructurezi, nu să înghesui totul într-un singur scenariu.

Gânduri de final 

Fiecare foaie de calcul are undeva niște ipoteze. Poate fi o rată de creștere, un preț sau o dobândă.

Analiza What-If nu elimină acele ipoteze. Îți arată ce se întâmplă dacă se dovedesc greșite, ceea ce, de fapt, reprezintă esența unei decizii bune.

Dacă ești la început, nu porni cu un model cu cinci variabile și trei scenarii imbricate. Construiește mai întâi ceva mic, de tipul un input, un output, un Goal Seek, și observă cum se mișcă împreună inputul și rezultatul. Lucrurile complexe pot veni mai târziu, după ce bazele sunt clare.