How to: Feed a datatable from a solver/Goal seek generated answer

[DAGUET]

Dear Fellows
I have attached my file : here is the point : when we fill datatable, we usually do it to assess variation of a specific calculated output (return, profit....). When you do a breakeven analysis (as the one attached) you seek or solve the Min number of units you need to sell to breakeven. Question : how would you build a two-variable datatable reflecting results of various pair of assumptions (here 25, row is made up of 5 possible position of variable 1 and column is the same) feeded with repeatedly generated number of goal seek or solver? This in my view requires a bit of coding (loop?) to make it work. Your help/ideas much appreciated

best

 

[Hui]

The problem with Solver and Goal Seek is that they aren't automatically generated which is a shame as it limits you in the situation like yours

The only way is to put them inside a loop in some VBA and then store the answers as required

Can you explain specifically which data tables and where they should feed in your model and I'll have a look later

 

[NARAYANK991]

Hi Patrick ,

The uploaded file is a simple way of getting what you want ; I have not used Goal Seek , since the number of data points are not too many.

There are 3 data tables involved , for which there are 3 macros :

Calculate_DataTable1
Calculate_DataTable2
Calculate_DataTable3

What the macro does is define the row input cell , column input cell and the output cell for each of the data tables ; thereafter , it calls the macro :

Insert_DataTable_Values

which inserts the values to be found in the headers of the data table into the row input cell and column input cell , and then transfers the resulting output cell value into the data table.

I have filled in the second data table using this , and I think the resulting values are OK.

The only point is that for data table 1 , there will be one set of data table values for each of the values of planes sold , ranging from 20 through 70 ; thus , to look at the data table 1 , you will need to manually enter the values 20 , 30 , 40 , 50 , 60 and 70 in the cell K35 , and each time run the macro Calculate_DataTable1.

Narayan

 

[Hui]

I agree with this approach except that I thought the No. of Planes is what the Goal Seek determined for each case?

 

[NARAYANK991]

Hi Hui ,

From my understanding , the output cell is the one whose address is given in cells O3 , O13 and O23.

Thus , in O13 , the address is C48 , which is the Net present Value. Thus , for the second data table , the Operating Margin ( C36 ) values and the Planes Sold ( K35 ) values are changed to arrive at the NPV value in each case.

Similarly , in O3 , the address is K35 , which is the Planes Sold. Thus , for the first data table , the Operating Margin ( C36 ) values , and the R & D Costs ( L2 ) values are changed to arrive at the Planes Sold value in each case.

However , here the problem is that the Planes Sold and the Net Present Value are related , and so unless we can fix one , we cannot arrive at the other. Using Goal Seek would ensure this , but that is adding another step. And since the objective is to have a positive NPV , it is obvious that the value of Planes Sold cannot be one value ; for each set of values of Operating Margin and R & D Costs , there will be one value of Planes Sold. Entering 30 here , and running the Calculate_DataTable1 procedure gives several combinations of Operating Margin and R & D Cost. The same will happen with Planes Sold being put to 40 , 50 , 60 and 70.

By asking the user to enter values of 20 , 30 , 40 , 50 , 60 and 70 in the Planes Sold ( K35 ) cell , I am hoping that it will not be asking for too much , since the procedure is simplified. Otherwise , the code for the first data table will be quite different from the code for the second.

Narayan

 

[Hui]

I did say "I agree with this approach"
He will need to interpolate or chart the Data Table to get other results

 

[DAGUET]

I did say "I agree with this approach"
He will need to interpolate or chart the Data Table to get other results

Dear All -

you are a wonderful community and I love that. Laways ready to help as you did before for me. I am really indebted to you guys.

The point is I did not really have an issue to feed datatable #2 as it was the usual stuff for me defining row/column inpu cell and defining output as NPV. that is working the way it normally does.

On datatable 1 and 3 the issue is the output cell should be a number of planes, as it is a breakeven analysis. Therefore, the struggle is to generate runs one after another and then feed the datatable with the results (ouput variable K35).

Both datatable would use : L2 as column input variable and C36 as row input variable. I thought using solver and imbedding a loop in the solver could help but I am not sure....

Hope this is clearer

My Best and Warm Regards

 

[DAGUET]

Hi Patrick ,

The uploaded file is a simple way of getting what you want ; I have not used Goal Seek , since the number of data points are not too many.

There are 3 data tables involved , for which there are 3 macros :

Calculate_DataTable1
Calculate_DataTable2
Calculate_DataTable3

What the macro does is define the row input cell , column input cell and the output cell for each of the data tables ; thereafter , it calls the macro :

Insert_DataTable_Values

which inserts the values to be found in the headers of the data table into the row input cell and column input cell , and then transfers the resulting output cell value into the data table.

I have filled in the second data table using this , and I think the resulting values are OK.

The only point is that for data table 1 , there will be one set of data table values for each of the values of planes sold , ranging from 20 through 70 ; thus , to look at the data table 1 , you will need to manually enter the values 20 , 30 , 40 , 50 , 60 and 70 in the cell K35 , and each time run the macro Calculate_DataTable1.

Narayan

Dear Narayan

I understand what you did and thank you warmfully for that.
However, this does not tackle the issue of the table 1 and 3. table two is fine and works through a typical two-variable datatable caller (I can manually call the table Row and Column input and it works. When it comes down to table 1 and 3, I can use the same process and this does not work.

Help is much appreciated. On my side I am still trying.....

best

 

[Hui]

If you follow Narrayn's solution and then chart the results
The answers should be clear where the NPV is < 0

 

[DAGUET]

If you follow Narrayn's solution and then chart the results
The answers should be clear where the NPV is < 0

Thanks Hui. Now it is clear

Regards

 

[Hui]

There's always two ways to kill a rabbit with Excel

 

[p45cal]

I presume in the 3rd data table you (DAGUET) wanted to see the numbers of planes. I will try and give you that, but since I don't use what-if data tables much at all. it's been a case of looking it all up (again!).
I have two questions, one to everyone:
1. Is there a way, everyone, when viewing a data table and its results, of determining what the user used as Row Input Cell and Column Input Cell. So far I haven't been able to find this info.

2. And Daguet, if there is no answer to the above could you tell me what you would use as Input Row Cell for the operating margin percent, and the Row Input cell for ther Reasearch and Development costs (for the 3rd table)?

My first exploration would be to try and devise a formula (if it's not too complex) to determine the number of planes required to be sold. This I'd then use in a what-if data table.
If this proves too hard, then I would look at what I think you, Daguet, were already hoping for when you first posted this question, a bit of looping Solver vba code to get the values into the table

 

[NARAYANK991]

Hi ,

The formula for a Data Table is :

=TABLE(RowInputCell , ColumnInputCell)

Thus , for the second data table , the formula would be :

=TABLE(C36 , K35)

In VBA , the coding would be :

inputrange.Table RowInput:=Range("C36"), ColumnInput:=Range("K35")

where inputrange would be Range("O13:T19")

Narayan

 

[p45cal]

Narayan, all,
regarding Q1: have I been STUPID or what!? (I didn't look at the formulae in the data table!)
Thanks to Narayan,
cheers, p45cal.

And to Daguet, I should really read your posts, answer to Q2 you already told us:"L2 as column input variable and C36 as row input variable."

 

[p45cal]

re:"This in my view requires a bit of coding (loop?) to make it work."

In the attached are two buttons in column U next to the tables. Clicking them will fill the adjacent table. The two macros called are very similar, and currently give exactly the same results, so to deal with taxes/no taxes, the formula of (one of) the cells C48/C46/C29 will need changing.
The macros use your original idea of looping through the GoalSeek with different values because my idea of devising a formula did prove too complex (at least for me).
One of the macros:

Sub blah1()
For Each cll In Range("P3:T3")
  For Each celle In Range("O4:O8")
    Range("L2").Value = celle.Value
    Range("C36").Value = cll.Value
    Range("C48").GoalSeek Goal:=0, ChangingCell:=Range("K35")
    Cells(celle.Row, cll.Column).Value = Range("K35")
  Next celle
Next cll
End Sub

 

[DAGUET]

re:"This in my view requires a bit of coding (loop?) to make it work."

In the attached are two buttons in column U next to the tables. Clicking them will fill the adjacent table. The two macros called are very similar, and currently give exactly the same results, so to deal with taxes/no taxes, the formula of (one of) the cells C48/C46/C29 will need changing.
The macros use your original idea of looping through the GoalSeek with different values because my idea of devising a formula did prove too complex (at least for me).
One of the macros:

Sub blah1()
For Each cll In Range("P3:T3")
  For Each celle In Range("O4:O8")
    Range("L2").Value = celle.Value
    Range("C36").Value = cll.Value
    Range("C48").GoalSeek Goal:=0, ChangingCell:=Range("K35")
    Cells(celle.Row, cll.Column).Value = Range("K35")
  Next celle
Next cll
End Sub

To p45cal : just ran into it, WORKS!!! exactly what I was looking for. Bravo!! I am extremely happy and I understood the loop through which you went; many many thanks!! you are an top notch expert!!!

To ALL - You contributed equally to that . My Warm Thanks!!! from Paris, France