ACCT804 Final Project Case MK Consulting Boston Division MK Consulting Boston Division (MCBD) is a division located in Boston, MA. The organization is a consulting firm and it is imperative that the division shows impressive revenues to the parent company. Equally important in the charter of the company is contributing to their \"community\" by having fund raising activities for non-profit organizations. A systems analyst from your firm has met with the president of MCBD and she has asked for your assistance in designing a database for her division. She would like the new database to keep track of the division employees, departments, and the activities of MCBD (both revenue generating and philanthropic). Below you will find a manuscript of a conversation that your systems analyst recently had with the MCBD president. Please read this information very carefully. Q: Please tell me about the MK Consulting Boston Division. A: MK Consulting Boston Division (MCBD) is a part of a very large international company, MK Headquarters that is located in Melbourne, Australia. Being a part of this larger company allows us to share best practices across the company. However, we are just interested in having you develop a database for our division here in Boston (MCBD). We generate dollars for two different reasons. We generate revenue that contributes to MK Headquarters bottom line, and we sponsor activities where the proceeds are distributed to non-profit organizations. I am proud to say that last year our division had revenues of $73 Million and contributed over $250,000 to non-profit organizations. Q: Let us talk about your employees. Please provide me with some details about MCBD's employees. A: We have great employees. We have 224 employees in our division here at MCBD. We want to keep a record of each employee's unique employee number, name, address, email, telephone number, commission rate, and the date that they became an employee of our division. It is also necessary to keep track of each employee's specific skills. Each of our employees has at least one skill and skills have unique names. Each skill also has a description. A skill can be recorded before the employee information. Employees at MCBD are one of three Employee types: Associate, Senior, and Partner. When joining, the employee immediately is an Associate. On his/her third (3rd) anniversary (three years in the organization), he/she becomes a Senior, and on his/her fifth (5th) third anniversary, he/she becomes a Partner. When MCBD was first started, there were only Seniors and Partners. We need to record the unique code and description of each of the three employee types. We also need to track the base salary for each Employee type. All employees of a specific type have the exact same base salary. 1 At some time during an employee's first (1st) month, each Associate is assigned a Senior to assist in his/her transition into the division. An Associate will only be assigned one Senior; however, a Senior may be assigned to assist multiple Associates, or it may be possible that a Senior does not have an Associate to assist. Once the Associate becomes a Senior, the assistance relationship ceases. Q: What else can you tell me about your new employees here at MCBD? A: All employees go through a \"new employee training\" soon after starting as an employee. We want to track the date that each employee completes his or her training. After training is complete (usually in the second (2nd) month of service here at MCBD), the employee applies to interview for a position in one of our departments. Q: Okay, I see. Employees are assigned to work in departments. Please give me more details about your departments and the department leaders. A: For each department, we need to record its department code (unique for each department), name, description, main contact phone number and fax number, as well as its manager. Each department has at most one manager. A department manager can manage up to three departments. Of course, not all employees are managers. During the yearly strategic planning meeting held every December, the Partners discuss the work that MCBD will be performing in the next fiscal year. The Partners establish additional division departments that they determine are necessary to maximize business opportunities in the following year. This means that departments can exist without employees. However, in order to limit the span of control, there are at most five employees in a department. Each department has department goals, if any. Each goal must be recorded. Additionally, the unique name and description of each department goal needs to be tracked. Department goals can be the same for different departments and can be entered before we record the department information. Each department also has at least one skill requirement. For an employee to be eligible to interview for a job in a department, they must possess at minimum one of the department skills. Each skill's unique name and the skill's description need to be tracked. Let me describe how the MCBD skills work. The Finance Department and the Travel Department both have multi-lingual as a requisite skill. However, the skill typing, that two of our employees have, is not a skill that any of our current departments have a need for, and is not a department requisite skill. However, typing is recorded since at least one employee possesses that skill. Another example: The skill plumbing is not a department requisite skill. Nor is plumbing currently a skill that any of our current employees at MCBD have; however, we still want that skill in the database. When an employee wants to interview for a position in a specific department, the employee's skills are compared to the specific department's skill requirements. As stated, employees must have at least one of the requisite department skills to interview for a job in the 2 department. Once this requirement is met, the employee can interview for the open position. Of course, we need to track the employees who are actually in each specific department. Q: That is quite interesting. What else can you tell me about your departments and the employee requirements that make-up its membership? A: We have three types of departments. A department must be of exactly one of these types. The department types are Company Transition, Technology, and Staff. You must be a Senior or Partner to be in a department of type Company Transition. Any of our employees can be in a department of type Technology or Staff. Each of our Department types has a unique code and a description. Let me describe the different types of our departments in more detail. Company Transition Departments A department of type Company Transition provides strategic solutions for companies going through transitions. For each Company Transition department, we need to keep a list of each company (non-MCBD) that the department has partnered with. This includes the partner company's unique name, the address, telephone number, contact person's name, and a description of the partner company. A partner company works with zero or one of our departments that are of type Company Transition. A Company Transition department may work with many partner companies. I remember back in 1999 when this department type was first organized, there were not any departments that were of this type for at least six months; now there are four. Technology Departments Each Technology department focuses on providing knowledge regarding a specific technology. Besides the department code (unique for each department), name, description, main contact phone number and fax number, we also need to record the specific technology that is the focus of the department. Staff Departments Staff departments mainly deal with internal management and administration issues. For each Staff department, besides the department code (unique for each department), name, description, main contact phone number and fax number, we also need to keep track of its yearly budget. Q: Okay, I see. Is there anything else about the departments that I should know? A: The 15 departments that we currently have at MCBD are also categorized by what we call an \"underlying function.\" All departments must have one of two functions, and the department information is entered after the function information. The department is categorized as either Revenue Generating or as Philanthropic. For each of these two categories, we need to record a unique code and a description. A department that is in either of the aforementioned two categories can be of any department type (Company Transition, Technology, or Staff). The departments that are in the Revenue Generating function do not sponsor any activities; they 3 just perform consulting and contribute directly to the company bottom line. However, the departments that are in the Philanthropic function departments have a main objective of bringing in money by sponsoring activities for specific non-profit organizations. The individual departments that are categorized in this function sponsor activities for a specific, or several non-profit organizations. However each non-profit organization works with one and only one Philanthropic function department. Each department that is a Philanthropic function department has a yearly collection target of $400,000. Each Philanthropic function department is assigned a division van and we need to keep track of the van's Vehicle Identification Number (VIN). Each activity is sponsored by exactly one Philanthropic function department. Each activity is given a unique name, a date and time it happens, how much money is eventually raised, and a location where the activity occurs. Most Philanthropic function departments sponsor many activities. But, there are some that have not sponsored any activity as of yet. Usually, several non-profit organizations are the recipient of proceeds from an activity; however, it is quite possible that there is only one beneficiary. In either case, MCBD needs to know how much (in dollars) from the activity goes to each recipient non-profit organization. In other words, the amount per non-profit organization per activity needs to be recorded. In our non-profit organizations list we have organizations that have not benefited from our activities yet. Here is an example of how activities supporting non-profit organizations work: The 2012 MCBD Formal (activity) proceeds went to New Hope, the Polar Club, and the Boys/Girls Club of Boston. New Hope received $20000, The Polar Club got $10000, and $10000 went to the Boys/Girls Club of Boston. Another activity was the 2012 MCBD Yearly Cruise. Proceeds of $27,000 from this activity were split evenly between the non-profit organizations: Jack and Jill, The National Urban League, and New Hope (the same group that benefited from the 2012 MCBD Formal). For each non-profit organization, we need to keep the organization's unique name, the address, a contact person's name, the telephone number, an email address, a fax number, and a short 2-3 sentence write-up on what the non-profit does. When sponsoring activities, the Philanthropic function departments usually place orders to buy items for the activity. We track all the items by recording each item's unique code and the name. Each department can place an order to buy certain items for a specific activity. The same item can be on multiple orders. We only record an item when we have an order for it. There could be multiple items on an order. We need to track the unique order number, the date for each order, which activity this order is for, and by which department this order is placed. The quantity also needs to be recorded for each item ordered. We do not have fixed pricing. Therefore, the price of the same item on one order could be different on another. This allows us to get quantity discounts and reduced pricing. (See the sample order form in Figure 1.) There may be multiple orders per activity, or none at all. However, each order is for exactly one activity and is placed by exactly one department. We need to keep records of all the 4 orders that the department placed (it is possible that a newly created department has not placed any orders yet). Q: Is there anything else that you think I should know? A: I think for now, that is enough. We are excited about seeing our new database in action. Thank you. Figure 1. Sample Order Order #: S988833 Order Date: 09/3/2009 Item Code 4 16 10 For Activity: MIS Ball Department: University Alliance Item 1 inch diamond Scrolls - 15 count Crimson Streamers 5 Qty 3 20 10 Unit Price $130.99 $ 15.19 $ 4.99