Your manager wants you to predict total profit for next year based on a fixed quantity produced and determine how profit will change as demand changes.

INSTRUCTIONS TO CANDIDATES

ANSWER ALL QUESTIONS

Project 3 - Monte Carlo Risk Simulation for

Business Planning

Your manager wants you to predict total profit for next year based on a fixed quantity produced and determine how profit will change as demand changes. Suppose that the demand, unit cost, and fixed cost are uncertain. Historical data for the demand might suggest a probability distribution with the following values:

Demand Probability

40,000 0.1

45,000 0.3

50,000 0.4

55,000 0.15

60,000 0.05

 

While you don’t know the exact unit cost, suppose you do know that it varies between $22 and $26; therefore you can model it with a uniform distribution. You estimate that fixed cost might be as low as $355,000 or as high as $500,000, but is most likely $425,000. For fixed cost, a triangular distribution can be used.

If values from these distributions are generated randomly in the appropriate cells of the spreadsheet, the value of profit will also change. By repeating this many times, we will obtain a probability distribution of profit; this is the essence of Monte Carlo simulation.

First, fill in values for the data table. The unit price is $40.00. Find unit cost, fixed cost, and demand.

Next, fill in values for the model table. Unit price, unit cost, and fixed cost are the same as the above table (use cell references). Assume that quantity produced is 40,000. Find quantity sold, revenue, variable cost, and profit. Assume quantity sold is either equal to demand or quantity produced, depending on which value is lowest (use the MIN function).

HINT: The  ONLY values you should manually type into the profit mode  l are the unit price in cell I5, and quantity produced in cell I18. Everything else in the profit model (data table and model table) should be cell referenced, even if it’s just set equal to another number.

 

Now you can move on to the data table. We are using a one-way data table with 100

observations. Cell M2 should be set equal to the value you found for profit in cell J22 (use a cell reference). To automatically generate the random observations of the 100 trials, follow these steps:

- Select the range of the table L2:M102

- Go to the Data tab, select What-if-Analysis, then select Data Table

- Here’s the trick: in the Column Input Cell field in the Data Table dialog, enter any blank cell in the spreadsheet. Make sure it is one that you will not use.

- Click OK in the Data Table dialog, then the values in column M will display the simulation results for the profit.

Why does this work? When you create a data table, the Column Input Cell generally refers to some parameter in the model. The data table simply takes these values, replaces them in the model, then displays the output. Because we used a blank cell for the Column Input Cell, the trial numbers do not affect the model. However, for each trial, the spreadsheet is recalculated. Since we used the RAND function to generate random variates, each recalculation uses difference values for the uncertain inputs. We may repeat the simulation by recalculating the spreadsheet. Because you may want to preserve the results for subsequent analysis, I suggest setting the Calculation Options in the Formulas tab to Automatic Except for Data Tables .

The last step in the simulation process is to analyze the results using various statistical tools such as summary statistics, percentiles, confidence intervals, and frequency distributions and histograms. I don’t recommend using Data Analysis tools because if you wish to recalculate the spreadsheet and run a new simulation, the results will not update. Instead, use Excel functions.

Answer the following questions. You must use complete sentences to receive full credit. To answer these questions, you may want to copy and paste your data table into a new spreadsheet as values .

1. Calculate the summary statistics. What is the mean, standard deviation, minimum, and maximum?

2. Because the simulation is based on only 100 observations, sampling error may be significant.

a. Assess this by developing a 95% confidence interval. What does that tell you?

b. How would a larger number of trials decrease the width of your 95% confidence interval?

3. The percentiles allow you to make probability statements about the profit. To find

percentiles, use the PERCENTILE.EXC function.

a. Find the 90th percentile. What does it mean in terms of profit?

Attachments:

Related Questions

. The fundamental operations of create, read, update, and delete (CRUD) in either Python or Java

CS 340 Milestone One Guidelines and Rubric  Overview: For this assignment, you will implement the fundamental operations of create, read, update,

. Develop a program to emulate a purchase transaction at a retail store. This  program will have two classes, a LineItem class and a Transaction class

Retail Transaction Programming Project  Project Requirements:  Develop a program to emulate a purchase transaction at a retail store. This

. The following program contains five errors. Identify the errors and fix them

7COM1028   Secure Systems Programming   Referral Coursework: Secure

. Accepts the following from a user: Item Name Item Quantity Item Price Allows the user to create a file to store the sales receipt contents

Create a GUI program that:Accepts the following from a user:Item NameItem QuantityItem PriceAllows the user to create a file to store the sales receip

. The final project will encompass developing a web service using a software stack and implementing an industry-standard interface. Regardless of whether you choose to pursue application development goals as a pure developer or as a software engineer

CS 340 Final Project Guidelines and Rubric  Overview The final project will encompass developing a web service using a software stack and impleme