Question

Construct a spreadsheet simulation model for calculating profits for a company given that the demand for their new product is normally distributed with a mean of 6 and standard deviation of 1. The selling price is $30/unit. The fixed cost is $50. The per unit variable cost is uniformly distributed between $10 and $20. Assume that all that is produced is sold and that production and demand are always equal. Execute the simulation for 50 trials.
*Please answer with all steps taken in excel.

Answer 1

The intermediate rows are hidden as they are simple copy-paste.

Run #	RN	Demand	RN	Unit variable cost	Selling price	Fixed cost	Profit
1	0.308	5.50	0.39	13.93	$30	$50	$38.37
2	0.377	5.69	0.59	15.88	$30	$50	$30.29
3	0.872	7.14	0.08	10.77	$30	$50	$87.18
4	0.710	6.55	0.36	13.61	$30	$50	$57.43
5	0.946	7.60	0.60	15.96	$30	$50	$56.80
6	0.818	6.91	0.42	14.20	$30	$50	$59.12
7	0.298	5.47	0.06	10.61	$30	$50	$56.10
8	0.305	5.49	0.69	16.87	$30	$50	$22.06
9	0.083	4.61	0.44	14.41	$30	$50	$21.93
10	0.695	6.51	0.60	16.02	$30	$50	$41.05
11	0.842	7.00	0.51	15.08	$30	$50	$54.42
12	0.507	6.02	0.80	18.04	$30	$50	$21.98
13	0.300	5.48	0.56	15.65	$30	$50	$28.59
14	0.076	4.57	0.38	13.81	$30	$50	$24.00
15	0.791	6.81	0.93	19.34	$30	$50	$22.59
16	0.285	5.43	0.86	18.56	$30	$50	$12.17
17	0.564	6.16	0.47	14.71	$30	$50	$44.19
18	0.138	4.91	0.74	17.38	$30	$50	$11.96
19	0.490	5.97	0.37	13.66	$30	$50	$47.65
20	0.050	4.36	0.79	17.92	$30	$50	$2.66
21	0.839	6.99	0.48	14.84	$30	$50	$56.00
22	0.667	6.43	0.60	15.99	$30	$50	$40.10
23	0.219	5.23	0.32	13.21	$30	$50	$37.72
24	0.508	6.02	0.08	10.79	$30	$50	$65.68
25	0.200	5.16	0.23	12.29	$30	$50	$41.37
26	0.038	4.22	0.73	17.25	$30	$50	$3.85
27	0.215	5.21	0.85	18.50	$30	$50	$9.96
28	0.046	4.31	0.89	18.93	$30	$50	($2.28)
29	0.107	4.76	0.78	17.80	$30	$50	$8.04
30	0.266	5.37	0.21	12.08	$30	$50	$46.33
31	0.311	5.51	0.43	14.29	$30	$50	$36.54
32	0.811	6.88	0.52	15.24	$30	$50	$51.58
33	0.853	7.05	0.82	18.19	$30	$50	$33.27
34	0.998	8.88	0.12	11.17	$30	$50	$117.29
35	0.598	6.25	0.89	18.92	$30	$50	$19.20
36	0.483	5.96	0.17	11.72	$30	$50	$58.89
37	0.226	5.25	0.30	12.95	$30	$50	$39.47
38	0.766	6.73	0.65	16.46	$30	$50	$41.08
39	0.212	5.20	0.01	10.08	$30	$50	$53.58
40	0.585	6.21	0.55	15.47	$30	$50	$40.31
41	0.964	7.80	0.09	10.86	$30	$50	$99.35
42	0.562	6.16	0.98	19.78	$30	$50	$12.92
43	0.027	4.07	0.52	15.24	$30	$50	$10.11
44	0.703	6.53	0.44	14.41	$30	$50	$51.85
45	0.365	5.65	0.23	12.28	$30	$50	$50.22
46	0.938	7.54	0.10	10.99	$30	$50	$93.34
47	0.276	5.40	0.78	17.82	$30	$50	$15.83
48	0.377	5.69	0.20	12.01	$30	$50	$52.31
49	0.702	6.53	0.71	17.12	$30	$50	$34.14
50	0.262	5.36	0.18	11.77	$30	$50	$47.75
						Avg.	$40.13