Challenge Overview
Challenge Objectives
-
Documenting Excel macros
-
Creating data model for the bunker app
-
Recommending viable technology stack
-
Will be used in future challenges to port the excel calculations to backend API
Project Goal
The intent of this project is to create a MVP web application - Bunker Desk Tool Kit - to manage the activities currently done with a combination of excel tools and manual tasks. Using the new web application, users will be able to:
-
Schedule bunker deliveries and resupply movements
-
Report and communicate effectively with third-party terminals, barges and agents
-
Provide optimal blend opportunities
-
Update Marketers and Traders of the status of inventories and deliveries
Project Background
Apophis currently uses a cumbersome combination of excel tools, manually generated reports, routine emails, calls and meetings to manage the bunker fuel operations for terminals and barges across 4 geographies.
Bunkering is the supplying of fuel for use by ships, and includes the shipboard logistics of loading fuel and distributing it among available bunker tanks. In many maritime contracts, such as charter parties, contracts for carriage of goods by sea, and marine insurance policies, the shipowner or ship operator is required to ensure that the ship is "seaworthy". Seaworthiness requires not only that the ship is sound and properly crewed, but also that it is fully fueled (or "bunkered") at the start of the voyage.
Excel tools currently help the scheduler to
-
Optimize cost-effective blends,
-
Track movements into and out of the terminal and barges,
-
Manage inventory and schedule deliveries based on various ports and report information to marketers and traders while managing physical, quality and logistical constraints.
-
Nomination documents for each movement formalize the instructions to Terminals and Barge Companies.
-
Daily logistics coordination with ships, port agents, terminals and barge owners occur via email and calls to respond to the rapidly changing operating environment.
-
Updates on scheduled deliveries, available inventory, and barge availability are shared with Marketers and Traders via manual reports generated periodically throughout the day.
-
Throughout the day Marketers and Traders will communicate orders for new bunker deliveries through Bunker Delivery Notifications based on available inventories and barge availability.
Technology Stack
-
Excel
-
Relational database, SQL
Code access
Sample Excel workbooks that contain the macros are available in the forums.
Individual requirements
The goal in this challenge is to document the excel macros and calculations in the excel workbook and create a data model (relational schema). Your submission should be a document containing the following:
-
ER diagram that covers all the data currently stored in the sheets
-
User actions list (ex create nominations, create daily emails, optimize, etc) and for each user action the following details:
-
Action inputs (names, data type, allowed values)
-
Calculation outputs (names, data types, valid values, calculations)
-
Macros and formulas documentation (what each of them does - inputs, dependent functions, calculation steps, and outputs). Make sure to document intermediate steps too (if there are intermediate columns with formulas) - we will need to do the same calculations when converting it into backend code, so all calculation details are needed
-
Here is the background on the business process and how it relates to data and operations in the excel sheet. Different oil products are stored in oil tanks in different locations (physically different, ex ports in different cities around the world) and each location has a fleet of vessels that transport oil from tanks to the ships. Each oil product has strictly defined oil properties such as viscosity, sulfur percentage, water percentage, etc. We’ll refer to this as assets data - once defined, it will rarely be changed.
Now the oil distributor company receives orders for the oil (ex a ship is coming in to refuel in 5 days). Orders are received by “scheduler” - an employee (or app user in our case) that manages the oil inventory and schedules the barges that deliver fuel to the ships. Each order can require a different oil product, but the ordered product often does not match with what is already available in the tanks, so the oil from different tanks is blended to get the ordered product specification. Once the blending is complete, the oil is loaded onto a barge that then transfers the oil to the ship and unloads it to the ship reservoir (the delivery is then inspected by an inspector from an external company). All these events have to be tracked by the scheduler, but that is just a part of his job. The oil also has to come into the tanks so the scheduler makes orders to purchase oil from refineries and tracks when the oil is actually delivered and loaded into tanks (and the oil delivery can happen via pipeline directly from the refinery, or manually from barges or trucks).
All these events are tracked as “inventory transactions” - each event updates the oil inventory data.
The scheduler also has to manage the blending process. There are different ways to make the target blend using the available oil products in tanks so the scheduler uses another tool (let's call it optimizer) that calculates the blend of oil with lowest cost per unit of weight.
Lastly, the scheduler provides status reports for Marketers (listing of deliveries), Barge companies (listing of deliveries and resupplies) and for other schedulers (a view of all the transactions for a time interval).
And here are the details of how the above process connects to the Excel spreadsheet. Assets data is configured in the sheet called “Config2”. Most of this data will naturally translate to database entities.
Inventory transactions are stored in the sheet called “MAIN”. Each transaction is added to a new row. Here are all the transaction (operation) types that are used:
-
D: When the barge is delivering product to a ship
-
L (new code, not present in the spreadsheet): When the barge is getting loaded.
-
PQ (new code, not present in the spreadsheet): Pre-nominated quotes for delivery.
-
SQ(new code, not present in the spreadsheet): Spot quotes for delivery.
-
R: Resupply Load means buy from refinery.
-
S: Resupply discharge means discharging the resupply load into tanks.
-
C: Terminal Constraint means a constraint which has been caused at the terminal due to bad weather or due to congestion at terminal.
-
P: Pipeline means if product is getting loaded into tanks via pipelines
-
T: Tank Transfer means when the product is getting moved between tanks. E.g. product could be moved from tank 1, 2 & 3 into tank 4 for blending Note: There is only 1 inlet to the tank, so the products are moved into the destination tank in a sequence which is usually defined by viscosity. Heavier product moves always first and the lightest goes in the last.
Note that D, R, C transactions do NOT change the oil balance in the tanks - they are just additional info for tracking the business events and scheduling the barges. All other operations affect either the balance of the oil in the tanks, or the properties of the oil in the tanks (ex blending), or both.
PQ and SQ are just different types of orders, but the order type doesn’t affect the process - order can either be delivered immediately (existing product, available in the tanks), or blending is needed - product specification must be provided in that case (the target oil properties)
Once a new order is received a PQ or SQ transaction is added with the appropriate type, and if it requires blending the scheduler uses another spreadsheet (blend optimizer) where he selects the oil tanks that are available for blending, runs the optimizer and based on the outputs adds a transaction with type T that contains details about changes to the tank oil balances (which tanks the oil is taken from and into which tank it is blended into) and the resulting oil product properties for the target tank (specific weight, API, etc).
Once the blending is complete a transaction with type L will be added (oil from tank loaded onto the barge) and once that is complete a transaction with type D is added (barge delivering oil to the ship).
Similar workflow happens for loading the oil into the tanks (resupply).
The important task here is to figure out the appropriate data model to store and track these transactions. Note that having up to date balance and properties of oil in the tanks is essential, as well as keeping the historical data - the final app will need to produce stats on the individual tanks (oil level, transactions, oil properties) and individual barges (transactions, loads) so the data model you propose has to support such use case. Please describe in your submission how the data can be filtered to get these views.
Once the transactions are created the user can do a few things (all of them are done through macros in the spreadsheet):
-
Create nominations - essentially add the selected transactions details to a template file
-
Create daily emails - send emails with transaction info to marketing and Barge companies and external agents departments
-
Test nominations - similar to create nominations
-
Create a schedule and email it (Schedule sheet)
-
Create and configure emails (Email sheet)
For each of them you need to document what exactly they do - what the inputs are, what are the outputs and the business logic.
Finally, here are the details of the blend optimizer spreadsheet. When blending is required, users would select the tanks that are available for blending (source tanks) and the target oil blend properties and amount. Oil properties for the source tanks would be loaded from the database (this doesn’t actually happen in the spreadsheet - users would manually copy paste those values - columns F-R, with the exception of cost - it is always entered by the user instead of being stored in the database). Then the optimizer is run (Optimize button in the spreadsheet) which uses Excel Solver Add-in. You need to document all the intermediate calculations (columns S-AI), the constraints that are added to the solver, type of optimization that is used (linear programming, or something else) and it’s configuration, and calculation outputs.
Note that we don’t need to store optimization details into the database - we will only need to store a new transaction with type T (blending details).
Technology Stack
As the last requirement for this challenge, you can recommend appropriate tech stack - programming language, database and libraries that we can use to implement the above use cases. Note that covering the optimizer requirement with an appropriate library is required.
-
Recommendations for technology choices for the web application should support responsive design with desktop and mobile format and can be hosted on Azure Cloud.
What To Submit
-
Submit the ER diagram
-
Calculation specification document
-
Technology stack recommendations
Scoring Methodology
Scoring will be based on how detailed your submission is - how well does it cover the calculation details from the Excel document and how good the data model is