تحلیل حساسیت با جداول داده (data tables)

آخرین به‌روزرسانی: 27 دی 1401

در فصل گذشته به بررسی  ابزار Auditing (وارسی) و افزونه Inquire (بررسی)  پرداختیم، در این فصل به تحلیل حساسیت با جداول داده می پردازیم.

سؤالات پاسخ داده شده در این فصل:

• تصمیم گرفته‌ام فروشگاهی در یک بازار محلی باز کنم که در آن نوشیدنی لیموناد فروخته شود. پیش از بازکردن فروشگاه کنجکاوم بدانم چگونه میزان سود، در‌آمد و هزینه‌های متغیر به قیمت محصول و هزینه هر واحد بستگی خواهد داشت.
• می‌خواهم خانه جدیدی بسازم. میزان پولی که می‌بایست وام بگیرم (با دوره پرداخت 15 ساله) بستگی به قیمت فروش خانه‌ای که هم‌اکنون مالکش هستم دارد. همین‌طور درمورد نرخ بهره سالیانه‌ای که به هنگام بسته شدن معامله دریافت خواهم کرد اطمینان ندارم. چگونه می‌توانم نحوه وابستگی پرداخت‌های ماهیانه‌ام به میزان وامی که گرفته‌ام و نرخ بهره سالیانه را مشخص نمایم؟
• شرکت اینترنتی بزرگی در فکر خرید فروشنده آنلاین دیگری می‌باشد. درآمد سالیانه این فروشنده 100 میلیون دلار و مخارج آن 150 میلیون دلار می‌باشد. به‌تازگی تخمین زده شده که درآمد این فروشنده آنلاین قرار است هرسال به میزان 25 درصد افزایش داشته باشد و مخارج آن نیز هرسال 5 درصد افزایش پیدا کند. می‌دانیم که تخمین‌های انجام شده ممکن است اشتباه باشند و می‌خواهیم از میان دسته‌ای از فرض و گمان‌ها در باره رشد درآمد و مخارج سالیانه و تعداد سال‌هایی که لازم است سپری شود تا این فروشنده سودی درست‌وحسابی کسب کند، اطلاعاتی قطعی به دست آوریم.
• چگونه می‌توان نموداری بر اساس جدول داده ایجاد کرد؟

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

• قیمت فروش یک لیوان لیموناد.
• هزینه تولید واحد یک لیوان لیموناد
• میزان حساسیت تقاضا برای لیموناد با قیمتی که به آن تخصیص‌داده‌شده.
• هزینه ثابت سالیانه برای اداره دکه فروش لیموناد.

می‌توانید بر اساس فرضیات وارد شده میزان خروجی بهره را محاسبه نمایید. برای مثال لیموناد، میزان خروجی بهره شامل موارد زیر است:

• سود سالیانه
• عایدی سالیانه
• هزینه‌های متغیر سالیانه

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

پاسخ به سؤالات این فصل:

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

جزئیات مربوط به این تحلیل در فایلی به نام Lemonade.xlsx(تصاویر 1-17، 2-17 و 4-17 را ببینید) فرضیه‌های ورودی در محدوده D1:D4 وارد شده‌اند. فرض کرده‌ایم که تقاضای سالیانه برای لیموناد (فرمول سلول D2 را ببینید) برابر 9000-65000 ضربدر قیمت می‌باشد. (فصل 87 به نام تخمین منحنی تقاضا حاوی بحثی درباره چگونگی تخمین منحنی تقاضا است) نام‌های مرتبط با محدوده D1:D7 در سلول‌هایC1:C7ایجادشده‌اند.

تصویر 1-17 تحلیل فروش در لیموناد فروشی

تصویر 2-17 جدول داده یک‌طرفه باقیمت‌های متفاوت

 درآمد سالیانه را با فرمول =demand*price در سلول D5محاسبه نموده‌ایم. در سلولD6هزینه‌های متغیر سالیانه را با فرمول =unit_cost*demand محاسبه می‌کنیم. بالاخره در سلول D7 میزان سود را با استفاده از فرمول =revenue–fixed_cost–variable_cost. محاسبه می‌کنیم.

فرض کنید می‌خواهیم بدانیم چگونه تغییرات در قیمت (مثلاً از 1.00 دلار تا 4.00 دلار با میزان افزایش 0.25 دلاری) بر سود سالانه، درآمد و هزینه‌های متغیر تأثیر می‌گذارد. ازآنجایی‌که قرار است تنها یک ورودی را تغییر دهیم، جدول داده یک‌طرفه بهترین راه‌حل مشکل است. این جدول داده در تصویر 2-17 نمایش‌داده‌شده است.

جهت تنظیم جدول داده یک‌طرفه کار را با فهرست کردن مقادیر ورودی در ستونی آغاز می‌کنیم. مقادیر بهره را (این مقدارها از  1.00دلار تا  4.00 دلار را با افزایشی0.25 دلاری در برمی‌گیرند.) در محدوده C11:C23درج می‌کنیم. سپس یک ستون را رد کرده در بالای ردیفی از لیست مقادیر ورودی‌ها رفته و در آنجا فرمول‌هایی را که می‌خواهیم جدول داده محاسبه نماید را فهرست می‌کنیم. در اینجا فرمول مربوط به محاسبه سود در سلول D10 درج شده است‌، فرمول مربوط به درآمد در سلول E10 و بالاخره فرمول مربوط به هزینه‌های متغیر در سلول F10 درج شده است. تنها اعداد را تایپ نکنید، اکسل نیاز به فرمول دارد!

حالا محدوده جدول را انتخاب می‌کنیم (C10:F23) محدوده جدول از یک ردیف بالای اولین ورودی شروع می‌شود، آْخرین ردیف آن حاوی آخرین مقادیر ورودی است. اولین ستون در محدوده جدول ستونی حاوی ورودی‌ها می‌باشد و آخرین ستون جدول آخرین ستون حاوی خروجی‌های جدول می‌باشد. بعد از انتخاب محدوده جدول سربرگ Data را در بخش ریبون نرم‌افزار فعال کنید. حالا در گروه گزینه‌های Forecast روی گزینهWhat if Analysis کلیک کنید و پس از آن روی گزینه Data Table کلیک نمایید. حال کادر محاوره‌ای Data Table را همان‌طور که در تصویر 3-17 نشان داده شده تکمیل کنید.

تصویر 3-17 ایجاد جدول داده

در بخش Column Input Cell (سلول ستون ورودی) ، می‌توانید نام سلولی که می‌خواهید ورودی‌های فهرست شده (که اینجا منظور مقادیر فهرست شده در اولین ستون محدوده جدول داده‌ها است) به آن تخصیص داده شوند را وارد کنید. ازآنجاکه داده‌های فهرست شده قیمت‌ها هستند، من سلول D1 را به‌عنوان سلول ستون ورودی انتخاب کرده‌ام. پس از کلیک روی دکمه OK اکسل جدول داده‌های یک‌طرفه را ایجاد می‌کند که در تصویر 4-17 نشان داده شده است.

تصویر 4-17 کل جدول داده‌های یک‌طرفه با قیمت‌های متغیر

 می‌بینید که در محدوده D11:F11 میزان سود، درآمد و هزینه متغیر برای قیمت 1.00 دلار محاسبه شده‌اند. در سلول‌های D12:F12 میزان سود، درآمد و هزینه متغیر برای قیمت 1.25 دلار محاسبه شده‌اند و همین محاسبه برای دسته‌ای از قیمت‌های متفاوت انجام شده است. قیمت سودافزا در تمامی قیمت‌های فهرست شده 3.75 دلار می‌باشد. قیمت 3.75 دلار سود سالیانه‌ای برابر با 58.125.00 دلار، درآمد سالیانه‌ای برابر با 117.187.50 و هزینه متغیر سالیانه‌ای برابر با 14.062.50 دلار ایجاد می‌نماید.

فرض کنید می‌خواهیم مشخص کنیم که چگونه سود سالیانه با تغییر قیمت‌ها از1.50 دلار تا 5.00دلار (با میزان افزایش0.25 دلاری) و تغییر هزینه و احد از0.30دلار به0.60دلار (افزایش0.05دلاری)تغییرمی نماید. چون در اینجا دو مقدار ورودی را تغییر می‌دهم نیاز به جدول داده دوگانه داریم (تصویر 5-17 را ببینید) مقادیر یکی از ورودی‌ها را در اولین ستون از محدوده جدول وارد کرده‌ایم (از محدودهH11:H25 برای درج مقادیر قیمت استفاده می‌کنیم) و مقادیر ورودی دیگر را در اولین ردیف از محدوده جدول درج می‌کنیم.

(در این مثال محدوده I10:O10 دارای فهرستی از مقادیر هزینه واحد می‌باشد) یک جدول داده دوگانه می‌تواند تنها یک سلول خروجی داشته باشد و فرمول خروجی می‌بایست در گوشه سمت چپ بالای محدوده جدول درج شود؛ بنابراین فرمول محاسبه سود را در سلول H10  قرارمی دهیم.

در اینجا محدوده جدول (سلول‌هایH10:O25) را انتخاب می‌کنیم و آنگاه تبDisplay را فعال می‌نماییم. در گروه گزینه‌هایForcast بر روی گزینه What-If Analysis کلیک کرده و پس از آن گزینه Data Table را کلیک می‌کنیم. سلول D1 (قیمت) را در بخش Column Input Cell و سلول D3 (هزینه متغیر واحد) را در بخشRow Input Cell وارد می‌کنیم. این تنظیمات تضمین می‌کند که مقادیر در اولین ستون محدوده جدول به‌عنوان مقادیر قیمت و مقادیر اولین ردیف از محدوده جدول به‌عنوان هزینه‌های متغیر واحد مورداستفاده قرار می‌گیرند.

پس ازآنکه بر روی دکمه OK کلیک کردیم، جدول داده دوگانه را که در تصویر 5-17 نشان داده شده را می‌بینیم. به‌عنوان‌ مثال می‌توان مشاهده کرد که وقتی قیمت را به میزان 3.50 دلار و هزینه متغیر را به 0.40 دلار تغییر می‌دهیم، میزان سود سالیانه برابر با 58.850.00 دلار می‌شود. در اینجا قیمت سودافزا برای هریک از هزینه‌های واحد را متمایز کرده‌ایم. توجه کنید همچنان که هزینه واحد افزایش پیدا می‌کند، قیمت سودافزا نیز افزایش پیدا می‌کند چراکه برخی از افزایش هزینه‌ها به مشتریان منتقل می‌گردد. البته تضمین می کنم که قیمت سودافزا در جدول داده در میان 0.25 دلار از قیمت سودافزای واقعی ‌باشد.

وقتی در فصل 88 بنام قیمت‌گذاری کالاها با استفاده از ابزار Excel Solver کار کنید، یاد می‌گیرید که چگونه میزان دقیق قیمت سودافزا (با جزئیات قیمت) را مشخص نمایید.

جدول 5-17 یک جدول داده دوگانه که سود را به‌عنوان تابعی از قیمت و هزینه متغیر واحد نشان می‌دهد.

در اینجا مواردی مرتبط با این مسئله را باهم مرور می‌کنیم:

• وقتی مقادیر ورودی را در کاربرگ تغییر می‌دهید، مقادیر محاسبه شده توسط جدول داده نیز تغییر می‌کنند. مثلاً اگر میزان هزینه ثابت را به‌اندازه 10000 دلار افزایش دهید، تمامی ارقام مربوط به سود در جدول داده به میزان 10000 دلار کاهش پیدا خواهند کرد.
• شما نمی‌توانید بخشی از جدول داده را ویرایش و یا پاک کنید. اگر می‌خواهید مقادیر موجود در جدول داده را ذخیره کنید، ابتدا محدوده جدول را انتخاب کرده، مقادیر را کپی کنید و بعد راست کلیک کرده و گزینه Paste Special را انتخاب کنید. سپس مقادیریا Values را از منوی Paste Special انتخاب کنید. هرچند اگر این موارد را هم انجام دهید، تغییرات در ورودی‌های کاربرگ دیگر باعث به‌روز شدن محاسبات جدول داده نخواهد شد.
• وقتی‌که جدول داده دوگانه‌ای را ایجاد می‌کنید، مراقب باشید که سلول‌های ستون ورودی و ردیف را با هم اشتباه نگیرید. این اشتباه معمولاً منجر به دست آوردن نتایجی بی‌معنی خواهد شد.
• بیشتر اشخاص حالت محاسبات کاربرگ خود را در حالت خودکار قرار می‌دهند. با وجود این نوع تنظیم، هر تغییری در کاربرگ شما باعث محاسبه مجدد تمامی جداول داده شما خواهد شد. معمولاً این همان چیزی است که شما می‌خواهید اما اگر جداول داده شما بزرگ باشند، محاسبه مجدد خودکار بسیار کند خواهد بود. اگر محاسبه مجدد جدول داده‌ها کار شما را کند می‌نماید، می‌توانید روی تب File در ریبون کلیک کرده، روی گزینه Options کلیک کرده و آنگاه تب Formulas را در کادر محاوره‌ای Excel Options انتخاب نمایید. سپس در بخش Calculation Options گزینه Automatic Except For Data Tables را انتخاب نمایید. در این حالت وقتی گزینه Automatic Except For Data Tables را انتخاب می‌کنید تمامی جداول داده تنها وقتی‌که کلیدF9 (محاسبه مجدد) را فشار دهید مجددا محاسبه می‌شوند. می‌توانید از روش دیگری استفاده کرده و این بار بر دکمه Calculation Options (در گروه Calculation روی تب Formula) کلیک کرده و سپس بر گزینه Automatic Expect For Data Tables کلیک کنید.

می‌خواهم خانه جدیدی بسازم. میزان پولی که می‌بایست وام بگیرم (با دوره پرداخت 15 ساله) بستگی به قیمت فروش خانه‌ای که هم‌اکنون مالکش هستم دارد. همین‌طور درمورد نرخ بهره سالیانه‌ای که به هنگام بسته شدن معامله دریافت خواهم کرد اطمینان ندارم. چگونه می‌توانم نحوه وابستگی پرداخت‌های ماهیانه‌ام به میزان وامی که گرفته‌ام و نرخ بهره سالیانه را مشخص نمایم؟

قدرت واقعی جداول داده وقتی نمایان می‌شود که جدول داده‌ای را با یک یا دو تابع اکسل ترکیب کنید. در این مثال ما می‌خواهیم از یک جدول داده دوگانه برای تغییر دو ورودی (میزان وام‌گرفته شده و نرخ بهره سالیانه) با تابع PMT مورد استفاده قرار دهیم و مشخص کنیم که چگونه پرداخت‌های ماهیانه با تغییر این مقادیر ورودی تغییر می‌کنند. (تابعPMT در فصل دهم بنام سایر توابع مورد استفاده در اکسل با جزئیات مورد بررسی قرار گرفت. (جزئیات کار این مثال در فایلی بنام Mortgagedt.xlsx در تصویر 6-17 نشان داده شده است.

تصویر 6-17 جدول داده‌ نمایانگر پرداخت قسط‌ها به هنگام تغییر میزان وام‌گرفته شده و تغییرات نرخ بهره

 فرض کنید وامی با بازپرداخت 15 ساله گرفته‌ایم که پرداخت‌های ماهیانه آن در انتهای هر ماه انجام می‌شوند. در اینجا مقدار وام را در سلول D2 وارد کرده، تعداد ماه‌های پرداخت وام را (180) در سلول D3 و نرخ بهره سالیانه را در سلول D4 وارد کرده‌ایم. نام‌های محدود را در سلول‌های C2:C4 با سلول‌های D2:D4 مرتبط کرده‌ایم.

بالاخره بر اساس این ورودی‌ها، پرداخت ماهیانه را در سلول D5  با فرمول PMT (Annual_int_rate/12,Number_of_Months,Amt_Borrowed) محاسبه کرده‌ایم.

فرض کنید میزان وام‌گرفته شده بین 300000 دلار و 650000 دلار است (این موضوع به قیمت فروش خانه فعلی شما بستگی دارد) و نرخ بهره شما بین پنج درصد و هشت درصد می‌باشد.

در حین آماده‌شدن برای ایجاد جدول داده، مبلغ وام‌گرفته شده را در محدوده C8:C15 و مقادیر نرخ بهره ممکن را در محدوده D7:J7 ‌وارد می‌کنیم. سلول C7 حاوی خروجی است که قصد دارید آن را از ترکیب ورودی‌های مختلف محاسبه کنید؛ بنابراین من سلول C7 را برابر با سلول D5 قرار می‌دهم.

پس از آن محدوده جدول (C7:J15) را انتخاب کرده، در تب Data (گروه گزینه‌های Forecast) روی گزینه What-If Analysis کلیک کرده و بعد روی گزینهData Table کلیک می‌کنیم.

از آنجا که عددهای اولین ستون از محدوده جدول میزان وام‌گرفته شده هستند در بخش Column Input Cell  نام سلول D2 را وارد می‌کنیم. ارقام اولین ردیف از جدول نرخ بهره سالیانه می‌باشند بنابراین در بخش Row Input Cell  نام سلول D4  را وارد می‌کنم. بعد از اینکه روی گزینه OK کلیک شد، جدول داده‌ها را همان‌طور که در تصویر 6-17 نشان داده شده می‌بینیم.

این جدول مثلاً نشان می‌دهد که چنانچه شما 400000 دلار با نرخ بهره سالیانه 6 درصد وام بگیرید، پرداخت‌های ماهیانه شما چیزی بیش از3375 دلار خواهد بود.

این جدول داده‌ها همچنین نشان می‌دهد که افزایش 50000 دلاری در میزان وام‌گرفته شده با نرخ بهره پایین (مثلاً 5 درصد) باعث افزایش میزان پرداخت ماهیانه به مبلغ 395 دلار خواهد شد، درحالی‌که افزایش 50000 دلاری در میزان وام‌گرفته شده با نرخ بهره بالا (مثلاً 8 درصد) پرداخت ماهیانه را به مبلغ 478 دلار افزایش خواهد داد.

شرکت اینترنتی بزرگی در فکر خرید فروشنده آنلاین دیگری می‌باشد. درآمد سالیانه این خرده‌فروش 100 میلیون دلار و مخارج آن 150 میلیون دلار می‌باشد. به‌تازگی تخمین زده شده که درآمد این فروشنده آنلاین قرار است هرسال به میزان 25 درصد افزایش داشته باشد و مخارج آن نیز هرسال 5 درصد افزایش پیدا کند.

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

قصد داریم با استفاده از نرخ رشد سالیانه درآمد از 10 درصد تا 50 درصد و نرخ رشد مخارج از 2 درصد تا 20 درصد تعداد سال‌هایی را که نیاز داریم تا به نقطه سربه‌سر برسیم را محاسبه کنیم. بگذارید همچنین فرض کنیم که اگر شرکت نتواند در عرض 13 سال به نقطه تعادل برسد به این نتیجه برسیم که” نمی‌تواند به نقطه سربه‌سر برسد”. کارهای انجام شده در فایلی بنام Bezos.xlsx در تصویر 7-17 نشان داده شده است.

تصویر 7-17 می‌توانیم در محدوده E19:R61 از جدول داده دوگانه‌ای برای محاسبه تعداد سال‌های مورد نیاز برای رسیدن به نقطه سربه‌سر استفاده کنیم.

در اینجا تصمیم گرفته‌ایم ستون‌های A  و B  و ردیف‌های 16 الی 18 را پنهان کنیم. برای پنهان کردن ستون‌های A  و B در ابتدا همه سلول‌های ستون‌های A و B‌را انتخاب می‌کنیم (یا سرستون‌ها را انتخاب می‌کنیم) و بعد تبHome را فعال می‌سازیم. در گروه گزینه‌های Cells روی گزینه Format‌کلیک می‌کنیم و سپس گزینهHide & Unhide و بعد از آن گزینه Hide Columns را انتخاب می‌کنیم.

برای پنهان کردن ردیف‌های 16 الی 18 ، ابتدا همه سلول‌های هریک از ردیف‌ها را انتخاب می‌کنیم (و یا اینکه بر روی عنوان ردیف کلیک می‌کنیم) و روال قبلی را تکرار کرده این بار گزینهHide Rows را انتخاب می‌کنیم. البته گزینه‌های نمایشی شامل گزینه‌های نمایش‌دهنده ردیف‌ها و ستون‌ها هم هستند.

چنانچه کاربرگی دریافت کنید که در آن ردیف‌ها و ستون‌های فراوانی پنهان شده باشند و بخواهید آنها را به‌سرعت ظاهر کنید، می‌توانید تمامی کاربرگ را با کلیک بر دکمهSelect All در نقطه تلاقی سرستون‌ها و سر ردیف‌ها انتخاب کنید. انتخاب گزینه‌های Unhide Rows  و یا Unhide Columns تمامی ردیف‌ها و یا ستون‌های پنهان در کاربرگ را نمایان می‌کند. اگر کل کاربرگ پنهان شده باشد گزینه Unihide Sheet قابل‌دسترس خواهد بود و می‌توانید آن را انتخاب کرده تا تمامی کاربرگ از حالت پنهان خارج شود.

در ردیف 11 درآمد شرکت در طی 13 سال را (بر اساس نرخ رشد در آمد سالیانه در سلولE7) با کپی‌کردن فرمول=E11*(1+$E$7) از سلول F11 به محدوده G11:R11 محاسبه کرده‌ایم. در ردیف 12 مخارج شرکت را در طی 13 سال (بر اساس نرخ رشد مخارج سالیانه که در سلولE8 گمان زده شده) با کپی‌کردن فرمول =E12*(1+$E$8) از سلولF12  به محدوده G12:R12 محاسبه کرده‌ایم (تصویر 7-17 را ببینید)

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

کار را با استفاده از یک عبارت If در ردیف 13 برای هریک از سال‌ها شروع می‌کنیم تا مشخص شود آیا در آن سال به نقطه سربه سر خواهیم رسید یا نه. چنانچه در سالی به نقطه سربه سر برسیم، عبارت If عدد آن سال را برمی‌گرداند در غیر این صورت عدد صفر را برمی‌گرداند. سالی که در آن به نقطه سربه سر می‌رسیم را در سلول E15 به‌سادگی با اضافه‌کردن همه ارقام ردیف 13 مشخص می‌کنیم. دست آخر می‌توان از سلولE15 به‌عنوان سلول خروجی برای جدول داده دوگانه خود استفاده کرد.

از سلول F13 فرمول =IF(AND(E11<E12,F11>F12),F10,0)را در محدوده G13:R13 کپی می‌کنیم. فرمول حاضر این واقعیت را منعکس می‌کند که کسب و کار حاضر در طول یک سال تنها و تنها چنانچه در سال قبل درآمدها کمتر از هزینه‌ها باشد و در سال جاری درآمدها بیشتر از هزینه‌ها باشد به نقطه سربه سر خواهد رسید. اگر این مورد درست باشد، عدد سال در ردیف سیزده نشان داده می‌شود و در غیر این صورت عدد صفر نشان داده می‌شود. حالا می‌توان در سلول E15 سال نقطه سربه سر را (اگر چنین سالی وجود داشته باشد) با فرمول زیر مشخص کرد:

=IF(SUM(F13:R13)>0,SUM(F13:R13),”No BE”

چنانچه در طی 13 سال آْینده به نقطه سربه سر نرسیم فرمول رشته متنی NO BE (عدم وجود نقطه سربه سر) را در سلول وارد می‌کند.

اکنون نرخ رشد درآمد سالیانه را (10 درصد تا 50 درصد) در محدوده E21:E61‌وارد می‌کنیم. در اینجا نرخ رشد هزینه‌های سالیانه را (2 درصد تا 20 درصد) در محدودهF20:X20 وارد کرده‌ایم. اطمینان حاصل می‌کنیم که فرمول مرتبط با سال نقطه سربه سر با فرمول =E15 در سلول E20 کپی شده باشد. پس از آن محدوده E20:X61 را انتخاب کرده و روی گزینه What-If Analysis در گروه گزینه‌های Forcast در تب Data و پس از آن بر روی گزینه Data Table‌کلیک می‌کنیم.

در اینجا سلولE7 (نرخ رشد درآمد) را در بخش Column Input Cell  و سلول E8 (نرخ رشد هزینه‌ها) را در بخش Row Input Cell ‌وارد می‌کنیم و سپس روی دکمه Ok  کلیک می‌کنیم. با انجام این تنظیمات جدول داده‌های دوگانه نشان داده شده در تصویر 7-17 را به دست می‌آوریم.

توجه کنید که مثلاً اگر هزینه‌ها در طول سال 4 درصد افزایش پیدا کنند، با نرخ رشد درآمد سالیانه ده‌درصدی در طی هشت سال به نقطه سربه سر خواهیم رسید، درحالی‌که با نرخ رشد درآمد سالیانه 50 درصدی تنها در ظرف دو سال به نقطه سربه سر خواهیم رسید. همچنین بهتر است توجه شود که اگر هزینه‌ها سالیانه 12 درصد رشد داشته باشند و درآمدها 14 درصد در سال رشد داشته باشند حتی تا پایان سال سیزدهم نیز به نقطه سربه سر دست نخواهیم یافت.

چگونه می‌توان نموداری بر اساس جدول داده ایجاد کرد؟

جدول داده‌ها تنها ارقام را نشان می‌دهد. اغلب اوقات نموداری که بر اساس جدول داده‌ها تنظیم شده باشد دیدگاه بیشتری از وضعیت موجود در اختیار تحلیلگران قرار می‌دهد. برای اینکه نشان بدهیم چگونه یک جدول داده دوگانه را می‌توان برای تولید نموداری بکار گرفت بهتر است نگاهی دوباره به جدول داده دوگانه در فایلی به نام Lemonade.xlsx (تصویر 8-17 را ببینید) برای ایجاد نمودار از جدول داده دوگانه ابتدا مقادیر موجود در جدول داده را در بخش دیگری از صفحه گسترده کپی می‌کنیم (در اینجا محدوده H28:O43 را انتخاب کرده‌ایم) پس از آن سلول ورودی کپی شده را پاک می‌کنیم.

بعد از انتخاب محدوده H28:O43، نمودار X-Y یا نمودار پراکندگی را از تبInsert انتخاب می‌کنیم تا نمودار نشان داده در تصویر 8-17 را به دست آوریم.

در اینجا درمی‌یابیم که همان‌طور که انتظار می‌رفت بالاترین منحنی مرتبط با کمترین هزینه واحد می‌باشد. همچنین می‌بینیم که افزایش قیمت وقتی‌که از حد 3.75 دلار یا 4.00 دلار فراتر رود، سود بیشتری ایجاد می‌نماید. همچنین وقتی‌که هزینه تولید واحد افزایش پیدا می‌کند منحنی‌های سود، نزدیک به یکدیگر رشد پیدا می‌کنند.

تصویر 8-17 نمودار نشان می‌دهد که چگونه میزان سود به قیمت و هزینه واحد بستگی دارد

برای شروع داده‌ها را از محدوده H10:O24 به محدوده H28:O43 کپی کرده و بعد محتویات سلول H28 را پاک می‌کنیم. بعد از انتخاب محدوده H28:O43 ، از تب Insert سومین نمودار (نموداری که تنها دارای خطوط است) را انتخاب می‌کنیم. نمودار X-Y یا نمودار پراکندگی. سپس نمودار را به دست می‌آوریم که در تصویر 8-17 نشان داده شده است.

با این نمودار درمی‌یابیم که همان‌طور که انتظار می‌رفت بالاترین منحنی با کمترین هزینه واحد مرتبط است. همچنین می‌بینیم که افزایش قیمت سود بیشتری تولید می‌کند تا وقتی‌که میزان افزایش قیمت یا بیشتر از 3.75 دلار و یا 4.00 دلار باشد. همچنین با افزایش قیمت واحد منحنی‌های سود نزدیک تر به یکدیگر افزایش می یابند.

مسئله‌های این فصل:

به شما مأموریت داده شده تا میزان سودرسانی فروش کتاب زندگینامه شخصی بیل کلینتون (به قلم خودش) را تحلیل کنید. فرضیه‌های زیر در نظر گرفته شده‌اند:

1. بیل قرار است پرداخت یکجایی به میزان 12 میلیون دلار دریافت کند.
2. هزینه ثابت تولید نسخه جلد سخت (جلد چرمی) کتاب 1 میلیون دلار می‌باشد.
3. هزینه متغیر تولید هر یک از نسخ جلد سخت کتاب 4 دلار است.
4. سود خالص کتاب بر اساس نسخه جلد سخت برای ناشر 15 دلار می‌باشد.
5. ناشر انتظار دارد 1 میلیون نسخه جلد سخت از کتاب را به فروش برساند.
6. هزینه ثابت تولید نسخه جلد مقوایی کتاب 100000 دلار است.
7. هزینه متغیر تولید هر نسخه جلد مقوایی 1 دلار است.
8. سود خالص از فروش نسخه جلد مقوایی کتاب 4 دلار است.
9. فروش نسخه‌های جلد مقوایی دوبرابر نسخه‌های جلد سخت خواهد بود.

از این اطلاعات استفاده کنید تا به سؤال‌های زیر پاسخ دهید:

1. مشخص کنید چگونه سود پیش از مالیات ناشر با تغییر فروش نسخه جلد سخت از 100000 تا 1 میلیون نسخه تغییر خواهد کرد.
2. مشخص کنید چگونه سود پیش از مالیات ناشر با تغییر فروش نسخه‌های جلد سخت از 100000 تا 1 میلیون نسخه تغییر خواهد کرد و نسبت فروش نسخه‌های جلد مقوایی به جلد سخت نیز از یک تا 2.4 نیز تغییر خواهد کرد.

تقاضای سالیانه برای محصولی برابر با فرمول 500-3p+10a.5 خواهد بود، در اینجا p قیمت محصول به دلار و a هم صدها دلار هزینه صرف شده برای تبلیغات فروش محصول می‌باشد. هزینه ثابت سالیانه تولید محصول 10000 دلار و هزینه متغیر تولید هر واحد از محصول 12 دلار می‌باشد. قیمتی از محصول (در حدود 10 دلار) و میزان هزینه تبلیغات (در حدود 100 دلار) را مشخص کنید که سود را بالا خواهد برد.

در فصل دوازده به نام: توابع IF ، IFERROR، IFS، CHOOSE و SWITCH  سؤال شماره دو در مورد ممانعت از خطر کاهش ارزش سهام را دوباره مورد بررسی قرار دهید. با در نظر گرفتن ارزش سهام شش‌ماهه در محدوده 20 دلار تا 65 دلار و قرارداد اختیار فروش‌هایی که از صفر تا 100 (با افزایش 10 رقمی)، درصد بازده سرمایه‌گذاری خود را مشخص نمایید.

درمورد مثال وام مسکن، فرض کنید می‌دانید که نرخ بهره سالیانه 5.5 درصد خواهد بود. جدولی ایجاد کنید که تفاوت پرداخت‌های دوره‌های پرداخت 15 ساله، 20 ساله و 30 ساله را در مورد وام‌های دریافتی به مبلغ 300000 دلار تا 600000 دلار (با افزایش 50000 دلاری) را نشان دهد.

در حال حاضر 40000 واحد از کالایی را به قیمت واحد 45 دلار به فروش می‌رسانید. هزینه متغیر تولید هر واحد از کالا 5 دلار می‌باشد. در نظر دارید قیمت محصول را تا 30 درصد کاهش دهید. اطمینان دارید که این کار میزان فروش را از ده درصد تا 50 درصد افزایش می‌دهد. تحلیل حساسیتی را انجام دهید که نشان دهد چگونه سود به‌عنوان تابعی از درصد افزایش فروش تغییر می‌کند. قیمت‌های ثابت را نادیده بگیرید.

بیایید فرض کنیم که در انتهای هریک از چهل سال آینده مبلغ ثابتی را در حساب بازنشستگی خود پس‌انداز می‌کنید و هرسال همان نرخ بهره به شما تعلق می‌گیرد. نشان دهید چگونه میزان پولی که برای بازنشستگی خود خواهید داشت با تغییر میزان پس‌انداز از 5000 به 25000 و با تغییر نرخ بهره از 3 درصد به 15 درصد تغییر خواهد کرد.

دوره بازپرداخت یک پروژه تعداد سال‌های مورد نیازی است که سود آتی آن پروژه سرمایه‌گذاری اولیه آن را جبران می‌کند. پروژه‌ای در زمان شروع (زمان صفر) نیاز به 300 میلیون سرمایه‌گذاری دارد. این پروژه برای ده سال سود کسب می‌کند و جریان نقدی شماره 1 بین 30 میلیون و 100 میلیون است. رشد جریان نقدی در هرسال از 5 درصد تا 25 درصد می‌باشد. بازپرداخت این پروژه چگونه بستگی به جریان نقدی سال اول و نرخ رشد جریان نقدی دارد؟

شرکت توسعه نرم‌افزاری به فکر ترجمه یک محصول نرم‌افزاری به زبان سواحیلی است. در حال حاضر 200000 واحد از این کالا در سال به قیمت واحد 100 دلار فروخته شده است. هزینه متغیر هر واحد 20 دلار است. هزینه ثابت ترجمه 5 میلیون دلار است. ترجمه این محصول به زبان سواحلی باعث افزایش فروش در سه سال آینده به میزان درصدی نامشخص و بیش از فروش 200000 واحدی اکنون این محصول خواهد شد.

نشان دهید که این تغییرات در سود که در نتیجه ترجمه صورت می‌گیرند چگونه به افزایش درصد فروش محصولات مرتبط خواهند بود می‌توانید در محاسبات خود ارزش پولی زمان حاضر و مالیات را نادیده بگیرید.

فایلی به نام Citydistances.xlsx طول و عرض جغرافیایی بسیاری از شهرهای ایالات متحده را در اختیار شما قرار می‌دهد. همچنین در آن فرمولی وجود دارد که فاصله بین دو شهر را با استفاده از طول و عرض جغرافیایی داده شده مشخص می‌کند. جدولی ایجاد کنید که فاصله بین هریک از شهرهای فهرست شده را محاسبه نماید.

شروع به پس‌انداز برای تحصیلات دانشگاهی فرزند خود نموده‌اید. برنامه شما این است که سالی 5000 دلار پس‌انداز کنید و می‌خواهید مقدار پولی را که در طول 10 الی 15 سال با نرخ بهره سرمایه‌گذاری به میزان 4 تا 12 درصد برای تحصیلات دانشگاهی پس‌انداز کرده‌اید را بدانید.

اگر شما در طول سال بهره‌ای با نرخ بهره r به دست آورید و بهره خود را n بار در سال جمع‌آوری کنید، آن‌وقت در y سال یک دلار شما به (1+(r/n))ny دلار افزایش خواهد یافت. با فرض نرخ بهره سالیانه ده درصد، جدولی ایجاد کنید که ضریبی را نشان دهد که با آن 1 دلار در 5 تا 15 سال با جمع‌آوری روزانه، ماهیانه، فصلی و نیم‌سال بهره افزایش پیدا کند.

فرض کنید که صد دلار در بانک پس‌انداز داریم. هرسال x درصد (4 درصد تا 10 درصد) از حساب خود برداشت می‌کنیم. با در نظر گرفته نرخ رشد سالیانه سه تا 10 درصد، مشخص کنید چند سال طول می‌کشد تا حساب خالی شود. توصیه: بهتر است در اینجا از تابع IFERROR استفاده کنید (که در فصل 12 مورد بحث قرار گرفت) چراکه اگر نرخ رشد سالیانه از نرخ برداشت بیشتر شود، حساب هیچ‌وقت خالی نمی‌شود.

اگر بهره‌ای با نرخ سالیانه x درصد به دست آورید بنابراین در طول n سال 1 دلار شما به (1+x)n دلار تبدیل خواهد شد. با در نظر گرفتن نرخ بهره سالیانه‌ای از 1 درصد تا 20 درصد، زمان دقیقی (به سال) که 1 دلار دوبرابر خواهد شد را مشخص کنید.

200000 دلار از کسی وام‌گرفته‌اید و در آخر هر ماه قسط‌های آن را پرداخت می‌کنید. با در نظر گرفتن نرخ بهره سالیانه‌ای که از 5 درصد تا 10 درصد نوسان دارد، در طی دوره‌های پرداخت 10، 15، 20 ، 25 و 30 ساله، کل بهره پرداخت شده برای این وام را محاسبه کنید.

در حساب مخصوص دانشگاه فرزندتان پس‌انداز می‌کنید. قرار است در پایان هرسال میزان معینی از پول را به این حساب واریز کنید. هدف آن است که 100000 دلار پول پس‌انداز کنید. با در نظر گرفتن بازده سرمایه‌گذاری سالیانه‌ای که از 4 درصد تا 15 درصد در نوسان است و تعداد سال‌های پس‌انداز که از 5 تا 15 می‌باشد، مشخص کنید که هرسال چقدر می‌بایست پول به این حساب واریز کنید.

فایلی به نام Antitrustdata.xlsx شروع و پایان سال‌های بسیاری از پرونده‌های دادگاه را نشان می‌دهد. تعداد پرونده‌های فعال در طول هرسال را مشخص کنید.

می‌توانید در سن 62 سالگی بازنشسته شده و سالیانه 8000 دلار دریافت کنید و یا اینکه در سن 65 سالگی بازنشسته شده و سالیانه 10000 دلار دریافت کنید. تفاوت (به دلار نرخ امروز) بین این دو گزینه انتخابی را همچنان که نرخ سالیانه تخفیف جریان نقدی را بین 2 درصد و 10 درصد تغییر می‌دهید و همچنین تغییر سن فوت بین 70 تا 84 سال، مشخص نمایید.

به فکر گشودن رستورانی هستید که دارای شش میز چهارنفره است. هر روز که رستوران باز است دو بار نهار و سه بار شام سرو می‌کنید.

1. رستوران روز دوشنبه بسته خواهد بود.
2. میانگین پرداخت برای نهار 20 دلار است و از پرداخت‌های نهار 40 درصد حاشیه سود به شما تعلق می‌گیرد.
3. میانگین پرداخت برای شام 40 دلار است و شما 50 درصد حاشیه سود از پرداخت‌های شام کسب می‌کنید.
4. فرض کنید که هزینه ثابت اداره رستوران در سال 400000 دلار می‌باشد.

حالا با فرض وجود 364(52*7) روز در سال، از جدول داده‌ای استفاده کنید که به شما نشان دهد چگونه سود سالیانه با درصد پر بودن صندلی‌هایی که بین 10 درصد و 100 درصد متغیر باشد دچار تغییر خواهد شد.

در نمایش جدیدی  در تئاتر برادوی به نام “حشره‌ای در نور” سرمایه‌گذاری کرده‌اید. اطلاعات زیر درباره این نمایش به شما ارائه شده است:

1. هزینه ثابت اجرای این نمایش 5 میلیون دلار است.
2. میانگین قیمت بلیت 100 دلار است.
3. صندلی‌های تئاتر گنجایش 2000 نفر را دارند و در سال 365 اجرا انجام می‌شود.

از جدول داده‌ای استفاده کنید تا مشخص نمایید که چگونه سود کلی که ناشی از اجرای نمایشنامه است با تغییر طول اجرای نمایش از 1 تا 5 سال و تغییر میانگین نسبت پر بودن سالن بین 70 درصد و 90 درصد تغییر می‌نماید.

در حال حاضر نشریه‌ای 5000 مشترک دارد. مشخص کنید که چگونه تعداد مشتریان در انتهای سال ششم انتشار با تغییر تعداد مشترکان جدید بین 1000 و 5000 نفر و همچنین تغییر نسبت ابقای مشتریان بین 60 درصد و 90 درصد تغییر خواهد کرد.

فرض کنید وقتی مشتری‌ای از کاتالوگی چیزی سفارش می‌دهد، سودی 50 دلاری (که البته هزینه پستی کاتالوگ شامل آن نمی‌شود) کسب می‌شود. از جدول داده‌ای استفاده کنید تا مشخص نمایید چگونه سود کل کسب شده از ارسال پستی با تغییر نسبت پاسخ دهی به ارسال پستی بین 1 تا 10 درصد دچار تغییر خواهد شد.

امروز پنج سهم از سهام شماره 1 به قیمت 30 دلار برای هر سهم و سه سهم از سهام شماره 2 به قیمت 25 دلار برای هر سهم خریداری کرده‌اید. قرار است در طول یک ماه تمامی سهام خود را به فروش برسانید. از جدول داده‌ای استفاده کنید تا مشخص نمایید چگونه سود کل با تغییر در قیمت هریک از سهام از 5- دلار تا 5 دلار دچار تغییر خواهد شد.