Hello Friends Its Sachin Here with another wonderful blog on Data Table in Excel

The need of This blog arise when i was working with a business model where there were so many future possibilities and outcome of each of such possibility was different.

Now i have two possible solutions for such problem

  1. calculate the possible outcome for each future possibility, well in that case it will consume so much time and energy and at the same time chances of clerical error are too high
  2. make a Data Table in excel which will hardly take 10 minutes

So lets understand that how to make data table with the help of various examples

Data Table to assess the Profitability of Business at Different level of Production

Case 1 – I wanted to start a business of T shirt manufacturing and the total cost allocation in Fixed cost , variable cost , Selling price per T shirt i:e Revenue per T shirt , % of Defective T Shirts to be produced in Total output.

In the First table individual cost per unit has been given

and in 2nd table total variable cost, Total cost , total revenue and total profit on the basis of a single assumption which is production = 2000 unit is given.

Now Suppose i want to know the Total revenue , Total Variable Cost , Total Fixed Cost and Total profitability at various levels of Production.

For that we have another 2 options

  1. Scenario Analysis – but it will take huge time
  2. Make a Data Table

Now when we have decided to make a data Table lets discuss the steps to be followed to make it.

Data Table Showing Profitability at various levels
Chart Showing Profitability of business on the basis of diff level of production
Profitability = Gap between Blue line and Orange line

Data table with 1 variable

click over 3 dots to download video

Step – 1 the first condition to make a data table is to make sure that you must have formulas that use various inputs

able is to make sure that you must have formulas that use various inputs

For example in the above case formulas in 2nd table are using the inputs such as Variable cost per unit, fixed cost, labor cost per units etc

Step – 2 Construct the Units of Production in column A

Step – 3 Below Total Revenue Create a Formula that point towards Total Revenue and same with other variables such as Total Cost , Fixed Cost and Total Profits

Step – 4 Now Select a range Starting from units to last row of total profits amounting Rs 12,78,000

Step – 5 Now Press Alt + D + T to open data table tab and in that fill the column input data with cell containing original quantity data shown in decision variable and leave the row input data blank

Now table has been filled

the above example is of one variable data table as there is only one variable that is changing which is Production Quantity.

If this steps are hard to interpret kindly download the video attached above to clear your remaining doubt.

Now lets see an example of Data Table with 2 Variables

One Variable here will be – Production Quantity

Second Variable will be – % of defective item in total Output

Data table with 2 variables

Click over 3 dot to download vide

Data table with 2 variable

Steps to Create 2 Variable Data Table

Step – 1 Select cell containing amount of profit Rs 3,46,000 in above table and point it towards cell containing profit amount in decision variable table

Step 2 – Form column A with units in it

Step – 3 Construct a row Containing % of Defective items in total output

Step – 4 Now Select a range starting from profit amount 346000 and ending at last row of last column of Rs 12,36,000

Step – 5 Press Alt+D+T

Step – 6 Now Fill Column Input with cell number containing Quantity data in decision variable table and Row Column with cell Containing % of defective item.

In case of any doubt i have uploaded a video above. you can view it after downloading.

Now when We have discussed Data table with 1 and 2 variable let me give you two another example on how to data table in case of loan analysis and such other scenarios.

Data Table with One variable to analyse he loan installment in various scenerios

PMT means Installment

In the above example we have analysed the different Installment that will be there in case we select different years to maturity.

we can modify the table as per our requirement

Lets discuss another example of Data table with 2 variable in case of loan analysis

Data Table with 2 variables – Years to maturity and Rate of Interest

In the above example we have analysed different installments of Loan on the basis of changing rate of interest and years to maturity

With this i will end the blog in case of any query in data table you can always contact me at chetnanisachin@gmail.com and if you want above excel file to practice , All you have to do is just mail me !

Have a Nice Day

Leave a Reply

Your email address will not be published. Required fields are marked *