LGT5945 Assignment

Individual Assignment

The purpose of this assignment is to test your ability to use data and apply the quantitative methods to solve operations management problems.

Answer the following questions and submit your answers via both of the following ways by 23:59, November 16, 2025.

1.   Submit ONE PDF or WORD file containing both the answers and solution steps to:

Blackboard / Content / Assignments / Individual Assignment File Submission.

Your  submission title  and  file name  should be: Your_Name_LGT5945,  for  example: CHAN_Tai_Man_LGT5945.pdf.

Remember to write your name and Student ID on the first page of the submitted file.

2.   Submit your answers via this Blackboard entrance:

Blackboard / Content / Assignments / Individual Assignment Answer Submission

Instructions:

(i).   The   demand   data   needed   for   Question    1    can   be    found    in   the   Excel    file (LGT5945_Assignment_Data.xlsx) appended to this assignment on Blackboard. Open the Excel file, input your student ID into the cell “B1” of the sheet “ID”. Then, a set ofdata will be generated for you in the sheet “Q1” . You need to answer Question 1 using these data.

(ii). For the first submission requirement, you only need to submit ONE file. You don’t need to submit your Excel calculation file. You can type your answers or write your answers on paper and then scan them or take photos. Make sure to combine all your answers in ONE file.

(iii). In Excel, you can use these functions for calculation:

•   “=AVERAGE( : )”: calculate the average of a set of data;

•   “=STDEV( : )”: calculate the standard deviation of a set of data;

•   “=NORMSINV(SL)”: find the SL percentile of a standard normal distribution. That is, you can use this function to find the value z such that the probability that a standard normal random variable is less than or equal to z is SL.

•   “=SQRT( )”: calculate the square root of a number.

•   “=A1^A2”: calculate the value of A1 raised to the power of A2. For example, the formula “=2^3” returns a value of 8.

Reference for the Excel functions:

https://support.microsoft.com/en-us/office/average-function-047bac88-d466-426c-a32b-8f33eb960cf6

https://support.microsoft.com/en-us/office/stdev-function-51fecaaa-231e-4bbb-9230-33650a72c9b0

https://support.microsoft.com/en-us/topic/excel-statistical-functions-normsinv-3b33f03c-c50b-9d84-5269- 0dc85692d349

1.   (6 pts) Newsvendor problem. Lakeside Bakery, owned by Katherine, bakes fresh cupcakes every morning. The daily demand data in the past months are provided in the spreadsheet “Q1” (here we assume that the store intentionally stocked more so that it can observe the exact demand data). Each cupcake costs $12 to make and is sold for $30. Unsold cupcakes at the end of the day are purchased by a nearby nursing home for $6 each. Assume no goodwill cost.

a)   (2 pts) What is the optimal service level that should be set by Katherine? (Keep two decimal places in your answer.)

b)  (2 pts) Use the empirical distribution of the demand data, find the optimal number of

cupcakes that should be made every day. (Your answer should be an integer.)

[Instruction: Similar to the example in lecture notes, you should sort the historical demand in ascending order and calculate the cumulative frequency/percentage to obtain the empirical distribution of the demand. Then, you can find the order quantity that can result in the optimal service level.]

[Pay attention: The demand data in column B are function values from a hidden sheet and hence you may not be able to sort them directly in some versions of Excel. In this case, to avoid errors, you are suggested to copy these demand data and paste their values to somewhere else using “Paste Values”. Then sort your pasted data.]

c)   (2 pts) Suppose the cost of the cupcake increases due to raw material inflation. Assume  all else unchanged, the optimal daily production quantity __________ (fill the blank with increases / decreases / remains unchanged) as the cost of cupcake increases.

2.   (14 pts) Manner Coffee is a Shanghai-based Chinese coffee chain that is positioned as affordable specialty coffee. Unlike many chain brands that use fully automatic coffee machines, Manner insists on using semi-automatic coffee machines. Although this choice ensures the fineness and taste of coffee production, it also brings complexity and time cost to the production process. From grinding the powder, pressing the powder, clamping the handle to pressing the button for extraction, every step requires careful operation by the barista, resulting in relatively slow production of a single cup of coffee.

Furthermore, Manner's store area is usually small, which limits the number of employees. It can only improve human efficiency as much as possible and achieve the ultimate production efficiency with the least labor. In many Manner's small shops, baristas work alone.

To maintain the cost-effectiveness, the company has set a policy that one person (barista) will be assigned to a store with a daily turnover (daily sales) of less than x yuan (¥, Chinese currency), and two people will be assigned to a store with a daily turnover of x yuan or more. Management wants to control the average waiting time to get a cup of coffee (from the time a customer arrives to the time he/she gets the coffee) to no more than 10 minutes.

Suppose that the average processing time to make a cup of coffee is p = 120 seconds. Furthermore, consider the following simplifications and assumptions.

•   The price of a cup of coffee is ¥20.

•   Each customer purchases one cup of coffee only.

•   A store is open 10 hours per day.

•   The customer arrival process is stationary when the store is open.

•   The coefficients of variation are Cva   = 1 and Cvp  = 1.

For ease of expression, we refer to the average waiting time of customers to get a cup of coffee as the average flow time (T). Answer the following questions using the waiting line formula.

(a) (4 pts) For a store with a daily turnover of ¥3,600 yuan, estimate the average flow time.

(b) If one barista is assigned, what hourly arrival rate would result in an average flow time of 10 minutes? (6 pts)

[Hint: the average processing time is already given. You need to find the utilization that would result in a T= 600 seconds. Then, calculate the arrival rate that yields this utilization.]

What is the value of x that should be set by the management if the company wants an average flow time of no more than 10 minutes? (2 pts)

(c) (2 pts) For a store with a daily turnover of x yuan (i.e., the answer to part (b)), the company will assign two baristas. In this case, what is the average flow time? (For this part, express your answer in seconds.)

[Note: For all your calculation steps, please just use Microsoft Excel’s default setting and do not do rounding. Then, when you submit your numerical answers to Blackboard, keep two decimal places for your answers when there are decimals.]

3.   (10 pts) Watch the YouTube video at the link:https://www.youtube.com/watch?v=vT5-cV4oMY8 According to the video, briefly summarize the operational structure Aldi uses to support its “no- frills” strategy. [Word limit: 250]