Paladin Holy
User avatar
EU Firemaw
Posts: 54
Likes: 48
Human
Paladin
2 weeks ago (1.13.2)
 •  Unread

Image

Mk. Ultra - Comprehensive Guild Management Spreadsheet
by @Xcellers • 26th August 2019
Image
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.

Image
Introduction
Spreadsheet Overview
Basics
Public Test Version
Usage
Updates HOW-TOs FAQ
Credits
Image
Image
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.
Image
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.


Image

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?
SpoilerShow
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?
SpoilerShow
Image
Image

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.
Image

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.
Image

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)
Image

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.
Image

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.
Image

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.
Image

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.
Image

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.
Image

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.
Image
Image
Image
Image
Image
Image
Image
Image
Image
While 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
SpoilerShow
Image
Ensure that Player Names, Gear Check and Join Date are filled out properly
Step 2. Prepare your Signups Tab
SpoilerShow
Image
Select the appropriate date and raid
Step 3. Await member signups
SpoilerShow
Image
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
SpoilerShow
Image
Data in the overview should correspond to your Signups
Step 5. Begin Force Building
SpoilerShow
Image
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
SpoilerShow
Image
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
SpoilerShow
Image
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
SpoilerShow
Image
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
SpoilerShow
Image
Step 10. *NB. POST RAID* Log Raid + Attendance
SpoilerShow
Image
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
SpoilerShow
Image
Select Debuffs for your 16 available debuff slots. Select Raid Date, Select members responsible for world buffs, and dousing runes.
Step 12. Assign responsibilities
SpoilerShow
Image
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
SpoilerShow
Image
Add items as you go, should you have duplicate items that are not raid-rolled, then the sheet should support adding additional rows
Step 14. Keep watch of the Loot Distribution tab
SpoilerShow
Image
This tab is fed with available raid data based on your entries.
Image
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, it is not updated, full of bugs and it is for display purposes only!
Image
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:

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):

This information can be sent to me through pm here , via PM on Reddit or via discord Xcellers#1681

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.
Image
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.


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.


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.


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.


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.
Image
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.


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.
SpoilerShow
Image
NB. If you edit ranks, you will manually have to update the COUNT BOX formula on the DATA tab to accept your new values


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.
SpoilerShow
Image


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.
SpoilerShow
Image


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.
SpoilerShow
Image
Alchemy,Blacksmithing (Armour),Blacksmithing (Weapon),Enchanting,Engineering (Gnomish),Engineering (Goblin),Herbalism,Leatherworking,Mining,Skinning,Tailoring

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.
SpoilerShow
Image
Roster!A3:A502
Image

Image
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.

Image

Image
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!

Image

Image
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.

Image

Image
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.

Image

Image
Guilds Using the Mk. Ultra

Currently, the Mk. Ultra is deployed to 200 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!

Image
Raid Banners, Courtesy of @Teebling @ The Barrens Chat @ https://barrens.chat

Class Icons, Courtesy of WowPedia @ https://wow.gamepedia.com

   Naturefly Hodor
- Xcellers
|Paladin|Firemaw EU-PVP|
Mk. Ultra - Comprehensive Guild Management Spreadsheet
A comprehensive tool for guild managers maintaining rosters & loot distribution in LC based guilds
ImageImage

Vault of the Silverhands, A Gear Primer for Holy Paladins
A progressive and customisable item, enchant and buff overview for Holy Paladins
Image
Paladin Holy
User avatar
EU Firemaw
Posts: 54
Likes: 48
Human
Paladin
2 weeks ago (1.13.2)
 •  Unread

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!

- Xcellers
|Paladin|Firemaw EU-PVP|
Mk. Ultra - Comprehensive Guild Management Spreadsheet
A comprehensive tool for guild managers maintaining rosters & loot distribution in LC based guilds
ImageImage

Vault of the Silverhands, A Gear Primer for Holy Paladins
A progressive and customisable item, enchant and buff overview for Holy Paladins
Image
Badlands
User avatar
EU Flamelash
Posts: 237
Likes: 108
Horde
Shaman
2 weeks ago (1.13.2)
 •  Unread

Very nice work!

Image  Image  Image  Image
Youtube Channel: https://www.youtube.com/channel/caperfin
Paladin Holy
User avatar
EU Firemaw
Posts: 54
Likes: 48
Human
Paladin
2 weeks ago (1.13.2)
 •  Unread

Caperfin wrote:
2 weeks ago
Very nice work!
Thanks @Caperfin means a lot, coming from someone so invested in the community!

- Xcellers
|Paladin|Firemaw EU-PVP|
Mk. Ultra - Comprehensive Guild Management Spreadsheet
A comprehensive tool for guild managers maintaining rosters & loot distribution in LC based guilds
ImageImage

Vault of the Silverhands, A Gear Primer for Holy Paladins
A progressive and customisable item, enchant and buff overview for Holy Paladins
Image
Paladin Holy
User avatar
EU Firemaw
Posts: 54
Likes: 48
Human
Paladin
1 week ago (1.13.2)
 •  Unread

Update/added guide: Switching to Simple Attendance Tracking instead of Raid Builder mode

- Xcellers
|Paladin|Firemaw EU-PVP|
Mk. Ultra - Comprehensive Guild Management Spreadsheet
A comprehensive tool for guild managers maintaining rosters & loot distribution in LC based guilds
ImageImage

Vault of the Silverhands, A Gear Primer for Holy Paladins
A progressive and customisable item, enchant and buff overview for Holy Paladins
Image
User avatar
EU Pyrewood Village
Posts: 1
Likes: 0
Alliance
Rogue
5 days ago (1.13.2)
 •  Unread

Xcellers wrote:
2 weeks ago
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):

Paladin Holy
User avatar
EU Firemaw
Posts: 54
Likes: 48
Human
Paladin
4 days ago (1.13.2)
 •  Unread

filofteia wrote:
5 days ago
Xcellers wrote:
2 weeks ago
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:

- Xcellers
|Paladin|Firemaw EU-PVP|
Mk. Ultra - Comprehensive Guild Management Spreadsheet
A comprehensive tool for guild managers maintaining rosters & loot distribution in LC based guilds
ImageImage

Vault of the Silverhands, A Gear Primer for Holy Paladins
A progressive and customisable item, enchant and buff overview for Holy Paladins
Image
Paladin Holy
User avatar
EU Firemaw
Posts: 54
Likes: 48
Human
Paladin
3 days ago (1.13.2)
 •  Unread

Just hit guild no. 200 adopting the Mk. Ultra for guild management! :lol:

   teebling
- Xcellers
|Paladin|Firemaw EU-PVP|
Mk. Ultra - Comprehensive Guild Management Spreadsheet
A comprehensive tool for guild managers maintaining rosters & loot distribution in LC based guilds
ImageImage

Vault of the Silverhands, A Gear Primer for Holy Paladins
A progressive and customisable item, enchant and buff overview for Holy Paladins
Image
Warrior Protection
User avatar
EU Hydraxian Waterlords
Posts: 1257
Likes: 2426
Horde
Warrior
3 days ago (1.13.2)
 •  Unread

Xcellers wrote:
3 days ago
Just hit guild no. 200 adopting the Mk. Ultra for guild management! :lol:
Grats Xcellers, nice milestone there!

Paladin Holy
User avatar
EU Firemaw
Posts: 54
Likes: 48
Human
Paladin
2 days ago (1.13.2)
 •  Unread

teebling wrote:
3 days ago
Xcellers wrote:
3 days ago
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!

- Xcellers
|Paladin|Firemaw EU-PVP|
Mk. Ultra - Comprehensive Guild Management Spreadsheet
A comprehensive tool for guild managers maintaining rosters & loot distribution in LC based guilds
ImageImage

Vault of the Silverhands, A Gear Primer for Holy Paladins
A progressive and customisable item, enchant and buff overview for Holy Paladins
Image
Warrior Protection
User avatar
US Smolderweb
Posts: 3
Likes: 0
Tauren
Warrior
2 days ago (1.13.2)
 •  Unread

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

Paladin Holy
User avatar
EU Firemaw
Posts: 54
Likes: 48
Human
Paladin
1 day ago (1.13.2)
 •  Unread

iiuseless wrote:
2 days ago
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!

- Xcellers
|Paladin|Firemaw EU-PVP|
Mk. Ultra - Comprehensive Guild Management Spreadsheet
A comprehensive tool for guild managers maintaining rosters & loot distribution in LC based guilds
ImageImage

Vault of the Silverhands, A Gear Primer for Holy Paladins
A progressive and customisable item, enchant and buff overview for Holy Paladins
Image
Similar topics
to 'Mk. Ultra Guild Management Sheet'
Posts ViewsLast post