Excel Solver: Hva er det og når du bør bruke det

Excel-regnearkverktøy som Goal Seek som endrer en enkelt variabel er nyttige, men dessverre er de fleste problemene i virksomheten ikke så enkle. Du vil vanligvis møte formler med minst to og noen ganger dusinvis av variabler. Ofte vil et problem ha mer enn én løsning, og utfordringen din vil være å bruke Excel for å finne den optimale løsningen (det vil si den som maksimerer fortjenesten, eller minimerer kostnadene, eller samsvarer med andre kriterier). For disse større utfordringene trenger du et mer muskuløst verktøy. Excel har bare svaret: Løser. Excel Solver er et sofistikert optimaliseringsprogram som lar deg finne løsningene på komplekse problemer som ellers ville kreve matematisk analyse på høyt nivå.

Forstå Excel Solver

Solver, som Goal Seek , bruker en iterativ metode for å utføre sine beregninger. Å bruke iterasjon betyr at Excel Solver prøver en løsning, analyserer resultatene, prøver en annen løsning, og så videre. Denne sykliske iterasjonen er imidlertid ikke bare gjetting fra Solvers side. Det ville vært dumt. Nei, Excel Solver undersøker hvordan resultatene endrer seg med hver ny iterasjon og kan, gjennom noen sofistikerte matematiske prosesser (som heldigvis skjer langt i bakgrunnen og kan ignoreres), vanligvis fortelle i hvilken retning den bør gå mot løsningen.

Fordelene med Excel Solver

Ja, Goal Seek og Solver er begge iterative, men det gjør dem ikke like. Faktisk bringer Excel Solver en rekke fordeler til bordet:

• Excel Solver lar deg spesifisere flere justerbare celler. Du kan bruke opptil 200 justerbare celler i alt.
• Excel Solver lar deg sette opp begrensninger på de justerbare cellene. Du kan for eksempel fortelle Solver å finne en løsning som ikke bare maksimerer fortjenesten, men som også tilfredsstiller visse betingelser, for eksempel å oppnå en bruttomargin mellom 20 og 30 prosent, eller holde utgiftene mindre enn $100 000. Disse forholdene sies å være begrensninger for løsningen.
• Excel Solver søker ikke bare et ønsket resultat ("målet" i Goal Seek), men også det optimale. For eksempel kan det å lete etter et optimalt resultat bety at du kan finne en løsning som er maksimalt eller minimum mulig.
• For komplekse problemer kan Solver generere flere løsninger . Du kan deretter lagre disse forskjellige løsningene under forskjellige scenarier.

Når bør du bruke Excel Solver?

Ok, la oss skyte rett her: Excel Solver er et kraftig verktøy som de fleste Excel-brukere ikke trenger. Det ville være overkill, for eksempel å bruke Solver til å beregne nettoresultat gitt faste inntekter og kostnadstall. Mange problemer krever imidlertid intet mindre enn Solver-tilnærmingen. Disse problemene dekker mange forskjellige felt og situasjoner, men de har alle følgende egenskaper til felles:

• De har én enkelt objektivcelle (også kalt målcellen ) som inneholder en formel du vil maksimere, minimere eller sette til en bestemt verdi. Denne formelen kan være en beregning som totale transportkostnader eller netto fortjeneste.
• Objektcelleformelen inneholder referanser til én eller flere variable celler (også kalt ukjente eller skiftende celler ). Solver justerer disse cellene for å finne den optimale løsningen for målcelleformelen. Disse variable cellene kan inkludere elementer som solgte enheter, fraktkostnader eller annonseringsutgifter.
• Eventuelt er det en eller flere begrensningsceller som må tilfredsstille visse kriterier. Du kan for eksempel kreve at annonseringen er mindre enn 10 prosent av de totale utgiftene, eller at rabatten til kunder er et beløp mellom 40 og 60 prosent.

For eksempel viser bildet nedenfor en regnearkdatamodell som er satt opp for Excel Solver. Modellen viser inntekter (pris ganger solgte enheter) og kostnader for to produkter, fortjeneste produsert av hvert produkt, og total fortjeneste. Spørsmålet som skal besvares her er dette: Hvor mange enheter av hvert produkt må selges for å få en total fortjeneste på $0? Dette er kjent i næringslivet som en break-even-analyse.

Målet for denne datamodellen er å finne break-even-punktet (der total fortjeneste er $0).

Det høres ut som en enkel Goal Seek-oppgave, men denne modellen har et vanskelig aspekt: ​​de variable kostnadene. Normalt er de variable kostnadene for et produkt dets enhetskostnad ganger antall solgte enheter. Hvis det koster $10 å produsere produkt A, og du selger 10.000 enheter, er de variable kostnadene for det produktet $100.000. Men i den virkelige verden er slike kostnader ofte blandet opp mellom flere produkter. Hvis du for eksempel kjører en felles reklamekampanje for to produkter, bæres disse kostnadene av begge produktene. Derfor forutsetter denne modellen at kostnadene til det ene produktet er relatert til de solgte enhetene til det andre. Her er for eksempel formelen som brukes til å beregne kostnadene for den oppblåsbare dartskiven (celle B8):

=B7 * B4 – C4

Med andre ord, de variable kostnadene for den oppblåsbare dartskiven reduseres med én dollar for hver enhet som selges av hundepoleringsmaskinen. Sistnevntes variable kostnader bruker en lignende formel (i celle C8):

=C7 * C4 – B4

Å ha de variable kostnadene knyttet til flere produkter setter denne datamodellen utenfor det Goal Seek kan gjøre, men Solver er opp til utfordringen. Her er spesialcellene i modellen som Solver vil bruke:

• Målcellen er C14; den totale fortjenesten og målløsningen for denne formelen er 0 (det vil si break-even-punktet).
• De skiftende cellene er B4 og C4, som inneholder antall solgte enheter for hvert produkt.
• For begrensninger kan det være lurt å legge til at begge produktfortjenestecellene (B12 og C12) også skal være 0.

Laster inn Excel Solver-tillegget

Et tillegg er programvare som legger til en eller flere funksjoner i Excel. Ved å installere tillegg får du ekstra Excel-funksjoner som ikke er tilgjengelige på båndet som standard. Medfølgende tilleggsprogramvare følger med Excel, men installeres ikke automatisk når du installerer Excel . Flere tillegg kommer som standard med Excel, inkludert Solver, som lar deg løse optimaliseringsproblemer.

Du installerer de medfølgende tilleggene ved å bruke dialogboksen for Excel-alternativer; du finner dem i tilleggsdelen. Etter at de er installert, er tillegg tilgjengelige umiddelbart. De vises vanligvis på en fane relatert til funksjonen deres. For eksempel vises Solver på Data-fanen.

Her er trinnene du må følge for å laste Excel Solver-tillegget:

Velg Fil→ Alternativer.
Dialogboksen for Excel-alternativer vises.

Velg Tillegg.

I Administrer-listen velger du Excel-tillegg og velger deretter Gå.
Excel viser dialogboksen Tillegg.

Merk av for Solver Add-In

Klikk OK.
Excel legger til en Løser-knapp i Data-fanens Analyser-gruppe.

Ta en titt for å finne ut hvordan du bruker Excel Solver .