تحلیل سناریو در مدلسازی مالی

همواره عناصر بالقوه مطلوب و نامطلوبی وجود دارد که ممکن است بر عملکرد مالی کسب و کار ما تاثیر بگذارند. موضوعاتی از این دست سبب شده است که در مدلسازی مالی، انجام تحلیل سناریو  برای سنجش شرایط نامعلوم آینده به ضرورتی بسیار مهم بدل شود.

در این مطلب قصد داریم درباره اهمیت تحلیل سناریو  و نحوه ایجاد آن در مدل مالی صحبت کنیم. در مطلب مدلسازی مالی گفتیم که مهارت ما در استفاده از توابع اکسل، اولین گام برای ورود به دنیای مدلسازان حرفه ای است. از اینرو در این مطلب می خواهیم مهارت های اکسلی خود را نیز در قالب ایجاد سناریو بسنجیم. با ما همراه باشید.

تحلیل سناریو چیست؟

تحلیل سناریو، فرآیندی برای بررسی و ارزیابی رویداد های احتمالی است. با کمک تحلیل سناریو می توانیم مدل خود را با چندین سناریو مختلف از خوشبینانه ترین تا بدبینانه ترین حالت، آزمایش کنیم.

اهمیت تحلیل سناریو در مدلسازی مالی

در مواجه با تحولات اقتصادی و انواع ریسک های محیطی یک ضرورت عقلایی، در نظر گرفتن موارد محتمل است. پیش بینی وقایع و درج چندین سناریو در مدل مالی به ما امکان انعطاف پذیری در برابر تغییرات را می دهد. مدیران کسب و کار می توانند با تجزیه و تحلیل مواردی که ممکن است به نتایج نامطلوب منجر شود، با اتخاذ رویکرد های بهتر به نتایج بهتری برسند. به هر حال آماده شدن برای بدترین وضعیت رویکردی آگاهانه در محیط کسب و کار است. نباید از اهمیت آن غافل شویم!

روش ساخت سناریو در مدل مالی

متداول ترین روش ساخت سناریو ها در مدل مالی، استفاده از ترکیبی از توابع و Drop-Down Menu (منوی کشویی) است. ما نیز در این مطلب آموزشی تحلیل سناریو را با ساختن منوی کشویی در اکسل با استفاده از data validation و پیوند آن به آنالیز سناریو با استفاده از  ترکیب توابع Offset و Match انجام خواهیم داد.

اگر مطالب قبلی مدلسازی مالی را دنبال کرده باشید، حتما به یاد دارید که ما یک صورت سود و زیان طراحی کردیم و در آن یک دوره پیش بینی بر مبنای فرضیات درآمدی و هزینه ای تشکیل دادیم.

اگر مطالب قبلی را مطالعه نکرده اید، پیشنهاد می کنیم برای درک بهتر این مطلب آموزشی، حتما مطلب زیر را مطالعه نمایید.

[otw_shortcode_info_box border_style=”bordered” background_pattern=”otw-pattern-3″]چگونه یک مدل مالی می سازیم؟[/otw_shortcode_info_box]

مراحل انجام تحلیل سناریو در مدل مالی

به طور خلاصه روند کار بدین ترتیب است که ابتدا باید جداول سناریوهای محتمل و ورودی های آنها را ایجاد کنیم. سپس نام سناریوها را به یک سلول دارای قابیلت Drop-Down Menu مرتبط کنیم. در نهایت بین ورودی های مدل با جداول سناریوها پیوند ایجاد کنیم. با این کار، کاربرنهایی می تواند با انتخاب هر یک از گزینه های لیست کشویی نتایج مدل را با توجه به سناریوهای تعریف شده در مدل، تغییر دهد.

تشکیل جدول سناریوهای محتمل

ابتدا مانند تصویر پایین جدول مفروضات را براساس سناریوهای پایه، بهترین و بدترین بسط دهیم. سناریو های پایه دربردارنده فرضیات متوسطی هستند که معمولا میانگین داده های تاریخی است. بهترین سناریوها یک پیش بینی از ایده آل های مدیران است. بدبینانه ترین سناریوها هم بدترین و جدی ترین نتایج محتمل را در نظر می گیرند.

توجه کنیم فرضیات مربوط به دوره های تاریخی برای هر سه سناریو باید یکسان باشد و برای جلوگیری از تغییر ناحیه انتخابی کافی است در محاسبه فرمول ها، سطر مورد نظر مطلق شود.

و در نهایت ورودی ها را بر اساس بدبینانه ترین و خوشبینانه ترین سناریوهای محتمل تخمین می زنیم.

ایجاد لیست کشویی

در سلول B2 یک لیست دربردارنده سناریو را ایجاد می کنیم. این کار را با استفاده از data validation در نوار Data انجام می دهیم. همان طور که قبلا گفتیم این کار برای ایجاد ارتباط  یک سلول با نام سناریوها است.

انتخاب سناریو

در این مرحله با ایجاد پیوند بین ورودی ها و جداول سناریو، می توانیم هر یک از سناریو ها را از لیست خود انتخاب کنیم. برای این هدف از توابع مختلفی می توان بهره برد که مهم ترین آنها ترکیب توابع Offset با Match است.

اگر با عملکرد فوق العاده تابع Offset در اکسل آشنایی ندارید، بهتر است کمی وقت بگذارید و ویدئو زیر را نگاه کنید که در آن به نحو عالی تابع Offset و کاربرد آن توضیح داده شده است.

[otw_shortcode_info_box border_style=”bordered” background_color_class=”otw-silver” background_pattern=”otw-pattern-3″]آموزش فرمول offset در اکسل [ویدئو][/otw_shortcode_info_box]

حال که با این تابع، آشنا شده اید می توانیم کار خود را ادامه دهیم.

ابتدا باید یک نقطه مرجع را در مدل خود تنظیم کنیم. نقطه شروع ما سلول F16 است. در گام بعدی باید بدانیم که چند سطر و چند ستون باید جابجا شویم.

برای اینکه بدانیم چند سطر باید جابجا شویم از تابع Match استفاده می کنیم و تعداد ستون جابجا شده را باید برابر صفر قرار دهیم.

برای آشنایی کامل با تابع Match می توانید به آموزش مفید و دقیق زیر مراجعه کنید.

[otw_shortcode_info_box border_style=”bordered” background_color_class=”otw-silver” background_pattern=”otw-pattern-3″]آموزش فرمول match در اکسل[/otw_shortcode_info_box]

آرگومان های تابع Match را مانند تصویر زیر پر می کنیم.  توجه کنیم که سلول B2 حتما باید مطلق شود. 

فرمول زیر را برای مفروضات دیگر کپی می کنیم.

 دقت شود در فرایند copy و paste کردن، رعایت مطلق کردن ستون ها، برای اجتناب از جابجایی ستون ها الزامی است. 

باید توجه کنیم که در نتیجه اضافه شدن سناریو های محتمل بهترین و بدترین، همه سطرهای مربوط به محاسبات فرضیات برای دوره های پیش بینی، باید یک سطر به طرف بالا جابجا شود. به طور مثال در محاسبه درآمدهای عملیاتی سال 99 باید جای سطر F17 با F16 جابجا شود. این امر از آن جهت است که ما  در اینجا مدل ارائه شده در مطالب قبلی را بسط داده ایم.

و در نهایت توانستیم در یک مدل پویا، سناریو های مختلف را نمایش دهیم.

برای فراگیری بهتر، فایل مثال بالا در لینک زیر قرار داده شده است:

دانلود فایل آموزش  

در پایان ذکر چند نکته الزامی است:

نکته اول: برای ایجاد پیوند میان لیست و مفروضات، ترکیب توابع Choose با Match و ترکیب توابع Vlookup با Columns نیز استفاده می شود. بنا بر تجربه استفاده از ترکیب دو تابع Offset با Match آسان تر است. لذا این توابع را برای آموزش انتخاب کردیم.

نکته دوم: با توجه به اینکه قصد داریم ساخت مدل مالی را طی مطالب سریالی کامل کنیم، ممکن است در این مسیر تغییرات متداولی را در مدل خود اعمال کنیم. مانند آنچه که در بالا و در جابجایی سطر ها دیده شد.

نکته سوم: در مدل های پیچیده تر که برای اهداف برون سازمانی طراحی می شوند، ترجیح بر آن است صورت های مالی، محاسبات مربوط به تحلیل سناریو و  همچنین نمودار در صفحات جداگانه ارائه شود. از اینرو در مطالب بعدی ما هم یک گام به سوی حرفه ای شدن بر می داریم و در صفحات مجزا صورت های مالی را نمایش خواهیم داد.