Optimeerimismodelleerimine Excelis

Optimeerimismodelleerimine Excelis

Jaan Übi

jaanbi.blogspot.com

1

Sissejuhatuse pidepunktid

• Aine kolm koostisosa

• Juhtimisteadus: mis ja miks?

• Mudeli näide Excel'is

• Modelleerimise sammud ja praktiline näide

2

Majanduse teatud harud kui rakendusmatemaatika

Matemaatika

Majandus

Excel, Solver, VBA

3

Rahandusmodelleerimise näide – “quandid”

• Google: wiki computational finance

• Google: MSc Quantitative Finance and Financial Engineering at Manchester University

• Ülikooli lehelt: Programme structure

4

Kursuse põhirõhk

• juhtimisteadusel (Management Science)

• operatsioonianalüüsil (Operations Research)

5

Ettevõtte kolmemõõtmeline kuju

6

Majandusest tõusetuvad probleemid

• Tootmisfunktsioon– toodete portfell– selline tootmine, kus osaliselt ka omatoodangut

tarbitakse– mitmeperioodiline mudel ja toodangu laos

hoidmine– tööjõuhulga ümberstruktureerimisega

arvestamine– tootmisrajatiste asukoha modelleerimine

7


• Turundusfunktsioon– reklaamiportfelli mudelid

• Üldjuhtimine– töötajate koormuse mudel– projekti ajagraafiku mudel– järjekorrateooria

8


• Logistika– kaupade transpordi mudel(ladudest kauplustesse)– lühima teekonna mudel (rändkaupmehe

ülesanne)– erinevate asukohtade keskustest katmise mudel

• Finantsjuhtimine– võimalike investeeringute vahel valimise mudel– investeerimisportfelli koostamine mudel

9

Modelleerimise õpe

• Mudel kui abstraktsioon – vaid teatud seose välja toomine

• Mudelit Excelis kirjeldades õpime me edasi andma üht või teist tüüpi seoseid

• Meie ülesanne on aga mitte õppida erinevaid mudeleid pähe, vaid lõpuks omandada oskus kuidas erinevaid seoseloomise tehnikaid kombineerida

10

• Erinevate seoste kombineerimisel tulemuseks keerulisemad mudelid – meie eesmärgiks oleksid reaalsed olukorrad, sellised kus saab öelda “and someone somewhere made a lot of money”

• United Airlines kasutusele võetud DFI süsteemid, mille maksumus ca 10m.$, lisanduv tulu aga 50m.$

• GAP riiete jaemüügi keti tööjõuvajaduse mudelid

• Ravimifirmade testimishulka mimeerivad mudelid

11

• Matemaatika osakaalu vähendamine igal pool, k.a. USAs AACSB mandaadi äravõtmine antud kursuselt – mille tulemusena see ei olnud enam keskses õppekavas

• Viimasel aastakümnel on aga rõhk mudelite päheõppimiselt – ehk teisisõnu valmislahendustelt – modelleerimisele – individuaalsele lähenemisele

• Aine sai tänu sellisele Excelis modelleerimisele ka mandaadi tagasi 12

Kasutavad matemaatilised meetodid

• Lineaarne planeerimine

• Täisarvuline planeerimine

• Boole’i muutujatega planeerimine

• Mittelineaarne (k.a. ruut-) planeerimine

• Võrkplaneerimine

• Dünaamiline planeerimine

13

Keeruka matemaatilise algoritmi näide – Rändkaupmehe ülesanne

• Ülesandeks on teatud arvu linnade optimaalse teepikkusega läbimine

• Alustatakse ühest linnast, kõiki külastatakse üks kord ja lõpuks jõutakse alguspunkti tagasi

• Keerukus vastab ülesande suurenemisel faktoriaalile – kõige kiirem suurenemine

• Näide: VBAs

14

Travelling Salesman Problem

• Näide matemaatikute võistlusest – üks viimaseid on kõigi Rootsi 24978 linna läbimise tee arvutamine, mis ilma spetsiaalsete algoritmideta oleks võtnud astronoomilise aja

• Näide reaalsest kasutusest on puuri trajektoori koostamine. Tarvis on elektroonikaplaadile teha komponentide paigaldamiseks kõik augud, optimaalse trajektooriga.

15

Raskuskese arvutimudelitel

• Excelis seoste loomine

• Solveri lahendamiseks õige seadistamine, vajadusel tundlikkusanalüüsi tegemine

• Võimalik VisualBasicu kasutamine– kui Solverit tuleb korduvalt käivitada, vahepeal

väikesi muutusi tehes– selleks, et valmistada nö. rumalale kasutajale

paari nupuvajutusega tööle minevat programmi, mis talle ka kohe vastused ette söödaks

– mingi algoritmi tervikuna VBAs loomine

16

www.solver.com

• Exceliga kaasas Solver, Internetis SolverTable tundlikkusanalüüsiks ja õpikuga kaasas Decision Tools simulatsioonideks.

• Frontline Systems’i Solver on terviklplatform, mis on paljude erinevate matemaatiliste algoritmide laiendustarkvara ostuvõimalusega (koduleheküljel jaotus Solver Technology), ning töötab nii Exceliga, Matlabiga kui ka vajadusel C++ ja muude programmeerimiskeeltega otse töötades 17

Operatsioonianalüüsi materjalid

• Areng seotud arvutite ilmumisega, teedrajav artikkel 1949, Danzig

• Tööd tehakse palju USAs

• http://www.informs.org/

• Põhilised ajakirjad Operations Research ja Management Science, aga ka Interfaces

18

Õpik

• Albright, S. C. and W. L. WinstonSpreadsheet modeling and applicationsThomson Brooks/Cole, 2005, 672 p.– Kelley ärikool, USAs Top10s

• Õpikust ka raskem, ning VBA variant, antud versiooni kasutab näiteks USAs üle 200 ülikooli

19

Lihtne teenindussüsteemi mudel

• Väikekauplus– Üks kassaaparaat– Kaua järjekorras seisnud inimene ei tule poodi

tagasi– Kui järjekord on "liiga" pikk, ei viitsi inimesed

poodi siseneda

• Omanik tahab kontrollida oma kahtlusi matemaatilise mudeli abil

• Kirjeldavad mudelid ja optimeerimismudelid

20

Kirjeldav mudel

• Järjekorra mudel

• Praeguse olukorra kirjeldus

• Muutujad– Uute klientide sisenemise intensiivsus –

sisendivoog A (arrival rate)– Teenindamise intensiivsus S (service rate)– Keskmine järjekorras seismise aeg W (waiting time)

ASS

AW

21

Kirjeldav mudel

Descriptive queueing model for 7-Eleven

InputsArrival rate (customers per minute) 0.5Service rate (customers per minute) 0.4Maximum customers (before others go elsewhere) 5

OutputsAverage number in system 3.13Average number in queue 2.22Average time (minutes) in system 8.59Average time (minutes) in queue 6.09Percentage of potential arrivals who don't enter 27.1%

22

Kirjeldav mudel




23

Kirjeldav mudel

24

Kirjeldav mudel




25

Kirjeldav mudel

26

Optimeerimismudel

• Tulemust iseloomustav kriteerium

• Kulud ja tulud (costs and benefits)

• Kitsendused

• Võimalikud sammud– Palgata pakkija– Osta efektiivsem kassaaparaat

27

Lahendamisel vajalikud sammud

• Mudeli parameetrite saamiseks on vaja alguses mõõtmisi läbi viia

• Peab saama veenduda selles, et erinevatel perioodidel on parameetrid samade väärtustega

• Mudelit ennast saab kontrollida – kas tuleminumbrid ikka reaalsusele vastab – et selle valemeid siis vajadusel muuta

28

Matemaatilise mudeli eelised

• Aitab probleemi konkretiseeritud ja lühidal kujul formuleerida

• Nõnda formuleeritud probleemide jaoks on olemas lahendusmeetodid

• Probleeme saab firma siseselt “müüa”

29

Modelleerimise protsess

Ärisituatsioon

Mudel Lahend

Otsus

Abstraheerimine

Analüüs

Intrepretatsioon

Intuitsioon

Sümboolne maailm

Reaalne maailm

30

Modelleerimise seitse sammu

• Samm 1 Ülesande püstitus– Määratlege probleemi

• Mida tahetakse saavutada probleemi lahendusega?• Missuguseid organisatsiooni osi tuleb uurida?

– Kui probleem on teile ette antud, siis kas selle tõeline allikas on tuvastatud?

Ülesande püstitus

31


• Samm 2 Andmete kogumine– Tehke kindlaks, missugused parameetrid on osa

probleemi kirjeldusest.– Koguge andmed, et anda nendele parameetritele

kvantitatiivne sisu• Võib olla tülikas• Olemasolevad andmed ja nende vorming ning

talletamise viis• Uued andmed

Ülesande püstitus

Andmete kogumine

32


• Samm 3 Mudeli väljatöötamine– Selles aines tegeleme just selle sammuga– Mudel peab olema täpne– Samas aga lihtne

Ülesande püstitus

Andmete kogumine

Mudeli väljatöötamine

33


• Samm 4 Mudeli testimine (valideerimine)– Kas mudel peegeldab meid huvitava reaalsuse aspekti

vajaliku täpsusega?• Sisestada otsustusparameetrite praegused väärtused• Sisestada otsustusparameetrite teised võimalikud väärtused• Sisestada otsustusparameetrite ekstreemsed väärtused• Kas saadud väljundmuutujate väärtused on realistlikud?

– Intuitsioonile tuginedes võib sattuda eksiteele

Ülesande püstitus

Andmete kogumine


Mudeli testimine

34


• Samm 5 Optimeerimine ja lahendi valik– Analüütik peab valima mudeli võimalikest

lahenditest optimaalse.

Ülesande püstitus

Andmete kogumine


Mudeli testimine

Optimeerimine ja lahendi valik

35


• Samm 6 Mudeli ja selle optimaalse lahendi tutvustamine juhtkonnale– Tähtis on kaasata tshempionist juhi mudeli väljatöötamise

protsessi algusest– Juhid ja teised töötajad ei valda matemaatikat nii hästi kui

analüütik– Intuitiivsus ehk kasutajasõbralikus

Ülesande püstitus

Andmete kogumine


Mudeli testimine


Mudeli / lahendi tutvustamine juhtkonnale

36


• Samm 7 Mudeli rakendamine– Mudeli opereerimine peab olema kasutajatele

selgeks tehtud ja kasutajad peavad mudeli omaks võtma

– Jäädakse organisatsiooniga kontakti– Siiani oli tegu positiivse stsenaariumiga

Ülesande püstitus

Andmete kogumine


Mudeli testimine


Mudeli / lahendi tutvustamine juhtkonnale

Mudeli rakendamine

37

1. Ülesande püstitus

2. Andmete kogumine

3. Mudeli väljatöötamine

4. Mudeli testimine

5. Optimeerimine ja lahendi valik

6. Mudeli ja lahendi tutvustamine juhtkonnale

7. Mudeli rakendamine

38

Tähtaegselt tasumata arvete sissenõudmine GE Capital'is

• Tugineb 1992. aastal avaldatud artiklile, vt. viidet ajakirjale Interfaces

• GE Capital'i krediitkaardi arvete portfell– Üldmaht – $12 miljardit– Tähtaegselt tasumata arved – ca $1 miljard– Kulud sissenõudmisele – ca $100 miljonit– Erinevad meetodid: salvestatud telefonisõnum,

telefonikõne, kiri, ühendust mitte võtta

39


• Samm 1: Probleemi püstitus– Missugust sissenõudmise võtet kasutada

konkreetse kliendi puhul?

• Samm 2: Andmete kogumine– Seosed kasutatud võtte ja arve seisundi vahel

• Samm 3: Mudeli väljatöötamine– Dünaamiline lineaarse planeerimise mudel

• Samm 4: Mudeli testimine– Pilootprojekt – ühe kaubamaja arvete portfell

($62 miljonit) 40


• Samm 5: Optimeerimine ja otsuste langetamine– Mudelil lastakse "joosta" iga kuu lõpus

• Samm 6: Mudeli tutvustamine juhtkonnale– Tööseminarid

• Samm 7: Mudeli rakendamine– Ühe kauplusteketi arvete portfell ($4,5 miljardit)– Sissenõutud arvete maht suurenes $19 miljoni

võrra aastas

41

Mudelite klassifikatsioon

• Muutujate ja nendevaheliste seoste omaduste alusel– Deterministlikud

• Sisendmuutjate väärtused on teada• Seosed muutujate vahel on kindlad

– Tõenäosuslikud (stohhastilised)• Teada on sisendmuutujate väärtuste jaotusi• Seoses muutjate vahel on tõenäosuslikud

42

• Mudeli lahendamise viisi alusel– Analüütilised

• Lahendatav valemi abil (näiteks lineaarse võrrandisüsteemi lahendamine Krameri valemiga)

• Eelistatud variant (juhul kui realiseeritav)

– Algoritmi abil lahendatavad• Lahendatav algoritmi abil (näiteks lineaarse

võrrandisüsteemi lahendamine Gaussi meetodiga)

– Heuristilised mudelid• Näidatud Rändkaupmehe ülesande algoritm

• Eelnevatest põhimõtteliselt teise lahendusviisiga– Simulatsiooni mudelid

• Suure hulga võimalike lahendite genereerimine43

• Mudeli kasutussageduse alusel– Strateegilised

• Kasutatakse harva, igal kasutuskorral on mõju äri tulemustele suur

– Taktikalised• Sagedasem kasutus, igal kasutuskorral on väiksem

mõju äri tulemustele

– Operatsioonilised• Kasutatakse iga päev, tund või pidevalt

44

Kursuse ülesehitus

• Kursus koosneb sellest sissejuhatavast loengust

• Matemaatilist tausta tutvustavast loengust

• Exceli mudelite lahendamise praktikumidest, mis on vaadatavad ja varustatud kirjalike konspektidega, mis nende meeldejätmiseks kasutatavad

Optimeerimismodelleerimine Excelis

Documents

Transcript of Optimeerimismodelleerimine Excelis