Answer Happy

Simple Linear Regression 3 Bret's Accounting & Tax Services is a small well-known accounting firm in Sioux City, IA which completes taxes for individuals. Every year, firms like Bret's decide how much they will charge to complete and file an individual tax return. This price determines how many tax retums firms complete each year. Suppose you are an office manager for a firm like Bret's Accounting and Tax Services and you are trying to determine what your firm should charge next year for tax returns. Use the following data to answer these questions. a) Graph the data using a scatter plot. Using the "Insert Trendline" function in Excel, determine whether you should use linear regression or log-linear. Insert the graph below. Be sure to label both axes. We should use regression. b) Using Excel's Regression Analysis Function, run a regression and answer the following questions about your output. i) What is your estimated demand function? ii) Discuss the fit and significance of the regression. R² = % of the variation in Returns Completed is explained by our regression using Return Price. The model statistically significant. c) How many returns do you expect to be completed if the firms charges $65 per return? What is the elasticity at this point in the demand curve? Are you on the elastic, inelastic, or unit elastic portion of your demand curve? Can you make a recommendation to increase or decrease price with this information? Price (P) $65.00 Estimated Retums Completed (Q) = Own Price Elasticity= We are on portion of the demand curve. price. The recommendation d) Suppose the firm has a cost function for individual tax returns of TC = 5200 + 6Q. Using functions and Excel's Solver functionality, determine how much the firms should be charging for a return to maximize profit and the corresponding total revenue, total cost, and profit. TC- Price (P) = Estimated Retums Completed (Q) Total Revenue (TR) = Total Cost (TC) = Profit 5200 + 6 Q

Return Price 60 74 69 84 88 83 73 79 82 89 61 90 81 66 62 85 75 78 72 67 Returns Completed 972 824 921 786 742 776 853 852 772 715 937 744 791 935 946 762 812 818 843 939

Project In this problem, you will investigate the relationship between the price charged and the number of tax returns a firm completes each year. You will do this by providing a scatterplot of the relationship where the price charged may be used to estimate the number of tax returns. You will add the least squares regression line' (trendline) and the R² value using Excel's scatter graph option. You will use Excel's Regression Model to find the equation of the regression line. Then you will use Excel's Solver Add-in to maximize Profit. Description: For the purpose of grading the project you are required to perform the following tasks: Instructions Step 1 Start Excel. 3 In cell C6, insert 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 Seriesl. 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 4 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. 7 Important: Use cell reference to the value obtained in the regression model on the Data worksheet. Points Possible 0 2 2 2 1 5 1

8 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. 9 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. 10 Note: Enter the elasticity as a negative value. 11 In cell E17, determine whether the given price is on the elastic, inelastic, or unit elastic portion of the demand curve. 12 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. 13 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 14 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. 15 Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. 2 1 4 4 0