Multiple Linear Regression ja kunnossapitokustannusten ennustaminen
Ennustetaan ajoneuvojen kunnossapitokustannuksia Excelin monimuuttujaregression (Multiple Linear Regression - MLR) avulla
Ajoneuvojen kunnossapito on merkittävä kustannustekijä kalustoa omistaville yrityksille. Näiden kulujen ennustaminen voi auttaa optimoimaan budjetteja sekä suunnittelemaan ajoneuvojen korvausajankohtaa. Yksi työkalu tällaiseen analyysiin on monimuuttujaregressio (MLR) Microsoft Excelissä. Tässä blogikirjoituksessa näytän, miten voit käyttää Exceliä ennustamaan kalustosi kunnossapitokustannuksia tosielämän tekijöiden, kuten ajoneuvon iän, ajokilometrien, vuotuisen käytön ja aiempien kunnossapitokustannusten perusteella.
Miksi käyttää monimuuttujaregressiota ajoneuvojen kunnossapitokustannusten ennustamiseen?
Mikäli toimit kalustopäällikkönä, tai vastaavassa roolissa ja tehtävänäsi on vastata kalustonne kustannustehokkuudesta, on sinulle hyödyllistä ymmärtää kuinka kalustosi kunnossapitokustannukset kehittyvät ajan myötä. Kuten jokainen arvata saattaa, kunnosaspitokustannuksiin vaikuttaa monen monta tekijää, kuten ikä, ajettavat ja ajetut kilometrit, työtehtävä, ajoneuvon tyyppi, kuljettaja... Näitähän riittää.
Kaikkea on hankala ottaa huomioon kun kustannuksia ennustetaan, eikä tämä excel-työkalu siihen kykene, saati sitä yritä. On kuitenkin jo arvokasta itsessään, että saat mahdollisesti ennustettua kalustosi kunnossapitokustannusten kehityksen suunnan - pysyykö samana vai onko nousussa. Tätä tietoa voit käyttää esimerkiksi budjetin tekemiseen, mutta myös korvausinvestointiehdotusten tueksi. Käymme tässä ohjeessa läpi sen, miten voit käyttää MLR:ää ja mitä tietoa siihen tarvitset, jolloin voit itse kokeilla saatko käytössäsi olevalla datalla kenties itsellesi hyödyllistä tietoa. Aikaa tähän ei kulu paljoa, koska excel tekee suurimman osan työstä, joten kokeile ihmeessä!
Vaiheittainen opas monimuuttujaregression luomiseen Excelissä
Vaihe 1: Valmistele tietosi
Kerää aluksi seuraavat tiedot jokaisesta kalustosi ajoneuvosta:
Ajoneuvon ikä (vuosina)
Kumulatiiviset ajokilometrit (ajetut kilometrit yhteensä)
Vuotuinen käyttö (vuosittain ajetut kilometrit, arvio ennustettavalle vuodelle)
Edellisen vuoden (tai useiden) kunnossapitokustannukset
Järjestä tiedot Excel-taulukkoon selkeillä otsikoilla. Esimerkiksi sarakkeet voivat näyttää tältä:
| Ajoneuvo ID | Vuosi | Ikä (vuosina) | Kumulatiiviset kilometrit | Vuotuinen käyttö | Edellisen vuoden kunnossapitokustannukset | Kunnossapitokustannus (kohde) |
|---|---|---|---|---|---|---|
| V1 | 2021 | 5 | 100 000 | 20 000 | 15000 | 2 000 |
| V1 | 2022 | 6 | 120 000 | 20 000 | 20000 | ? |
Sarakkeet ikä, kilometrit jne. Edustavat riippumattomia muuttujia .
Kohdesarake (Kunnossapitokustannus ): Edustaa kunnossapitokustannuksia, joita haluamme ennustaa.
Jotta saat ennusteyhtälön muodostettua, jaa tietosi kahteen taulukkoon: ensimmäisessä on edellisten vuosien data, ja toisessa sen vuoden data mitä haluat ennustaa. Eli ne rivit mistä kunnossapitokustannus-tieto puuttuu. Tällöin sinulla pitäisi olla taulukko, missä on kaikkien ajoneuvojesi kustannukset, kilometrit, mittarilukemat ja iät, mielellään usealta menneeltä vuodelta, sekä toinen taulukko missä sinulla on ne ajoneuvot mille haluat kustannukset ennustaa, niiden iät, mittarilukemat, arvio kilometreistä sekä edellisen vuoden kunnossapitokustannukset.
Vaihe 2: Ota Excelin Analysis ToolPak käyttöön
Jotta voit suorittaa regressioanalyysin, sinun on otettava Analysis ToolPak käyttöön:
Mene kohtaan
Tiedosto > Asetukset > Lisäosat.Hallinta-kohdassa valitse
Excel-lisäosatja napsautaSiirry.Valitse
Analysis ToolPak-valintaruutu ja napsautaOK.
Vaihe 3: Suorita regressioanalyysi
Siirry
Data-välilehdelle ja valitseData-analyysi.Valitse listasta
Regressioja napsautaOK.Input Y Range: Valitse kunnossapitokustannusten sarake (ilman puuttuvia arvoja).
Input X Range: Valitse kaikki ennustemuuttujien sarakkeet (ikä, kilometrit jne.).
Muista valita
Labels-ruutu, jos sisällytit otsikot.Valitse tulosten sijainti, johon haluat regressiotulokset näkyviin. Voi hyvin tulostuttaa ne uudelle välilehdelle.
Vaihe 4: Tulkitse regressiotulokset
Excel antaa sinulle keskeisiä mittareita, kuten:
Kertoimet kullekin ennustemuuttujalle (esim. ajoneuvon ikä, kilometrit jne.). Käytä näitä luodaksesi yhtälö, joka edustaa arvioitua kunnossapitokustannusta.
R-squared-arvo, joka osoittaa, kuinka hyvin muuttujasi selittävät kunnossapitokustannusten vaihtelua. Mitä lähempänä arvo on yhtä, sitä parempi.
P-arvo (p-value): Tämä arvo kertoo, kuinka tilastollisesti merkittävä jokainen muuttuja on ennustemallissa. Yleisesti ottaen, jos p-arvo on alle 0,05, kyseinen muuttuja on merkittävässä roolissa ennustettaessa kunnossapitokustannuksia. Jos p-arvo on korkeampi, muuttuja ei ehkä ole yhtä hyödyllinen ennustamiseen, ja sen voi harkita poistettavaksi mallista.
Huomio muuttujien skaalaus
On tärkeää huomata, että muuttujien erilaiset mittakaavat, kuten ikä (joka vaihtelee esimerkiksi 0 ja 10 vuoden välillä) ja kumulatiiviset ajokilometrit (jotka voivat olla esimerkiksi 20 000 tai enemmän), voivat vaikuttaa mallin toimintaan. Kun muuttujilla on hyvin erilaiset mittakaavat, tämä voi johtaa siihen, että mallin kertoimet eivät ole helposti tulkittavissa tai että suurempiarvoiset muuttujat dominoivat mallia.
Tämän vaikutuksen minimoimiseksi voit harkita muuttujien normalisointia tai standardointia. Yksi tapa on käyttää Excelin Standardize - funktiota (suomeksi Normita).
Vaihe 5: Käytä mallia puuttuvien kustannusten ennustamiseen
| A | B | C | D | E | F | G | H |
|---|---|---|---|---|---|---|---|
| 1 | Ajoneuvo ID | Vuosi | Ikä (vuosina) | Kumulatiiviset kilometrit | Vuotuinen käyttö | Edellisen vuoden kunnossapitokustannukset | Kunnossapitokustannus (kohde) |
| 2 | V1 | 2024 | 7 | 140 000 | 20 000 | 15000 |
Käytetään esimerkkinä yllä olevaa taulukkoa (Taul1), haluamme ennustaa sen kunnossapitokustannukset käyttäen Excelin antamaa tulosta.
Esimerkki tuloksesta (Taul2):
| A | B | C | D | E | F | G | H | I | J |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Kertoimet | Keskivirhe | t Tunnusluvut | P-arvo | Alin 95% | Ylin 95% | Alin 95,0% | Ylin 95,0% | |
| 2 | Leikkauspiste | -5125,362013 | 5487,678457 | -0,933976371 | 0,362034828 | -16611,20503 | 6360,481 | -16611,20503 | 6360,481 |
| 3 | Ikä (vuosina) | 854,2217178 | 1359,377125 | 0,628392005 | 0,537228564 | -1990,987304 | 3699,43074 | -1990,987304 | 3699,43074 |
| 4 | Kumulatiiviset kilometrit | -0,02015974 | 0,043893636 | -0,45928618 | 0,651239977 | -0,112030176 | 0,071710695 | -0,112030176 | 0,071710695 |
| 5 | Vuotuinen käyttö | 0,194610749 | 0,175328521 | 1,109977706 | 0,280859735 | -0,172356062 | 0,561577561 | -0,172356062 | 0,561577561 |
| 6 | Edellisen vuoden kunnossapitokustannukset | 0,739441793 | 0,201103147 | 3,676928009 | 0,001601796 | 0,318528069 | 1,160355516 | 0,318528069 | 1,160355516 |
Tämä voi näyttää sekavalta, mutta itse yhtälön muodostaminen on helppoa: käytetään "Kertoimet"-sarakkeen arvoja, sekä ennustettavan ajoneuvon vastaavia arvoja.
Leikkauspiste kerroin + Ikä (vuosina) kerroin * ikä (vuosina) + Kumulatiiviset kilometrit kerroin * Kumulatiiviset kilometrit + Vuotuinen käyttö kerroin * Vuotuinen käyttö + Edellisen vuoden kunnossapitokustannukset kerroin * Edellisen vuoden kunnossapitokustannukset.
Eli tässä tapauksessa yhtälö olisi
-5125,362013 + 854,2217178 * 7 + -0,02015974 * 140 000 + 0,194610749 * 20 000 + 0,739441793 * 15000
Ja Excel-muodossa =Taul2!C2 + Taul2!C3 * Taul1!D2 + Taul2!C4 * Taul1!E2 + Taul2!C5 * Taul1!F2 Taul2!C6 * Taul1!G2
Käytännön esimerkki: Kaluston kunnossapidon ennustaminen
Kuvittele, että sinulla on historiallisia kunnossapitotietoja ajoneuvolta viimeisen viiden vuoden ajalta ja haluat tietää, mitä budjetoida seuraavalle vuodelle. Käyttämällä luomaasi regressiomallia voit arvioida kunnossapitokustannukset tekijöiden, kuten kasvavien kilometrien ja iän perusteella.
Tämän ennustekyvyn avulla voit harkita, onko kustannustehokkaampaa uusia kalustoa, tai esimerkiksi muuttaa kunnossapito-ohjelmaa ja siten vähentää seisokkiaikoja ja optimoida resurssien käyttöä.
Analyysin hyödyt
Ennakoiva budjetointi: Voit kohdentaa varoja suuremmalla varmuudella ja tarkkuudella.
Optimoitu kaluston hallinta: Tunnista, mitkä ajoneuvot todennäköisesti tarvitsevat merkittävää kunnossapitoa, ja suunnittele sen mukaisesti.
Dataohjattu päätöksenteko: Hyödynnä tiedon voimaa kunnossapitostrategioiden tukena.
Rajoitukset ja huomioitavat seikat
Vaikka monimuuttujaregressio Excelissä on loistava työkalu monille kalustopäälliköille, sillä on myös rajoituksia:
Excel voi olla haastava monimutkaisten aineistojen kanssa. Muuttujien vaikutukset kustannuksiin eivät suinkaan aina ole lineearisia.
Hyvin suurille aineistoille kehittyneemmät työkalut, kuten Python ja sen erinomaiset kirjastot, voivat olla sopivampia.
Yllä käyttämämme esimerkki ajoneuvon kustannuksista oli täysin tuulesta tempaistu, mutta mitä mieltä olet kaavamme p-arvoista? Mikäli oma tuloksesi on vastaava, ovatko luvut mielestäsi luotettavia?
Jos Excelin rajat tulevat vastaan, harkitse laajentamista kehittyneempiin ennustemalleihin koneoppimisympäristöjen avulla, jotka voivat auttaa epälineaaristen suhteiden tunnistamisessa, joissa perinteinen regressio ei välttämättä onnistu. Voit pyytää tässä apuani.
Yhteenveto
Hyödyntämällä monimuuttujaregressiota Excelissä kalustopäälliköt voivat kehittää tarkkoja ennusteita kunnossapitokustannuksista. Tämä yksinkertainen mutta tehokas lähestymistapa auttaa paitsi kustannusten vähentämisessä myös resurssien paremmassa kohdentamisessa, mikä lopulta parantaa kaluston suorituskykyä ja kustannustehokkuutta. Mikä parasta, sinulla on jo Excel käytössäsi (hyvin todennäköisesti) ja pääset melko varmasti pääset myös käsiksi kalustosi kustannus- ja käyttödataan. Voit siis helposti kokeilla, saisitko tästä hyötyä itsellesi, esimerkiksi käyttämällä vuosien 2021 ja 2022 dataa ennustamaan vuoden 2023 kustannuksia, jolloin voit verrata tulostasi näihin oikeasti toteununeisiin lukuihin.
Jos haluat oppia lisää kehittyneestä kunnossapidon ja kalustonkorvauksen suunnittelusta tai tarvitset apua dataohjattujen ratkaisujen toteuttamisessa kalustollesi, ota yhteyttä!