Reverse engineering :)

[Ammammata]

un cliente ha preparato un "semplice" foglio che calcola il prezzo
finale di un prodotto, partendo da un valore base e aggiungendo riga
per riga costi e quisquilie varie

si parte da 220 (prezzo della fabbrica) e si arriva in fondo a 564
(prezzo per il cliente finale)

ora si stabilisce che il prezzo finale deve essere p.e. 549.95 per cui
si deve rifare il calcolo al contrario per arrivare al prezzo da
chiedere alla fabbrica, fermo restando che i vari valori aggiunti
(percentuali oppure costi fissi divisi per la caricabilità del
container oppure fattori di conversione) non devono cambiare

c'è qualche barbatrucco per avere le formule al contrario?
ho cominciato a farlo, ma ci sono punti dove finisco in un loop da cui
non ne esco

faccio un esempio:

ho un valore A
calcolo un valore B che non dipende da A
calcolo un valore C che dipende da A e da un altro parametro

sommo A+B+C e ottengo D, per cui A = D-C-B

ora, supponendo di cambiare D con DX devo risalire al valore AX

quindi AX = DX -C-B ... ma C dipende da A(X), che ancora non conosco in
quanto lo sto calcolando proprio adesso, servirebbe una funzione che
itera per avvicinarsi al valore giusto, oppure qualcosa di molto più
semplice che non mi viene in mente :(


spero di aver anonimizzato correttamente ;)
https://we.tl/t-k6MunukxcB

--
/-\ /\/\ /\/\ /-\ /\/\ /\/\ /-\ T /-\
-=- -=- -=- -=- -=- -=- -=- -=- - -=-
........... [ al lavoro ] ...........

[casanmaner]

Premesso che hai alcuni dati che dovrebbero rappresentare una
"percentuale" dove i luogo della virgola è presente il punto, il che
porta ad un errore di valore nelle formule", per trovare il valore del
prezzo di produzione in modo che il prezzo finale assuma il valore da te
desiderato.
Ho fatto una prova è il prezzo finale di 213,987329414284 porta ad un
prezzo finale di 549,87142857.
A quanto pare il prezzo 549,95, in base alle formule che portano al
prezzo finale, non è raggiungibile perché o si va un po' sopra o un po'
sotto.

[casanmaner]

Mi sono accorto che manca una parte :-)
Volevo dire che dovresti provare la funzione, presente in Dati - >
Analisi di simulazione - Ricerca obiettivo ...

[Ammammata]

casanmaner formulated the question :

> A quanto pare il prezzo 549,95, in base alle formule che portano al prezzo
> finale, non è raggiungibile perché o si va un po' sopra o un po' sotto

sì, ma i centesimi di differenza non sono un problema in quanto poi si
mette alla fine un piccolo arrotondamento sul listino; la cosa
importante è tornare al prezzo in alto in modo che rifacendo il calcolo
verso il basso si arrivi quanto più possibile vicino a quello chiesto
dal cliente finale

per quanto riguarda i punti e le virgole concordo con te, quando mi ha
inviato il file ho dovuto correggere almeno 3 celle

[casanmaner]

Il 06/02/2024 14:51, Ammammata ha scritto:
> casanmaner formulated the question :
>> A quanto pare il prezzo 549,95, in base alle formule che portano al
>> prezzo finale, non è raggiungibile perché o si va un po' sopra o un
>> po' sotto
>
> sì, ma i centesimi di differenza non sono un problema in quanto poi si
> mette alla fine un piccolo arrotondamento sul listino; la cosa
> importante è tornare al prezzo in alto in modo che rifacendo il calcolo
> verso il basso si arrivi quanto più possibile vicino a quello chiesto
> dal cliente finale
>
> per quanto riguarda i punti e le virgole concordo con te, quando mi ha
> inviato il file ho dovuto correggere almeno 3 celle
>

Ok, allora vai di Ricerca obiettivo :-)
Volendo puoi anche registrare una macro e poi aggiustare il codice in
modo che inserendo il prezzo desiderato in una cella venga calcolato
quello iniziale in base al valore assunto dalla formula finale di quello
schema.

[Ammammata]

casanmaner presented the following explanation :

> Volendo puoi anche registrare una macro e poi aggiustare il codice in modo
> che inserendo il prezzo desiderato in una cella venga calcolato quello
> iniziale in base al valore assunto dalla formula finale di quello schema.

cioè, in pratica, prendo tutte le formule "a scendere" e preparo un
pistolotto lungo un kilometro che le mette tutte insieme e da lì parto
con la ricerca obiettivo... faccio un tentativo

[Ammammata]

Ammammata wrote:
> preparo un pistolotto lungo un kilometro

E44 =
(((CEILING.MATH(SUM(E28;0;(SUM(CEILING.MATH(SUM(SUM((E11*$C$6/$C$15));$C$19/100*E17+$C$20;((SUM((E11*$C$6/$C$15))+$C$19/100*E17+$C$20)/$C$21)-(SUM((E11*$C$6/$C$15))+$C$19/100*E17+$C$20));0.1);0;($C$24/E10);0;0;
(SUM((E11*$C$6/$C$15))*E5))*C30)
;((SUM(CEILING.MATH(SUM(SUM((E11*$C$6/$C$15));$C$19/100*E17+$C$20;((SUM((E11*$C$6/$C$15))+$C$19/100*E17+$C$20)/$C$21)-(SUM((E11*$C$6/$C$15))+$C$19/100*E17+$C$20));0.1);0;($C$24/E10);0;0;
(SUM((E11*$C$6/$C$15))*E5))+(SUM(CEILING.MATH(SUM(SUM((E11*$C$6/$C$15));$C$19/100*E17+$C$20;((SUM((E11*$C$6/$C$15))+$C$19/100*E17+$C$20)/$C$21)-(SUM((E11*$C$6/$C$15))+$C$19/100*E17+$C$20));0.1);0;($C$24/E10);0;0;
(SUM((E11*$C$6/$C$15))*E5))*C30))*C31)
;((SUM(CEILING.MATH(SUM(SUM((E11*$C$6/$C$15));$C$19/100*E17+$C$20;((SUM((E11*$C$6/$C$15))+$C$19/100*E17+$C$20)/$C$21)-(SUM((E11*$C$6/$C$15))+$C$19/100*E17+$C$20));0.1);0;($C$24/E10);0;0;
(SUM((E11*$C$6/$C$15))*E5))+(SUM(CEILING.MATH(SUM(SUM((E11*$C$6/$C$15));$C$19/100*E17+$C$20;((SUM((E11*$C$6/$C$15))+$C$19/100*E17+$C$20)/$C$21)-(SUM((E11*$C$6/$C$15))+$C$19/100*E17+$C$20));0.1);0;($C$24/E10);0;0;
(SUM((E11*$C$6/$C$15))*E5))*C30)+((SUM(CEILING.MATH(SUM(SUM((E11*$C$6/$C$15));$C$19/100*E17+$C$20;((SUM((E11*$C$6/$C$15))+$C$19/100*E17+$C$20)/$C$21)-(SUM((E11*$C$6/$C$15))+$C$19/100*E17+$C$20));0.1);0;($C$24/E10);0;0;
(SUM((E11*$C$6/$C$15))*E5))+(SUM(CEILING.MATH(SUM(SUM((E11*$C$6/$C$15));$C$19/100*E17+$C$20;((SUM((E11*$C$6/$C$15))+$C$19/100*E17+$C$20)/$C$21)-(SUM((E11*$C$6/$C$15))+$C$19/100*E17+$C$20));0.1);0;($C$24/E10);0;0;
(SUM((E11*$C$6/$C$15))*E5))*C30))*C31))*$C$32);0;0;($C$35/E8));0.1)/C42)/C43)*C44)+((CEILING.MATH(SUM(E28;0;(SUM(CEILING.MATH(SUM(SUM((E11*$C$6/$C$15));$C$19/100*E17+$C$20;((SUM((E11*$C$6/$C$15))+$C$19/100*E17+$C$20)/$C$21)-(SUM((E11*$C$6/$C$15))+$C$19/100*E17+$C$20));0.1);0;($C$24/E10);0;0;
(SUM((E11*$C$6/$C$15))*E5))*C30)
;((SUM(CEILING.MATH(SUM(SUM((E11*$C$6/$C$15));$C$19/100*E17+$C$20;((SUM((E11*$C$6/$C$15))+$C$19/100*E17+$C$20)/$C$21)-(SUM((E11*$C$6/$C$15))+$C$19/100*E17+$C$20));0.1);0;($C$24/E10);0;0;
(SUM((E11*$C$6/$C$15))*E5))+(SUM(CEILING.MATH(SUM(SUM((E11*$C$6/$C$15));$C$19/100*E17+$C$20;((SUM((E11*$C$6/$C$15))+$C$19/100*E17+$C$20)/$C$21)-(SUM((E11*$C$6/$C$15))+$C$19/100*E17+$C$20));0.1);0;($C$24/E10);0;0;
(SUM((E11*$C$6/$C$15))*E5))*C30))*C31)
;((SUM(CEILING.MATH(SUM(SUM((E11*$C$6/$C$15));$C$19/100*E17+$C$20;((SUM((E11*$C$6/$C$15))+$C$19/100*E17+$C$20)/$C$21)-(SUM((E11*$C$6/$C$15))+$C$19/100*E17+$C$20));0.1);0;($C$24/E10);0;0;
(SUM((E11*$C$6/$C$15))*E5))+(SUM(CEILING.MATH(SUM(SUM((E11*$C$6/$C$15));$C$19/100*E17+$C$20;((SUM((E11*$C$6/$C$15))+$C$19/100*E17+$C$20)/$C$21)-(SUM((E11*$C$6/$C$15))+$C$19/100*E17+$C$20));0.1);0;($C$24/E10);0;0;
(SUM((E11*$C$6/$C$15))*E5))*C30)+((SUM(CEILING.MATH(SUM(SUM((E11*$C$6/$C$15));$C$19/100*E17+$C$20;((SUM((E11*$C$6/$C$15))+$C$19/100*E17+$C$20)/$C$21)-(SUM((E11*$C$6/$C$15))+$C$19/100*E17+$C$20));0.1);0;($C$24/E10);0;0;
(SUM((E11*$C$6/$C$15))*E5))+(SUM(CEILING.MATH(SUM(SUM((E11*$C$6/$C$15));$C$19/100*E17+$C$20;((SUM((E11*$C$6/$C$15))+$C$19/100*E17+$C$20)/$C$21)-(SUM((E11*$C$6/$C$15))+$C$19/100*E17+$C$20));0.1);0;($C$24/E10);0;0;
(SUM((E11*$C$6/$C$15))*E5))*C30))*C31))*$C$32);0;0;($C$35/E8));0.1)/C42)/C43)

il bello è che funziona pure... ma lo strumento di analisi "Goal seek"
non ce la fa

[casanmaner]

Non c'è bisogno di creare tutta la formula che ripercorre tutti i
passaggi precedenti.
I conteggi sono già sviluppati.
Quello che devi fare è un semplice "Ricerca obiettivo" dove imposti il
risultato ottenuto dall'ultima formula, sulla base di un determinato
valore da te scelto, andando a modificare la prima cella da cui partono
i calcoli.

Vedi il tuo precedente file:
https://www.dropbox.com/scl/fi/knctz4peytl6qrkfx9pw9/esempio.xlsm?rlkey=033mfcl6l9dht47nzzngod9qy&dl=0

[Ammammata]

casanmaner expressed precisely :

> Quello che devi fare è un semplice "Ricerca obiettivo" dove imposti il
> risultato ottenuto dall'ultima formula, sulla base di un determinato valore
> da te scelto, andando a modificare la prima cella da cui partono i calcoli.

lo ho fatto, e ha fallito; ho provato con un valore un po' più basso,
con uno più alto, con un valore fuori dal mondo: in tutti i casi ha
pensato qualche secondo mostrando un sacco di variazioni e si è arreso
tutte le volte, proponendo valori finali a 14 cifre, anche negativi

per questo ho provato a fare il formulone, ma sapevo già che non
avrebbe cambiato la cosa :(

non fa nulla, son già d'accordo che venerdì vedremo insieme il
programma (fatto con Access) che fa le stesse cose senza problemi di
retro-analisi

[casanmaner]

Il 06/02/2024 17:29, Ammammata ha scritto:
> casanmaner expressed precisely :
>> Quello che devi fare è un semplice "Ricerca obiettivo" dove imposti il
>> risultato ottenuto dall'ultima formula, sulla base di un determinato
>> valore da te scelto, andando a modificare la prima cella da cui
>> partono i calcoli.
>
> lo ho fatto, e ha fallito; ho provato con un valore un po' più basso,
> con uno più alto, con un valore fuori dal mondo: in tutti i casi ha
> pensato qualche secondo mostrando un sacco di variazioni e si è arreso
> tutte le volte, proponendo valori finali a 14 cifre, anche negativi
>
> per questo ho provato a fare il formulone, ma sapevo già che non avrebbe
> cambiato la cosa :(
>
> non fa nulla, son già d'accordo che venerdì vedremo insieme il programma
> (fatto con Access) che fa le stesse cose senza problemi di retro-analisi
>

Sul file che ho linkato, e che era il tuo con le modifiche delle virgole
al posto dei punti, il valore lo ha trovato, anche se per approssimazione.

Non è che c'è qualche problema di formula?

ciao

[casanmaner]

Se vuoi un consiglio.
Arrotonda all'unità la formula della cella E44.
E chiedi di trovare prezzi al consumatore finale all'unità di euro.
Così dovrebbe riusicre a trovare i risultati senza fare troppe
iterazioni (anche in negativo).

[Ammammata]

casanmaner laid this down on his screen :

> Se vuoi un consiglio.
> Arrotonda all'unità la formula della cella E44.
> E chiedi di trovare prezzi al consumatore finale all'unità di euro.

grande!

arrotondato all'euro funziona

arrotondato a 0.1 fallisce

arrotondato a 0.5 funziona

non ho fatto altre prove, per adesso passo la palla così come è :)