WHAT-IF ANALYSIS IN MICROSOFT EXCEL (GOAL SEEK AND DATA TABLE)

• In today’s blog, I’ll be writing on how to use and apply two of the what-if analysis options in Microsoft Excel.
• The third one will be discussed in my next blog.
• The first one I will write about is the goal seek analysis. The goal seek analysis is used to calculate backwards in order to obtain an input hat will result in a given output. We will consider a scenario below.
• For example, let’s assume we want to give discounts on four products in a shop and make a target value of 8000 after selling the products, but we only have the initial price of three of the products. The goal seek analysis can help us compute the price of this product.
• The first thing is to go to the DATA tab in Microsoft Excel — What-If Analysis — Goal Seek

picture depicting the values to be entered in the dialog box

• After clicking the Goal Seek option, a dialog box will appear which will ask for the following as seen in the picture above:

1. Set cell: This is the cell with the present total value which is cell E8 in our case.

2. To value: This represents the target value which is 8000 as seen in cell E10.

3. By changing cell: This is the empty cell we need to get the price for which is cell C7 in our case (price of Toothpaste).

4. Press OK after imputing all these values as described, the price of Toothpaste will automatically be calculated such that when imputed, it will give our target value which is 8000.

picture depicting the price of toothpaste after using goal seek analysis

• The second one is the data table. The data table is used to check how the output changes when the input changes.
• Let’s assume again that we want to give discount on a product based on the quantity bought and then decided to vary for different product price and quantity to get the net price, it will take a lot of time trying to compute individual values especially if we have to populate a wide range of cells. This is where the data table is useful.
• The first thing is to select the entire table to be populated which is from cell B7 to I13 in our case — go to the DATA tab in Microsoft Excel — What-If Analysis — Data Table

NB: Cell B7 is the net price gotten from calculating the discount in cell C4 on the price in cell C2 and quantity in cell C3.

picture depicting the values to be entered in the dialog box

• After clicking the Data Table option, a dialog box will appear which will ask for the following as seen in the picture above:

1. Row input cell: This is the cell that contains the row input which is price in our case in cell C2.
2. Column input cell: This is the cell that contains the column input which is quantity in our case in cell C3.
3. Press OK after imputing these values as described, the net price for the various prices and quantities will automatically be calculated and populated in the selected table range.

picture depicting the populated net prices after using data table analysis