A local pharmacy orders 15 copies of a monthly magazine. Depending on the cover
ID: 453313 • Letter: A
Question
A local pharmacy orders 15 copies of a monthly magazine. Depending on the cover story, demand for the magazine varies. Historical records suggest that the probability distribution of demand is Poisson with a mean of 10. The pharmary purchases the magazines for $2.25 and sells them for $5.00. Any magazines leftover at the end of the month are donated to hospitals and other health care facilities. Model this newsvendor problem in Excel. Test your model to show the corresponding profit when demand for the month is 5, 10, 15 or 20 magazines.
please post excel and show formulas too please.
Explanation / Answer
This is a simple Newspaper boy Problem.
The newspaper boy Model = C1/(C1+C2) , Where C1 is the cost of unsold and C2 is the cost of the item
where Cost of Unsold C1= $5-$2.25 = $2.75
and Cost of item C2= $2.25
Newspaper boy Model = C1/(C1+C2) = 2.75/(2.75+2.25) = 2.75/5 = 0.55
This means the probability = 0.55
The area under Normal distribution when the toal probability = 0.55 is when Z is 0.13
As we know that Z= (X-Mean)/SD
It's given that, the distribution follows poisson with mean =10, then SD= Square root (10) = 3.16
(In poisson Mean and Variance are same)
Z=(X-Mean)/SD
0.13 = (X-10)/3.16
0.13*3.16+10 =X
X= 10.41 units
The optimal quantity to be purchased = 10.41 , approximately = 11 Units
The profit Model when optimum purchasing = 11 units and when purchasing quantity = 15 units is given below
Unit Price 2.25 Formula When Purchasing Unit 11 Purchasing Unit 15 Cost of Purchasing = 24.75 D7*D6 Cost of Purchasing = 33.75 C6*G15 Selling Price 5 Selling Price 5 DEMAND Revenue Profit DEMAND Revenue Profit 5 25 $c$9*C9 0.25 5 25 -8.75 G11-$G$8 10 50 25.25 10 50 16.25 15 75 50.25 15 75 41.25 20 100 75.25 20 100 66.25 Formula c11-$c$8Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.