[SOLVED] Data Analytics Exercise 6
Im studying and need help with a Excel question to help me learn.
What should we do?
We are Three Dog Industries, Inc., a small company in the central United States that services and repairs high tech equipment for organizations all over the world. The type of equipment varies but includes medical monitors, security monitors, and inventory tracking monitors used in warehouses.
When these items malfunction or come up for planned service, our customers send these items to us, and they are assessed, repaired if needed, serviced (to include updating software), cleaned, and sent back to our customers. We have 50 full-time employees that do the work on the equipment, and another 25 employees that conduct administrative tasks, to include shipping and receiving, sales, accounting, etc..
The 50 equipment repair employees are given a daily quota (usually three to four) of devices to work on, using three different models of diagnostic equipment (the FLTWD MC, the EA GLS, and the ZZT 03). The diagnostic equipment is rather expensive (~$50,000 per unit). Additionally, with technological innovations happening all of the time, the repair employees have to constantly train on how to repair the equipment coming in from the customers. This training is very technical and time-consuming, taking the employee off the repair floor during the training time. Finally, all completed work passes through a Quality and Assurance (Q&A) department to make sure the device is ready to send back to the customer in prime condition. If all employees meet their quota for a given day, the company breaks even, so exceeding quota is just gravy for the company, but not at the expense of poor work.
Our company has been trying to improve productivity during the last fiscal year and was authorized to pay a $100 per day bonus to any employee that exceed their daily quota. Unfortunately, the improvement on productivity was not as much as the executives had hoped for over the time of the study. The Chief of Operations has asked you to look over some data that she has collected to see if you can identify any factors that affect the number of days that the employees have exceeded their quota of devices to repair.
In this exercise, students provide a business decision recommendation using SAS and Excel. You will prepare and submit a business memorandum that provides a recommendation based on your analysis of the dataset provided. Your COO has told you that any course of action you develop should address the cost of implementation, the time to implement, and the quality of repair services. Cost and time are important, but the quality of work is twice as important. Poor quality equipment repair means losing customers.
Be sure to:
- Address the memo to your instructor, date the memo, provide a meaningful subject line.
- Clearly identify the issue. Concisely state the research question(s), and provide the statistical question(s) you hope to answer.
- Provide descriptive statistics of the dataset.
- Conduct your analysis.
- Discuss your findings. Include any meaningful tables, charts, or figures. Make sure you put a caption on any table, chart, or figure you include in your memo, and make sure you reference any table, chart, or figure you include in your discussion.
- Based on your assessment of the data provided, identify at least three courses of action that company could make to improve productivity. Be creative, but logical and reasonable? what can be done within the limited resources of the company? Examples of courses of action might include: 1) paying more money to each worker, 2) getting more customers, 3) firing the COO, etc. (By the way, those are all bad examples).
- Evaluate your courses of action using the matrix provided in the memo template. Choose one of the COAs you developed as a recommendation to the COO. Defend your recommendation.
Upload your Business Memo to this assignment as a PDF file, using your last name and DAX6 as the file name (e.g. Bohler DAX6). Remember to be clear and concise, do not exceed four pages, use 1″ margins on all sides, Calibri, size 11 font. Use the template provided.
- WorkerID = Worker Employee Identification Number
- QuotaPlus = Number of days that an employee exceeded their repair quota and earned a bonus
- YWC = The number of years the employee has worked with the company
- Salary = The annual salary of the employee before any bonuses
- Training = The number of hours that the employee attended a training session
- EquipAge = The number of years that the primary diagnostic equipment used by the employee has been in service.
- EquipType = The type of diagnostic equipment used by the employee. All three models do the same thing, they are just made by different manufacturers.