LGT5105 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_LGT5105, for example: CHAN_Tai_Man_LGT5105.pdf.
Remember to write your name and Student ID on the first page of the submitted file.
2. Submit your answers to this Blackboard link:
Blackboard / Content /Assignments /Individual Assignment Answer Submission
Instructions:
(i). The data needed for the questions can be found in the Excel file (LGT5105_Assignment_Data.xlsx) appended to this assignment on Blackboard.
(ii). Input your student ID into the cell “B1” of the sheet “ID”. Then, a set ofdata will be generated for you in the sheets “Q1”, “Q2”, and “Q3” . You need to answer the questions using these data.
(iii). 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.
(iv). 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. (10 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.
d) (4 pts) The bakery is considering raising the selling price. Assume all other costs unchanged, how will the increase in the price influence the optimal daily production quantity? Briefly discuss. [Word limit: 60]
2. (10 pts) Economic order quantity (EOQ)
The daily demand for a product is given in the spreadsheet “Q2” . Assume the demand approximately follows a normal distribution. The ordering cost is $800 per order, and the lead time from ordering to receipt is 4 days. The unit cost of the product is $30. The inventory cost consists of both capital cost and physical holding cost: the annual capital cost of holding inventory is equal to 10% of the unit cost; the annual cost of inventory handling, obsolescence, spoilage, etc is equal to 12% of the unit cost. Assume 365 days in a year.
The operations manager decides to use the EOQ model to decide the order quantity per order and desire a service level of 0.9 in determining the safety stock during the lead time. Do the following to find the inventory policy using the data provided.
a) (2 pts) Using the data in column B, calculate the mean and standard deviation of the daily demand.
b) (4 pts) Calculate the EOQ.
c) (2 pts) If a service level of 0.9 is desired during the lead time, what is the corresponding z value?
d) (2 pts) Calculate the reorder point if a service level of 0.9 is desired.
[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 if there are decimals.]
3. (10 pts) Jacob, the operations manager of a service counter, recorded the customer interarrival times and service times (in seconds) during busy hours when the arrival process is stationary (data can be found in the spreadsheet “Q3”).
[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, follow the instructions to keep proper decimal places.]
(a) (4 pts) Using the data in column B, calculate the average and the coefficient of variation (cva) of the interarrival time.
Using the data in column C, calculate the average and the coefficient of variation (cvp) of service time.
[Keep two decimal places for the averages and four decimal places for the CV’s.]
(b) (1 pt) Based on your calculation in part (a), the system needs at least m = (fill an integer value to the blank) servers to guarantee a utilization below 100%.
(c) (5 pts) Suppose Jacob sets the number of servers equal to the m which you calculated in part (b). Calculate the following.
• The system utilization
• Customers’ average time in queue (express your answer in seconds)
• Customers’ average time in the system (express your answer in seconds)
• The average number of customers waiting in queue
• The average number of customers in the system
[Keep two decimal places in your answers.]