Use variable value not yet declared. Circular Reference in c#

Question

I know the question seems absurd but a client of mine gave me an excel spreadsheet that has a variable that is used before having its calculation defined. In excel this calls Circular Reference.

I need to do it in my code.

Follow the part where I need it:

decimal valorFaturamentoPrevistoObra = ((subtotalGrupo1 + subtotalGrupo2) + valorDespesasEspecificas) * (1 + bdi);

decimal taxaRateioAdmCentral = (valorAdmCentralPeriodo * (valorFaturamentoPrevistoObra / (valorFaturamentoPeriodo + valorFaturamentoPrevistoObra)) / (subtotalGrupo1 + subtotalGrupo2)) * 100;

Taxa taxa = new Taxa();
var listaTaxas = taxa.ListaTaxaOrcamento(orcamento_id);

bdi = CalculaBDI(orcamento_id, listaTaxas, valorDespesasEspecificas, taxaRateioAdmCentral);

Note that I need the variable "bdi" in the first line but that its calculation is only done further down. And that to calculate it I need the value of the first line.

Does anyone have any idea how to solve this? It could be any solution.

Circular reference no c#

Answer 1

This is a typical linear programming problem and there are several algorithms to solve it. You could use, for example, a Simplex maximization algorithm.

Many people prefer to use Excel to solve this type of problem because it already comes with tools such as Hypothesis Test and Solver, implementing some linear programming algorithms.

In particular, I find the implementation of these algorithms complex. After my Operational Research classes (and that was a long time ago!), I never needed to implement one of them. Of the few times I came across a problem like this, I used Excel even.

Solution 1

For a robust and efficient solution, I suggest you implement in your system a linear programming algorithm. You find an implementation of the Simplex maximization method in Simplex maximization Algorithm in C#.

Solution 2

For a much simpler solution, you can set up a loop of hypothesis tests until you find the most suitable value.

Whereas you want to find the value of dbi resulting in the highest possible value for valorFaturamentoPrevistoObra, the algorithm below loops to test assumptions of the billing value until you find the maximized value (the detailed explanation is in the comments).

Taxa taxa = new Taxa();
var listaTaxas = taxa.ListaTaxaOrcamento(orcamento_id);

//Crie uma varíavel para determinar o menor valor possível do faturamento
decimal faturamentoMinimo = (subtotalGrupo1 + subtotalGrupo2 + valorDespesasEspecificas);

//Crie uma variável para testar hipóteses de valor do faturamento
//Como se quer um valor maximizado, inicie o valor da hipoótese com um valor bem alto
//Estou sugerindo aqui que seja 3 vezes o faturamento mínimo mas pode ser maior ou menor
//Lembrando que, quanto maior, mais lenta será a execução
decimal hipoteseFaturamento = 3 * faturamentoMinimo;

//Faça um loop para testar o valor da hipótese até o limite mínmo
while (hipoteseFaturamento > faturamentoMinimo)
{
    //calcule a taxa de rateio com base no valor da hipótese
    taxaRateioAdmCentral = (valorAdmCentralPeriodo * (hipoteseFaturamento / (valorFaturamentoPeriodo + hipoteseFaturamento)) / (subtotalGrupo1 + subtotalGrupo2)) * 100;            

    //Calcule o bdi
    bdi = CalculaBDI(orcamento_id, listaTaxas, valorDespesasEspecificas, taxaRateioAdmCentral);

    //Calcule o faturamento previsto                
    valorFaturamentoPrevistoObra = ((subtotalGrupo1 + subtotalGrupo2) + valorDespesasEspecificas) * (1 + bdi);

    //Verifique se o faturamento previsto é igual à hipótese, 
    //se for igual, você encontrou o bdi desejado
    //se não for, diminua o valor da hipótese e calcule novamente               
    if (hipoteseFaturamento == valorFaturamentoPrevistoObra)
        break;
    else
        hipoteseFaturamento -= 1;                       
}