Question

Return Price 70 70 75 75 80 80 85 80 80 80 90 90 95 95 100 100 90 90 85 80 Returns Completed 932 932 910 920 876 852 811 857 847 865 785 802 789 731 663 709 792 831 834

In cell C6, insert a Scatter Chart for the Returns Completed versus Return Price data from the Data worksheet. You may be used to seeing Price placed on the Y-axis from other economics courses, but in this problem we are using price as the independent variable. Inserting Chart Select the Scatter chart from the provided chart options in the Charts group of the Insert tab of the Ribbon. Selecting Data Series Then choose Select Data in the Design tab on the Ribbon. Delete any series created automatically using the Remove button and add new series using Add button. Select the range of data on the Data worksheet. Note that Returns Completed should stand for the Y values and Return Price for the X values. Type the series name as Series1. Edit Chart Elements On the Ribbon, select design Style 1. Go to the Add Chart Elements dropdown list in the Design tab of the Ribbon. Delete the chart title and the legend. Add Return Price as the title for the horizontal axis. Add Returns Completed as the title for the vertical axis. Chart Size and Position Go to the Format tab on the Ribbon. Set the chart height to 3 inches and the chart width to 5 inches. Drag the chart to position the entire chart so that it fits within cell C6. (For more convenience, you can adjust the chart size and position at the end of the assignment.)

Add a linear trendline to the data on the chart. Adding Linear Trendline Select any point on the chart line 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. Close out of the trendline box before adding the next trendline.

Add a logarithmic trendline to the data on the chart. Adding Logarithmic Trendline Select any point on the chart line and right click on it. Select the Add Trendline. In Trendline Options window select Logarithmic 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.

In cell E7, determine whether we should use linear or log-linear regression based on the two R-squared values shown next to the trendlines on the scatter plot.

Use the regression model of Data Analysis for the data. If the log-linear regression should be used, prepare the data before adding the regression model. Preparing Data (if the log-linear regression should be used) Go to the Data worksheet. In cell C1, type LN Price. In cell D1, type LN Quantity. In cell C2, by using a cell reference, calculate LN of the Return Price in cell A2. Copy the formula from cell C2 down the column to cell C21. Use the Excel LN function. In cell D2, by using a cell reference, calculate LN of the Return Completed in cell B2. Copy the formula from cell D2 down the column to cell D21. Use the Excel LN function. Adding Regression Model Go to the Data worksheet if you are not already there. Select the Data Analysis in the Analyze group of the Data tab of the Ribbon (note you should add this Add-in in case you do not have it in the Data tab already). Select Regression and click OK. Select the cells in the Returns Completed column as the Input Y Range and the cells in the Return Price column as the Input X Range if the linear regression should be used. Select the cells in the LN Quantity column as the Input Y Range and the cells in the LN Price column as the Input X Range if the log-linear regression should be used. Choose cell F1 on the Data worksheet as the output range. Do not check any additional boxes in the regression model menu. On the Problem worksheet, in cells D9 and H9, determine the correct variables to be used in the least squares regression equation. In cells E9 and G9, enter the values of the coefficients to write the least square regression equation in the proper form. Use cell references to the values obtained in the regression model on the Data worksheet.

In cell E10, enter the value of the R-squared. Important: Use cell reference to the value obtained in the regression model on the Data worksheet.

In cell D11, by using a cell reference, calculate the % of the variation in Returns Completed that is explained by the regression using Return Price. Use cell E10. In cell E12, determine whether the regression model is statistically significant.

In cell E15, by using cell references, calculate the estimated returns completed for the given price. Use cells E9, G9, and E14.

In cell E16, by using cell references, calculate the elasticity at the given price. Use cells G9, E14, and E15. Note: Enter the elasticity as a negative value.

In cell E17, determine whether the given price is on the elastic, inelastic, or unit elastic portion of the demand curve.

In cell E18, give a recommendation regarding changes in price.

Prepare cells E22, E23, E24, and E25 to use the Solver Add-in. In cell E22, by using cell references, calculate the estimated returns completed for the given price. Use cells E9, G9, and E21. In cell E23, by using cell references, calculate the total revenue for the given price and the estimated returns completed. Use cells E21 and E22. In cell E24, by using cell references, calculate the total costs for the estimated returns completed. Use cells E20, G20, and E22. In cell E25, by using cell references, calculate the profit. Use cells E23 and E24.

Use the Solver Add-in to find the price that maximizes profit. Using Solver Add-in Select the Solver in the Analyze group of the Data tab of the Ribbon (note you should add this Add-in in case you do not have it in the Data tab already). Choose cell E25 in the Set Objective field. Select the Max option below. Choose cell E21 in the By Changing Variable Cells field. There are no additional constraints. Make sure that the “Make Unconstrained Variables Non-Negative” box is checked. Leave the GRG Nonlinear solving method. Click Solve. In the popup window select the Keep Solver Solution option. Do not check any additional boxes and click OK. As the result, you will see the price in cell E21, that gives the maximum profit. The values for estimated returns completed, total revenue, total cost, and profit will update automatically.

Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed.

Answer 1