BU Daily Bagel Order Excel Spreadsheet

Question 4. (27 points)

Jacklin runs a small cafe at Monbis School, where, among other things including her cookies, she sells fresh bagels. She buys bagels every morning from a local bagel shop, Bagel World, on Dupont. She only sells fresh bagels and unsold bagels cannot be sold the next day. Bagel World offers Jacklin two purchasing options:

Option 1: Bagel World offers to set its price at $1 and agrees to buy back the unsold bagels at the end of the day for $0.5. Bagel World also only sells fresh bagels, hence the returned bagels have no value and donated to a homeless shelter.

Option 2: Bagel World offers to set its price at $0.75 but does not buy back the unsold bagels. Jacklin donates any leftover bagels to a homeless at the end of the day.

Daily demand for bagels is normally distributed with mean of 50 and standard deviation 10. Jacklin sell’s each bagel for $1.5 and it costs Bagel World $0.25 to produce each bagel.

Answer the following questions based on the information provided. Use the Excel spreadsheet for the Newsvendor Problem, if/when needed.

NOTE-1: SHOW ALL YOUR WORK. USE 4 DECIMAL PLACES IN ALL CALCULATIONS.

NOTE-2: Instead of using online tutoring services or websites to get the answers, you are encouraged to ask for assistance from your instructor and your TAs. We know how the problem should be solved. We know which formula needs to be used. We know the contents of the course. And, our services are FREE! Plus, seeking outside assistance or sharing the questions electronically or in other form with other teams, platforms, websites, messaging boards, tutors etc is in violation of the Academic Integrity Code of AU. See the first page for more information.

1. (15 points) Analysis for Option 1: Jacklin will use the newsvendor model to determine the order quantity of the bagels every day for Option 1. Jacklin is interested in maximizing the expected profits every day. Answer all the questions for Jacklin. You must show all the work you do as you calculate each value. You can also provide screenshots of any work you do in Excel. Make sure you include all inputs and outputs in your screenshot. After you complete the work, add your answers to the table below:

Unit cost of overage of Jacklin	0.75
Unit cost of underage of Jacklin	0.5
Critical ratio of Jacklin	0.4
Order quantity of Jacklin	47.4665
Expected amount of lost sales for Jacklin	4.85
Expected amount of sales for Jacklin	45.15
Expected amount of unsold inventory for Jacklin	2.85
Expected sales revenue of Jacklin	67.72
Expected cost of purchasing of Jacklin	48
Expected salvage value of Jacklin	0.71
Expected profit of Jacklin	20.44
In-stock probability of Jacklin	40%
Out-of-stock probability of Jacklin	60%
Expected fill rate of Jacklin	90.3
Expected profit of Bagel World

Table-Q5b. Inventory performance with the optimal newsvendor quantity

1. (1 point) What is the unit cost of underage for the product?

0.75

1. (1 point) What is the unit cost of overage for the product?

0.5

1. (1 point) What is the critical ratio (critical ratio) for the product?

0.4

1. (1 point) What is the optimal order quantity that maximizes expected profits?

47.4665

1. (1 point) What will be the expected amount of lost sales?

4.85

1. (1 point) What is the expected amount of goods sold?

45.15

1. (1 point) What is the expected amount of unsold inventory?

2.85

1. (1 point) What will be the expected sales revenue?

67.72

1. (1 point) What will be the expected cost of purchasing the goods from the manufacturer?

48

1. (1 point) What will be the expected salvage value?

0.71

1. (1 point) What will be the expected profit level?

20.44

1. (1 point) What will be the expected fill rate?

90.3

1. (1 point) What will be the in-stock probability of the product?

40%(0.4)

1. (1 point) What will be the out-of-stock probability of the product?

60%(0.6)

1. (1 point) What will be the expected profit of Bagel World if Jacklin’s order quantity is the value you calculated in part iv?

1. (9 points) Analysis for Option 2: Jacklin will use the newsvendor model to determine the order quantity of the bagels every day for Option 2. Jacklin is interested in maximizing the expected profits every day. Answer all the questions for Jacklin. You must show all the work you do as you calculate each value. You can also provide screenshots of any work you do in Excel. Make sure you include all inputs and outputs in your screenshot. After you complete the work, add your answers to the table below:

Unit cost of overage of Jacklin
Unit cost of underage of Jacklin
Critical ratio of Jacklin
Order quantity of Jacklin
Expected amount of lost sales for Jacklin
Expected amount of sales for Jacklin
Expected amount of unsold inventory for Jacklin
Expected sales revenue of Jacklin
Expected cost of purchasing of Jacklin
Expected salvage value of Jacklin
Expected profit of Jacklin
In-stock probability of Jacklin
Out-of-stock probability of Jacklin
Expected fill rate of Jacklin
Expected profit of Bagel World

Table-Q5b. Inventory performance with the optimal newsvendor quantity

1. (0.5 points) What is the unit cost of underage for the product?
2. (0.5 points) What is the unit cost of overage for the product?
3. (0.5 points) What is the critical ratio (critical ratio) for the product?
4. (1 point) What is the optimal order quantity that maximizes expected profits?
5. (0.5 points) What will be the expected amount of lost sales?
6. (0.5 points) What is the expected amount of goods sold?
7. (0.5 points) What is the expected amount of unsold inventory?
8. (0.5 points) What will be the expected sales revenue?
9. (0.5 points) What will be the expected cost of purchasing the goods from the manufacturer?
10. (0.5 points) What will be the expected salvage value?
11. (1 point) What will be the expected profit level?
12. (0.5 points) What will be the expected fill rate?
13. (0.5 points) What will be the in-stock probability of the product?
14. (0.5 points) What will be the out-of-stock probability of the product?
15. (1 point) What will be the expected profit of Bagel World if Jacklin’s order quantity is the value you calculated in part iv?

1. (3 points) Which option is better for Jacklin? Discuss by providing any numbers necessary based on your analysis in parts a and b.