Question
PART A AND B are not part of this question because I can only include up to 4 questions per post. c. Create a model
PART A AND B are not part of this question because I can only include up to 4 questions per post.
c. Create a model in Excel that can be used to calculate the profit for a given order quantity and a simulated demand. Use the revenue and cost numbers shown below. Using your model, what would the profits be for an order quantity of 1 million discs and the demands shown in the Details below?
Demands for Question C
828,141
919,281
2,267,428
996,502
297,162
1,826,945
Marketing Outlay | $1,200,000 | ||
Retail Price: | $30.00 | ||
Price Disney Receives From Retailers: | $20.00 | ||
Cost to Produce: | $5.30 | ||
Success Level | Probability | Low Value | High Value |
Dog | 0.25 | 0 | 700,000 |
Average Success | 0.5 | 700,001 | 1,600,000 |
Smash Hit | 0.25 | 1,600,001 | 2,300,000 |
Demands for Question 2c | |||
834,046 | |||
11,577 | |||
1,385,871 | |||
1,229,895 | |||
341,875 | |||
1,823,016 |
d. Now using the model that you created in previous question C, replicate profit 1000 times for order quantities of 1 million to 2 million in increments of 100,000. Of the order quantities tried, which one gives the highest expected profit? What is its expected profit for that order quantity? Find a 95% confidence interval on the mean profit for the order quantity that gave the highest profit. Show your work. For this question you will get full credit only if you have one data table. I prefer that you not copy and paste values for this question, but instead show all of your calculations and leave them for me to see.
Optimal Order Quantity: ??
Corresponding Expected Profit: ???
Left Side of Confidence Interval: ??
Right Side of Confidence Interval: ??
e. For the best order quantity found in question D, find the probability of losing money and do a 95% confidence interval on the probability. (Note that you should be able to use the same table you created in part d to answer this question.) How many replications would be necessary if we wanted to estimate the probability with a margin of error of .01? Again, I prefer that you do not copy and paste values but keep all of your computations in place.
Probability of Losing money: ??
Left Side of Confidence Interval: ??
Right Side of Confidence Interval: ??
Required Sample Size: ??
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored 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