Vessel Management System - Spreadsheet Prototype

I start this project by make a prototype using spreadsheet. I divide it into 2 parts, those are ship scheduling and inventory management. Figure 1 below show how I visualize the ship movement. In fig 1 below there are 3 ship (1,2,3), 2 loading port (B,D), 2 discharge port (A,C), 4 ship activity (anchorage, sailing, discharge, loading), load or discharge quantity, and date (planning horizon). 

Figure 1. Gantt Chart Ship Activity

The second part is inventory management which is show in fig 2 below. There are four part in inventory management, those are port name, daily stock movement, and initial stock. The red color in row “out” show the forecast in that day while the black color show the actual volume out from the storage. Using formula “sumifs” I can link the ship scheduling & inventory management so that it can automatically calculate the daily stock every day.

Figure 2. Inventory Management

Finally to notify the officer at discharge point and simplify the planning visualisation I make a chart which show in fig 3. There are four part in that chart represent the safety stock (blue line), storage capacity (red line), planned incoming ship (green column), and daily stock (purple line). The unlabelled column represent the delivery using land transportation which is truck.   

Figure 3. Inventory Management Chart

Okay, enough for tonight because I have to work tomorrow. This part isn't complete yet and I am going to complete it tomorrow. Good night guys. J