**Unformatted text preview:**

Module 6 Frozen Pizza Sales and Prices Background In this module you will be looking at sales and price data for frozen pizza in grocery stores You have data from 83 stores on three brands DiGiorno RedBaron and Totino s over about two years Using this data you will build a model to estimate the quantity that will sell at a specified price point and assess what price point will maximize profit The Excel workbook for this module consists of 1 tab The Filtered tab contains all the data but it is filtered to show only data for DiGiorno at Store 5 It also includes some additional calculations Note that if you change the filters different rows of the data are displayed and hidden If you are unfamiliar with working with filters first view the screencast Working with Filters Part 0 Linear Models Linear equations are very simple equations but they can be quite powerful at modeling real world phenomenon Once a model is created it can be used to make predictions To begin this Module view the screencast Linear Models Screencast Screencast 1 Part 1 Price vs Quantity Scatter Plots and Correlations Here is a scatter plot for the price and quantity pairs for DiGiorno at Store 5 for the 116 weeks of data available A modified version of this scatter plot shows on the Filtered tab Quantity vs Price 0 0 00 1 00 2 00 4 00 5 00 6 00 3 00 Price 1 What is the correlation between price and quantity for DiGiorno at Store 5 If you need a review on correlation view the screencast Correlation Coefficient 2 RedBaron at Store 5 at Store 5 a Create a similar scatter plot that shows the price and quantity pairs for RedBaron b What is the correlation between price and quantity for RedBaron at Store 5 100 000 Units 80 000 60 000 40 000 20 000 2 Part 2 Estimating the Price and Quantity Relationship for DiGiorno at Store 5 Above we looked at the scatter plot of price and quantity for DiGiorno in Store 5 In the chart below you see a line that passes through two points 1 Looking at the graph above and using the data in the spreadsheet find the equation of this line Note that the initial line appearing in the Excel workbook may not be the same as the line shown above 2 In your spreadsheet there is a graph with a scatter plot of price versus quantity combined with the graph of an equation of quantity as a function of price a If you type in 85 000 for the Intercept b and 17 000 for the Slope m under the linear model what is the value for the quantity estimated for a price of 4 25 3 b Adjust the intercept b and slope m values in the spreadsheet until you have what looks to you from your graph like a best fit What are the intercept b and slope m values b m Difference c Notice that there is a column for Estimated Quantity and one for Squared The Estimated Quantity is the y value of the line y mx b where the m and the b are in cells on the spreadsheet and the x is the price value for that row To find the Squared Difference subtract the Quantity Sold actual value from the data from the Estimated Quantity predicted by the line Then square that difference Looking at the graph how would you describe what Squared Difference represents 4 d There is also a cell on that tab that shows Total Squared Difference i e the sum of the values in the Squared Difference column The lower that total is the better the fit of the line to the data Find the Total Squared Difference when the intercept is 75 937 and the slope is 15 333 e Find the Total Squared Difference when the intercept is 80 000 and the slope is 16 000 Screencast f Find the values of the intercept and slope that make the Total Squared Difference as low as possible Hint Try using the Solver tool in Excel This is an add in and you may need to add it If you are unfamiliar with the Solver Tool first view the screencast SOLVER b m 5 Part 3 Estimating the Price and Quantity Relationship for RedBaron at Store 5 1 Answer the following questions about RedBaron at Store 5 a What was the price of RedBaron in Store 5 in week 44 b What was the quantity sold of RedBaron in Store 5 in week 66 2 Determine the equation of the linear model that passes through the price quantity pair for RedBaron at Store 5 in Weeks 46 and 71 What is the Total Squared Difference for all weeks for RedBaron at Store 5 with this specific slope and intercept 3 Find the values for the intercept b and the slope m that make the Total Squared Difference as low as possible b m 6 4 The steps above are the building blocks for running a simple regression which is procedure for finding the best fit y as a linear function of x by minimizing the sum of squared differences between the points and the line There are at least two other ways to find that best fit line in Excel See if you can get them to replicate your optimal m and b a Look for an option to add a Trendline to your scatter plot There should also be a formatting option for the Trendline to display to equation of the line if it doesn t display by default b The SLOPE and INTERCEPT functions can be used to determine the slope and intercept of the line of best fit 7 Part 4 Price and Profit You will calculate the store s actual profit Actual profit price cost actual quantity sold for each product for each week 1 For DiGiorno at Store 5 assume that DiGiorno costs the store 1 17 a Create a scatter plot of price on the horizontal axis and actual profit on the vertical axis for all weeks b What was the price during the week with the highest profit 2 For RedBaron at Store 5 assume that RedBaron costs the store 1 03 a Create a scatter plot of price on the horizontal axis and actual profit on the vertical axis for all weeks b What was the price during the week with the highest profit 3 For DiGiorno at Store 5 a Using the cost for DiGiorno listed above calculate estimated profit for price levels of 1 00 1 50 2 00 5 50 Estimated profit price cost estimated quantity For estimated quantity use the quantity predicted by the best fit line from the end of Part 2 These come from the m and b values that minimized Total Squared Difference What is the estimated profit for a price of 3 50 b Write a function by hand for the weekly estimated profit f x where the price is x dollars 8 c Create a graph that has both …

View Full Document