Question

Follow the directions below to create an Excel graph for a perfectly competitive company. A company has total costs of 200+ 2000 - 2.5Q++ 1/3Q'. The price per unit is $500. Use excel to create a table to solve the problem as in question 5. Add two columns for average total costs and average variable costs. Create a graph for quantities from 1 to 25 that has ATC, AVC, MC, and MR on it. . Give the graph a title and label the four curves Adjust the y-axis so that it ranges from 0 to 600 units Note the breakeven and shutdown points on the graph with arrows Create text boxes to label breakeven and shutdown Use Landscape orientation Center horizontally and vertically on the page and fit to one page Print one copy and attach to this homework assignment

Answer 1

Formulas:

Total Cost =200+(200*Q)-(2.5*(Q^2))+((1/3)*(Q^3))

Total Variable Cost = (200*Q)-(2.5*(Q^2))+((1/3)*(Q^3))

Average Total Cost = Total Cost/Q

Average Variable Cost = Total Variable Cost//Q

Average Fixed Cost = 200/Q

Marginal Cost = TC₂-TC₁

Total Revenue = 500*Q

Marginal Revenue = TR₂-TR₁

(Replace Q with cell numbers on the excel sheet)

Units	TC	TVC	ATC	AVC	AFC	MC	TR	MR
1	397.83	197.83	397.83	197.83	200.00	-	500	-
2	592.67	392.67	296.33	196.33	100.00	194.83	1000	500
3	786.50	586.50	262.17	195.50	66.67	193.83	1500	500
4	981.33	781.33	245.33	195.33	50.00	194.83	2000	500
5	1179.17	979.17	235.83	195.83	40.00	197.83	2500	500
6	1382.00	1182.00	230.33	197.00	33.33	202.83	3000	500
7	1591.83	1391.83	227.40	198.83	28.57	209.83	3500	500
8	1810.67	1610.67	226.33	201.33	25.00	218.83	4000	500
9	2040.50	1840.50	226.72	204.50	22.22	229.83	4500	500
10	2283.33	2083.33	228.33	208.33	20.00	242.83	5000	500
11	2541.17	2341.17	231.02	212.83	18.18	257.83	5500	500
12	2816.00	2616.00	234.67	218.00	16.67	274.83	6000	500
13	3109.83	2909.83	239.22	223.83	15.38	293.83	6500	500
14	3424.67	3224.67	244.62	230.33	14.29	314.83	7000	500
15	3762.50	3562.50	250.83	237.50	13.33	337.83	7500	500
16	4125.33	3925.33	257.83	245.33	12.50	362.83	8000	500
17	4515.17	4315.17	265.60	253.83	11.76	389.83	8500	500
18	4934.00	4734.00	274.11	263.00	11.11	418.83	9000	500
19	5383.83	5183.83	283.36	272.83	10.53	449.83	9500	500
20	5866.67	5666.67	293.33	283.33	10.00	482.83	10000	500
21	6384.50	6184.50	304.02	294.50	9.52	517.83	10500	500
22	6939.33	6739.33	315.42	306.33	9.09	554.83	11000	500
23	7533.17	7333.17	327.53	318.83	8.70	593.83	11500	500
24	8168.00	7968.00	340.33	332.00	8.33	634.83	12000	500
25	8845.83	8645.83	353.83	345.83	8.00	677.83	12500	500

Cost Curves 600.00 Shut Down 500.00 400.00 Cost and Revenue 300.00 - AVC Break Even 100.00 0.00 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 Quantity

Formulas

Total Revenue = Q*10

Marginal Revenue = 10

Total Fixed Cost = 100

Total Variable cost = (2*Q)+(0.01*(Q^2))

Total Cost = Total Variable Cost + Total Fixed Cost

PROFIT= Total Revenue - Total Cost

(Replace Q with cell numbers on the excel sheet)

The profit max point is at 400 units

Units	TR	MR	TVC	TFC	Total Cost	PROFIT
100	$              1,000	10	300	100	$              400	$             600
110	$              1,100	10	341	100	$              441	$             659
120	$              1,200	10	384	100	$              484	$             716
130	$              1,300	10	429	100	$              529	$             771
140	$              1,400	10	476	100	$              576	$             824
150	$              1,500	10	525	100	$              625	$             875
160	$              1,600	10	576	100	$              676	$             924
170	$              1,700	10	629	100	$              729	$             971
180	$              1,800	10	684	100	$              784	$         1,016
190	$              1,900	10	741	100	$              841	$         1,059
200	$              2,000	10	800	100	$              900	$         1,100
210	$              2,100	10	861	100	$              961	$         1,139
220	$              2,200	10	924	100	$          1,024	$         1,176
230	$              2,300	10	989	100	$          1,089	$         1,211
240	$              2,400	10	1056	100	$          1,156	$         1,244
250	$              2,500	10	1125	100	$          1,225	$         1,275
260	$              2,600	10	1196	100	$          1,296	$         1,304
270	$              2,700	10	1269	100	$          1,369	$         1,331
280	$              2,800	10	1344	100	$          1,444	$         1,356
290	$              2,900	10	1421	100	$          1,521	$         1,379
300	$              3,000	10	1500	100	$          1,600	$         1,400
310	$              3,100	10	1581	100	$          1,681	$         1,419
320	$              3,200	10	1664	100	$          1,764	$         1,436
330	$              3,300	10	1749	100	$          1,849	$         1,451
340	$              3,400	10	1836	100	$          1,936	$         1,464
350	$              3,500	10	1925	100	$          2,025	$         1,475
360	$              3,600	10	2016	100	$          2,116	$         1,484
370	$              3,700	10	2109	100	$          2,209	$         1,491
380	$              3,800	10	2204	100	$          2,304	$         1,496
390	$              3,900	10	2301	100	$          2,401	$         1,499
400	$              4,000	10	2400	100	$          2,500	$         1,500
410	$              4,100	10	2501	100	$          2,601	$         1,499
420	$              4,200	10	2604	100	$          2,704	$         1,496
430	$              4,300	10	2709	100	$          2,809	$         1,491
440	$              4,400	10	2816	100	$          2,916	$         1,484
450	$              4,500	10	2925	100	$          3,025	$         1,475
460	$              4,600	10	3036	100	$          3,136	$         1,464
470	$              4,700	10	3149	100	$          3,249	$         1,451
480	$              4,800	10	3264	100	$          3,364	$         1,436
490	$              4,900	10	3381	100	$          3,481	$         1,419
500	$              5,000	10	3500	100	$          3,600	$         1,400