Business Analytics Assignment: Capacity Planning, Transportation Problem, Process Control, and Inven

• The Assignment is marked out of 100 marks and is worth 8% of the coursework mark

• Please do not repeat questions as this inflates your Turnitin similarity score

• The marking rubric is available on Canvas

• The MS Excel Solver might not be capable to find the optimal solutions for all the exercises in this assignment. We recommend using OpenSolver (available on Lab computers or as a free add-on on GoogleSheets)

Well written, correctly referenced, and properly formatted reports are essential in all business environments. Hence it is crucial that you can apply professional formatting to a WORD document.

To get the highest mark for this part, make sure you follow the assignment layout template available on Canvas | Modules | Additional Assessment Info.

1. Capacity Planning 

The purpose of this part of the assignment is to assess your understanding of how spreadsheets can be used for capacity planning, and the role that it plays in different types of organisations.

Capacity Planning for Always Healthy:

Always Healthy manufactures a variety of medical products such as vaccines, injections, and medicines.They own a subsidiary company that employs specialist nurses who run flu vaccination pop-up clinics at Community Centres and at large companies across the Greater Auckland region.

Always Healthy faces variable demand for this flu vaccination service. They have prepared the following annual forecast of service units for the demand they expect each month for the next calendar year for the flu vaccination pop-up clinics. Assume, the company will employ 60 specialist vaccination nurses before Month 1.

The management of Always Healthy needs to decide for the next year how many specialist vaccination nurses to hire and fire at the start of each month, based on the following demand forecast: 

Assume that one nurse working for one month can provide a total of 100 vaccination service units during that period. Each nurse is paid $3,500 per month (+3% compulsory Kiwisaver contributions).

Only at the beginning of each month nurses can be hired or fired; no part-time employment is possible. Hiring each nurse costs $3,000 and firing each nurse costs $7,000.

a) Develop the least-cost plan that fully satisfies the forecast demand. Present this plan to the management of Always Healthy in a report that summarises your findings, your capacity plans,and gives a detailed recommendation based on your findings.

b) The manager has also asked you to discuss in the report the following questions:

Learning Objective

• How much money does the company save using this plan rather than a level capacity plan (also fully satisfying demand)? Show you calculation details (reasoning) to support (explain) your answer.

• What TWO methods could the company feasibly use in order to smooth demand? Which ONE of those would you recommend here and why?

• Reworking your least-cost plan – what savings could be made if the company outsourced the

HR function which would half the cost for hiring and firing of specialist vaccination nurses?What drawbacks would need to be considered in this specific business situation?

2. Transportation Problem 25 marks

Learning Objective:

The purpose of this part of the assignment is to assess your understanding of how spreadsheets can be used in optimisation to find a lowest cost answer to a common operations problem. You must also be able to demonstrate the ability to adjust the spreadsheet to account for changes in situations and understand how to analyse a changing situation.

Optimising transportation for Best Rentals:

Best Rentals is a car rental company with multiple branches around New Zealand. Each month, Best Rentals operations manager has to look at the customer bookings for the following month and decide how to relocate the vehicles across branches to satisfy all the demand. Table 2 shows the demand at each branch for the following month. Table 3 contains information about the current vehicle location.

Table 4 shows the relocation costs for Best Rentals to move vehicles between different branches.

a) Using this information, find the lowest total cost for Best Rentals to meet the demand at each branch.

Report to management on the total cost for the relocation. Suggest ONE capacity or demand management policy that could help the company reduce the number of relocations in the future.

b) Assume that Best Rentals plan to close their Dunedin branch. It means all the vehicles in excess to customer demand for the following month needs to be relocated from there. What will be the lowest total relocation cost with this constraint in mind?

3. Process Control 20 marks

Learning Objective:

The purpose of this part of the assignment is to assess your understanding of process control tools and their role in an organisation.

Process Control Charts for Super Hotel:

The performance of front desk staff at the Super Hotel office was assessed. During a week, the receptionists were observed six times each day (at randomly selected times). The activities that the receptionists were performing were identified and the times (in seconds) to service a customer were recorded as noted below.

a) What value of n (number of observations) should be used for computing the X-bar and R control charts?

b) Calculate the 3-sigma control limits for the X-bar and the R charts. Create charts for the X-bar and the R charts based on the values you calculated and the ones provided above.

c) After the control limits were established, another sample was taken and the following times were recorded (in seconds): 175, 128, 131, 161, 201, and 150. Is the process in control? Explain why or why not. Show calculations where applicable. 

4. Inventory Management 20 marks

Learning Objective:

The purpose of this part of the assignment is to assess your understanding of inventory management and the role that it plays in an organisation.

Inventory Management for University of Buckland:

a) The University of Buckland (UoB) consumes 10,000 whiteboard markers per year. The current policy is to purchase all the markers once a year. Discuss ONE advantage and ONE disadvantage of the current inventory policy.

b) Table 6 provides more information on whiteboard markers ordered by UoB:

Under this pricing offer, what is the optimal order quantity that minimises annual total inventory costs for UoB? Present your workings in a table format and also explain to management how you reached your solution.