Excel homework
Problem
Problem 7-2
Use a cell reference or a single formula where appropriate in order to receive full credit. Do not copy and paste values or type values, as you will not receive full credit for your answers. The Green Revolution was based in part on extensive experimentation. The following data illustrates the relationship between nitrogen fertilizer (in pounds of nitrogen) and the output of a particular type of wheat (in bushels). Each observation is based on one acre of land and all other relevant inputs to production (such as water, labor, and capital) are held constant. The fertilizer levels are 20, 40, 60, 80, 100, 120, 140, and 160, and the associated output levels are 47, 86, 107, 131, 136, 148, 149, and 142.
f q
20 47
40 86
60 107
80 131
100 136
120 148
140 149
160 142
a) Use Excel to estimate the short-run production function showing the relationship between fertilizer input and output. (Hint: Use the Trendline option to regress output on fertilizer input. Try a linear function and try a quadratic function and determine which function fits the data better.)
function fits the data better.
b) Does fertilizer exhibit the law of diminishing marginal returns?
Fertilizer the law of diminishing marginal returns.
What is the largest amount of fertilizer that should ever be used, even if it is free?
The largest amount of fertilizer is equal to .
Instructions
Project Description: In this problem, you will investigate the relationship between nitrogen fertilizer and wheat by using two trendlines.
Steps to Perform:
Step Instructions Points Possible
1 Use a cell reference or a single formula where appropriate in order to receive full credit. Do not copy and paste values or type values, as you will not receive full credit for your answers. Start Excel. 0
2 In cells C17-J17, insert a Scatter Chart for the data illustrating the relationship between nitrogen fertilizer and the output of a particular type of wheat. Inserting a Chart On the Insert tab, in the Charts group, click the arrow next to Insert Scatter (X,Y) or Bubble Chart and choose Scatter Chart. Selecting Data Series Then in Select Data Source window, delete any series created automatically. Add new series for the given data using cells C6:C13 for the X values and cells D6:D13 for the Y values. Use D5 as the series name. Edit Chart Elements Select design Style 1 for the chart. Go to the Add Chart Elements dropdown list in the Design tab of the Ribbon. Add Production Function of Wheat as the chart title. Add Nitrogen Fertilizer (pounds) as the title for the horizontal axis and Wheat (bushels) as the title for the vertical axis. Chart Position Set the chart height and width so the entire chart fits within cells C17-J17. 4
3 Add a linear trendline to the data on the chart. Adding Linear Trendline Select any point on the chart and right click on it. Select the Add Trendline. In Trendline Options window select Linear with automatic trendline name. Trendline Options In Trendline Options window check the “Display equation on chart” and “Display R-squared value on chart” boxes. You can grab the added equation and R-squared value and drag it to any place on the chart so that it is more visible to read. Edit Linear Trendline Double click on linear trendline. In Format Trendline, click Fill & Line. Select Red color and choose Solid dash type. 1
4 Add a polynomial trendline to the data on the chart. Adding Polynomial Trendline Select any point on the chart and right click on it. Select the Add Trendline. In Trendline Options window select Plynomial with automatic trendline name. Trendline Options In Trendline Options window check the “Display equation on chart” and “Display R-squared value on chart” boxes. You can grab the added equation and R-squared value and drag it to any place on the chart so that it is more visible to read. Edit Polynomial Trendline Double click on polynomial trendline. In Format Trendline, click Fill & Line. Choose Solid dash type. 1
5 In cell C19, determine which function fits the data better. 1
6 In cell D22, determine whether the fertilizer exhibits the law of diminishing marginal returns. 1
7 In cell F25, by using a cell reference, enter the largest amount of fertilizer that should ever be used. 1
8 Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. 0