OPEN THE EXCEL FILE INCLUDED WITH THESE INSTRUCTIONS.

STEP 1) Calculate the Average (Expected) Profits based on average (expected) input values. a. In cells B20:F20 in the spreadsheet, you will see places to enter the average (expected) amounts for each of the inputs. Using the information given at the end of rows 5-8, populate the cells with the correct values. b. Then use the profit equation in row 10 to calculate the average (expected) profit in cell F20.

STEP 2) Populate cells I20:L20 with the formulas appropriate for the random variables specified at the top of the spreadsheet. a. Use the examples provided in learning materials to help you use the correct formulas for the normal, discrete uniform, and continuous uniform random variables. b. Then calculate the profit equation based on these random numbers in cell M20 (again using the profit equation in row 10). Note: these values will and are supposed to change when you press enter or make a change to the spreadsheet. They are "volatile" calculations.

STEP 3) Copy the formulas in cells I20:M20 to the yellow cells in rows 27-126. This will simulate 100 possible future outcomes based on the random numbers we have estimated. [Again, these will be volatile and will change frequently. That is OK.]

STEP 4) Summarize results of trial runs in cells T26:T30 a. Use Excel formulas/functions to calculate the Minimum, Maximum, Average, and Standard Deviation of the 100 trial profits you generated. b. Determine what percentage of the trial runs have negative profits. Hint: first use a =COUNTIF function to determine how many trials are negative, then divide by 100.

STEP 5) Calculate the relative frequency distribution of results in cells U35:U42. Remember that the relative frequency is just the percentage (rather than the count) of trials with values in that range.

STEP 6) Draw a bar graph of the relative frequency distribution in U35:U42. a. Insert a bar chart by first highlighting the Relative Freq/Percentages b. After creating the chart, use a right click and choose "Select Data". Then point to the "Class" column (cells R35:R42) for the Horizontal (Category) Axis labels.

STEP 7) Answer the discussion question (also shown in the Excel sheet) by entering text directly into the boxes provided in in cells Q74:U78. Discuss the Minimum, Maximum and Percent of Profits that were negative. Use these values to make a recommendation as to whether to proceed with this new product development.

New Product Simulation
RANDOM (UNCERTAIN) INPUTS:
Demand --		Normally distributed with mean 2700 units and st dev 600 units	(Average/Mean/Expected value= 2700)
Selling price/Revenue per unit --	Discrete Uniform between $250 and $300 (whole Dollars only)	(Average/Mean/Expected value= $275)
Variable Costs Per Unit --	Continuous uniform between $100-$120 per unit (partial dollars allowed)	(Average/Mean/Expected value= $115)
Fixed Costs --		Normally distributed with mean $300,000, st dev $50,000	(Average/Mean/Expected value= $300,000)
MODEL:	Profit = Demand*(Selling Price Per Unit - Variable Cost per unit) - Fixed Costs
Your Name (enter here)
	Stage 1 Model - Use Average (Mean/Expected) Numbers			Stage 2 Model - Random Numbers
	Avg Demand	AvgSelling Price Per Unit	Avg Variable Costs Per Unit	Avg Fixed Costs	Avg Profits			Demand	Selling Price Per Unit	Variable Costs Per Unit	Fixed Costs	Profits
STEP 1							STEP 2
	Place expected values here and then determine profit equation			Replace expected values to left with random variables as described above
								Stage 2 Model - Multiple Trials			Summarize Results
							Trial/Run #	Demand	Selling Price Per Unit	Variable Costs Per Unit	Fixed Costs	Profits			STEP 4		Minimum Profits
				STEP 3			1								Calculate these descriptive statistics		Maximum Profits
				Copy Step 2 cells above to this	2								for the 100 profits in M27:M126		Average Profits
				table so that there are 100	3										St Dev of Profits
				repetitions or trials		4										Probability Profits are Negative		(Hint for this: first count how many are negative, then calculate the percent that are negative)
				representing 100 possible	5
				future outcomes		6
							7								Distribution of Results	Number	Percent of Total
							8								STEP 5		Class	Cum Freq	Count/Freq	Rel Freq/Pct
							9								Calculate the percentage of time	Less than -50k	0	0
							10								profits fall into each range	-50k to 0	0	0
							11										0-50k	0	0
							12										50k-100k	0	0
							13										100k-150k	0	0
							14										150k-200k	0	0
							15										200k-250k	0	0
							16										250k or more	0	0
							17
							18
							19								STEP 6	Insert a vertical bar chart showing the Rel Freq/Pct
							20									Then right click and choose "Select Data" to add the "Class" column as the Horizontal (Category) Axis labels
							21
							22								Graph/Histogram of Results
							23								insert bar chart/graph here
							24
							25
							26
							27
							28
							29
							30
							31
							32
							33
							34
							35
							36
							37
							38
							39
							40
							41
							42
							43								STEP 7	Answer this question
							44
							45									Discuss the Minimum, Maximum and Percent of Profits that were negative. Use these values to make a recommendation as to whether to proceed with this new product development.
							46
							47
							48
							49
							50
							51
							52
							53
							54
							55
							56
							57
							58
							59
							60
							61
							62
							63
							64
							65
							66
							67
							68
							69
							70
							71
							72
							73
							74
							75
							76
							77
							78
							79
							80
							81
							82
							83
							84
							85
							86
							87
							88
							89
							90
							91
							92
							93
							94
							95
							96
							97
							98
							99
							100

Note- Please list out the steps.