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
- 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
- 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
- Scenario Analysis – but it will take huge time
- 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 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
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.
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
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 firstname.lastname@example.org and if you want above excel file to practice , All you have to do is just mail me !
Have a Nice Day