Hey can someone tell me how to graph this in excel please. Thanks.
Produce a chart in Excel showing TC and TR with Q on the horizontal axis. Have Q go from 0 to 15,000 units. Produce a second chart showing MC and MR with Q again on the horizontal axis. Here is the problem for it.
Bavarian Crystal works designs and produces lead crystal wine decanters for export to international markets. The production manager of Bavarian crystal works estimates total and marginal production costs to be
TC=10,000+ 40Q+0.0025Q2
And MC=40 + 0.005Q
Where costs are measured in U.S. dollars and Q is the number of wine decanters produced annually. Because Bavarian Crystal Works is the only one of many crystal producers in the world market, it can sell as many of the decanters as it wishes for$70 apiece. Total and marginal revenue are
TR = 70Q and MR= 70
Where revenues are measured in U.S. dollars and Q is annual decanter production.
A.What is the optimal level of production of wine decanters? What is the marginal revenue from the last wine decanter sold?
B.What are the total revenue, total cost, and net benefit(profit) from selling the optimal number of wine decanters.
C.At the optimal level of production of decanters’ an extra decanter can be sold for $70, thereby increasing total revenue by $70. Why would the manager of this firm not produce and sell either one more or one less unit?”
Thanks.
To graph the given information in Excel, you can follow these steps:
1. Open Microsoft Excel and create a new worksheet.
2. In the first column, label the cells from A2 to A15000 as "Q" to represent the number of decanters produced annually. Start from 0 and increment by 1 until you reach 15000.
3. In cell B1, label it as "TC" to represent total cost.
4. In cell C1, label it as "TR" to represent total revenue.
5. In cell D1, label it as "MC" to represent marginal cost.
6. In cell E1, label it as "MR" to represent marginal revenue.
7. In cell B2, enter the formula "=10000+40*A2+0.0025*A2^2" to calculate total cost (TC) based on the given equation.
8. In cell C2, enter the formula "=70*A2" to calculate total revenue (TR) based on the given equation.
9. In cell D2, enter the formula "=40+0.005*A2" to calculate marginal cost (MC) based on the given equation.
10. In cell E2, enter the constant value "70" to represent marginal revenue (MR), as stated in the problem.
11. Select the range of cells from A2 to E15000.
12. Click on the "Insert" tab in the Excel ribbon.
13. Choose the desired chart type, such as "Line" or "Scatter," from the "Charts" section.
14. Excel will generate a chart showing the relationships between Q and the respective variables (TC, TR, MC, MR).
A. To find the optimal level of production and the marginal revenue from the last decanter sold, analyze the MR and MC curves on the second chart. The optimal level of production is where MR equals MC. Look for the point where the MR curve intersects the MC curve. The corresponding Q value will indicate the optimal level of production. The marginal revenue from the last decanter sold will be equal to the MR at this point.
B. To find the total revenue, total cost, and net benefit from selling the optimal number of decanters, look at the Q value identified in the previous step. Calculate the corresponding total revenue by multiplying the Q value by 70. Calculate the corresponding total cost by substituting the Q value into the TC equation. The net benefit (profit) can be found by subtracting the total cost from the total revenue.
C. The manager of the firm would not produce and sell one more or one less unit because the optimal level of production maximizes net benefit or profit. Producing and selling one more or one less unit would result in a lower net benefit. Therefore, it is optimal to produce and sell the number of units that corresponds to the intersection point of the MR and MC curves.
I hope the explanation helps you in understanding how to graph and solve the given problem in Excel.