اکسل برای مدیران, مهندسان, حسابداران
وتولید ایده های نو
مدل های قبلی را
میتوانید روی لینک های زیر مطالعه بفرمایید:
مدل 4) آنالیز سه
بعدی با کلیک
یکی از
بهترین ابزارها جهت آنالیز در اکسل, استفاده از Data Table بوده که
جهت بررسی و آنالیز مربوط به تاثیر یک یا دو متغیر همزمان بر روی یک الگوریتم بکار
میرود. اسم این آنالیز Sensitivity
Analysis میباشد.
همانطور
که گفتم حداکثر تعداد متغیر ها جهت آنالیز با این روش, دو متغیراست اما در این
مقاله با استفاده از روش قید شده در مدل 3 (لینک: http://www.emfps.org/2017/09/3.html), من یک مثال در زمینه حسابداری برای آنالیز سه طرفه و یا سه بعدی
آورده ام.
قبل
ازآن دو مثال بصورت فیلم آموزشی در رشته مهند سی برق و حسابداری برای روش آنالیزدو
بعدی که با اکسل قابل اجراست بشرح زیر توضیح داده شده است:
مثال
در زمینه مهندسی برق (طراحی یا دستیابی به حداقل افت ولتاژ هدف)
همانطورکه
در مدل شماره 1و 3 قید شد, میتوان یک الگوریتم در اکسل برای محاسبه افت
ولتاژ نوشت و در این مدل با یک کلیک بر اساس تغییرات سطح مقطع و مقاومت AC یا DC , میزان
افت ولتاژ را محاسبه کرد.حال در آنالیز دو بعدی برای دو متغیر دیگر یعنی شدت جریان
و فاصله روی شیت اکسل دامنه ای تعریف میکنیم. دامنه برای فاصله روی یک ردیف و شدت
جریان روی یک ستون. روش گام به گام آنالیز دو بعدی طبق فیلم آموزشی زیر بشرح زیر
است:
- در
صد افت ولتاژ را به جدول بین دو متغیر شدت جریان و فاصله منتقل میکنیم.
- برای
هر متغیر مذکور یک رنج یا دامنه روی شیت اکسل تایپ میکنیم.
- روی
کل اعداد و سل های خالی جدول با موس میکشیم و از نوار بالا ی اکسل روی DATA و
What
– If – Analysis و سپس Data Table کلیک میکنیم.
- در
محل Row input cell , مقدار فاصله در الگوریتم اولیه را کلیک
میکنیم.
- در
محل Column input cell , مقدار شدت جریان در الگوریتم اولیه را کلیک
کرده و OK میکنیم.
- حال
کل جدول مقدار محاسبه شده افت ولتاژ را به ما میدهد.
- یک
سل انتخاب کرده و مقدار حداکثر افت ولتاژطراحی را به عنوان در صد افت ولتاژ هدف
تایپ میکنیم.
- روی
کل جدول با موس میکشیم و سپس در نوار بالای اکسل روی HOME و Conditional Formatting و
Highlight
Cells Rules وسپس Less than کلیک
میکنیم.
- داخل
محل ورودی اطلاعات در Less than , روی مقدار در صد افت ولتاژ هدف کلیک میکنیم.
حالا
با هر کلیک روی نوار سبز و یا قرمزدر انتهای الگوریتم, قسمتی از جدول که
به رنگ قرمز های لایت میشود, همگی دارای افت ولتاژ کمتر از افت ولتاژ هدف
است بنابراین از مقادیر فاصله و شدت جریان مرتبط با قسمت رنگ قرمز میتوان برای
ادامه طراحی استفاده کرد.
مثال در زمینه حسابداری (آنالیز رسیدن به سود خالص هدف در
سال آتی)
فرض کنیم
یک خلاصه صورت سود و زیان سال 1396همانگونه که در فیلم آموزشی زیرمی بینید در
دسترس دارید. در این الگوریتم ما سه متغییر بشرح زیرداریم که بعنوان فرضیات در این
مدل درنظرمیگیریم:
1) در
صد رشد فروش خالص
2)
درصد رشد بهای کالای فروش رفته
3) در
صد رشد هزینه های فروش, اداری وعمومی
از
آنجایی که این مدل فقط برای آنالیزدو بعدی است, من
متغیر های (1) و (2) بالا یعنی در صد رشد فروش خالص و ودرصد رشد بهای کالای فروش
رفته را بعنوان متغیرهای جدول آنالیز در نظر گرفته و متغیر (3) را ثابت فرض میکنم.
حال می
خواهیم بدانیم برای رسیدن به سود خالص هدف در سال 1397, متغیرهای ما
چگونه باید حرکت کنند که همان مفهوم آنالیزرسیدن به سود هدف است. فرق این مثال با
مثال مهندسی برق که در بالا قید شد, تعریف یک رنج و محدوده برای هدف می باشد که در
اینجا افزایش تعداد سود خالص هدف است که من آنرا با لیست سود خالص هدف در شیت اکسل
نمایش داده ام.
با
توجه به فیلم آموزشی زیر, مراحل گام به گام این آنالیز بشرح زیر می باشد:
- مقدارسود
خالص پیش بینی شده در سال 1397(Cell C14) را به جدول بین دو متغیردرصد رشد بهای
کالای فروش رفته و در صد رشد فروش خالص منتقل میکنیم.
- برای
هر متغیر مذکور یک رنج یا دامنه روی شیت اکسل تایپ میکنیم.
- روی
کل اعداد و سل های خالی جدول با موس میکشیم و از نوار بالا ی اکسل روی DATA و
What
– If – Analysis و سپس Data Table کلیک میکنیم.
- در
محل Row input cell , درصد رشد بهای کالای فروش رفته Cell B3)) در
الگوریتم اولیه را کلیک میکنیم.
- در
محل Column input cell , در صد رشد فروش خالص Cell B2)) در
الگوریتم اولیه را کلیک کرده و OK میکنیم.
- حال
کل جدول مقدارسود خالص پیش بینی شده در سال 1397 را به ما میدهد.
- یک
سل انتخاب کرده و یک مقداردلخواه بعنوان سود خالص هدف تایپ میکنیم (Cell B16).
- یک ستون
انتخاب کرده و یک رنج و محدوده برای سود خالص هدف تایپ میکنیم. در اینجا دامنه
مذکور بین سل D8 و D19 تایپ شده است.
- روی
کل جدول با موس میکشیم و سپس در نوار بالای اکسل روی HOME و Conditional Formatting و
Highlight
Cells Rules وسپس Great than کلیک میکنیم.
- داخل
محل ورودی اطلاعات در Great than , روی مقداردلخواه بعنوان سود خالص هدف (Cell B16) کلیک کرده
و در قسمت سمت راست رنگ (های لایت) را سبز انتخاب میکنیم.
- روی
سل D9 کلیک کرده و سپس از نوار بالای صفحه اکسل روی DATA وData Validation و در قسمت Allow کلیک
کرده و List را انتخاب می کنیم و نهایتا در محل Source محدوده برای
سود خالص هدف (D10:D19) را وارد کرده وOK می کنیم.
- حال D9را به B16 منتقل میکنیم.
حالا
با هر کلیک روی سل D9ومقادیر مربوط به
سود خالص هدف, قسمتی از
جدول که به رنگ سبز های لایت میشود, همگی دارای مقادیربیش
ازسود خالص هدف است بنابراین از دو متغیردرصد رشد بهای کالای فروش رفته و در صد
رشد فروش خالص مرتبط با قسمت رنگ سبز میتوان برای آنالیزوپیش بینی سود خالص سال
آتی استفاده کرد.
مثال در زمینه حسابداری (آنالیز سه بعدی با
کلیک)
همانگونه
که در فیلم آموزشی زیر می بینید, در این مدل علاوه بر درصد رشد فروش خالص و همچنین
درصد رشد قیمت تمام شده کالا به عنوان دو متغیر تاثیر گذار بر الگوریتم تعیین سود
خالص سالیانه شرکت, تاثیرهمزمان متغیر سوم که درصد رشد هزینه های فروش, اداری و
عمومی نیز بر آنالیز تعیین مقدار سود خالص سالیانه شرکت لحاظ شده است. کاربرد مدل
شماره 3 (http://www.emfps.org/2017/09/3.html) که مربوط به دینامیک و حرکت در اکسل می
باشد, هنگام شروع فیلم آموزشی مجددا نشان داده شده است
بطوریکه شما فقط با یک کلیک روی نوار قرمز سمت چپ (سل A15) , همزمان
چندین سل متن دار را به هر جایی که میخواهید میتوانید انتقال دهید.
در این
مدل, متغیر سوم که درصد رشد هزینه های فروش,
اداری و عمومی می باشد, روی یک ستون (H3:H11) تایپ
میشود و سپس با استفاده از روش قید شده در مدل شماره 3 به سل B4 انتقال
می یابد جایی که با هرکلیک روی نوار سبز(سل های B15 و (B16
, مقادیر سود خالص در جدول بر اساس سه متغیربدست می
آید. در اینجا محل ورودی اطلاعات ((Inputs شامل:
درصد رشد فروش خالص و همچنین درصد رشد قیمت تمام شده کالا در سل های C2 و C3 بترتیب
به سل های F14 و G13 لینک بوده و سل های D2 و D3 بترتیب
برای کنترل تغییرات دامنه F15:F24 و H13:N13 می باشد. همچنین سل C4مربوط به درصد رشد هزینه های فروش, اداری و عمومی به سل H4 لینک
بوده و سل D4
برای کنترل تغییرات دامنه H4:H11 می باشد.
