Question
III - Newsvendor Problem for Anime Magazine. (3.5 points) Excel Tab: Anime Young entrepreneur Fan Bingbing has launched a business venture in which she uses
III - Newsvendor Problem for Anime Magazine. (3.5 points) Excel Tab: Anime Young entrepreneur Fan Bingbing has launched a business venture in which she uses stories submitted by university students as the basis for comics in a monthly anime-style magazine. Based on market research, she has decided to model monthly demand as the maximum of 0 and a normal random variable with a mean of 500 and a standard deviation of 300. Fan must pay a publishing company $3.75 for each copy of the comic printed. She then sells the magazine for $5 each. Rather than having a storefront, Fan sells the magazines through a group of student vendors who sell the comics out of their backpacks while on campus. Fan pays a student vendor $0.35 for each magazine he/she sells. As Fan distributes a new issue each month, she only sells each issue for a month. However, the publishing company has agreed to buy back from Fan any unsold copies at the end of each month for $2.25. I have prepared the excel sheet with required formulas. Create a simulation model with 1000 trials. a. What is the estimate of the average profit if Fan sets the order quantity to 300 ? What is the 95% confidence interval on this estimate of average profit? b. What is the estimate of the average profit if Fan sets the order quantity to 1,000 ? What is the 95% confidence interval on this estimate of average profit? c. Which order quantity should Fan choose based on the average profit?
please using excel format.
\begin{tabular}{|c|c|c|c|c|c|c|} \hline Anime & & & & & & \\ \hline & & & & & & \\ \hline \multicolumn{7}{|l|}{ Parameters } \\ \hline Publishing Cost & 3.75 & & & & & \\ \hline Selling Price & 5 & & & & & \\ \hline Student Vendor Fee & 0.35 & & & & & \\ \hline Buyback Price & 2.25 & & & & & \\ \hline \multirow{4}{*}{ Monthly Demand } & & & & & & \\ \hline & & & Monthly Demand (Normal) & & & \\ \hline & & & Mean & 500 & & \\ \hline & & & Standard Deviation & 300 & & \\ \hline \multicolumn{7}{|l|}{ Model } \\ \hline Order Quantity & 300 & 1000 & & & & \\ \hline Quantity Sold & 300 & 1000 & & & & \\ \hline \multirow[t]{2}{*}{ Quantity Unsold } & 300 & 1000 & & & & \\ \hline & & & & & Profit Per Unit Statistics & \\ \hline Sales Revenue & 1,500 & 5,000 & & & Order Quantity 300 & Order Quantity 1000 \\ \hline Buyback Revenue & 675 & 2,250 & & & Minimum & Minimum \\ \hline Publishing Cost & 1,125 & 3,750 & & & Maximum & Maximum \\ \hline Vendor Cost & 105 & 350 & & & Average & Average \\ \hline Profit & 945 & 3,150 & & & Standard Deviation & Standard Deviation \\ \hline Simulation Trial & Demand & Profit for Order Quantity 300 & Profit for Order Quantity 1000 & & 95%CI around mean & 95%CI around mean \\ \hline \end{tabular}Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access with AI-Powered Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started