How to Create a Provisions Order in Excel
The most common way to create a provisions order is to use a Commodity List which has been created using an excel spreadsheet, an excel spreadsheet offers you ultimate freedom to change and adapt a spreadsheet to suit your needs the way no other computer program or data base can, you have total control.
Using your Commodity List as a starting point for your order sheet, you need the following columns across the top of the spreadsheet.
It is possible to have additional columns with product codes but for ships I don’t recommend you use product codes as product codes tend to be specific to a single supplier so as the ships changes port and supplier it is certain all the product codes will change too, another use for an additional column would be the have the items translated into the local language. However if your vessel operates on the same routes and uses the same supplier then product codes would be advantageous to ensure the correct products are delivered to the vessel, this would be suitable for Ferries for instance.
Lets look at what these column headings mean;-
Item; – This is the name of product you want.
Unit ; – This is the size, unit of weight or volume of the product eg, kg, fl’ozs, ml, litre’s.
Price :- This is the price per Unit.
Quantity ;- This is how much or how many you want.
Value ;- This is the value of the units times by the quantity.
Here is a typical example of how items on a provision’s order look, you could send the order without the prices and values, these prices should be considered estimate prices which are more for your benefit so that you know the approximate value of your order based on historical prices form previous deliveries.
A typical order I send has approximately 600 different product lines listed sometimes I may order up to 400 different products, and on other occasions I might just order 100 different items.
No mater how many items you have on your order sheet you don’t want to send a 600 items list on 10 pages of commodities if you are ordering just 100 different items. Luckily Excel Spreadsheets are wonderful for the flexibility and control they offer, and it is easy to hide all the rows of items you don’t need to order, and here is how?
1. Go to the top of the column with the quantity you want to order then left click and holding the left click scroll down the column to highlight the whole column right down to the bottom of your spreadsheet.
2. Move the mouse to the toolbar and click on Find and Select.
3. A small Menu will appear on screen, scroll down the list and select ;- Go To Special and left click.
4. Another box will appear on the screen with Go To Special at the top. There is a selection of different things you can click on.
5. Click on Blanks, then click OK. Your spread sheet will look the same and will not have changed at this point.
6. Go to the toolbar again and left click on Format, another list will appear on the screen.
7. You will see the word Visibility, directly under that you will see Hide and Unhide.
8. Place the curser on Hide and Unhide and another list of options will appear, from this new list click on Hide Rows and all the rows with no products ordered will disappear from the screen.
9. Save the Spreadsheet
10. What you are left with is an order sheet which only has the products you wish to order visible.
11. You can then send your provisions order to your supplier as an attachment with an e-mail with additional information.
Apart from the list of items you wish to order, you will need some basic additional information like the name of the vessel and the port of delivery etc. Although all this information can be sent in the covering email, I find it more efficient and effective to duplicate the information at the top of the spreadsheet.
Sometimes a ship might not come into port due to operational needs, my last ship didn’t go into port for over one year, working in the South China Sea, so all supplies were brought out to the vessel by supply boat. Putting delivery and contact information at the top of the excel order sheet helps everyone to understand who sent the order and where and when it needs to be supplied.
- Related Articles to follow;-
The Provisions Spaces
The time it will take to Count All Provisions
Physical Stock Take