Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Solve the following Monte-Carlo simulation problem both with Excel data tables and @Risk. Include all your answers directly in your Excel file using text boxes

Solve the following Monte-Carlo simulation problem both with Excel data tables and @Risk. Include all your answers directly in your Excel file using text boxes or comment boxes. Please show formulas used within excel.

NCAA Sweatshirt Problem

An enterprising OU student is trying to decide how many sweatshirts to print for an upcoming NCAA Basketball Tournament game. The final four teams have emerged from the quarterfinal round, and there is a week left until the semi-finals, which are then followed by the finals a few days later. Each sweatshirt costs $10 to produce and sells for $25. However, in 3 weeks, any leftover sweatshirts will be put on sale for half price, $12.50. The student assumes that the demand for her sweatshirts during the next three weeks has the distribution shown in the file NCAASweatshirts-shell.xlsx in F5:G10. The residual demand, after the sweatshirts have been put on sale, has the distribution also shown in the same file in J5:K10. The student, being a profit maximizer, realizes that every sweatshirt sold, even at the sale price, makes a profit. However, she also realizes that any sweatshirts printed and still unsold (even at the sale price) must be given away, resulting in a loss of $10 per discarded shirt. Your job is:

1) Build an Excel based Monte-Carlo simulation model that will produce a probability distribution of profit for a given number of sweatshirts printed. Your simulation model should do 1000 replications. (please show excel spreadsheet and how answers were derived through the excel)

For your base model,

2) Set the number of sweatshirts printed in your model to 10000. Run your model and find the mean and standard deviation of profit as well as the 5th and 95th percentiles of profit.

3) Construct a 95% confidence interval on the mean profit.

4) Now use your model to explore different numbers of printing quantities and find the:

a. Quantity that maximizes mean profit

b. Quantity that maximizes 5th percentile of profit

c. Quantity that maximizes 95th percentile of profit

d. Do these quantities differ?

image text in transcribed

The probability distribution should be for 1000 times.

1Sweatshirts for NCAA tournament 3 Inputs 4 Unit cost 5 Original price 8 Sale price Distribution of demand at regular price Distribution of demand at sale price Cumul. Prob Demand Probability 0.05 0.10 7,000 8,000 9,000 10,000 11,000 12,000 Cunul. Pob Demand Probability 0.20 0.30 0.20 0.15 0.10 0.05 2,000 3,000 4,000 5,000 6,000 .000 0.30 10 Decision variable 11 Order quantity (trial value 16 1Sweatshirts for NCAA tournament 3 Inputs 4 Unit cost 5 Original price 8 Sale price Distribution of demand at regular price Distribution of demand at sale price Cumul. Prob Demand Probability 0.05 0.10 7,000 8,000 9,000 10,000 11,000 12,000 Cunul. Pob Demand Probability 0.20 0.30 0.20 0.15 0.10 0.05 2,000 3,000 4,000 5,000 6,000 .000 0.30 10 Decision variable 11 Order quantity (trial value 16

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Modern Database Management

Authors: Jeff Hoffer, Ramesh Venkataraman, Heikki Topi

12th edition

133544613, 978-0133544619

More Books

Students also viewed these Databases questions

Question

Need help with all parts of the question

Answered: 1 week ago

Question

1. Which position would you take?

Answered: 1 week ago