Kuinka rakentaa avattavia skenaarioita rahoitusmallissasi

Yleisimmin käytetty tapa luoda skenaarioita on käyttää kaavojen ja avattavien ruutujen yhdistelmää. Talousmallissa luot taulukon mahdollisista skenaarioista ja niiden syötteistä ja linkität skenaarioiden nimet syötesolun pudotusvalikkoon. Mallin syötteet on linkitetty skenaariotaulukkoon. Jos malli on rakennettu oikein niin, että kaikki syötteet virtaavat ulos lähtöihin, mallin tulokset muuttuvat, kun käyttäjä valitsee pudotusvalikosta erilaisia ​​vaihtoehtoja.

Tietojen validoinnin avattavia ruutuja käytetään useisiin eri tarkoituksiin taloudellisessa mallintamisessa, mukaan lukien skenaarioanalyysi.

Tietojen validointien käyttäminen kannattavuusskenaarioiden mallintamiseen

Lataa tiedosto 0801.xlsx . Avaa se ja valitse välilehti 8-1-aloitus.

Tapa, jolla tämä on mallinnettu, syötteet ovat rivissä sarakkeessa B. Voit suorittaa herkkyysanalyysin yksinkertaisesti muuttamalla yhtä syötteistä – esimerkiksi muuttamalla asiakkaita per puheluoperaattori solussa B3 arvosta 40 arvoon 45, niin saat nähdä kaikki riippuvat numerot muuttuvan. Tämä olisi herkkyysanalyysi, koska muutat vain yhtä muuttujaa. Sen sijaan aiot muuttaa useita muuttujia kerralla tässä täydellisessä skenaarioanalyysiharjoituksessa, joten sinun täytyy tehdä muutakin kuin säätää muutamaa numeroa manuaalisesti.

Suorita skenaarioanalyysi käyttämällä avattavia tietojen validointiruutuja seuraavasti:

Ota ladattu malli ja leikkaa ja liitä kuvaukset sarakkeesta C sarakkeeseen F. Voit tehdä tämän korostamalla solut C6:C8, painamalla Ctrl+X, valitsemalla solun F6 ja painamalla Enter.

Solujen B3 - B8 tulot ovat aktiivinen alue, joka ohjaa mallia ja pysyy sellaisena. Niistä on kuitenkin tehtävä kaavoja, jotka muuttuvat luomasi avattavan valikon mukaan.

Kopioi sarakkeen B alue sarakkeisiin C, D ja E.

Voit tehdä tämän korostamalla B3:B8, painamalla Ctrl+C, valitsemalla solut C3:E3 ja painamalla Enter. Nämä summat ovat samat jokaisessa skenaariossa, kunnes muutat niitä.

Rivillä 2 syöttää otsikot parhaassa tapauksessa , Base Case , ja pahimmassa tapauksessa.

Mallin asettaminen skenaarioanalyysiä varten.

Huomaa, että kaavat linkittävät edelleen sarakkeen B syötteisiin, kuten näet valitsemalla solun C12 ja painamalla F2-pikanäppäintä.

Muokkaa syötteitä kunkin skenaarion alla.

Voit laittaa mitä pidät todennäköisenä, mutta anna arvot täsmäyttääksesi numerot tässä esimerkissä. Ohita sarake B toistaiseksi.

Syötteet skenaarioanalyysiin.

Nyt sinun on lisättävä yläreunaan avattava laatikko, joka ohjaa skenaarioitasi. Sillä ei ole väliä, mihin sijoitat avattavan valikon, mutta sen pitäisi olla helposti löydettävissä olevassa paikassa, yleensä sivun yläreunassa.

Kirjoita soluun E1 otsikko Skenaario .

Valitse solu F1 ja muuta muotoilu syötteeksi, jotta käyttäjä näkee, että tämä solu on muokattavissa.

Helpoin tapa tehdä tämä on noudattaa näitä vaiheita:

Napsauta yhtä soluista, jotka on jo muotoiltu syötteeksi, kuten solua E3.

Paina Aloitus-välilehden vasemman reunan Leikepöytä-osiossa olevaa Format Painter -kuvaketta. Kursorisi muuttuu siveltimeksi.

Valitse solu F1 liittääksesi muotoilun.

Format Painter on yleensä kertakäyttöinen. Kun olet valinnut solun, sivellin katoaa osoittimesta. Jos haluat, että Muotoilun maalaamisesta tulee "tahmea" ja sitä käytetään useissa soluissa, kaksoisnapsauta kuvaketta, kun valitset sen Aloitus-välilehdeltä.

Valitse nyt solussa F1 Data Validation Data-välilehden Tietotyökalut-osiosta.

Data Validation -valintaikkuna tulee näkyviin.

Muuta Asetukset-välilehden Allow-pudotusvalikosta Luettelo, valitse hiirellä alue =$C$2:$E$2 ja napsauta OK.

Tietojen vahvistuspudotusvalikon skenaarioiden luominen.

Napsauta avattavaa ruutua, joka näkyy nyt solun F1 vieressä, ja valitse jokin skenaarioista (esimerkiksi Perustapaus).

Kaavojen soveltaminen skenaarioihin

Sarakkeen B solut ohjaavat edelleen mallia, ja ne on korvattava kaavoilla. Ennen kuin lisäät kaavoja, sinun tulee kuitenkin muuttaa alueen solujen muotoilua osoittamaan, että ne sisältävät kaavoja kovakoodattujen numeroiden sijaan. Toimi seuraavasti:

Valitse solut B3:B8 ja valitse täyttöväri Aloitus-välilehden Fontti-ryhmästä.

Vaihda täyttöväri valkoiseksi taustaksi.

On erittäin tärkeää erottaa mallissa kaavat ja syöttösolut. Sinun on tehtävä selväksi kaikille mallia avaaville käyttäjille, että tämän alueen solut sisältävät kaavoja eikä niitä tule ohittaa.

Nyt sinun on korvattava sarakkeen B kovakoodatut arvot kaavoilla, jotka muuttuvat pudotusvalikon muuttuessa. Voit tehdä tämän käyttämällä useita eri toimintoja; HLOOKUP, sisäkkäinen IF-käsky, IFS ja SUMIF tekevät asiansa. Lisää kaavat seuraavasti:

Valitse solu B3 ja lisää kaava, joka muuttaa arvoa sen mukaan, mitä solussa F1 on.

Tässä on kaava eri vaihtoehtojen alla:

• =HAKU($F$1,$C$2:$E$8,2,0)

  Huomaa, että tämän ratkaisun avulla sinun on muutettava rivin indeksinumero 2:sta 3:een ja niin edelleen, kun kopioit kaavan alas. Sen sijaan voit käyttää ROW-funktiota kolmannessa kentässä seuraavasti: =HLOOKUP($F$1,$C$2:$E$8,ROW(A3)-1,0)

• =JOS($F$1=$C$2,C3,IF($F$1=$D$2,D3,E3))
• =IFS($F$1=$C$2,C3,$F$1=$D$2,D3,$F$1=$E$2,E3)
• =SUMMA($C$2:$E$2,$F$1,C3:E3)

  Kuten aina, valittavana on useita eri vaihtoehtoja, ja paras ratkaisu on se, joka on yksinkertaisin ja helpoin ymmärtää. Mikä tahansa näistä funktioista tuottaa täsmälleen saman tuloksen, mutta rivin indeksinumeron muuttaminen HLOOKUPissa ei ole kestävää, ja ROW:n lisääminen voi olla hämmentävää käyttäjälle. Sisäkkäistä IF-lausetta on vaikea rakentaa ja seurata, ja vaikka uusi IFS-funktio on suunniteltu yksinkertaistamaan sisäkkäistä IF-funktiota, se on silti melko hankala. SUMIF on melko yksinkertainen rakentaa ja seurata, ja sitä on helppo laajentaa, jos tarvitset lisää skenaarioita tulevaisuudessa.

  Huomaa, että IFS on uusi toiminto, joka on käytettävissä vain Office 365:n ja Excel 2016:n tai uudemman kanssa asennettuna. Jos käytät tätä toimintoa ja joku avaa tämän mallin aiemmassa Excel-versiossa, hän voi tarkastella kaavaa, mutta hän ei voi muokata sitä.

Kopioi solun B3 kaava sarakkeen alapuolelle.

Valmis skenaarioanalyysi.

Jos käytät tavallista kopiointia ja liittämistä, menetät kaiken muotoilusi. On tärkeää säilyttää mallin muotoilu, jotta näet yhdellä silmäyksellä, mitkä syötteet ovat dollariarvoina, prosentteina tai asiakaslukuina. Käytä Liitä kaavat säilyttääksesi muotoilun. Voit käyttää sitä kopioimalla solun leikepöydälle, korostamalla kohdealueen, napsauttamalla hiiren kakkospainikkeella ja valitsemalla Liitä kaavat -kuvakkeen liittääksesi vain kaavat ja jättämällä muotoilun ennalleen.

Nyt hauska osa! On aika testata mallin skenaariotoiminnallisuutta.

Napsauta solua F1, vaihda pudotusvalikkoa ja seuraa mallin tulosteiden muuttumista, kun vaihdat eri skenaarioiden välillä.