How to Create a Data Table in Excel

Ernesto Hontoria

A Data Table is a wonderful Excel solution to automate sensitivity analysis, and to help us to understand the influence of one or two variables in the outcome of an Excel model. The tool works auto populating the results of the model in a table, in a similar way one person would manually do changing the value of a variable and taking notes of the results. In other words, you can use a data table to automatically populate the results of different incomes in a table.

Let see how the Data Tables work with an example:

Assume you want to rent a store to sell shoes. The rent is $ 1,000 a month plus $300 by month in utilities. You want to know how many pairs of shoes you need to sell every month just to cover the rent and utilities, before making any deal. The range of selling prices, for the shoes you plan to sell, is between $50 and $100, and you know that the average margin for the industry is around is 30%.

Here are the steps to build the data table:

1. Create an excel model with variables in independent cells like the picture below. Cell C9 equals C7 + C8, while F9 equals F7 times F8. Units to Cover Fixed cost in cell F12 =  C9/F9

   

   Figure 1: Creating a model in Excel
   

   
   
2. Let’s starts creating one variable data table to analyze the price effect. Type the range of prices you want to analyze in cells E13 to E18.

   

   Figure 2: Creating a One Variable Data Table
   

   
   
3. Select the range E12:F18, then on the Data tab, in the Forecast group, click What-If Analysis, click Data Table

   

   Figure 3: Inserting a Data Table
   

   
   
4. A Data Table menu screen will pop up, asking for row and column input cell. Since the variable we want to analyze is the price, which is in cell F7, and we have populate the values in the column of the table, we link cell F7 to the column input, and click ok.

   

   Figure 4: Linking the Data Table to a variable in the model
   

   
   

Excel will populate the results in the table, and every time a variable is updated (Rent, Utilities, or Margin %) the Data Table will refresh its numbers automatically. Please, notice that you can also put the price in the rows, instead that in the column, and create an equivalent data table as shown in Figure 5. To do that you follow the same process but instead of linking the variable price (F7) to the column input, you link the variable to the row as shown in Figure 6.

Figure 5: One Variable Data Table - Horizontal

Figure 6: In an Horizontal One Variable Table link the row to the model

Data tables can be used as well to analyze how the combination of two variables impact your results. Say for example that you want to analyze the effect of the selling price combined with the margin. In that case you can build a data table with the values of one variable in the rows and the values of a second variable in the column as shown in Figure 7.

Figure 7: Two variables Data Table

1. To build a two variable data table, type the values you want one of the variables to take, in the cells beside the formula; and the values you want for the second variable in the cells beneath the formula. For example, if you want to analyze the combination of margin and price in a two variable data table, and the formula is in cell F12.
2. Type the different margin rates in cells G12 to M12, beside the formula.
3. Type the prices in cells F13 to F18 beneath F12.
4. Select the table from F12 (formula) to the last value in the table in both directions M18, then with the table selected, go to the Data tab, and click What-If Analysis in the Forecast group. Then click Data Table

   

   Figure 8: Inserting a Data Table
   

   
   
5. The screen menu of the data table will pop up. Click in the 'Row input cell' box and select cell F8, to link the margin % values of your table (row) to the cell F8. Then click in the 'Column input cell' box and select cell F7, to link the prices in the column of you table to the variable F7 of your model. Then click ok.

   

   Figure 9: Linking the model to a two variables Data Table
   

   
   

Voila! Your table is ready! It is telling you that if you sell the shoes for 50 pesos at 20% margin you will need to sell 130 pairs of shoes to cover your fixed cost. If your price is 60 pesos you will need to sell 108 pairs at the same margin. And so on.

Why the Carbon Tax?

 Ernesto Hontoria

My decision to support the Carbon Tax is not a political statement, but it heavily influences my voting decision. I support the Carbon Tax because I am convinced that it is an efficient way to include the hard to measure cost of polluting the air. In other words, polluting is not free and should be accounted for. But let me explain my thoughts from an economical perspective.

I used to work for an oil company that extracted heavy crude oil from the Orinoco belt. To extract the oil, the company goes to very pristine areas to drill wells, then extracts oil for many years. When the oil extraction is finalized, or oil wells are no longer profitable, the company closes those wells and restores the land as close as possible to its initial state. Leaving the land as you found it, is simply the right thing to do. Don’t you think so?

Normally, the directors of companies are responsible citizens that want to do the best they can to reduce the environmental damage. Of course, there are some that just look for the immediate return, and do not care for the consequences, but in my experience, in the oil industry, they are not a majority. For that reason, companies have in their books a reserve[1] for those restorations works, that they know, the company will need to do, once they finish their job. Therefore, every time an oil company clears an area to drill a new well, they start to accumulate funds in this reserve called ‘Remediation Cost’, that will hit the company economic results (their P&L) immediately.

This is the way that oil companies proceed with their oil wells, and oil fields as well, because it is visible and measurable. It is not in their best interest that people start sharing pictures of abandoned wells and oil fields, with clear damage done to the environment. It is best to restore the area, plant new trees, and leave as minimal visible disruption as possible. Environmental damages that are not visible, or measurable, are a different story, and for these it is hard to establish a ‘Remediation Cost’.

Air pollution enters into this last category of invisible and hard to measure remediation cost. Indeed, it is more difficult than just that; as it is not easy to discern who bears responsibility for this particular damage: the oil company which extracts the oil, or the consumer that burns that oil and liberates the CO₂ in the air?

The carbon tax is finally addressing the issue of putting a cost to air pollution. It is not perfect, but so far, it is the most efficient way to start tackling the problem. Without this cost being considered, the transition to cleaner sources of energy will be more difficult.

Let me give you two examples that embrace the importance of having this cost in the equation. 

In the late 1800s, the supply and transportation cost of coal to drive the industrial revolution was a concern to the French government. So much that the government decided to fund studies to develop other sources of energy. As a result of these investments, in 1869, Augustin Mouchot tested in Paris the largest solar engine built till that moment, and two years later installed and experimental solar generator at the Tours library. He compiled his research and work into a book becoming a prominent figure in the field of solar power. In 1878, Mouchot won a Gold Medal for transforming the solar heat into energy. His solar powered mechanical invention was displayed at the Universal Exhibition in Paris. However, as coal became increasingly cheaper in France, the government resolved that solar energy was uneconomical, and ended Mouchot's research funding.[2]

Of course, at that time French authorities did not know the consequences of the CO₂ emissions in the environment. But would they have got to the same conclusion if they had added the ‘Remediation Cost’ of burning coal? One way of getting an idea of the magnitude of this ‘Remediation Cost’ is calculating how much it would have costed to sequestrate all the CO₂ that was liberated to the atmosphere from those coal powered factories that flourished in France.

The second example came in 1913, this time in Egypt. An engineer named Frank Shuman built the first solar thermal power station in the world. At the time of its construction, it was a cost competitive source of energy, able to pump 6,000 gallons of water per minute from the Nile River to adjacent cotton fields[3]. The plant was dismantled or destroyed (not sure) during the First World War, and after the war finished, the substitution of the coal for cheaper oil in the 1930s completely discouraged the advancement of solar energy. Oil powered plants replaced the missed solar facility.

This time was ‘cheaper oil’, that not having in its cost structure the ‘Remediation Cost’ of restoring the CO₂ levels in our atmosphere, did not allow for an opportunity to develop a cleaner source of energy.

The carbon tax it is not a radical political idea, but an economic concept that helps to deal with a negative externality. Negative externalities occur when an individual or company does not have to pay the full cost their goods are causing to society; when the cost to society is greater than the cost consumer is paying for it. Pollution is a good example of a negative externality, and air pollution it is not different. What probably makes the carbon tax difficult to digest is to discover that we (ourselves) are the polluters. But in my mind is just a way to level the field to those who want to produce cleaner energy.

---

[1] A reserve in accounting are funds that are set apart (reserved) to deal with a potential cost in the future.

[2] https://en.wikipedia.org/wiki/Augustin_Mouchot

 

[3] https://en.wikipedia.org/wiki/Frank_Shuman