WHAT-IF using Data Tables

Data Tables in Excel

A Data Table is very useful when we have to see the results by altering input variables. Following example illustrates an example of using data table to find out the profit by varying selling price of a product.

Suppose you are a manager in a manufacturing company which produces product P. The demands for Product P are 120 units per week at $85 per unit. The overhead (fixed) cost is around $3500. P requires two raw materials: Steel Widget and Rubber Gasket. Steel Widget costs $20.00 and Rubber gasket is $12.00. You are going to decide how many Ps to make given:

a. To break-even (the net profit is zero)                                                                                             

b. In order to make a profit of $20000                                                                                              

c. If you have a limitation of manufacturing 200 products only and want a minimum profit of 12000, what would be your selling price?

Answer:

A and B can be solved using “Scenario manager” and “Goal Seek” methods of WHAT-IF analysis that was discussed in my earlier posts.

To solve C. We can use DATA TABLE.

Step 1: Enter Number of Products in Cell F14, Selling Price in J14. Calculate Total cost, Total Revenue and Net Profit using appropriate formulas.

Step 2: Prepare a Table first by entering Selling price on each columns (O24 to S24) and product on the rows (M24:M32) as shown below:

Step 3: In Cell D18 enter, reference to the Net Profit cell (N18) as shown below. Cell N24 should have 2860 value (Net profit).

Step 4: Select the Table N23:S30 as shown.

Step 5: From the Excel menu bar, click on Data
Step 6: Locate the Data Tools panel
Step 7: Click on the "What if Analysis" item
Step 8: Click on “Data Table”

Step 9: You will get this window

Step 10: Enter the row and column values. In Column input cell, enter NetProfit cell reference = N18.

Step 11: In Row Input cell, enter selling price cell reference:
Step 12: In Column Input Cell, enter product manufactured cell reference:

Step 13: Enter OK to get the complete Table with Netprofit Values filled.

Step 14: In the table go horizontal on Product 200 row (N30) to get the Net Profit you are looking for (12000). You will find this value under “selling price” of 110.

Conclusion: With a limitation of manufacturing capacity of 200, expecting a Net profit of around 12000, selling value of the product will be 110.