Linear Regression in Excel



Supply and Demand Estimation Using ExcelShow all of your work in the excel file you turn in. I encourage you to work on the problems together in small groups, but everyone must do their own work in their own file and turn in their own answers.DEMAND AS FUNCTION OF PRICEJohn is the manager for University of Akron bookstore and is considering the pricing for University of Akron t-shirts. John is aware that the bookstore is in close competition with other stores in town such as Target that also sell University of Akron t-shirts. To develop a better understanding of the consumers’ sensitivities, John wants to estimate the price elasticity of shirts, and he wants to focus on the best-selling Zips shirt. Luckily for John, there exists some variation due to the frequent price promotion for this shirt (various sales, etc.). More specifically, the price schedule of the shirt is as follows: Price schedule of Zips shirtRegular price25.3710% off22.8320% off20.330% off17.7640% off15.22 Initially John believed that the demand for shirts is only closely related to its price. So he collected the data of price (in dollars) and quantity sold, which is given in Table 1. John now uses Excel to make a scatter diagram of the quantity sold and price (to verify that a linear relationship does exist) and develop a regression equation to estimate this relationship. Table 1 WeekPrice of ShirtQuantity125.3745225.3740325.3740425.3743522.8341620.345725.3745820.346917.76471025.37411122.83401217.76421325.37411425.37441525.37391620.3431722.83431825.37421925.37432015.2245Excel Instructions for DemandOpen the Excel file with the data (use this to fill in your work/answers). (Note: you should save your work as you go making sure to save as an excel file .xlsx MAC users need to get an Office package downloaded on your computer and available for free from the University of Akron – you need to download the program not use it on a webpage/app).You need to use excel not google sheets (or numbers) and do all of your calculations in excel (not a calculator), this is an excel assignment.Select the Quantity and Price data and then click on Insert on the toolbar and then within the Charts tab select the XY (scatter) chart type (Figure 1)Give your chart a title and label your axes by clicking on your chart and then selecting Add Chart Element or Quick Layout in the Design tab. Right click on the data points in your chart to add a linear trendline to your data and display the equation on the chart.Use the slope and intercept from the demand equation (recall Price is on the y-axis and Quantity is on the x-axis) to predict the quantity of t-shirts that will be sold if the price was $25.00 and if the price was $20.00. In the highlighted cell to the right of the N3 cell type in your equation: = (N3-intercept)/slope to predict the quantity at a price of $25.00. You can either type N3 or click on the cell and excel will automatically fill in N3 into your equation. You should use Excel as your calculator for the entire assignment. You will need to change the equation above to calculate the corresponding quantity for the price in cell N4.Use the highlighted cells to calculate the percent change in quantity and percent change in price (using the midpoint method). For example for percent change in price you would type into excel: =(N3-N4)/((N3+N4)/2). Note: all calculations should be done in excel (i.e. do not calculate figures in a calculator and simply type in the answer into excel)Calculate the elasticity of demand using the percent change in quantity and percent change of price from the previous step. Recall, the elasticity of demand is always positive. You should use the absolute value function in excel: = abs(%?Q/%?P) where you should fill in the cells that correspond to %?Q and %?P.In the final highlighted cell, you will use an if function to automatically indicate whether the elasticity you calculated was inelastic, unit elastic, or elastic: =IF(O9<1, "Inelastic", IF(O9>1, "Elastic", "Unit Elastic ")). You can either type O9 or click on cell O9. Figure 1: Enter Data and Insert Scatter Plot SUPPLY AS FUNCTION OF PRICEJohn knows his supply schedule. He knows the cost of each additional shirt produced (or the marginal cost). Excel Instructions for SupplyOpen the Excel file with the data and go to the Supply sheet.Select the Quantity and Price data and then click on Insert on the toolbar and then within the Charts tab select the XY (scatter) chart type.Give your chart a title and label your axes by clicking on your chart and then selecting Add Chart Element or Quick Layout in the Design tab. Right click on the data points in your chart to add a linear trendline to your data and display the equation on the chart.Use two data points given (when Q=10 and Q=50) to calculate the percent change in quantity and percent change in price (using the midpoint method). Then calculate the elasticity of supply.In the final highlighted cell, you will use an if function (as in the previous instructions for the demand sheet) to automatically indicate whether the elasticity you calculated was inelastic, unit elastic, or elastic.EQUILIBRIUMJohn would now like to find what the equilibrium price and quantity of shirts should be in this market. Excel Instructions for the EquilibriumOpen the Equilibrium tab of the excel file and fill in the price data points for supply and demand using the information from the supply sheet and the information from the trend line you created in the demand sheet.Calculate the slope for the demand curve by using the slope equation: =SLOPE(C3:C7,B3:B7). You can either type in the range of cells C3:C7 or you can highlight the cells C3 to C7 and excel will automatically fill in C3:C7.Calculate the intercept for the demand curve by using the intercept equation: =INTERCEPT(C3:C7,B3:B7)Calculate the slope and intercept for the supply curve. Then write out the equations for the demand function and supply functionCalculate the equilibrium quantity by setting the demand equation (P1) equal to the supply equation (P2) and solving for Q. Then calculate the equilibrium price by filling in the equilibrium quantity you calculated into either the demand equation or the supply equation (hint: see the Chapt.4 lecture slide on calculating the Equilibrium price and quantity).Select the Quantity and Price data for demand and supply and then click on Insert on the toolbar and then within the Charts tab select the XY (scatter) chart type. Give your chart a title and label your axes by clicking on your chart and then selecting Add Chart Element or Quick Layout in the Design tab. Add a point to highlight the equilibrium. Right click on the chart space and click on Select Data, type in the name Equilibrium for the series name then click on the box below and to the right of the Series X value that looks like this and then click on the Equilibrium Quantity you calculated (D14) then click on the box again. Click on the box below and to the right of the Series Y value and click on the Equilibrium price you calculated (D13) and click on the box again and click OK. Finally, change the point you just added to a large black square (by right clicking the point and selecting Format Data Series). At this point, you should have no lines on your graph (only a bunch of dots). Note: Step 8 comes after step 7. Right click on one of the points on the demand curve and select Format Data Series then change the line to a solid blue line (see Figure 2) and remove the markers (see Figure 3). Then right click on one of the points on the supply curve and change the line to a solid orange line and remove the markers.At the regular price ($25.37) calculate the quantity demanded and the quantity supplied. Will there be a shortage or surplus? By how much? Figure 2: Add LineFigure 3: Remove MarkersFigure 4: Add Data PointFigure 5: Final GraphFinal Excel InstructionsTurn in your excel file with all of your answers into Brightspace (under Assessments then click Assignments). Make sure you upload a file that is a .xlsx (or .xls) file.In a word document (1 page double-spaced), describe the market for Zips Shirts. Specifically, paste your demand scatter plot graph (with the linear trend line) and describe what it shows. Paste your final equilibrium graph and explain what the equilibrium price and quantity should be and why. Use the elasticity of demand you calculated to describe how your buyers will respond to a price increase from the equilibrium price to $25.37 and how the market will respond. Explain why pricing shirts at $25.37 would not be a good pricing strategy. Is there any business strategy that could move the market to an equilibrium price of $25.37? If so, what? ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download