Worksheet “Data” 1 Inventory Assignment: Okay to work with a partner or individu

Question

Worksheet “Data” 1 Inventory Assignment: Okay to work with a partner or individually (if you work with a partner, turn in only one spreadsheet) 2 Enter your student ID- 900000009 Name: Partner name: Paste your cumulative frequency table here: The campus bookstore wants to figure out the optimal number S of an operations management textbook to buy for the upcoming semester 6 The cost and price is as ollows 7 Cost/unit 8 Selling price/unit145.50 9 At the end of the semester,the leftover books can be sold back to the publisher 10 at cost less 10%. That is, they get 90% of the cost back 11 Put your answers here: 12 a. What is the shortage cost per unit? 13 14 b. What is the excess cost per unit? 15 16 C. What is the optimal service level? 17 18 d. The bookstore has historical data for this textbook for 30 semesters. See worksheet"Data" 19 Create a cumulative frequency table of the number of textbooks sold per semester 20 Paste your cumulative frequency table to the right 21 e. What is the optimal number of books to buy for this semester? Use the newsvendor 109.00 22 model for discrete demand 23

Explanation / Answer

a) Shortage cost per unit, s = Selling price - cost = 145.5 - 109 = 36.5

b) Excess cost per unit, e = Cost - buyback = 109 - 109*0.9 = 10.9

c) Optimal service level = s/(s+e) = 36.5/(36.5+10.9) = 0.77

d) Cumulative frequency table is created by couning the number of semesters when a particular number of textbooks were sold. Excel function FREQUENCY can be used to create the frequency table. FREQUENCY() is an array formula, therefore, it is entered by pressing CTRL+SHIFT+ENTER

Create cumulative probability table.

Probability is calculated by dividing the frequency of particular bin (number of textbooks sold) by the total number of semesters (30). So probability = Frequency/30

We look for cumulative probability value in the above table, which is greater than or equal to the optimal service level as determined in part c.

Cumulative probability F(z) just greater than 0.77 is 83.3%

It is associated with bin (number of books sold) = 54

Bin Frequency 37 1 40 2 44 2 45 2 47 2 48 5 50 5 52 3 54 3 55 2 56 2 58 1

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.