# MASYGC 1210- Programming Data Analysis Multiple Linear Regression Project.

Group Assignment 2 MASY GC1210-201 Fall 2021 PART 1 Multiple Linear Regression Understanding Retail Sales • Eight-Twelve Convenience Stores Inc. is evaluating sales in its 27 franchise store locations to get a better understanding how to plan new stores. Its franchise operators have provided annual sales, square footage (size), inventory, advertising spending, number of families in their district, and the number of competing stores in their district. • Eight-Twelve Management would like to answer: • What factors affect sales in our franchises and how? • Can we create a model so we design a store size given certain demographics so we can achieve a certain level of sales? Multiple Linear Regression Using Excel • Use the attached course data file Franchises.xlxs. • Open it using Excel. • Following the practice of not changing the raw data, select the entire the file and copy it as the shaped file in a new spreadsheet and label the tab Model. • Make sure the file is properly shaped by having the dependent variable (SALES) to the left of the independent variables (SQFT, INVENTORY, ADVERTISING, FAMILIES, STORES), and that the independent variable columns are contiguous. Multiple Linear Regression Using Excel • Invoke the Excel Analysis ToolPak and select the Regression function. For the y-variable use the SALES data (make sure to include the column header and click “my data has headers” box.) For the xvariable use all the data in the impendent variable columns (also include headers.). Check “My data has headers” box. • You can put the results on another spreadsheet or right next to our data table. For now, select a cell next to the table. • Analyze the results. Multiple Linear Regression Using Excel • How much of the variation in the SALES data can be explained by the model? (See R-squared.) • How confident are you in the validity of this model (See Significance F.) • What are the relationships between SALES and the other factors (See Coefficients.) • Build a linear model using the intercept and the coefficients. • How confident are we on each coefficient? (See Pvalue.) • What does the model predict the sales for the first store and how far off is it compared to actual data? Multiple Linear Regression Using Excel • Now that management understands its existing stores, they would like you to apply what you found to project sales at a new store opening. • We just opened a store in a neighborhood with 5,000 families, the store is 5,000 sq ft, we are planning to spend \$5,000 a month in advertising, carry \$250,000 in inventory and there are 5 competing stores in the neighborhood. What are the projected sales? (Hint, use the information in the data dictionary to normalize the variables properly and use the model equation) • Management would also like you to evaluate up to how much we should spend on advertising at a potential new store: • We want to open a 10,000 sq ft store and realize \$500,000 a month in sales in a neighborhood with 10,000 families, we are planning to spend \$10,000 a month in advertising, carry \$500,000 in inventory and there are 10 competing stores in the neighborhood. How much should we spend monthly in advertising to realize our expected sales? (Hint, use the model equation in reverse) PART 2 Time Series Analysis & Forecasting Presenting a Data Set and Analysis • Your team should imagine itself a consulting firm being charged to evaluate a large publicly available data set and demonstrate a wide application of the concepts learned in our Time Series Analysis & Forecasting class and text and make meaningful analysis of the data and what decisions they can inform. • Choose an appropriate real-world dataset that allows you to graphically illustrate “horizontal” pattern that includes a MINIMUM of 5 years of data in a monthly breakdown or 10 years of data in a quarterly breakdown • Illustrate the data set graphically and apply moving average, weighted moving average, exponential smoothing, & linear regression to forecast a future year. Also demonstrate calculation of the forecasting errors. • Analyze and comment on the accuracy of these forecasts and what they tell you about the behavior and conclusions you can make of future behavior of the system you are analyzing going forward and what decisions it may inform. Note if the data demonstrates seasonality or cyclicality and apply the appropriate adjustments to your analysis Potential sources for data sets (optional) • https://www.springboard.com/blog/data-science/free-public-datasets-data-science-project/ • https://libraryguides.missouri.edu/datasets/public-use • https://guides.emich.edu/data/free-data • https://r-dir.com/reference/datasets.html Part 3 Presenting a Decision Analysis Team 1 Case – Property Purchase Strategy Glenn Foreman, President of Oceanview Development Corporation, is considering submitting a bid to purchase property that will be sold by sealed bid at a county tax foreclosure. Glenn’s initial judgement is to submit a bid of \$5 million. Based on his experience, Glenn estimates that a bid of \$5 million will have a 0.2 probability of being the highest bid and securing the property for Oceanview. The current date is June 1. Sealed bids for the property must be submitted by August 15. The winning bid will be announced on September 1. If Oceanview submits the highest bid and obtains the property, the firm plans to build and sell a complex of luxury condominiums. However, a complicating factor is that the property is currently zoned for single-family residences only. Glenn believes that a referendum could be placed on the voting ballot in time for the November election. Passage of the referendum would change the zoning of the property and permit construction of the condominiums. The sealed-bid procedure requires the bid to be submitted with a certified check for 10% of the amount bid. If the bid is rejected, the deposit is refunded. If the bid is accepted, the deposit is the down payment for the property. However, if the bid is accepted and the bidder does not follow through with the purchase and meet the remainder of the financial obligation within six months, the deposit will be forfeited. In this case, the county will offer the property to the next highest bidder. To determine whether Oceanview should submit the \$5 million bid, Glenn conducted some preliminary analysis. This preliminary work provided an assessment of 0.3 for the probability that the referendum for a zoning change will be approved and resulted in the following estimates of the costs and revenues that will be incurred if the condominiums are built: Cost and Revenue Estimates Revenue from condominium sales \$15,000,000 Cost Property \$5,000,000 Construction expenses \$8,000,000 If Oceanview obtains the property and the zoning change is rejected in November, Glenn believes that the best option would be for the firm not to complete the purchase of the property. In this case, Oceanview would forfeit the 10% deposit that accompanied the bid. Because the likelihood that the zoning referendum will be approved is such an important factor in the decision process, Glenn suggested that the firm hire a market research service to conduct a survey of voters. The survey would provide a better estimate of the likelihood that the referendum for a zoning change would be approved. The market research firm that Oceanview Development has worked with in the past has agreed to do the study for \$15,000. The results of the study will be available on August 1, so that Oceanview will have this information before the August 15 bid deadline. The results of the survey will be either a prediction that the zoning change will be approved or a prediction that the zoning change will be rejected. After considering the record of the market research service in previous studies conducted for Oceanview, Glenn developed the following probability estimates concerning the accuracy of the market research information: Perform an analysis of the problem facing the Oceanview Development Corporation, and prepare a report that summarizes you