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 functions in Excel, follow these steps:
1. Open Excel and enter the values of Q (number of wine decanters) in a column starting from Cell A2. Let Q range from 0 to 15,000 units.
2. In the adjacent column, calculate the values of the Total Cost (TC) using the formula: "=10000 + 40*A2 + 0.0025*A2^2". Enter this formula in Cell B2 and drag it down to fill the column for all values of Q.
3. In another column, calculate the values of the Total Revenue (TR) using the formula: "=70*A2". Enter this formula in Cell C2 and drag it down to fill the column for all values of Q.
4. Go to the Insert tab in Excel and select "Scatter" from the Charts section.
5. Choose the chart type "Scatter with Straight Lines" (the one with lines connecting the points).
6. Select the range of cells containing Q (Column A) and TC (Column B) as the data source for the chart.
7. Click on "OK" to create the first chart showing TC and TR with Q on the horizontal axis.
8. Repeat steps 4-7 to create the second chart showing MC and MR with Q on the horizontal axis. However, this time use the range of cells containing Q (Column A) and MC (derived from the formula "=40 + 0.005*A2") as the data source.
Now, let's answer the questions based on the given problem:
A. The optimal level of production of wine decanters can be determined by finding the quantity where marginal revenue (MR) is equal to marginal cost (MC). From the given information, MR is always equal to $70 because the firm can sell as many decanters as it wishes for that price. Set MR equal to MC and solve for Q:
70 = 40 + 0.005Q
Solving this equation, we get:
0.005Q = 30
Q = 6000
Therefore, the optimal level of production is 6000 wine decanters annually. The marginal revenue from the last decanter sold is $70.
B. To find the total revenue (TR), total cost (TC), and net benefit (profit), substitute the optimal production level (Q = 6000) into the given formulas:
TR = 70 * 6000 = $420,000
TC = 10000 + 40 * 6000 + 0.0025 * (6000^2) = $900,000
Net Benefit or Profit = TR - TC = $420,000 - $900,000 = -$480,000
The firm would incur a net loss of $480,000 from selling the optimal number of wine decanters.
C. The manager of the firm would not produce and sell either one more or one less unit at the optimal level of production because it would lead to a decrease in net benefit or profit. Selling one more unit would increase total revenue by $70 but also increase total cost, resulting in a larger net loss. Similarly, producing and selling one less unit would reduce total revenue while the total cost remains the same, leading to a greater net loss. Hence, the firm would stick to the optimal level of production to minimize losses.
To graph the total cost (TC) and total revenue (TR) with Q on the horizontal axis, and the marginal cost (MC), and marginal revenue (MR) with Q on the horizontal axis using Excel, you can follow these steps:
Step 1: Set up the data
Create a table with three columns: Q (quantity), TC (total cost), and TR (total revenue). Populate the Q column with numbers ranging from 0 to 15,000, and use the TC and TR formulas given.
```
Q | TC | TR
---------------------------------------
0 | 10000 | 0
1 | 10040 | 70
2 | 10080.01 | 140
. | ... | ...
. | ... | ...
15000| 24000000 | 1050000
```
Step 2: Create a TC and TR chart
1. Select the Q and TC columns (including heading).
2. Go to the "Insert" tab on the Excel ribbon.
3. Choose the chart type you prefer (line chart, scatter plot, etc.).
4. Excel will generate the chart with Q on the horizontal axis and TC and TR on the vertical axis.
Step 3: Create an MC and MR chart
1. Create two additional columns for MC and MR using the given formulas.
2. Select the Q and MC columns (including heading).
3. Go to the "Insert" tab on the Excel ribbon.
4. Choose the chart type you prefer (line chart, scatter plot, etc.).
5. Excel will generate the chart with Q on the horizontal axis and MC on the vertical axis.
6. Repeat steps 2 to 5, but this time select the Q and MR columns to create the MR chart.
Now that your charts are ready, let's answer the questions:
A. What is the optimal level of production of wine decanters? What is the marginal revenue from the last wine decanter sold?
To find the optimal level of production, look for the point where MR equals MC. In this case, MR is $70, and MC is given by MC = 40 + 0.005Q. Setting them equal:
70 = 40 + 0.005Q
0.005Q = 30
Q = 6000
So, the optimal level of production is 6,000 wine decanters. The marginal revenue from the last wine decanter sold is $70.
B. What are the total revenue, total cost, and net benefit (profit) from selling the optimal number of wine decanters?
To find the total revenue, total cost, and net benefit (profit), use the given formulas for TR and TC:
Total Revenue (TR) = 70Q = 70 * 6000 = $420,000
Total Cost (TC) = 10000 + 40Q + 0.0025Q² = 10000 + 40 * 6000 + 0.0025 * (6000)² = $880,000
Net Benefit (Profit) = TR - TC = 420,000 - 880,000 = -$460,000 (Loss)
From selling the optimal number of wine decanters, the total revenue is $420,000, the total cost is $880,000, and there is a net benefit (profit) of -$460,000 (a loss).
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?
The manager will not produce and sell either one more or one less unit because the marginal cost (MC) curve is increasing. This means that producing one more unit would result in a higher cost than the additional revenue it would generate. Similarly, producing one fewer unit would result in a loss of revenue greater than the cost saved. Therefore, the manager would stick to the optimal level of production where MR equals MC.