Solution and formula to create a template for Jetty Utilization

[Lukma]

good day

Hi Friends

Please i like some solution and how to go about it, i was ask by my manager to find a way and create a template that will capture a report for the jetty Berthing Utilization in percentage.

and the idea is to see if to extend and increase the Jetty Berthing Length

Presently the Jetty is with maximum having 14 berth in Jetty and maximum vessel tie up is 9 to 10 Vessel, while some vessel are double bunking along side in second position waiting for First position

Now the Question i was ask is to determine and calculate the performance of Utilization of the Berth in percentage, but still i don't seems to no how i can calculate or design the template to capture this Berth Utilization in percentage

Please if there is a way on how i could design the template i will be glad to have this solution as this is new task

from all i know is that once vessel arrive the Jetty securing First position and why others vessels are waiting along side.

So the management want to know how the Berth is been utilize and what is the percentage of it Monthly Wise


Thanks and i hope i will be able to find a solution to this new task giving

 

[RasGhul]

Hi

Hopefully I understand your goal correctly, create a Berthing log to calculate from. I've converted to excel table(named Berthdata) which keeps the calculations dynamic from the log.

Then you can either use the fixed tables or reproduce similar with pivot tables;

Jetty Utilization_Lukma.xlsx
	A	B	C	D	E	F
1	DATE	VESSEL NAME	VESSEL NUMBER	BERTH POSITION	STATUS
2	1/01/2021	SHIP1	1456	1
3	13/01/2021	SHIP2	1457	2
4	25/01/2021	SHIP3	1458	3
5	6/02/2021	SHIP4	1459		Doublebunked
6	18/02/2021	SHIP1	1460	5
7	2/03/2021	SHIP2	1461	6
8	14/03/2021	SHIP3	1462	7
9	26/03/2021	SHIP4	1463	8
10	7/04/2021	SHIP1	1464	9
11	19/04/2021	SHIP2	1465	1
12	1/05/2021	SHIP3	1466		Doublebunked
13	13/05/2021	SHIP4	1467	3
14	25/05/2021	SHIP1	1468	4
15	6/06/2021	SHIP2	1469		Doublebunked
16	18/06/2021	SHIP3	1470	6
17	30/06/2021	SHIP4	1471	7
18	12/07/2021	SHIP1	1472		Doublebunked
19	24/07/2021	SHIP2	1473	9
20	5/08/2021	SHIP3	1474	1
21	17/08/2021	SHIP4	1475		Doublebunked
22	29/08/2021	SHIP1	1476	3
23	10/09/2021	SHIP2	1477	4
24	22/09/2021	SHIP3	1478		Doublebunked
25	4/10/2021	SHIP4	1479	6
26	16/10/2021	SHIP1	1480	7
27	28/10/2021	SHIP2	1481		Doublebunked
28	9/11/2021	SHIP3	1482	9
29
30
31	MTD	Vessels	Position Count	Doublebunked	% Postions	% Doublebunked
32	1/01/2021	3	3	0	100.00%	0.00%
33	1/02/2021	2	1	1	50.00%	50.00%
34	1/03/2021	3	3	0	100.00%	0.00%
35	1/04/2021	2	2	0	100.00%	0.00%
36	1/05/2021	3	2	1	66.67%	33.33%
37	1/06/2021	3	2	1	66.67%	33.33%
38	1/07/2021	2	1	1	50.00%	50.00%
39	1/08/2021	3	2	1	66.67%	33.33%
40	1/09/2021	2	1	1	50.00%	50.00%
41	1/10/2021	3	2	1	66.67%	33.33%
42	1/11/2021	1	1	0	100.00%	0.00%
43	1/12/2021	0	0	0	0.00%	0.00%
44
45	Summary	Vessels	Position Count	Doublebunked	% Postions	% Doublebunked
46	Total	27	20	7	74.07%	25.93%
Sheet1

Cell Formulas
Range	Formula
A3:A28	A3	=A2+12
B32:B43	B32	=COUNTIFS(BerthData[DATE],">="&$A32,BerthData[DATE],"<="&EOMONTH($A32,0))
C32:C43	C32	=COUNTIFS(BerthData[DATE],">="&$A32,BerthData[DATE],"<="&EOMONTH($A32,0),BerthData[BERTH POSITION],">0")
D32:D43	D32	=COUNTIFS(BerthData[DATE],">="&$A32,BerthData[DATE],"<="&EOMONTH($A32,0),BerthData[STATUS],"Doublebunked")
E32:E43	E32	=IFERROR(C32/B32,0)
F32:F43	F32	=IFERROR(D32/B32,0)
A33:A43	A33	=EOMONTH(A32,0)+1
B46:D46	B46	=SUM(B32:B43)
E46	E46	=C46/B46
F46	F46	=D46/B46

 

[Lukma]

Dear RasGhul

Thanks so much I believe this will give all i needed however but i have one question which in this case what will be the utilization Percentage of the berth Utilize

will the utilization be 74.07%

Appreciate your feedback if am to use this template to calculate the Berthing utilization monthly

Regards

 

[RasGhul]

Hi Lukma,

Yes thats the percentage of vessels that berthed on a jetty position from 1 - 9 (Column D data), unless they doublebunked (Column E data)

 

[Lukma]

Thanks so Much i believe this is all i need to present am much grateful

 

[Lukma]

Hi

Please can you go through my Table i have create in this way

 

[Lukma]

Hi Rasghul

Please can you take a look at my Data i have entered few list of Vessel, for just a Month Report to understand how is works to i made my template similar, but i feel there is something not right with the report am creating.

Can Please have a look

Thanks

ADNOC L&S Port berth Utilization.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
5
6									Summary	Num Vessels	First Position Count	Double bunked	Berth Utilization % Postions	% Double bunked
7										269	183	86	68.0%	32%
8
9									January	2021		1-Jan-21	31-Jan-21
10	ARRIVED DATE & TIME	VESSEL NAME	VESSEL NUMBER	BERTH FIRST POSITION	VESSEL BERTH STATUS	DEPARTURE DATE & TIME	Total Duration		MONTH	Num Vessels	First Position Count	Double bunked	Berth Utilization % Postions	% Double bunked
11	01/01/21	ADNOC-229	11	1					01/01/21	27	20	7	74.1%	25.9%
12	01/01/21	LCT-MARWAH-1	12	2					02/01/21	24	17	7	70.8%	29.2%
13	01/01/21	Z-OCEAN	13	3					03/01/21	20	14	6	70.0%	30.0%
14	02/01/21	ADNOC-230	14		Doublebunked				04/01/21	15	10	5	66.7%	33.3%
15	02/01/21	A-LIBERTY	15	5					05/01/21	15	10	5	66.7%	33.3%
16	02/01/21	ADNOC-851	16	6					06/01/21	14	9	5	64.3%	35.7%
17	02/01/21	A-CHLOE	17	7					07/01/21	14	9	5	64.3%	35.7%
18	03/01/21	ADNOC-225	18	8					08/01/21	13	9	4	69.2%	30.8%
19	03/01/21	ADNOC-811	19	9					09/01/21	13	9	4	69.2%	30.8%
20	03/01/21	SWISSCO RUBY	20	1					10/01/21	12	8	4	66.7%	33.3%
21	03/01/21	ADNOC-512	21		Doublebunked				11/01/21	12	8	4	66.7%	33.3%
22	03/01/21	AMS-RUBY	22	3					12/01/21	9	6	3	66.7%	33.3%
23	05/01/21	ADNOC-511	23	4					13/01/21	9	6	3	66.7%	33.3%
24	07/01/21	ADNOC-810	24		Doublebunked				14/01/21	8	5	3	62.5%	37.5%
25	09/01/21	ADNOC-812	25	6					15/01/21	8	5	3	62.5%	37.5%
26	11/01/21	MAC PHOENIX	26	7					16/01/21	7	5	2	71.4%	28.6%
27	11/01/21	QMS MARIMBA	27		Doublebunked				17/01/21	7	5	2	71.4%	28.6%
28	11/01/21	ADNOC-227	28	9					18/01/21	6	4	2	66.7%	33.3%
29	13/01/21	LCT-WARDEH	29	1					19/01/21	6	4	2	66.7%	33.3%
30	15/01/21	Z-STAR	30		Doublebunked				20/01/21	5	3	2	60.0%	40.0%
31	17/01/21	B-HOMERE	31	3					21/01/21	5	3	2	60.0%	40.0%
32	19/01/21	ADNOC-226	32	4					22/01/21	4	3	1	75.0%	25.0%
33	21/01/21	Z-POWER	33		Doublebunked				23/01/21	4	3	1	75.0%	25.0%
34	23/01/21	ADNOC-223	34	6					24/01/21	3	2	1	66.7%	33.3%
35	25/01/21	SMIT LUMUT	35	7					25/01/21	3	2	1	66.7%	33.3%
36	27/01/21	A-HERCULES	36		Doublebunked				26/01/21	2	1	1	50.0%	50.0%
37	29/01/21	LCT-TARFFAH	37	9					27/01/21	2	1	1	50.0%	50.0%
38	31/01/21	SEACOR OHIO	38		Doublebunked				28/01/21	1	1	0	100.0%	0.0%
39	02/02/21	ADNOC-224	39	8					29/01/21	1	1	0	100.0%	0.0%
40	04/02/21	A-LIBERTY	40	1					30/01/21	0	0	0
41									31/01/21	0	0	0
Port Berth Utilization

Cell Formulas
Range	Formula
K7:M7	K7	=SUM(K11:K41)
N7	N7	=L7/K7
O7	O7	=M7/K7
M9	M9	=DATEVALUE("1"&J9)
N9	N9	=EOMONTH(M9,0)
J11	J11	=M9
K11:K41	K11	=COUNTIFS(B$11:H$37,">="&$J11,$B$11:$H$37,"<="&EOMONTH($J11,0))
L11:L41	L11	=COUNTIFS($B$11:$B$37,">="&$J11,$B$11:$B$37,"<="&EOMONTH($J11,0),$E$11:$E$37,">0")
M11:M41	M11	=COUNTIFS($B$11:$B$37,">="&$J11,$B$11:$B$37,"<="&EOMONTH($J11,0),$F$11:$F$37,"Doublebunked")
N11:N41	N11	=IFERROR(L11/K11,"")
O11:O41	O11	=IFERROR(M11/K11,"")
J12:J41	J12	=IF(J11<$N$9,J11+1,"")
B29:B40,B23:B25	B23	=B22+2

Cells with Data Validation
Cell	Allow	Criteria
J9	List	=Info!$D$4:$D$15
K9	List	=Info!$B$4:$B$15

 

[Lukma]

Thank

I got It now

 

[RasGhul]

I went with this setup, COUNTIFS(B$11:H$37 is counting the whole matrix rather than only the B Column.

The dates in the Months DValidation they are 01/01/21 etc but formatted as January etc, then J9 is a drop down control for M9 & N9.

Book2
	J	K	L	M	N	O	P	Q
6	Summary	Num Vessels	First Position Count	Double bunked	Berth Utilization % Postions	% Double bunked
7		28	20	8	71.43%	28.57%
8
9	January			1/01/2021	31/01/2021
10	MONTH	Num Vessels	First Position Count	Double bunked	Berth Utilization % Postions	% Double bunked		Months
11	1/01/2021	3	3	0	100.00%	0.00%		January
12	2/01/2021	4	3	1	75.00%	25.00%		February
13	3/01/2021	5	4	1	80.00%	20.00%		March
14	4/01/2021	0	0	0				April
15	5/01/2021	1	1	0	100.00%	0.00%		May
16	6/01/2021	0	0	0				June
17	7/01/2021	1	0	1	0.00%	100.00%		July
18	8/01/2021	0	0	0				August
19	9/01/2021	1	1	0	100.00%	0.00%		September
20	10/01/2021	0	0	0				October
21	11/01/2021	3	2	1	66.67%	33.33%		November
22	12/01/2021	0	0	0				December
23	13/01/2021	1	1	0	100.00%	0.00%
24	14/01/2021	0	0	0
25	15/01/2021	1	0	1	0.00%	100.00%
26	16/01/2021	0	0	0
27	17/01/2021	1	1	0	100.00%	0.00%
28	18/01/2021	0	0	0
29	19/01/2021	1	1	0	100.00%	0.00%
30	20/01/2021	0	0	0
31	21/01/2021	1	0	1	0.00%	100.00%
32	22/01/2021	0	0	0
33	23/01/2021	1	1	0	100.00%	0.00%
34	24/01/2021	0	0	0
35	25/01/2021	1	1	0	100.00%	0.00%
36	26/01/2021	0	0	0
37	27/01/2021	1	0	1	0.00%	100.00%
38	28/01/2021	0	0	0
39	29/01/2021	1	1	0	100.00%	0.00%
40	30/01/2021	0	0	0
41	31/01/2021	1	0	1	0.00%	100.00%
Sheet1

Cell Formulas
Range	Formula
K7:M7	K7	=SUM(K11:K41)
N7	N7	=L7/K7
O7	O7	=M7/K7
M9	M9	=J9
N9	N9	=EOMONTH(M9,0)
J11	J11	=M9
K11:K41	K11	=COUNTIFS($B$11:$B$40,">="&$J11,$B$11:$B$40,"<="&$J11)
L11:L41	L11	=COUNTIFS($B$11:$B$40,">="&$J11,$B$11:$B$40,"<="&$J11,$E$11:$E$40,">0")
M11:M41	M11	=COUNTIFS($B$11:$B$40,">="&$J11,$B$11:$B$40,"<="&$J11,$F$11:$F$40,"Doublebunked")
N11:N41	N11	=IFERROR(L11/K11,"")
O11:O41	O11	=IFERROR(M11/K11,"")
J12:J41	J12	=IF(J11+1>$N$9,"",J11+1)
Q12:Q22	Q12	=EOMONTH(Q11,0)+1

Cells with Data Validation
Cell	Allow	Criteria
J9	List	=$Q$11:$Q$22