Build an Excel based Monte-Carlo simulation model

Assignment Help Accounting Basics

Reference no: EM132820903

Question - 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 $9 to produce and sells for $24. However, in 3 weeks, any leftover sweatshirts will be put on sale for half price, $11.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 $9 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.

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?

Reference no: EM132820903

Questions Cloud

Compute and report the discounted payback period : Compute and report the discounted payback period of the following FCF. The initial outlay is $24,000,000. FCF in period 1 is $8,000,000

What is best practice in hrm : What is best practice in HRM? How is it explained and what theories are relevant?

Evaluation is critical to effectiveness of training : Reflect on and explore the reasons why evaluation is critical to training success. Discuss the reasons evaluation is critical to effectiveness of training

Discuss the budgeting cycle check points for the cycle : Discuss the budgeting cycle check points for the cycle and evaluate the pros and cons of your budgeting process. Find a video on YouTube

Build an Excel based Monte-Carlo simulation model : Build an Excel based Monte-Carlo simulation model that will produce a probability distribution of profit for a given number of sweatshirts printed

Which special journal is used to record merchandise : Which special journal is used to record merchandise or other items bought on account? Amounts are posted to the Accounts Payable Subsidiary Ledger

How subculture of violence differ from subculture of poverty : Crutchfield (2015) considers subculture theory (i.e., subculture of violence and subculture of poverty) to explain racial disparities in crime.

Company can sell or offer to global market : Identify two new products and/or services that the company can sell or offer to a global market.

Why a corporation would issue share dividends instead : Kis Co. declared $50,000 in dividends in 2001. Give two reasons why a corporation would issue share dividends instead of cash dividends

User Account

All Pages