verhuuradministratie studentenkamers

Ik kreeg ooit een mailtje of ik een systeem kon bedenken om een beter overzicht te krijgen in de verhuur van studentenkamers. De verhuurder noteerde alles in een schriftje en was inmiddels het overzicht een heel eind kwijt geraakt. Op verzoek kreeg ik het een en ander toegestuurd en na een aantal telefonische contacten met de verhuurder maakte ik onderstaande rekenmodel. De adressen en de namen zijn uiteraard fake. Mijn bedoeling was 2 tabellen huizen en huurders te ontwikkelen en deze te koppelen aan een boekingslijst

Huizenbestand.

bestand met huizen

Op het bovenste plaatje zie je het huizenbestand. Waarbij ook huizen zijn opgesplitst in kamers. Elke vorm van verhuur krijgt zijn eigen regel. Een compleet huis in de verhuur krijgt een donkerder regel door een X in de kantlijn te plaatsen. Voor de berekening van de servicekosten gaan we uit van het aantal M2 per verhuurd object. Op de regels van het complete huis vindt je in kolom M steeds het totaal aantal M2. dit aantal wordt voor de berekening van de juiste M2 vindt je steeds weer terug bij de servicekosten berekening. Als iets niet in de verhuur is dien je de aantallen in kolom M op 0 te zetten en ernaast in kolom Q het aantal M2. Bij een verandering kun je ze dan ook altijd weer terug zetten. Het aantal M2 per kamer en het totaal per huis dient U eenmalig handmatig in te voeren.

ook waren er een aantal objecten die compleet werden verhuurd

Uit de administratie maakte ik eerste een tabel waar alle adressen in kwamen te staan. Voor ieder adres maakte ik de mogelijkheid te verhuren per compleet huis of per kamer. Ook moest er een mogelijkheid zijn voor een postadres. Een compleet adres kreeg een nummer en alle verhuurmogelijkheden kregen een achterliggend nummer. In de kolommen G H en I kwamen de financiƫle cijfers te staan. Kolommen J K L voor kamernummer, aantal punten en M2. Op regel 1 van het plaatje zie je dat ik alle kolommen heb genummerd. Dit voor makkelijk aanpassen voor de formule verticaal zoeken huis/kamer aan een huurder te koppelen. Bij hele huizen heb ik voor het gemak de regel iets donkerder blauw gemaakt. Alleen in kolom 8 zit een formule in die kolom 6 en 7 bij elkaar telt.

huurdersbestand.

huurdersbestand

In het huurdersbestand komen alle huurders te staan. Kolom E vraagt om de ingangsdatum van de huur en kolom F de einddatum van de huur. Kolom G is een hulpkolom in kolom H vindt U er het bedrag van de borg, De hulpkolom is nodig voor de eerste kolom, dit is een berekende kolom die straks nodig is in de boekingslijst. Op het moment dat een verhuurder gaat vertrekken plaatst U in kolom F de datum einde huur en U plaats een 9 voor het huurdersnummer in kolom C. Voor het historisch overzicht hebben we het 4 cijferige nummer nodig. De hulp kolom neem de datum over uit kolom G waardoor er 90000 wordt afgetrokken van het nummer uit kolom C als er geen vertrek datum staat ingevuld wordt het 4 cijferige nummer in kolom B vermeld. Met het eerste nummer uit kolom B koppelt U op de boekingslijst de huurder aan het gewenste verhuurobject.

hoofdbestand verhuur.

boekingslijst linkerzijde

Door het objectnummer in kolom C te plaatsten zet U het te verhuren object op de boekingslijst. Met de formule verticaal zoeken worden kolommen D t/m I automatisch ingevuld. Kolom K wordt automatisch ingevuld als het te verhuren object in kolom N wordt gekoppeld aan de huurder. Kolom K kunt U filteren op de actuele verhuur door te filteren op IN. Wilt U het historisch overzicht raadplegen dan filtert U op OUT.

boekingslijst middelste gedeelte

Op het moment dat U het huurdersnummer in kolom N plaatst worden alle gegevens van bijbehorende huurder opgehaald. Kolom D t/m X worden d.m.v. de formule verticaalzoeken automatisch ingevuld. Als geen einde huur datum is ingevuld staat de sorteer kolom K op IN is een eind datum ingevuld dan staat de cel in kolom K op OUT. Hierboven ziet U boven kolom V W X een rood vak dit zijn de actuele huurprijzen. Momenteel (juni 2020) is het lopend jaar bijna afgelopen, U dient de huurprijzen uit het rode gedeelte over te nemen in het historisch overzicht 2019 kolommen AH en AI. Als de huurprijzen in juli gaat aanpassen, doet U dat in het huizenbestand kolommen G en H. de nieuwe huurprijzen worden automatisch doorgezet naar de kolommen hierboven. Pas dan alleen nog even de datum aan onder het rode vak van het lopend jaar.

lopende jaar met historisch overzicht

In het schema hierboven heb ik kolom K gefilterd op IN. alle actuele huurder zijn nu alleen nog zichtbaar. Ik heb 3 kolommen zichtbaar gemaakt achter het lopend jaar. Alle huren ziet U in het historisch overzicht van juli 2019 staan. Zodra de huren van 2020 zijn opgegeven in het huurdersbestand is dit rekenmodel compleet.

inkomsten en uitgave.

De volgende stap is om de inkomsten van huren en de uitgave van kosten te kunnen boeken in een overzicht betalingen en ontvangsten. In onderstaand tabblad plaatst U in kolom C de datum, kolom D rekent het maandnummer uit. ( =ALS(C5=””;””;MAAND(C5)) ) Kolom E gebruikt U voor het invullen van het categorienummer. Alle nummers boven de 50 zijn ontvangsten, alle nummers onder de 50 zijn uitgave. Kolom F wordt automatisch ingevuld formule: =ALS(E6=””;””;VERT.ZOEKEN(E6;Kostenoverzicht!$B$5:$C$114;2;ONWAAR)) In kolom G plaatst U het huurdersnummer, het objectnummer en de naam van de huurder wordt automatisch ingevuld. Als het categorie nummer onder de 50 is mag geen huurdersnummer worden ingevuld. Maar moet U het objectnummer invullen zonder de achterliggende nummering. Want de kosten gaan over het hele object. Daarom heb ik met voorwaardelijke opmaak de kolom donker gemaakt als het een kosten zijn. In kolom K wordt doorgegeven of het object onder de btw plicht valt, zoja dan ziet U een X, in kolom N geeft U H of L op voor de hoog of laag tarief btw. De btw wordt in gesloten kolommen berekend. Daarover later meer. De achterste kolommen geven de huurprijs en de servicekosten weer. Het ontvangen bedrag plaatst U in kolom L. met groter of kleiner dan 50 worden de kolommen L en M weer donker gemaakt als je daar niets mag invullen (uitleg voorwaardelijke opmaak vindt U op mijn andere website https://excel-spreadsheet.nl/voorwaardelijke-opmaak/

overzicht kosten en huur ontvangsten

Jaaroverzicht kosten.

Kosten en ontvangen huren: Voor de kosten en ontvangsten maken we een tabblad. kosten overzicht aan:LINKERZIJDE: In kolom B staan de nummers 1 t/m 20 dit zijn de nummers die je kunt gebruiken voor kosten. Helemaal onderaan vindt je de nummer 50t/m 54 deze zijn voor ontvangsten 51 zou bv ontvangsten van verzekeringen kunnen zijn. De nummers zijn alle maal vast want die gebruiken we voor de totaal tellingen.RECHTERZIJDE bovenin hebben we op de eerste regel alle objectnummers staan (zonder achtervoegsel) en er onder het aantal M2 per compleet object. Met de formule =SOMMEN.ALS wordt per kostensoort en objectnummer de totalen uit het blad betalingen en ontvangsten opgehaald.Een uitgebreide uitleg over deze formule vindt U op mijn website: https://excel-spreadsheet.nl/som-als-en-sommen-als/ Onderaan in het schema vindt U per object de totale kosten van het lopende jaar

kostenoverzicht

Jaaroverzicht huurinkomsten

Overzicht ontvangen huren: Op een nieuw tabblad maken we het onderstaande rekenmodel kosten. We maken 1 kolom waarin U het huurdersnummer vermeld, Hiermee worden de naam, de all in huurprijs en het objectnummer en de M2 van het object opgehaald. Alle huurbetalingen worden d.m.v. het huurdersnummer per maand van de betaling opgehaald vanaf het tabblad inkomsten en uitgave. Gebruikte formule: =SOMMEN.ALS(‘Inkomsten en uitgave’!$L$5:$L$5863;’Inkomsten en uitgave’!$G$5:$G$5863;$B5;’Inkomsten en uitgave’!$D$5:$D$5863;$H$3) de dik gedrukte cel adressen zijn de zoekcriteria.

huurontvangsten

De 2e gele kolom (F) op het plaatje hierboven heeft U nodig voor de berekening van de servicekosten. U vult per verhuurd object hier het objectnummer zonder achtervoegsel in. Op dit nummer worden nl ook alle kosten geboekt

Jaaroverzicht verdeling servicekosten.

jaaroverzicht kostenverdeling

D.m.v. kolom F worden alle servicekosten per object opgehaald van het tabblad inkomsten en uitgave. ook ziet U hier in kolom U alle M2 van het complete object. De servicekosten worden in kolom W terug gerekend naar kosten per M2 vervolgens worden deze berekend naar rato van de M2 per verhuurd object/kamer. Zodra U opgeeft in kolom Z het aantal maanden huur ziet U in de laatste kolom de te betalen service kosten op jaarbasis.

jaaroverzicht kostenverdeling bij meerdere huurders

Op bovenstaand plaatje ziet U dat het object nr 500 is uitgefilterd. U ziet op de 4 regel het object nr. 500-3 9 maanden is verhuurd. Onderaan ziet U het zelfde object dat 3 maanden in verhuurd, de servicekosten zijn daar eerlijk verdeeld.

gebruikte formules:Kolom F: =ALS($F4=””;””;VERT.ZOEKEN($F4;HUIZEN!$B$4:$M$322;12;ONWAAR)). Kolom G: =ALS($F4=””;””;HORIZ.ZOEKEN($F4;’jaaroverzicht kosten’!$E$25:$AG$26;2;ONWAAR)) Kolom W:=ALS(U4=””;””;V4/U4) kolom X:=ALS(B4=””;””;G4) kolom Y: .=ALS(B4=””;””;W4*X4). kolom Z: .=ALS(B4=””;””;Y4/12*Z4)

16-06-2020: uitleg van de btw administratie wordt nog toegevoegd

Hoe gebruikt U dit rekenmodel.

Plaats alle objecten in het huizenbestand houdt de nummering 500 t/m 528 aan.
Voorbeelden van de nummering met achtervoegsel vindt U in de voorbeelden hier boven.

Geef alle huurders een plek in het huurders bestand. Koppel met het huizen Id nr en het huurders Id nr beiden aan elkaar.

Op het moment dat een huurder Exit gaat, vermeldt U de exit datum in kolom F van het huurders bestand. Het huurdersbestand vraagt nu om een 9 voor het huurdersnummer te plaatsen in kolom C. Hiermee komt de huurder in het hoofdbestand in kolom Q op OUT te staan. De eerst volgende lege regel geeft U aan de nieuwe huurder. Op het hoofdbestand verhuur koppelt U het object aan de nieuwe huurder, door slechts de 2 id nrs op te geven. Dit doet U op de eerst volgende regel.

Alle inkomsten en uitgave plaatst U op het tabblad Inkomsten en uitgave. De totalen vindt U automatisch terug op de tabbladen Jaaroverzicht kosten en inkomsten. Alle kosten soorten hebben een eigen nummer. De omschrijvingen kunt U in de linker tabel plaatsen. Het jaaroverzicht aanmaken: U hoeft alleen maar de nummering over te nemen van alle huurders die op het hoofdbestand op IN staan. Een nieuwe huurder plaatst U onder bij en een oude huurder laat U op het jaaroverzicht staan. Dit i.v.m. de verdeling servicekosten. In het hoofdbestand mag U hem weg filteren in kolom Q. Vul ook in kolom F het objectnummer in zonder achtervoegsel.

Hoe maakt U het rekenmodel gereed voor het volgende verhuurjaar.


Start in de maand waarin jaarlijks de huurprijzen worden aangepast.
Als het jaar voorbij is moet U voordat U de huurprijzen op het tabblad huizen aanpast, de oude verhuurprijzen op het tabblad hoofdbestand verhuur overnemen in het historisch overzicht. U kunt dit doen met kopiƫren en plakken speciaal (aan geven waarde) U dient wel eerst de hele tabel zichtbaar maken dus in kolom Q.geeft U in het filter aan dat U alles wilt zien. Nadat U de oude prijzen in het historisch overzicht hebt gezet, filtert U het hoofdbestand weer op IN

Op het moment dat U in het huizenbestand de nieuwe huurprijzen heeft geplaatst ziet U ze ook in het hoofdbestand verhuur in de kolommen WXY staan. Past U ook nog even de ingangsdatum aan in de bovenste rij onder het rode kleurtje
Als U dit gedaan heeft, sla dan het rekenmodel op als huizenkamer bv juni 2020/2021
Ga hierna naar het tabblad Inkomsten en uitgave. Pas op doe dat kolom voor kolom

  • Ga hierna naar het tabblad inkomsten en uitgave en maak alle kolommen leeg. Doe dit kolom voor kolom U ziet de berekende kolommen vanzelf leeg raken.
  • maak kolom C en E leeg
  • Kolom G is nu weer donker geworden, maak hem nu leeg, donker is de standaard kleur
  • Ga nu bovenin op cel H5 staan, hier zit een formule. Die is hier een daar overschreven i.v.m boekingen van kosten en ontvangsten. Kopieer deze formule door naar beneden hierna is deze kolom weer hersteld
  • Maak nu kolom J leeg
  • Maak kolom L en m leeg
  • maak kolom N leeg

Na deze 6 puntjes zijn alle overzichten kosten en ontvangsten weer leeg en heeft U een compleet nieuw en actueel rekenmodel gereed voor weer een hele jaar. Dit kunt U jaarlijks doen. Als U het zelf maakt bent U klaar voor jaren en heeft U niemand nodig. Het kost U hooguit 30-40 uur. Kopen kan natuurlijk ook, dan blijft het een eenmalige investering. Meer info via de contactpagina

Ms Excel versus Online administratie

Vergelijk dat eens met online service verhuuradministratie studentenkamers U betaalt al vlot 2,50 tot 3,50 per kamer per maand. Als U van 10 kamers online 3,00 per maand moet betalen kosten deze op jaarbasis al 360,00 euro. En vraag dan ook even of de administratie Uw eigendom is als U van online dienst wilt wisselen. Of moet U daar dan ook voor betalen.