Formule: Afschrijvingen / Useful Life

[MauriceSmit]

Allen,

Ik loop even vast op het maken van een formule dat de frequentie van het aantal afschijvingen zou moeten weergeven in tijd.
Klinkt even lastig, maar in het voorbeeld laat ik aan de hand van een aantal voorbeelden mijn probleem zien.

Het volgende is het geval:
In mijn model (voorbeeld is een extract van een groter model!) worden kosten adhv klant eenheden opgebouwd. In mijn simpele voorbeeld pak ik een mailbox als voorbeeld
En in de tijd (maanden) kan hier een groei (of daal) patroon in zitten. Onder deze 'eenheid' staan kost eenheden. Hoe is deze 'mailbox-omgeving' opgebouwd.
Bijvoorbeeld, voor iedere 10.000 mailboxen heb ik een nieuwe server + licentie nodig.

Waar het nu om gaat is even de afschrijvingsperiode. Na een N-aantal maanden is het stukje harware afgeschreven en zou deze niet meer in de getallen terug moeten komen.
Hier loop ik simpelweg even op stuk (overigens, dit 'stoppen' heeft in het 'totale' tool een instelling 'hardware refresh' welke true/false kan zijn. Voor de eenvoud van het probleem heb ik deze even buiten beschouwing gelaten.

Let op, dit is een extract van een groter model. Het 'input' sheet kan meerdere klant eenheden onder elkaar hebben staan, en ook uit meerdere kosting regels bestaan (ook staff- en expense gerelateerde regels). Dus het toevoegen van een intermediate-kolom (bij voorkeur rechts van alle kolommen) is geen probleem.. maar extra regels ertussen zetten is geen optie.

Hoop dat er iemand wat licht kan laten schijnen Bekijk bijlage CapitalExpense_Example.xlsbBekijk bijlage CapitalExpense_Example.xlsb

 

[sylvester-ponte]

graag in het voorbeeld duidelijk aangeven wat waar moet komen en de (Nederlandse) uitleg er bij welke input gebruikt moet worden.

 

[MauriceSmit]

Ik heb de bijlage aangepast en met (onderstaande) commentaren voorzien.
Hoop dat dit meer duidelijkheid geeft.

Bekijk bijlage vb_afschrijvingen.xlsb

De formule die ik wil hebben moet dmv een '1' of een '0' aangeven wanneer een asset / regel nog niet is afgeschreven (in de blauwe velden)

Afschrijving is het afwaarderen van (vaak dure) investering, over een langere periode.
Denk bijvoorbeeld aan een bedrijf dat een auto koopt, en iedere maand hiervoor een stukje in de kosten opneemt (b.v. €30.000 aanschafwaarde gedeeld over 60 maanden = €500 per maand)
In mijn voorbeeld worden deze investeringen 'gedreven' door een baseline (de bovenste regel met aantallen). Iedere keer als deze baseline stijgt zijn er nieuwe investeringen benodigd.

Het belangrijkste is de combinatie tussen:
- een stijging in de baseline
- de lengte van de afschrijvingsperiode
- en het vlaggetje van 'vervangen?'
Als de baseline stijgt - begint er dus een 'afschrijving' --> in de formule dus graag een '1'
Dan moet deze '1' gedurende de afschrijvingsperiode blijven en daarna, afhankelijk van de vlag 'vervangen?' wel of niet naar '0' switchen

(voor de simpliciteit laten we de 'aankoopwaarde', 'aantal', 'schaalbaarheid', 'stapgrootte' achterwege)

 

[MauriceSmit]

Even de setting / analogie veranderd

Ik heb in de bijlage even een tabblad toegevoegd met een vergelijkbare vraag - alleen de setting op zijn 'JBF' (Jan Boeren Fluitjes) :thumb:

De vraag heb ik nu vertaald naar "Hoeveel mensen staan er op moment X in de wachtrij bij de Python van de Efteling".
Hierbij over de kolommen de tijd uitgedrukt en het aantal bezoekers dat de attractie in gaat (zie dit als een tellertje aan het begin van de wachtrij).
Daarna een drietal voorbeelden, met gemiddelde wachttijden (analogie hier is 'de lengte van de rit, tijd die het kost om in-uit te stappen, etc).

Ik moet in de blauwe velden dus een formule hebben - waar ik nu de antwoorden even handmatig heb ingevoerd.
Hoop dat de vraagstelling zo duidelijker is

 

[sylvester-ponte]

Maurice, ik hou het even bij de Efteling :
je schrijft:

Ik heb nu met het 'handje' de getallen ingevuld

wat is de redenatie van dat 'handje' ?

 

[MauriceSmit]

Deze waarden heb ik handmatig ingevoerd - maar moeten worden 'berekend'

 

[arrie23]

Bijgevoegd in tabblad 3 kolom G, H en I een mogelijke oplossing m.b.v. een INDIRECT-formule. Om deze makkelijker toe te kunnen passen heb ik je gegevens getransponeerd. Daarnaast een hulpkolom C ingevoerd. In cel G1, H1, I1 kun je desgewenst de wachttijd wijzigen. In kolommen D, E en F ter controle de handmatig gevulde oplossingen

Bekijk bijlage vb_afschrijvingen.xlsb

 

[arrie23]

Andere oplossing: zonder transponeren maar nog steeds met hulprij.

Uitgaande van je oorspronkelijke Efteling-voorbeeld

Maak in rij 6 een hulprij aan (toename aantal bezoekers tov voorgaande minuut):
In begincel N6 formule = N$5. In Cel O6 formule =O$5-N$5 en vervolgens naar rechts doortrekken tot het einde

Zet daarna in je oorspronkelijke voorbeeld onderstaande formule in groene cel N11 en trek naar rechts en naar beneden
=SOM(VERSCHUIVING(N$6;0;-($F7-1);1;$F7))

 

[sylvester-ponte]

met vba:
hoofd rij ( met month 2 enz ) is aangepast er staan nu gewoon getallen met een opmaak.

ps mijn oplossing geeft af en toe andere antwoorden dan jij aangeeft dus graag goed controleren
bij de efteling klopt het wel helemaal (en bij de efteling staat ook een formule zonder vba en zonder hulp kolom.)

 

[MauriceSmit]

Dank Syl - ik vind de vba oplossing erg netjes, doet wat ik verwacht + het maakt geen gebruik van hulp-regels (de capex vorm heeft op verschillende plekken op het workblad plekken waar deze regels 'geinsert' kunnen worden - dus het bijhouden van dubbele regels daarin is niet prettig). Ik ga jouw vba oplossing gebruiken in het 'gehele' model!

 

[sylvester-ponte]

Arrie, zonder jouw had ik niet begrepen wat de bedoeling was. :thumb:

 

[snb]

Deze funktie (met dank aan Sylvester voor het analysewerk!) doet hetzelfde:

Function F_snb(y, t, sn)
    sn = sn
    
    t0 = sn(1, 2) - sn(1, 1)
    t1 = (t - sn(1, 1)) \ t0 + 1
    
    F_snb = sn(2, t1)
    If (t1 - y \ t0) > 1 Then F_snb = F_snb - sn(2, t1 - y \ t0)
End Function

In N8:

=F_snb($I8;N$5;$N$5:$AQ$6)

 

[MauriceSmit]

Top heren - die is lekker snel ook! Heb mijn model gehotfixed hiermee (en weer verspreid onder meerdere gebruikers), veel mensen weer blij