Opgelost Factuurbedrag over perioden verdelen

[aandemaas]

Ik ben nu zeker een uur bezig en nog steeds aan het wielen. Gaat het bij jou wel sneller?

 

[aandemaas]

Na anderhalf uur was de mijne ook klaar. We komen steeds dichterbij.

Bij factuur 926653 zie ik dat er ook data voorkomen die niet in maanden te verdelen zijn:
31/10/24 tot 7/6/2025: pro rata moet er dan in juni ook een bedrag komen te staan. En in periodes moet het bedrag verdeeld worden door 9 maanden, zowel de startmaand als de eindmaand telt als maand mee.

Het bestand is te groot om mee te sturen, dus deze regels even geknipt en geplakt.

 

[aandemaas]

Bij de maandbedragen en de het restbedrag gaat iets nog niet goed:
Maandbedragen zou 53210/12=4434,16 moeten zijn. het restbedrag in oktober zou 4434,16-924.24=3.509 moeten zijn.

Hieronder de bedragen die nu uit de huidige macro komen:

 

[peter59]

@aandemaas
Ik begrijp er gelovig niet veel van.
Ik weet niet hoe er wordt gerekend maar dit krijg ik eruit v.w.b. de maandbedragen.
Zie in bijlage de eerste rij in de tabel. Hier heb ik het e.e.a. overgenomen van je fotootje in post #23.

 

[aandemaas]

@aandemaas
Ik begrijp er gelovig niet veel van.
Ik weet niet hoe er wordt gerekend maar dit krijg ik eruit v.w.b. de maandbedragen.
Zie in bijlage de eerste rij in de tabel. Hier heb ik het e.e.a. overgenomen van je fotootje in post #23.

Deze gestuurde berekening kan ik niet volgen.

Zoals ik aangegeven heb zouden het de volgende bedragen moeten zijn (voor pro rata) : 924,34 voor oktober 2024, de rest van de maanden 53210/12=4434,16 en het restbedrag in oktober 2025 = het maandbedrag minus het bedrag van oktober 2024 = 4434,16-924.24=3.509.

 

[peter59]

Deze gestuurde berekening kan ik niet volgen.

Oké, dat kan.
Een gedeelte van jouw berekening kan ik al de hele tijd niet volgen zoals ook nu.
Hoe kom je aan het bedrag van 924,34 voor oktober 2024?
Dat andere is mij wel duidelijk.

 

[aandemaas]

Oké, dat kan.
Een gedeelte van jouw berekening kan ik al de hele tijd niet volgen zoals ook nu.
Hoe kom je aan het bedrag van 924,34 voor oktober 2024?
Dat andere is mij wel duidelijk.

Ja inderdaad, ik kom ook niet uit op 924,34. Het zou 7/31 van 4434,16 moeten zijn en dan kom ik uit op 1001,26.

 

[peter59]

Vlgs mij klopt dat ook niet.
Je dient uit te gaan van het dagbedrag en dat is in dit geval het totaalbedrag / door het aantal dagen. Het aantal dagen is de einddatum - begindatum en dan kom ik op €146,1813.
Dat bedrag x 7 dagen in de eerste maand (Okt) = €1023,2691

 

[AHulpje]

Reken even mee:
Van 1 oktober t/m 31 oktober = 31 dagen
Dus 31-10-2024 minus 1-10-2024 plus 1.
Dus aantal dagen van 25-10-2024 t/m 24-10-2025 = 365 en niet 364.
Dan kom je op een bedrag per dag van 145,7807, en 7 keer dat bedrag is 1020,47.

 

[aandemaas]

Dat vind ik ook een prima berekening, als de tussenliggende maanden in ieder geval maar 1/12 van het jaarbedrag blijven.
Ik heb nu dit model nodig om het verleden te corrigeren.
Hopelijk kan een van jullie mij hiermee helpen, want van die macro begrijp ik niet zoveel.

 

[aandemaas]

Reken even mee:
Van 1 oktober t/m 31 oktober = 31 dagen
Dus 31-10-2024 minus 1-10-2024 plus 1.
Dus aantal dagen van 25-10-2024 t/m 24-10-2025 = 365 en niet 364.
Dan kom je op een bedrag per dag van 145,7807, en 7 keer dat bedrag is 1020,47.

Ook prima, als ik maar een model heb waarmee ik die correctie kan maken.

 

[AHulpje]

@cow18
Met Application.Calculation = xlCalculationManual worden de totalen op rij 4 niet voortdurend bijgewerkt, de totale verwerking duurt dan slechts een seconde of 10, dan kun je wat sneller/vaker testen.

 

[peter59]

Ik heb de rekenmethode van @AHulpje verwerkt in de bijlage.
Tevens ook negatieve bedragen ingevoerd.
Kijk eens of dat al een heel klein beetje in de richting komt.

 

[cow18]

sorry, 's avonds moet ik "babysitten" bij mijn moeder, dus kon ik zien dat jullie bezig waren op de gsm, maar kon ik niets doen.
Hier alvast een snelle versie met 20K rijen, nu even lezen wat jullie fout vonden

 

[aandemaas]

@cow18 Als ik kijk naar de bijna onderste 919443. Van 24/7-2024 tm 31/7/2025
Pro rata:
Het jaarbedrag is 316, het maandbedrag is dan 316/12=26,33, dus dat is voor de maanden aug24 tm juni het bedrag wat er moet staan.
Het bedrag in juli 2024 zou moeten zijn: 316/365 = 0,86575 per dag x 8 dagen = 6,92
En het bedrag in in juli 2024 is dan 26,33 - 6,92 = 19,41
In het sheet komt er iets anders uit.

Het bedrag in de berekening periodes gaat wel goed.

 

[aandemaas]

Ik heb de rekenmethode van @AHulpje verwerkt in de bijlage.
Tevens ook negatieve bedragen ingevoerd.
Kijk eens of dat al een heel klein beetje in de richting komt.

Het eerste bedrag is nu conform wat AHulpje aangaf, maar de maandbedragen daarna zouden 53210/12=4434,16 ipv 4439,69 moeten zijn, zodat het eerste en laatste bedrag ook uitkomt op 4439,69

 

[cow18]

deze namiddag stuur ik een andere versie, nu heb ik rijen geschrapt om onder de 1.5 MB te blijven
anders kopieer en plak je die +40K rijen (kolommen A:E van het vorige bestand in dit bestand)

 

[aandemaas]

Ja geweldig!

Er is nog een variant waarbij het nog niet helemaal goed gaat en dat is dat we buiten maand-, kwartaal-, en jaarabonnementen, ook nog heel veel oude abonnementen (vooral afgelopen oktober 2024) hebben gecrediteerd om daarna een nieuw abonnement af te sluiten. Bijvoorbeeld bij 810812. Het oude abonnement liep tot en met 8 januari 2025 en is op 9 april 2024 gecrediteerd. De eerste periode zouden er 22 dagen berekend moeten worden en de laatste periode 8 dagen. En de rest moet dan in gelijke delen verdeeld worden over de tussenliggende maanden.

Misschien is het dan sowieso handiger om altijd de eerste en laatste periode te berekenen en het restant door het aantal tussenliggende maanden te delen?

 

[aandemaas]

Dit is trouwens geen goed voorbeeld, want dit zijn wel hele maanden. In oktober heb ik wel voorbeelden die korter zijn.
Bij factuur 926653 is de startdatum 31/10/2024 en de einddatum 7/6/2025

 

[cow18]

ik weet even niet of die laatste 2 reacties voor mij of voor @peter59 waren.
hier het nieuwe bestand, de knop "vergroten/verkleinen" dient enkel om de grootte onder de 1.5 MB te brengen. Straks druk je op die knop en dan worden de gegevens van "Blad2" gekopieerd en dan is die knop niet meer belangrijk.