Mk. Ultra - Comprehensive Guild Management Spreadsheet
by Xcellers • 26th August 2019
A key challenge for Classic raiding guilds is heavy administrative tasks, involved in managing one or more 40-man raid groups. Adding to this challenge, is the strategic overview needed to successfully ensure the fair and transparent distribution of loot in Loot Council based settings. To this end the Mk. Ultra spreadsheet seeks to provide a intuitive & comprehensive platform for this, easing the administrative burden on officers, while ultimately benefiting the guilds member base.
NB. Guide Materials and Public Version is currently outdated, and as there is a big push for the next major content update before ZG launches, it will remain outdated for the time being. The current version changes are covered in the newsletter that went out to all users and is linked in the discussion - for reference you can find the info here: https://mailchi.mp/55bf7db05ecf/mk-ultra-guild-management-sheet-version-17-update
Important Notice - July 2020 - Due to IRL obligations, then the major 2.0 content overhaul is unlikely to be launched in advance of AQ, additionally, expect extended delays in getting access to the sheet in the coming months. I will be deploying to a diplomatic mission in Africa for the next foreseeable future and as you might expect, my focus is on that. I am still hoping to manage everything, but fair warning given!
images/guides/rg_contents.png
Introduction
Spreadsheet Overview
Basics
Public Test Version
Usage
Updates
HOW-TOs
FAQ
Credits
intro
/images/guides/wp_intro.png
The Mk. Ultra Guild Management Spreadsheet consists of a range of core elements, tracking valuable information on guild composition, crafting capabilities and heirachy to facilitate a single-visit location for all daily information needs. As noted, the spreadsheet maintains a design focus onloot council based guilds, as these adhere to less rigid formats for loot management, than the strict point accounting systems used in DKP settings.
The spreadsheet is specifically designed as a non-profit to non-profit, community oriented project, and as such the target audience is solely guilds operating without any economic interests tied to the game. This stance means that all users will be screened, providing practical information to facilitate the correct version of the sheet, opt in/out for updates and general administrative information. For more information on getting access to this guild management tool for your guild, please see the usage section.
As as passion-project, the Mk. Ultra is intended for running updates, as the current configuration of the the spreadsheet requires users to copy a faction specific sheet to their own google drive, and populate/implement it from there, it also means that updates has been decentralized. This means, that guilds who opt in for updates, will receive occasional update information with clear guidelines on updating the sheets. The first waves of these updates has already been successfully deployed and a number of requests are already being converted into new, fresh updates for the sheet. For those wishing to avoid this kind of hassle, then the initial invite version you will receive, will always be updated to the newest version, and each release can be tracked via. the guide tab.
section1
/images/guides/wp_overview.png
Below is a run-through of how the sheet is currently configured, modules are still being released so this overview may be expanded in the future with commonly requested features such as attendance tracking, raid composition and similar.
At its core, the spreadsheet will share data between tabs, basing itself around the initial Roster tab. The populated Roster tab will provide player data for all drop-down menus, and as a basis for all calculations across the sheet. The goal is to automate as many administrative tasks as possible, while still keeping the day-to-day operations as transparent as possible for members. It is highly recommended to protect most of the available sheets so that only guild officers have direct access to them, while still leaving them view-able by members, to ensure better oversight; notably in regard to loot distribution.
Roster
Data
Guild Info
Master Crafters
Loot Distribution
Compare
Signups
Raid Composition
Attendance
MC Assignments
MC
BWL
AQ40
Naxx
WBs
Ony
ZG
AQ20
Guide
The Roster is intended to be filled out by regular members in the following segments "Basic-, Class-, Crafting-, PVP-, Attunement- and Misc Info". The information can be sorted in whatever ways wanted, but by class gives the best overview. As soon as a member selects a class, the Roster will automatically assign them a corresponding colour, officers will however need to manually sort the sheet with regular intervals to keep things tidy.
A last segment of the Roster is the Officer Info, this is reserved for displaying special functions within the guild or to relay short information bits. The segment also has a tick box for gear checks, to provide an easy overview for Loot Councils if a player has adequately prepared for raiding, but pre-raid gearing. How this is approached in effect, is up to the individual guilds.
By default, the sheet supports up to 500 members in its current iteration,and faction specific versions are available.
Common Questions.
What is Gear Check?
Gear Check is a two-fold toggle, initially to enable Loot Councils a quick overview of which members maintain proper pre-raid gearing, and later, to ensure that members follow guild guidelines for enchants, consumables and buffs; this is optional, but it does factor in for many LC decisions
What is Quint?
Siphoning information from the Roster, the Data sheet provides easy oversight of the guild composition, this is mainly a neat feature, but is also useful for guilds setting specific roster targets, for recruitment purposes. Socials will not be counted in this overview, so they will not interfere with target values, if listed on the roster.
Set Targets can be adjusted to suit your specific recruitment needs.
The Guild Info sheet is a manually filled out sheet, typically locked to the GM, who can provide the guild with a strategic overview of the management core, and their responsibilities. The page can be adjusted to suit whatever heirachy is in effect for respective guilds, and is essentially a (highly recommended to use) playground that you can adjust as you see fit.
To easier facilitate member crafting needs, with member crafting capacities, the sheet also includes a Master Crafter list, these are your typical (tho by no means comprehensive) lists of desired items and services, that might be required in endgame settings. This tab can easily be expanded as you see fit.
The drop down lists on the sheet, are automatically populated based on roster list, and will be displayed in the order of your choosing (e.g. how you have your Roster sorted)
The Loot Distribution sheet is a locked sheet, that automatically imports guild members from the Roster Sheet, and performs automatic and instantaneous counts these usernames, in the following Raid Specific Sheets. The entire process serves to provide transparency to members, and to assist the Loot Council in handing out loot more fairly. As per Version 1.5 the Loot Distribution has been heavily expanded, and now provides an overview of Gear Check Status, Seniority, Attendance and additional loot statistics.
For more detailed oriented decision-making, it is possible to load up to four members into the Compare tab, here all the specific data is provided and a easy overview is attached in support.
Members with access to the sheet, can use the Signups tab to mark themselves ready for raiding. Declines are automatically assumed when a member chooses not to sign up.
Below is also the option for setting players MIA, providing an easy overview of who is extensively unavailable.
The Raid Composition tab is fed by the Signups tab, pulling available members to the Selector Overview, where Raidleaders can select the current raid force. The system is flexible, meaning that Raidleaders can build the raidgroup using building blocks from the Selector Elements. Each element provides a dropdown menu containing all signups from that specific class/spec combo. As the raid group and standbys are selected, the Export List will become populated with member data, this needs to be copied onto the attendance sheet for registering the group. The Export list will have grey markings when it moves into the standby segment.
From the Raid Composition tab, the Export list can be used as a basis for attendance tracking for successive raid-dates, this will feed the Loot Distribution tab with available data on number of raids and members attendance in such raids.
The assignment tab is a complex overview of typical raid functions, names can easily be found through dropdown lists containing the signup names, but can also be modified to automatically assign each specific slot a direct function, this does however require the guild to operate with standard raid configurations at all times.
Raid Specific sheets can be found for all available raids and world bosses, and all feed the Loot Distribution sheet to grant an overview of allocations.
For each raid, all viable drops, from all bosses and trash, is listed in the overview. generic blues, crafting recipes and similar are not supported as they tend to be handed out in a slightly different context than raid quality epics.
Data entry is suggested as locked to Officers and Privileged Users, and works by selecting a raid date by double-clicking in the date selector cells, and picking the raid date. As items drops through the course of the raid, the Loot Council can assign names to the items, as they are distributed, using an automatically populated list of members, much akin to the one under Master Crafters. Each time a name appears in these cells, will be credited to the Loot Distribution sheet.
section2
/images/guides/wg_basics.pngWhile the Mk. Ultra Guild Management Sheet is relatively intuitive to use, its advanced features for raid management can be a challenge to some users. As such, novice users are recommended to go through the basic operational guide, to familiarize themselves with the basic procedure.
Step 1. Ensure your Roster tab is populated with key data
Ensure that Player Names, Gear Check and Join Date are filled out properly
Step 2. Prepare your Signups Tab
Select the appropriate date and raid
Step 3. Await member signups
Ensure that your members are aware of the signup needs, and that they do in advance of your raid-planning.
Step 4. Verify your selector overview is populated
Data in the overview should correspond to your Signups
Step 5. Begin Force Building
Adjust the force composition to your demands; if you maintain a standard approach you can simply leave the tab as is, and just clear the signups when the raid is over.
Step 6. Populate your Raid Group and select Standbys
Drop down menus easily enables you to select viable members for your raid group; you can mark them off in the Selector Overview if needed to keep track.
Step 7. Ensure your Raid Group is properly configured
An extra check can save you a lot of trouble
Step 8. *NB. POST RAID* Once a Raid group is committed, copy out your Export List
The list contains all selected members in the Raid Force, e.g. your Raid Group + up to 20 Standbys
Step 9. Go to your Attendance tab
Step 10. *NB. POST RAID* Log Raid + Attendance
Select the appropriate date, and CTRL+SHIFT+V to paste your export list to the attendance tracker, this tab will feed your loot distribution overview with data. This tab can manually be adjusted, if needed.
Step 11. Prepare the Raid Assignments tab
Select Debuffs for your 16 available debuff slots. Select Raid Date, Select members responsible for world buffs, and dousing runes.
Step 12. Assign responsibilities
Assign players using the drop-down menus to specific tasks, and assign these players to debuff slots as required.
Step 13. Kill Bosses and Assign Loot
Add items as you go, should you have duplicate items that are not raid-rolled, then the sheet should support adding additional rows tho with the new compare feature, this is increasingly hard.
I also highly recommend that you only log Main Spec loot (BiS/Major+Minor Upgrades) and entirely ignore PvP+OS items, this gives you a more fair overview when performing loot distribution tasks, as values are relatively 1:1.
Step 14. Keep watch of the Loot Distribution tab
This tab is fed with available raid data based on your entries.
Step 14. If required, do a Compare
For increased detail in hard-to-decide scenarios, you are encouraged to load up, up to four members in direct comparison, providing more details and a faster overview of viable factors.
section3
Should you wish a closer look, I have a locked test version available, that you are free to browse to get a better feel for what the sheet might be able to offer you, and your guild.
Please visit the Public Test Version here.
Please do not request access to this version, request it as described below!!
section4
If you are interested in using the Mk. Ultra sheet for your guild, then I am naturally happy to oblige. As noted, this project is community oriented, and non-profit so you will not be requested to join any streams, channels, patreons or anything like that. I am happy with whatever real-life karma this might yield, but should this not be enough, then please see the guide sheet for excellent charities, that I would be happy to see supported!
The standard configuration for the use of Mk. Ultra, is to host a spreadsheet copy on your own google drives, and thus personally managing permissions, protections and the likes; it is technically possible for me to host it for guilds that prefer it, but with over a hundred guilds opting for the decentralized option of hosting it themselves, then this is by far the most convenient method for me to stick with.
To get the sheet, you will have to reach out to me and provide me with some basic information, as listed below.
Guild Name:
Faction:
Server:
Major (Professional) Streamers in guild? Y/N
Professional (Paid/Sponsored) guild? Y/N
Can I publicly list you as sheet users? Y/N
Would you like me to inform you of future updates Y/N
Contact Email (Google Docs/Drive connected):
Did you read and understand the limitations on sharing and usage?: Y/N
This information can be sent to me through pm here , via PM on Reddit or via discord Xcellers#1681 BUT PLEASE, use only a single point of contact!
It should be noted, that this is a non-profit service, intended for non-profit guilds and organisations, with no commercial interests. Furthermore, usage is personal and guild tied, and it is requested that you or your guilds do not share it with third parties.
Furthermore, I have to stress out the fact that I do expect users to follow the basic instructions provided, especially when involving the live versions of the sheet, that is accessed by hundreds of guilds, new and old. There has been a number of cases of unintentional vandalism, and what looks to be intentional sabotage on the sheets, which is totally unacceptable - I have poured hundreds of work hours into this project, and it leaves a sour taste when I have to recover/fix these easy-to-avoid issues, and when I have to spend my time sending out warnings for this kind of behaviour.
section5
As noted, the Mk. Ultra Guild Management sheet is by no means a final product, and while it has been viable and fairly solid since its initial version, there are always room for improvement. Below is an log over the updates deployed via mail to end-users, and updates to the main sheets that all users maintain access to.
subsection1
Version 1.2
The first update to the sheet has been deployed on 13.08.2019 - updating both Alliance & Horde versions to 1.2 and provides a fix to social members being counted in the recruitment targets on the Data sheet. Subscribers to updates has already received update info + guide, people who have opted out can request it via available channels, or simply copy over to the new version.
subsection2
Version 1.3
The second update to the sheet has been deployed on 14.08.2019 - this update is a per request update for larger guilds, for whom the 100 member cap is too little, the sheet has now been increased to 500. Subscribers to updates has already received update info + guide, people who have opted out can request it via available channels, or simply copy over to the new version.
subsection3
Version 1.4
The third update to the sheet has been deployed on 18.08.2019 - this update fixes a minor issue on Loot Distribution where the titles for the Ony/WBs had been switched around, it also adjusts the Master Crafter tab adding/removing a number of items. Subscribers to updates has already received update info + guide, people who have opted out can request it via available channels, or simply copy over to the new version.
subsection4
Version 1.5
The fourth update to the sheet has been deployed on 25.08.2019 - this is a major update to the Mk. Ultra, adding in four new tabs; Signups, Raid Composition, MC Assignments and Attendance, and further, adding new functionalities to the Roster and Loot Distribution tabs. This update covers the most requested feature for the sheet, and should hopefully provide a firm basis for guilds to launch their endeavours into the Molten Core.
subsection5
Version 1.6
The fifth update to the sheet has been deployed on 25.10.2019 - this is a complex quality of life update, adding improved tools for loot councils for comparing 1-4 candidates for a specific item, it further adjust attendance to be based on raids-since-join-date and adds a lot of minor improvements to the sheet.
subsection5
Version 1.7
The sixth version update for the Mk. Ultra released on 11.03.2020, in anticipation of the launch of WoW Classic Phase 3. The update is a big performance upgrade, and introduces a more strict system for the sheet, so more advanced features can be added in. Based on user research, the sheet has had its Signup/Raid Builder/Attendance system overhauled, and increased automation so make these features easier to use, through the use of Scripts.
section6
Recurring issues and minor will get short how-to guides, if its a more elaborate issue, then just get a hold of me, and i will do my best to get you sorted! Guides for updating is broadcast directly to users via email, as these require interaction with editable sheets, that will break if mismanaged by the general public. Should you have opted out from updates, but with to update anyways, you can request access to the guidelines through alternative channels.
subsection7
Edit Ranks
Should the default ranks not suffice for your guild composition, then you can easily edit the rankings as below, and repopulate your roster with suitable rank levels. This will impact rank calculations across the rest of the sheet, so you will have to adjust formulae to facilitate this change.
NB. If you edit ranks, you will manually have to update the COUNT BOX formula on the DATA tab to accept your new values
subsection8
Recover Class Icons
Should you, for whatever reason, find yourself in need of Class Icons for the Guild Info tab, then a backup has been conveniently hidden, a few scrolls down. The icons are copy/paste friendly so adjusting the page to your own needs should be easy.
subsection9
Adding Week Numbers to Raids/Attendance
If you wish to add week numbers to any of the dated fields in the sheet, such as Raids or Attendance, then you can use the following approach to add this feature. Sadly the default display value will neither be blank or zero, but instead be 52; this can be circumvented by setting up to hide but will effectively also hide this week for any raids during late December, so i do not recommend that.
subsection10
Adding profession specialisations
The initial versions of the sheet does not distinguish between the two blacksmithing specialisation in arms and armour, this has been hot fixed in relations to the version 1.5 deployment, but guilds using older versions can follow this guide to update the Roster tab to reflect this change.
Alchemy,BS(Armour),BS(Weapon),Enchanting,Engi(Gnomish),Engi(Goblin),Herbalism,LW(Tribal),LW(Elemental),LW(Dragonscale),Mining,Skinning,Tailoring
subsection11
Switching to Simple Attendance Tracking instead of Raid Builder mode
Not all guilds maintain an interest in using the complex attendance tracking attached to the raid group builder, for those, a simple fix can be implemented to add drop-down menus to the attendance list.
Roster!A3:A502
subsection12
Adjusting Time Formats
Go to File > Spreadsheet Settings
Select appropriate Locale and Timezone
Save Settings
subsection13
Cleaning up Unused Tabs
The sheet comes prepared for incoming phases and contains features that are redundant for certain guild settings, this means that certain sheets will just take up space and be eyesores. As deleting anything is likely to fuck things up grandly, then you can simply hide (and later recover) tabs a your leisure.
subsection14
Hotfix the Compare Tab
The Compare tab provides a on-the-fly comparison tool for loot councils, but is rather sensitive. A large number of these issues are due to the code going out of bounds, or by row corruption.
The fix is simple: simply copy/paste the the code segment from your linked master version, and the code should be reset to default (uncorrupted) values and it should work - if not, then contact me, and I will try my best to troubleshoot the issue
If you experience issues with the associated dates, then its sadly a ongoing issue that I am unsure how to fix, if you find a solution, then please let me know, so i can get it live!
subsection15
Enable Scripts
To run the more advanced features on the Mk. Ultra sheet, you will need to enable scripts. This is done quite easily by going to your Raid tab, and pressing CLEAR. This will produce a pop-up window, requesting permissions to run Mk. Ultra scripts on your google account. These scripts are perfectly safe and are only used on this particular spreadsheet.The process is covered below.
subsection16
Add room for duplicate items
You may, quite likely, run out of space to list duplicate item drops at various stages of the game. The sheet fully supports added rows, and the only restriction here is that you are required to name each item uniquely. My recommendation is to simply add a space at the end of each item name, beyond the first instance/listing. See below for example
=HYPERLINK("https://classicdb.ch/?item=19434","Band of Dark Dominion")
=HYPERLINK("https://classicdb.ch/?item=19434","Band of Dark Dominion ")
=HYPERLINK("https://classicdb.ch/?item=19434","Band of Dark Dominion ")
=HYPERLINK("https://classicdb.ch/?item=19434","Band of Dark Dominion ")
=HYPERLINK("https://classicdb.ch/?item=19434","Band of Dark Dominion ")
section7
/images/guides/wp_faq.png
Why is it called Mk. Ultra?
MK. Ultra (MKUltra) was a CIA mind control program running in the 1950/60/70s, I wont spoil the story, but recommend you putting on one of the many documentaries on the subject, while you level your characters. The tie in here is less nefarious than the original CIA covert project, as the goal here is not mind control, but rather to provide strong and professional looking infrastructure to guilds, so that it rubs off on members; look pro, think pro, feel pro.
Response Rate
After churning through a few hundred messages back and forth, it seems the worst hurdle has passed; I am CEST based (Denmark) so I can normally be reached during normal EU hours - and I am definitely doing my best to get back to you!
Contact Info
You can reach me through pm here , via PM on Reddit or via discord Xcellers#1681 - this is in order of preference so if at all possible, then I do prefer a message via the barrens.chat to keep things tidy.
Can I share this sheet with others?
In short; No. - you are most welcome to direct them here and they can get a copy through me, the same way that you got it - but requests are personal and intended for you and your guild.
Guilds Using the Mk. Ultra
Currently, the Mk. Ultra is deployed to 1100 guilds, split almost perfectly between the two factions and across all regions. Should anyone be interested in provided translated versions of the guildsheet then feel free to let me know, and we can get you sorted for that monumental task!
section8
Raid Banners, Courtesy of Teebling at The Barrens Chat
Class Icons, Courtesy of WowPedia
Compare Coding, Lidlwalker from Odobenus Rosmarus, Firemaw EU
Attendance Coding, Chumppi from Seven Sins, Golemagg EU
Happy to see the big Mk. Ultra guide made it live after the patch, for those not using it, it has been heavily updated across a handful of content updates, and now has a whole lot of added support for attendance tracking, raid management and added metrics for loot council decision making.
If you aint already on Mk. Ultra, then i highly recommend taking a look at the features and seeing if its a match for your guild!
Very nice work!
Very nice work!
Thanks Caperfin means a lot, coming from someone so invested in the community!
Update/added guide: Switching to Simple Attendance Tracking instead of Raid Builder mode
Guild Name:Night Lords
Faction:Alliance
Major (Professional) Streamers in guild? N
Professional (Paid/Sponsored) guild? N
Can I publicly list you as sheet users? N
Would you like me to inform you of future updates Y
Contact Email (Google Docs/Drive connected):
Snip
Hey filofteia
I would be happy to help you guys out, but ill need an email account to share it to, and i think it might be best if you sent me that in a PM :razz:
Just hit guild no. 200 adopting the Mk. Ultra for guild management! :lol:
Just hit guild no. 200 adopting the Mk. Ultra for guild management! :lol:
Grats Xcellers, nice milestone there!
Just hit guild no. 200 adopting the Mk. Ultra for guild management! :lol:
Grats Xcellers, nice milestone there!
Thanks a bunch teebling its about 10 times as many as i anticipated back when i decided to let it go beyond my own guild at the time, so its quite something!
This spreadsheet is extremely well done and in depth. It's not insanely hard to pick up on either! Thanks again for the hard work
This spreadsheet is extremely well done and in depth. It's not insanely hard to pick up on either! Thanks again for the hard work
Really happy to hear that its been a match for you guys iiuseless - if you come across any issues, dont hesitate to let me know so we can get them ironed out!
Hey Xcellers,
Great work, spreadsheet is looking better and better with every update. Sent you a PM with some questions.
Cheers!
Hey Xcellers,
Great work, spreadsheet is looking better and better with every update. Sent you a PM with some questions.
Cheers!
Happy to hear you guys are enjoying it Ken!
Great resource for guilds! Many thanks Xcellers!