Analytics Module 1 Comprehensive Assignment
aujam253
2-7-2019StrategicSourcingQDMA1-Module1Assignment.docxStrategic Sourcing
Quantitative Decision-Making and Analytics Module 1
Comprehensive Assignment (2/7)
Please submit along with your Excel file.
If you have forgotten the name of Excel formulas, please see the lecture note I distributed on 2/5.
1. What is the most likely outcome from BackOrderData case? How would incorporate the result into you inventory (purchasing/preparation) decision? Please offer a comparison before and after your decision is augmented through the result.
2. Please normalize following data first. (Use Standardize() formula)
a. X = 34, Mean = 45, STDev.P = 6
b. X = 678, Mean = 890, STDev.S = 60, n = 36, degree of freem = 35
3. What does the normalized value tell us (in question 2) about the probability of being smaller than the value?(hint: see visualization below.)
a. Hint: =Norm.S.Dist(NormalizedValue of a, True)
b. Hint: = T.Dist(NormalizedValue of b, df = 35, True)
4. Please review SafetyStock data. Each day, the company TTM receives same quantity of shipment from its supplier. However, every day, there will be uncertain number of items shipped DOA (dead on arrival, i.e., arrived damaged.) To prevent further loss occurring from the sales, TTM plans to keep some safety inventories in its warehouse. The data file is the historical record of DOA quantity during the past 13 months. Let us consider following questions.
1) Please find out probability of following number of DOA. (hint: PivotTable will help you save time.)
|
DOA Scenario |
Probability of Number of No-show |
|
0 |
|
|
1 |
|
|
2 |
|
|
3 |
|
|
4 |
|
|
5 |
|
2) Calculate the expected value (or the most likely DOA quantity in long-run). (hint: need to use SUMPRODUCT formula.)
3) Please use the expected value as the safety stock quantity in the same data. See how many losses in sales you could have prevented. (hint: may need to use IF formula.
IF((safety stock - DOA) > 0, DOA, safety stock)) since it is a different context, the logic is a little different than the class demo.)
Total Loss in Sales Due to DOA = 693
Total Prevented Loss when Safety Stock is Used =
5. Please open DistanceTraveled data and answer following questions.
1) (BONUS QUESTION) Please collect three group of random samples (each with size of 30) from the DistanceTraveled variable. What is the average of each sample? What is the average of all three averages? (hint: for sampling, the following data analytics module needs to be used. For further assistance, please see https://www.youtube.com/watch?v=5XrJcFmbpWI 1 min. 32 sec. You will need to use random sampling.)
|
|
|
Average of first sample of 30 =
Average of second sample of 30 =
Average of third sample of 30 =
Average of all three averages above =
2) Calculate the actual average of DistanceTraveled variable (for all 365 observations). Then compare it with the result in #(1). Which result in #5 is in the closest range with the actual mean? Intuitively, which one should be the closest one? Please share your thoughts. Any thoughts/reasonings is welcome!
3) Assuming the numbers in DistanceTraveled variable is a population data. Therefore, you can use STDEV.P() to calculate the standard deviation of the population. If the factory is trying to decide a defective part threshold such that only the lowest 5% of total production is considered as “defective”, what should that threshold value be? (hint: the value should be higher than the lowest 5% of total production. Try, =NORM.INV(probability, mean, stdev))
4) If a product has a measured DistanceTraveled value 98, is this part defective? (hint: run = Norm.dist(value, mean, stdev), is the probability smaller than 5%?)
5) Draw bar charts with DistanceTraveled and DistanceTraveled_R variables separately and paste each in the space below. (hint: Please use following grouping configurations in PivotTable.)
|
|
|
|
For DistanceTraveled |
DistanceTraveled_R |
6) Based on the visual inspection, do you think these two groups of data are generated by the same process?
