Calculul încrucișată a costurilor

În dezvoltarea unor modele economice una dintre cele mai interesante probleme este de a rezolva problemele de legături încrucișate între formule. Astfel de relații nu permit utilizarea de tip standard de calcul Excel Services - algoritm de calcul direct ofera un mesaj despre o referință circulară între formulele. În practică, astfel de probleme nu vstechayutsya foarte des. De aceea provoca unele confuzii chiar și în rândul utilizatorilor experimentați ca algoritm bine format nu se poate realiza modul obișnuit în foile de calcul. Luați în considerare opțiunile pentru a aborda acest tip de problemă pe exemplul modelului economic de calcul al costului magazin.

de exemplu, problema

Într-o unitate de producție mare are două unități de producție auxiliare:

Dificultatea constă în faptul că ei se consumă plante de putere reciproc - clădire de putere este încălzită și teplotseh consumă energie electrică (de exemplu, iluminat). Într-o situație reală, trebuie să luăm în considerare faptul că o parte din energia de sine a fiecărei plante este utilizat pentru consumul intern. Acest moment în problema este ignorată, deoarece, în esență, nu afectează soluțiile algoritmului.

Deci, se dovedește că clădirea de putere pentru calcularea costului de kW / h ar trebui să ia în considerare prețul căldurii consumate și teplotseh, la rândul său, pentru a calcula valoarea MJ trebuie să știe prețul kW / h. Un caz tipic de „eco-referință“. Sarcina, destul de ciudat, este adesea găsit în practică, este în această formulare. Este chiar situație mai dificilă în cazul în care magazinele auxiliare cu costuri mai multe eco: instalații frigorifice, alimentare cu gaze și apă, etc. Uneori consumate reciproc produse semi-finite fabricate într-un alt magazin. Pe cele mai multe întreprinderi industriale mari, în special cu rădăcinile trecutului sovietic, încă mai ia în considerare costul magazin, și vin în la un moment dat în impas algoritmică descris.

O formă simplificată a problemei este reprezentată în fișierul mostră la articol:

Formula din celula B9 (= B8 + B7 * E10) stabilește că costul final al clădirii puterii constă din bugetul costurilor interne (în exemplul reprezentat printr-un singur număr) și valoarea consumului de energie termică (volum înmulțit cu prețul). Celula B10 calculeaza costul kW / h de energie electrică (= B9 / B6). Dacă încercați să copiați formula de calcul a costului unitar al energiei electrice în formula de calcul de unități de căldură (B10 la E10), este logic pentru a obține un mesaj despre o referință circulară:

Ce soluții sunt disponibile și sunt folosite în practică?

Transformarea modelului matematic

Opțiunea Cardinal - un algoritm de calcul de revizuire, astfel încât să se elimine referințe încrucișate între formulele.

În problema noastră, astfel încât soluția este să renunțe la costurile artizanale (de porc), precum și utilizarea de calculație a costurilor directe. și anume calcularea costurilor la nivelul numai costurile directe. Această metodă de soluție, poate, cea mai corectă și adecvată pentru majoritatea întreprinderilor. Dar noi nu va locui pe ea, ca scopul articolului arată în continuare capacitățile de Excel, nu pentru a aranja o discuție teoretică.

atribuit costul

Pentru a rezolva problema poate fi o directivă pentru a stabili (aloca) valoarea de cost pentru una sau ambele tipuri de cheltuieli. De exemplu, costul energiei electrice este setat la 2 RUB / kWh. Acest lucru, în principiu, suficient pentru a rezolva problemele noastre - căldura deja poate fi calculată fără trimiteri încrucișate. Dar, evident, există o altă problemă. De ce i se atribuie la costul de două, mai degrabă decât de trei ruble? În plus, calculul final al costurilor ar trebui să fie introdusă o sumă de corecție, care nu este clar la care atribut centru de cost. Există riscul ca pur și simplu, ar fi luate în considerare costul total greșit. Aceasta este, costul produselor fabricate nu coincid cu costurile întreprinderii.

Ca o variantă a metodei anterioare, în practică, de multe ori folosit ca o perioadă scursă de cost efectiv alocate (luni). Acest exemplu de realizare este mai rezistent la abateri mari în rezultatele finale, dar nu rezolvă complet problema. Mai degrabă, este o încercare de a reduce la minimum riscul de eroare, iar în acest exemplu de realizare este de obicei omis ajustări de calcul totaluri din costul total.

selectarea valorilor

Este mai bine să încerce să ridica valoarea costurilor cu un grad maxim de precizie. Aceasta este, astfel încât costul total al bunurilor fabricate a coincis cu costul total al instalației. Această opțiune poate fi, de asemenea, luate în considerare de valoarea atribuită, dar această eroare este eliminat calculele finale. Este evident că pentru a sorta valorile manual este destul de dificil. În practică, puteți utiliza interfața Excel înseamnă „Seek“:

Soluție găsită. Dar, rețineți că „Seek“, referindu-se la celula de control al unui model matematic, atingere cu degetul numărul, atâta timp cât nu a fost zero. În practică, nu s-ar putea fi similar cu punctul de referință, deoarece este legată de calcularea unei varietăți de date externe. Un alt dezavantaj al acestei metode - poate fi utilizat pentru a procesa doar o singură celulă (număr unic de selecție). Dar uitați despre această opțiune nu este în valoare - în unele cazuri chiar mai convenabil să ia în considerare opțiunile de mai jos calcule iterative.

Calculul iterativ

Pentru a activa calcule iterative includ parametrul de tip titlu Excel. Apoi, puteți copia formula din celula B10 la E10. Ca rezultat, vom obține rezultatul corect și absența unui mesaj de referință circulară:

Parametrii de calcul iterativ (numărul maxim de iterații și eroarea relativă) poate fi schimbată dacă doriți mai multă acuratețe de calcule.

concluzie

calcule iterative - este un built-in Excel mecanism puternic pentru a rezolva problemele specifice de legături încrucișate în formulele. Dar folosind această metodă ar trebui să fie cu mare precauție și numai dacă crezi cu adevărat în algoritmul compilat adevărat pentru rezolvarea problemei. În cele mai multe cazuri, Excel un mesaj despre punctele de referință circulară la alinierea incorectă a modelului matematic, mai degrabă decât necesitatea unor calcule iterative.

Pentru a fi siguri de corectitudinea calculelor iterative, se recomandă pentru a crea puncte de control speciale (celule de serviciu cu formule). În exemplul problemei ca acest control este folosit pentru a compara suma centrelor de responsabilitate și costul costul final al consumului de resurse.

De asemenea, să fie conștienți de faptul că calculul iterativ încetinește viteza de ansamblu a calculului Excel. Se recomandă după primirea rezultatelor pur și simplu inserați formula în locul valorilor în una sau mai multe celule cu o trimitere, economisind undeva o copie a formulei de a repeta calculul cu datele sursă modificat.

Criza a genului există întotdeauna întotdeauna și la toate :)

Un „Seek“ este un frate mai mic Solver'a?

Solver (Solver) - este încă în afara suprastructurii, și Goal Seek (Seek) - built-in. De ce încă nu includ Solver în versiunea de bază, să fiu sincer, nu este clar. De altfel, eu nu sunt puternic în acest instrument. Ceva a încercat pentru o lungă perioadă de timp - nu-i place.
Dacă doriți să scrie ceva interesant despre Solver - fericit să publice, și că, în timp ce o mică criză a genului)

Vă mulțumim pentru articol interesant.
El a menționat pentru ei înșiși.