Question

You first need to compute the demand function associated with the data set and use that demand function to compute the optimal price for the product. You can use 20% of the average price as the cost of the product.

please show excel work

P	D
1	301
2	106
3	58
4	38
5	27
6	20
7	18
8	14
9	13
10	10
11	10
12	7
13	6
14	8
15	7
16	6
17	4
18	3
19	4
20	3
21	2
22	5
23	1
24	3
25	3
26	0
27	1
28	1
29	2
30	2
31	1
32	0
33	2
34	1
35	1
36	3
37	1
38	1
39	1
40	1
41	3
42	0
43	1
44	2
45	2
46	0
47	1
48	3
49	2
50	0
51	2
52	2
53	0
54	1
55	0
56	0
57	0
58	0
59	0
60	0
61	1
62	2
63	0
64	0
65	0
66	0
67	0
68	2
69	0
70	0
71	1
72	1
73	3
74	1
75	0
76	1
77	1
78	2
79	0
80	0
81	1
82	1
83	0
84	2
85	0
86	0
87	2
88	0
89	0
90	0
91	1
92	0
93	2
94	0
95	0
96	0
97	1
98	3
99	0
100	1
101	0
102	1
103	1
104	0
105	0
106	1
107	1
108	0
109	1
110	1
111	0
112	0
113	0
114	0
115	2
116	1
117	1
118	1
119	0
120	1

Answer 1

In excel, you can use the scatter-plot along with trendline function to find this.

Here are the steps:

1. Select both columns, including the labels.

2. Go to insert -> Scatter Plot (in charts)

3. Click on any one of the points on scatter plot. This will highlight all the points.

4. Right click on the point -> Add trendline

5. In the trendline options, select polynomial - order 6

6. In the trendline equations, check 'Display Equation on chart', 'Display R-squared equation on chart'

where y=demand and x=price

Now Revenue= y*x

Revenue = (1E-08x6 - 4E-06x5 + 0.0006x4 - 0.0456x3 + 1.7298x2 - 30.262x + 182.8)*x

Revenue = 1E-08x7 - 4E-06x6 + 0.0006x5 - 0.0456x4 + 1.7298x3 - 30.262x2 + 182.8x

At optimal price, profit should be maximized

Cost = 20% of average price = 20% of 60 = 12

Profit = 1E-08x7 - 4E-06x6 + 0.0006x5 - 0.0456x4 + 1.7298x3 - 30.262x2 + 182.8x - 12

At maximum profit, dP/dx=0

7*1E-08x6 - 6*4E-06x5 + 5*0.0006x4 - 4*0.0456x3 + 3*1.7298x2 - 2*30.262x + 182.8 = 0

Using the GoalSeek function to find the solution of this equation,

x=4.5

Thus, optimal price = 4.5