Hulp gevraagd inrichting databestand

Stel hier je vraag over een Excel probleem
renown
Berichten: 1
Lid geworden op: 13 mei 2020 08:50

Hulp gevraagd inrichting databestand

Bericht door renown » 18 mei 2020 09:22

Hi allen,

Meestal weet ik mezelf wel te redden met google en wat geknutsel in Excel, maar omdat ik me nu al weken loop blind te staren op een probleem wend ik me tot de ervaringsdeskundigen van dit forum. Hopelijk weet jullie mij op het juiste spoor te krijgen!

Het volgende is het geval: Ik heb een excel model waarin dmv draaitabellen verschillende referenties met elkaar worden vergeleken tbv analyses, bijvoorbeeld jaarplan vs realisatie, of jaarplan vs meest recente forecast. De onderliggende data is afkomstig uit een forecast tool en wordt als harde waarden in excel geplakt. In de bijlage een vereenvoudigde opzet van het data tabblad. Kolom C t/m O zijn afkomstig uit het systeem en staan dus vast, de overige kolommen zijn zelf toegevoegd. Ook is het uiteraard mogelijk om hulpkolommen toe te voegen, zowel ervoor als erachter.
De producten A t/m C (kolom C) zijn in werkelijkheid de namen van een stuk of 60 verschillende producten, deze staan redelijk vast maar zo nu en dan wordt er een product toegevoegd, verwijderd of gewijzigd qua naam. De realisatiecijfers komen per maand binnen op dezelfde producten en plak ik ook hard in het data-bestand.

Kolom B geeft de referentie aan, dus of betreffende regels realisatie cijfers, jaarplan cijfers of de forecast van een specifieke periode zijn
Kolom A is een hulpkolom die ik gebruik voor analyses van de Full Year cijfers. Hiervoor moet ik namelijk realisatiecijfers + forecast bij elkaar op tellen. In dit geval is dus full year forecast_03 = realisatie jan t/m feb + forecast mrt t/m dec. En dat schuift natuurlijk steeds een maand op, dus full year forecast_04 zal zijn realisatie jan t/m maart + forecast apr t/m dec, etc. Elke maand pas ik dus de variabele in cel W1 aan zodat kolom A weet welke forecast rijen gebruikt moeten worden.

Vanaf regel 21 heb ik de twee analyses getoond die ik sowieso maandelijks moet doen en worden ondersteund door het model.

Mijn probleem ontstaat bij de extra analyses die ik maandelijks wil kunnen doen, en die ik uitgewerkt heb vanaf regel 41
- Jaarplan vs Realisatie - YTD -> in dit geval dus de som van Jaarplan Jan + Jaarplan Feb vs som van Realisatie Jan + Realisatie Feb. Zoals jullie kunnen zien heb ik hier al twee hulpkolommen nodig, en naar mate we verder in het jaar komen zal de draaitabel steeds langer worden en elke maand zal ik de hulpkolommen moeten aanpassen. Ik wil dus eigenlijk aan het data model een kolom toevoegen waarin automatisch YTD wordt berekend. Dus dat het model op basis van huidige maand weet dat jan en feb moeten worden opgeteld, de volgende maand moet ook maart worden meegeteld, vervolgens ook april etc.
Ik verwacht dat ik hier iets met =NOW() moet doen? Maar hoe gebruik ik dat in combinatie met kolommen D t/m O?


- Forecast_02 vs Forecast 03 - Full Year -> de manager wil ook altijd weten wat de Full Year aanpassingen zijn geweest in de forecast. En met name deze vergelijking krijg ik niet goed in de draaitabellen
Forecast_02 = realisatie jan + forecast feb t/m dec
Forecast_03 = realisatie jan t/m feb + forecast mrt t//m dec
Hoe kan ik het data-bestand zo aanpassen dat bovenstaande geautomatiseerd wordt? Bij voorkeur zet ik in de draaitabel alleen full year in de meetwaarde en vervolgens Forecast_02 en Forecat_03 in de kolommen, maar het feit dat het een combinatie van realisatie regels en forecast regels is maakt het erg lastig.Hoe laat ik excel weten welke regels hij moet optellen voor forecast_02 en welke voor forecast_03 zonder dat dit een handmatig iets wordt?

Ik hoop dat mijn vragen zo duidelijk zijn, maar mochten bepaalde delen meer toelichting nodig hebben dan hoor ik het graag.
Bijlagen
Voorbeeld databestand.xlsx
(17.34 KiB) 184 keer gedownload

Plaats reactie