7WBS2010 Operations and Business Analytics Coursework 2 Question

Description of the assignment:

You are employed as a consultant for the Students’ Union Shop of the University of Puddletown and should:

Report on their current business, pointing out any existing problems;
Suggest improvements to their current strategies;
Provide them with an efficient tool for easily evaluating their current performance using the data provided or new data in a similar format.

You are required to produce a model using Excel, which could be used by the Students’ Union Shop management team for evaluation of their performance using the data provided or any new data that becomes available in a similar format. The model should be supported by technical documentation, which should give details of the analysis method used, include a description of how it was implemented in a spreadsheet and a brief description of how to use the spreadsheet. You are also required to produce a main report which should review the current situation of the Students’ Union Shop queueing system and suggest ways of improving the service level. You must give a convincing argument to support the improvements you are suggesting, e.g. by showing the cost-benefit of adding an extra cashier in terms of staff utilisation and customer waiting time.

The Students’ Union Shop management team are very busy and so value concise reports. Each report should be no more than 10 pages, and the total number of pages should not exceed 15.

Please see the attached assignment document for full details and marking criteria: …………………….

Mark scheme:
Please see the marking rubric on the module page for details

Citation

• For all references, use Cite it Right Harvard style.

Assignment Overview

• Title: Individual Report & Excel Modelling
• Word/Page Limit: No more than 10 pages each and a Maximum 15 pages in total

• Weighting: 50% of module grade

Case Study:

Students’ Union Shop

Assessing cashier needs for optimal service

• Current setup: 3 cashiers, can expand to 6

Challenges

• Wait times, efficiency, staff turnover

Cost

• Extra cashier costs £20/hour

Goal

• Optimise queuing system
• Busy rate, Average number of customers in the queue, average number of customers (system and queue), average waiting time (system and queue), etc.

Assignment Breakdown

Consultant role for Students’ Union Shop:

• Report on current queuing system
• Suggest improvements using analytics
• Develop Excel model for evaluation (submit)
• A report in Word (submit)

Excel Model

• A suitable queueing model (M/M/S)
• A working model
• Shop management able to evaluate performance using any new data in a similar format
• Initiation/creativity, e.g. graphs, presentation

What is Queuing Theory?

• Study of waiting lines to optimise efficiency
• Key metrics
• Arrival rates, service rates, queue length, wait time, busy rate, etc.
• Application
• Retail, banks, call centers, hospitals
• Relevance
• Improves customer satisfaction & operations

Applying Queuing Theory

• Use the two Excel files “Arrival Data” and “Service Time Data”
• Find the average customer arrival rate and service rate
• Spot busy periods (if they exist): find λ during busy and quiet periods.
• Spot outliers in the service time data (if they exist): argue to include/exclude
• Analyse the shop’s current queuing system (using 3 cashiers)
• Apply queuing models to assess performance
• Make reasonable adjustments when/if needed

Report Structure

• Introduction
• Overview & aims
• Methodology
• Use of Excel, models, formulas, identification of outliers in service time data (include/exclude), etc.
• Current System Analysis
• Measuring average customer arrival rate, service rate, busy periods (finding λ during busy and quiet periods)

Report Structure (Cont.)

• Proposed Solutions
• Implement models & suggest improvements
• Excel Model & Documentation
• Can use visuals in the form of an appendix in the main report too
• Conclusion
• Recommendation on the number of cashiers required

Marking Criteria

Main Report

• Clarity
• Accuracy
• Technical content 
  Technical Report & Spreadsheet
• Documentation
• Usability & Validation
• Functionality
• Creativity

1. Measures of Location for Raw Data

Measures of location give a typical value for a data set.

Mean (‘average’).
This is the most common measure used.
\[\text{Find the total of all the data}\]
\[\text{Mean} = \frac{x_1 + x_2 + x_3 + \cdots + x_n}{n} = \frac{\sum X}{n}\]
To find the mean using Excel use = AVERAGE( *select data*)

Median
Arrange the data in increasing order and take the middle one. It is the (n+1)/2 th term.
(You may need to average the middle two values).
To find the median using Excel use = MEDIAN( *select data*)

Mode
The value which occurs most often.
To find the mode using Excel use = MODE( *select data*)

Mean (Average)
The mean or the average is a measure of central tendency that offers a general picture of the data

Example
The following table reports the ages of a sample of five patients.

The average age of these patients equals 20.

Median

The central item in a group of observations when they are arrayed in either an ascending or a descending order

Example
The annual salaries of nine employees are sorted as follows.
£16,000–£17,500–£19,200–£20,400–£24,500–£29,800–£32,000–£37,800–£46,000.
The median is £24,500.

Mode

Mode is the group of £20,000–£29,999.

Summary and Excel functions

• Measures of location
  (Excel Functions are shown in brackets)
• Mean (=AVERAGE)
• Median (=MEDIAN)
• Mode (=MODE)
• Measures of spread
  (Excel Functions are shown in brackets)
• Range (=MAX – MIN)
• Standard deviation (=STDEV)
• Variance (=VAR)

Assessment Criteria

Learning Outcomes: Knowledge and Understanding assessed in this assignment:

• Evaluate the role of data, analytics and associated technologies in adding value to a variety of organisations
• Appraise the appropriate use of business analytics to support operational decision-making to create and add value to a business

Learning Outcomes: Skills and Attributes assessed in this assignment:

• Formulate, evaluate and reflect on operations management issues to be able to determine which business analytical technique is most beneficial
• Critique the importance of information sharing within the organisation and appraise the impact of this information on decision-making within an organisation
• Determine the appropriate tools and make recommendations to inform and support strategic decision-making