Scrapyard's Mining Op Worksheet
The EVE IVY Mining Op worksheet is what I (Scrapyard Bob) use when running a mining op. It lets me keep track of members, clock people in/out, track the ore as it gets hauled to the station, and do payouts in storyline ore or in refined minerals instead of ISK. The basic concept behind this sheet is that mining share payouts are based on time worked.
The latest version of my personal mining op worksheet is available at:
EVE IVY Mining Op Worksheet (blank) at Google Docs
As with all Google Doc Spreadsheets, you can either copy it to your own personal Google Doc area or download it as a Microsoft Excel (XLS) file or in an OpenOffice.org spreadsheet file.
Working with spreadsheets is easy, but a few basic tips are:
- Never move cells
- Never copy/paste random cells to other locations
- Don't insert or delete rows/columns
- Never sort the sheets on a particular column
- If you need to clear an entry cell, highlight it and use the [Delete] key
- Make backups
All of my worksheets follow a color scheme of background colors indicating where you can enter data and what type of data gets entered in that location. Those colors are as follows:
- Green - Text entry fields (such as names, yes/no flags, etc)
- Yellow - Numeric entry fields (1.00, 500, 28.75, etc).
- Blue - Time entry fields (expected in format hh:mm, i.e. 14:25)
- Help - This sheet will explain the other sheets. Lots of helpful information here such as what belongs in a particular column.
- Inventory - Used to track the progress of the mining op as ore is hauled to the station. Can also be used to track raw minerals looted from rats as well as the final refine amounts.
- Participation - This is where you track members and payouts.
- ExtraISK - Use this to add/remove ISK from the total. Such as payouts to freighter pilots (usually done at a rate of a few hundred thousand ISK per jump) or ISK received by selling off salvage or looted modules.
- Refining - Whoever is refining the ore should update this sheet to reflect their skill levels.
There's not much to getting started in terms of paperwork.
Copy the Spreadsheet
The first step is to always make a copy of the blank spreadsheet that you use. In Google Docs, when viewing the official version, you can pick "File, Make a Copy (Ctrl-Shift-S)" and save it to a new name in your personal area.
Update your Stats
In order to get accurate results from the worksheet, your first stop should always be the "Refining" page where you enter the skill levels of whoever is going to refine the ore into minerals. If you do not do this, you will get inaccurate estimates of the operation's payout.
The default values assume that you will pay zero tax and get a perfect refine at a 50% station. Not all stations in high security space are 50%.
Tracking the Operation
Here we'll explain what you'll want to do during the operation.
Transparency is good in shared-can mining operations as it lets people verify that they were clocked in/out correctly and that they are receiving the proper share. Therefore, if you are using Google Docs, I suggest sharing the operation worksheet in read-only mode to the rest of the fleet. This can be done by clicking on the "Share" button in the upper right, choosing "Get link to share". You should set it so that everyone can view without needing to sign into Google. The URL should be copy/pasted into fleet-chat so that people can monitor the spreadsheet for that operation.
If you have a second person in the fleet that you trust, who also has a Google account, you may wish to give them edit rights. That will provide a second set of hands to keep track of things in larger operations.
As people join the fleet, you should list them on the "Participation" tab of the worksheet. You should make sure to enter names exactly as they appear in game in the "Name" column and put other information like ship type, corp ticker, "alt of so-and-so" in the "Comment" column. By entering only the player name in the "Name" column, you will have an easier time later when doing the payouts.
The share weight column is used in rare cases (not often used in IVY fleets) where different ship types get different payouts. Alternatively, you could lump people with multiple alts together and mark them as a 2x (for two alts) share weight so that you have fewer payouts to make. Just make sure that the alts join/leave the op at the same time as the main character.
Before adjusting weights, you must get everyone in the fleet to agree with the system and say so in fleet chat. The vast majority of IVY fleets are run on a "time served, equal shares per person" basis with no variation based on ship type (see also Running a Mining Op). EVE University is first and foremost a teaching organization and shared-can ops are a good way to give younger pilots a helping hand.
The "Time Clock" columns allow you to clock a single person in/out up to two times. So if someone needs to leave for 30 minutes, you can clock them out, then clock them back in for a second session. Alternatively, you can enter time in the "Dock" column which will be subtracted from the person's total participation time. The "Hours Worked" will reflect wall clock time that the person spent mining / hauling, while the "Weight Time" will reflect their weighted time share.
You should still have members announce in fleet chat when they start/stop actively participating in the mining op. This will help you reconstruct who joined/left and when if there are questions about things after the operation has finished. It is also strongly suggested that you take periodic (every 10-20 minutes) screenshots of the Local or Overview window to keep track of who was around at a particular time.
In order to get accurate estimates, you should periodically update the "Estimated End Time" value in the top row to reflect when you think the operation will end. If it is a long running operation, just keep pushing the estimated end time out by 30-60 minutes at a time.
Note: The "Estimated Payout (ISK)" column is only accurate if you have updated the estimated end-time for the operation and have also been updating the "Inventory" sheet as ore is hauled to the station.
The second task that the leader of the mining op should attempt to keep on top of is tracking the amount of ore / materials that have been hauled to the station. If your hauler has a Google account, you may wish to grant them edit access to the worksheet so that they can update the totals themselves. It is not possible to only grant them access to part of the worksheet, so make sure that you trust them and that they know how to enter the values.
At the start of the operation, as soon as things have settled in, you should attempt to update the "Market ISK/unit" column for all of the ores that you will be mining as well as estimated values for selling the resulting minerals. These values are also used to calculate whether it is better to refine the ore or to sell it on the market (storyline ores often sell well as-is). For people who want to be paid in minerals, the market values are used to calculate the ISK value of their mineral share.
The "Action Taken" column should be changed to "Sell" if you are going to sell the raw ore on the market. In general, all ore except for regular Omber and regular Kernite is better off as refined, unless someone in the fleet is willing to immediately pay for a particular raw ore.
As your hauler makes trips back to the station, it's suggested that you periodically update the "Amt Mined (units)" column with the amount stored in the station. Then you will see the estimated values on the "Participation" sheet start to populate with estimated ISK payouts. You can also use the total volume (m3) in the top row of the worksheet to double-check the amount of ore stored in the station. This can be tricky with multiple haulers and you may have to rely on numbers being reported to fleet chat once an hour.
From a paperwork standpoint, there's not much else to do other then tracking members, tracking ore, and updating market values. Towards the end of the operation, you might wish to solicit requests for mineral or storyline ore payouts which get entered on the "Participant" sheet.
As the operation winds down, double-check the following things:
- Estimated End Time on the Participation sheet
- Market values of the minerals and storyline ores
- Estimated fees that need to be paid to haulers / refiners / helpers
- Member names
- Clock times for members, dock time, share weights
- Inventory of raw ore
Choose Payout Types
The "Pay With" column is merely a comment field used to remind you (as the fleet commander) what sort of payout people have requested. Most people want ISK, some might want ore (ORE), others might want minerals (MIN). For people that want minerals or storyline ores, you will need to enter quantities in the right side of the "Participation" sheet (Mineral Payout and Storyline Ore Payout columns). Make sure that you set this ore/minerals aside before refining/selling.
I suggest using a screenshot or posting the # of storyline units being set aside into fleet chat to keep track of this in addition to entering it on the worksheet.
Refine and Haul
After setting aside the storyline ore payouts, give the rest of the ore to your refiner. Hopefully someone with zero tax and perfect refines. The amount of minerals that you get from refining should match the totals near "Total Mineral Quantities from Refining Process" on the "Inventory" worksheet (hidden in the upper-right portion).
Now set aside the minerals being used as mineral payouts. Again, report this in fleet chat or capture a screenshot for auditing purposes.
The rest of the minerals should be hauled to market and sold. For the moment, both mineral payout units and not yet sold units should be entered in the "Not Sold Inventory" column.
Update the Amount
As you sell off minerals, subtract the units from the "Not Sold Inventory" and add the ISK amounts in the "Actual Sold Value (ISK)" column. Enter the net amount of ISK received from the transaction (after sales tax and broker fees are paid). Repeat this process until you have sold off all of the minerals that were not set aside for mineral payouts.
Once all minerals have been sold, you can distribute shares for the operation. There is an easy way and a hard way to distribute shares, I will teach you the easy way that eliminates errors. The key things to remember are:
- Always copy/paste names from the spreadsheet to the EVE client
- Always copy/paste amounts from the spreadsheet to the EVE client
Start by distributing storyline ore and mineral payouts. You can do this either via trade in stations, or by creating an "item exchange" contract. Never type names or amounts, always copy/paste from the appropriate cell in the mining op spreadsheet. Mineral/Ore payout totals are on the "Participation" sheet. As you pay people, enter "o" or "m" in the "Paid?" column to keep track of who you have paid.
Then repeat the process for the ISK payouts. Copy the person's name into the clipboard, open up People & Places and search for "Character (exact)". Paste the member's name into the search box. When the name shows up, right-click and "Show Info" to double-check the member, then right-click and use "Give Money". For the amount, go to the "Participation" sheet and copy the amount from the "Participation ISK Payout" column. Paste this value into the amount field and enter "(systemname) mining op" in the comment field.
Note: IVY fleets should keep these worksheets around for 1-2 weeks in case there are any questions or issues regarding fleet shares, payouts, or other problems. Make sure that you keep any screenshots as well as the fleet chat log files.