Veri Tabloları ile What-If Analizi

Excel'deki Veri Tablosu ile bir veya iki girişi kolayca değiştirebilir ve What-if analizi yapabilirsiniz. Veri Tablosu, bazı hücrelerdeki değerleri değiştirebileceğiniz ve bir soruna farklı yanıtlar bulabileceğiniz bir hücre aralığıdır.

İki tür Veri Tablosu vardır -

Tek değişkenli Veri Tabloları
İki değişkenli Veri Tabloları

Çözümleme probleminizde ikiden fazla değişken varsa, Excel'in Senaryo Yöneticisi Aracını kullanmanız gerekir. Ayrıntılar için, bu eğitimdeki Senaryo Yöneticisi ile Olabilirlik Analizi bölümüne bakın .

Tek değişkenli Veri Tabloları

Bir veya daha fazla formüldeki bir değişkenin farklı değerlerinin bu formüllerin sonuçlarını nasıl değiştireceğini görmek istiyorsanız, tek değişkenli bir Veri Tablosu kullanılabilir. Başka bir deyişle, tek değişkenli bir Veri Tablosu ile, bir girişi değiştirmenin herhangi bir sayıda çıktıyı nasıl değiştireceğini belirleyebilirsiniz. Bunu bir örnek yardımıyla anlayacaksınız.

Example

30 yıllık bir görev süresi için 5.000.000 kredi var. Değişken faiz oranları için aylık ödemeleri (EMI) bilmek istiyorsunuz. İkinci yılda ödenen faiz ve Anapara tutarını bilmek de ilginizi çekebilir.

Tek Değişkenli Veri Tablosu ile Analiz

Tek değişkenli Veri Tablosu ile analizin üç adımda yapılması gerekir -

Step 1 - Gerekli arka planı ayarlayın.

Step 2 - Veri Tablosunu oluşturun.

Step 3 - Analizi gerçekleştirin.

Bu adımları ayrıntılı olarak anlayalım -

1. Adım: Gerekli arka planı ayarlayın

Faiz oranının% 12 olduğunu varsayalım.
Gerekli tüm değerleri listeleyin.
Formüllerin hücre referansları yerine adlara sahip olması için değerleri içeren hücreleri adlandırın.
EMI, Kümülatif Faiz ve Kümülatif Ana için hesaplamaları Excel işlevleriyle (sırasıyla PMT, CUMIPMT ve CUMPRINC) ayarlayın.

Çalışma sayfanız aşağıdaki gibi görünmelidir -

C sütunundaki hücrelerin, D sütunundaki ilgili hücrelerde verildiği gibi adlandırıldığını görebilirsiniz.

Adım 2: Veri Tablosunu Oluşturun

Değerlerin listesini, yani değiştirmek istediğiniz faiz oranlarını giriş hücresinde E sütununa aşağıdaki gibi yazın -

Gördüğünüz gibi Faiz oranı değerlerinin üzerinde boş bir satır var. Bu satır, kullanmak istediğiniz formüller içindir.

İlk işlevi yazın (PMT) hücrede bir satır yukarıda ve bir hücre değer sütununun sağında. Diğer işlevleri yazın (CUMIPMT and CUMPRINC) ilk işlevin sağındaki hücrelerde.

Şimdi, Faiz Oranı değerlerinin üzerindeki iki satır aşağıdaki gibi görünür -

Veri Tablosu aşağıda verildiği gibi görünür -

Adım 3: Analizi What-If Analizi Veri Tablosu Aracı ile yapın

Değiştirmek istediğiniz formülleri ve değerleri içeren hücre aralığını seçin, yani - E2: H13 aralığını seçin.
Şerit üzerindeki VERİ sekmesine tıklayın.
Veri Araçları grubunda Durum Analizi'ni tıklayın.
Açılır listeden Veri Tablosu'nu seçin.

Data Table iletişim kutusu belirir.

Sütun giriş hücresi kutusundaki simgeyi tıklayın.
Hücreyi tıklayın Interest_Rate, C2.

Sütun giriş hücresinin $ C $ 2 olarak alındığını görebilirsiniz. Tamam'ı tıklayın.

Veri Tablosu, aşağıda gösterildiği gibi giriş değerlerinin her biri için hesaplanan sonuçlarla doldurulur -

If you can pay an EMI of 54,000, you can observe that the interest rate of 12.6% is suitable for you.

Two-variable Data Tables

A two-variable Data Table can be used if you want to see how different values of two variables in a formula will change the results of that formula. In other words, with a twovariable Data Table, you can determine how changing two inputs changes a single output. You will understand this with the help of an example.

Example

There is a loan of 50,000,000. You want to know how different combinations of interest rates and loan tenures will affect the monthly payment (EMI).

Analysis with Two-variable Data Table

Analysis with two-variable Data Table needs to be done in three steps −

Step 1 − Set the required background.

Step 2 − Create the Data Table.

Step 3 − Perform the Analysis.

Step 1: Set the required background

Assume that the interest rate is 12%.
List all the required values.
Name the cells containing the values, so that the formula will have names instead of cell references.
Set the calculation for EMI with the Excel function – PMT.

Your worksheet should look as follows −

You can see that the cells in the column C are named as given in the corresponding cells in the column D.

Step 2: Create the Data Table

Type =EMI in cell F2.

Type the first list of input values, i.e. interest rates down the column F, starting with the cell below the formula, i.e. F3.
Type the second list of input values, i.e. number of payments across row 2, starting with the cell to the right of the formula, i.e. G2.

The Data Table looks as follows −

Do the analysis with the What-If Analysis Tool Data Table

Select the range of cells that contains the formula and the two sets of values that you want to substitute, i.e. select the range – F2:L13.
Click the DATA tab on the Ribbon.
Click What-if Analysis in the Data Tools group.
Select Data Table from the dropdown list.

Data Table dialog box appears.

Click the icon in the Row input cell box.
Click the cell NPER, which is C3.
Again, click the icon in the Row input cell box.
Next, click the icon in the Column input cell box.
Click the cell Interest_Rate, which is C2.
Again, click the icon in the Column input cell box.

You will see that the Row input cell is taken as $C$3 and the Column input cell is taken as $C$2. Click OK.

The Data Table gets filled with the calculated results for each combination of the two input values −

If you can pay an EMI of 54,000, the interest rate of 12.2% and 288 EMIs are suitable for you. This means the tenure of the loan would be 24 years.

Data Table Calculations

Data Tables are recalculated each time the worksheet containing them is recalculated, even if they have not changed. To speed up the calculations in a worksheet that contains a Data Table, you need to change the calculation options to Automatically Recalculate the worksheet but not the Data Tables, as given in the next section.

Speeding up the Calculations in a Worksheet

You can speed up the calculations in a worksheet containing Data Tables in two ways −

From Excel Options.
From the Ribbon.

From Excel Options

Click the FILE tab on the Ribbon.
Select Options from the list in the left pane.

Excel Options dialog box appears.

From the left pane, select Formulas.
Select the option Automatic except for data tables under Workbook Calculation in the Calculation options section. Click OK.

From the Ribbon

Click the FORMULAS tab on the Ribbon.
Click the Calculation Options in the Calculations group.
Select Automatic Except for Data Tables in the dropdown list.