Question

A municipality operates buses is considering updating its fleet. Managers want your recommendation to best meet the public transportation demand in the area. A consultant company estimated the revenue and cost functions appropriate to evaluate existing operations were TR = 215Q –3Q²and TC = 35Q + 1.5Q². Construct an Excel spreadsheet using those equations with quantities between 1 and 25. Identify the number of buses you would recommend to meet those criteria and the optimum price of each bus. After you have constructed the Excel spreadsheet to do this analysis, identify a more straightforward process that you would suggest.

Answer 1

Q	Price($) = TR/Q	TR = 215Q –3Q2	TC = 35Q + 1.5Q2	MR = ∆TR/∆Q	MC = ∆TC/∆Q	Profit = TR-TC
1	212.00	212.00	36.50	212.00	36.50	175.50
2	209.00	418.00	76.00	206.00	39.50	342.00
3	206.00	618.00	118.50	200.00	42.50	499.50
4	203.00	812.00	164.00	194.00	45.50	648.00
5	200.00	1000.00	212.50	188.00	48.50	787.50
6	197.00	1182.00	264.00	182.00	51.50	918.00
7	194.00	1358.00	318.50	176.00	54.50	1039.50
8	191.00	1528.00	376.00	170.00	57.50	1152.00
9	188.00	1692.00	436.50	164.00	60.50	1255.50
10	185.00	1850.00	500.00	158.00	63.50	1350.00
11	182.00	2002.00	566.50	152.00	66.50	1435.50
12	179.00	2148.00	636.00	146.00	69.50	1512.00
13	176.00	2288.00	708.50	140.00	72.50	1579.50
14	173.00	2422.00	784.00	134.00	75.50	1638.00
15	170.00	2550.00	862.50	128.00	78.50	1687.50
16	167.00	2672.00	944.00	122.00	81.50	1728.00
17	164.00	2788.00	1028.50	116.00	84.50	1759.50
18	161.00	2898.00	1116.00	110.00	87.50	1782.00
19	158.00	3002.00	1206.50	104.00	90.50	1795.50
20	155.00	3100.00	1300.00	98.00	93.50	1800.00
21	152.00	3192.00	1396.50	92.00	96.50	1795.50
22	149.00	3278.00	1496.00	86.00	99.50	1782.00
23	146.00	3358.00	1598.50	80.00	102.50	1759.50
24	143.00	3432.00	1704.00	74.00	105.50	1728.00
25	140.00	3500.00	1812.50	68.00	108.50	1687.50

From the above table it is see that profit is maximized at the quantity of 20. So, i would recommend 20 number of buses and the optimum price of each bus is $155.