Question
1. For this series of questions you will use the data in the Excel file included to estimate a linear regression model in excel and
1. For this series of questions you will use the data in the Excel file included to estimate a linear regression model in excel and interpret the results. The spreadsheet named "sales data" contains information about monthly sales revenue for a franchised chain of 75 food trucks named The Cheddar Chariot in the greater mid-Atlantic region along with monthly sales revenue per location (in 000s of US dollars), average retail price of their most popular retail item (The Cheese Louise), and advertising dollars spent (in 000s of US $'s) in the region. I have included a codebook in the excel file describing what each variable is which should help. a. Create table of summary statistics containing columns for mean, standard deviation, minimum, and maximum value. It will likely be easiest to calculate those values using Excel formulas. Format the table to look like the example I have given in Excel, adding descriptive labels for the variable names so it will be possible for a reader to understand the scale (00s, 000s, $'s, 000s of $'s, for example). You will be graded on style and content. Paste the table here. c. Create table with linear regression results with average monthly sales revenue as the dependent variable and the other two variables as the independent variables. You will have to estimate a linear regression in Excel to get these values, I recorded a video describing the process of estimating a regression in Excel for Week 4. Format the table to look like the example I have given here adding descriptive labels for the variable names. You will be graded on style and content. Paste that table here. d. Interpret the coefficients you estimated in the model (constant, price of a cheese louise, and advertising dollars). For each of the dependent variables, tell me what a 1 unit increase in the value would cause to happen to the dependent variable. Be sure to take into account whether the coefficient is statistically significant in your description of the effect. e. Write the implied demand curve below based on your regression results (Monthly sales revenue = Estimated constant - (Coefficient)pcheeselouise).[1] f. Using the equation from the linear regression above, create table containing price, quantity, total revenue, total cost, and profit that begins at cheese louise price = $0.50 and increases in $0.50 increments until it reaches $15.00. You believe that the cost function for your food truck business is approximately C(Q) = 100 + 0.5Q2 (cost stated in thousands). Format this table to look professional, give it a title, and use consistent decimal places. Paste it here. g. What price/quantity combination will maximize profit? Inherently, given the structure of your problem your quantity sold is stated in thousands. What is the maximum total monthly profit? In dollars, what were you monthly sales and monthly cost of goods sold that generated that profit? h. Given what you know about the relationship between elasticity and profit maximization are you operating in the elastic, unit elastic, or inelastic portion of the demand curve? Why would a business always want to produce in this part of the demand curve?
sales | price | advert | |||||
73.2 | 5.69 | 1.3 | This is a sample of 75 restaurants from a franchised chain, the variables are represented as those below | ||||
71.8 | 6.49 | 2.9 | sales = S Monthly sales revenue ($1000s) | ||||
62.4 | 5.63 | 0.8 | price = P A price index for all products sold in a given month. | ||||
67.4 | 6.22 | 0.7 | advert = A Expenditure on advertising ($1000s) | ||||
89.3 | 5.02 | 1.5 | |||||
70.3 | 6.41 | 1.3 | |||||
73.2 | 5.85 | 1.8 | |||||
86.1 | 5.41 | 2.4 | |||||
81 | 6.24 | 0.7 | |||||
76.4 | 6.2 | 3 | |||||
76.6 | 5.48 | 2.8 | |||||
82.2 | 6.14 | 2.7 | |||||
82.1 | 5.37 | 2.8 | |||||
68.6 | 6.45 | 2.8 | |||||
76.5 | 5.35 | 2.3 | |||||
80.3 | 5.22 | 1.7 | |||||
70.7 | 5.89 | 1.5 | |||||
75 | 5.21 | 0.8 | |||||
73.7 | 6 | 2.9 | |||||
71.2 | 6.37 | 0.5 | |||||
84.7 | 5.33 | 2.1 | |||||
73.6 | 5.23 | 0.8 | |||||
73.7 | 5.88 | 1.1 | |||||
78.1 | 6.24 | 1.9 | |||||
75.7 | 5.59 | 2.1 | |||||
74.4 | 6.22 | 1.3 | |||||
68.7 | 6.41 | 1.1 | |||||
83.9 | 4.96 | 1.1 | |||||
86.1 | 4.83 | 2.9 | |||||
73.7 | 6.35 | 1.4 | |||||
75.7 | 6.47 | 2.5 | |||||
78.8 | 5.69 | 3 | |||||
73.7 | 5.56 | 1 | |||||
80.2 | 6.41 | 3.1 | |||||
69.9 | 5.54 | 0.5 | |||||
69.1 | 6.47 | 2.7 | |||||
83.8 | 4.94 | 0.9 | |||||
84.3 | 6.16 | 1.5 | |||||
66 | 5.93 | 2.8 | |||||
84.3 | 5.2 | 2.3 | |||||
79.5 | 5.62 | 1.2 | |||||
80.2 | 5.28 | 3.1 | |||||
67.6 | 5.46 | 1 | |||||
86.5 | 5.11 | 2.5 | |||||
87.6 | 5.04 | 2.1 | |||||
84.2 | 5.08 | 2.8 | |||||
75.2 | 5.86 | 3.1 | |||||
84.7 | 4.89 | 3.1 | |||||
73.7 | 5.68 | 0.9 | |||||
81.2 | 5.83 | 1.8 | |||||
69 | 6.33 | 3.1 | |||||
69.7 | 6.47 | 1.9 | |||||
78.1 | 5.7 | 0.7 | |||||
88 | 5.22 | 1.6 | |||||
80.4 | 5.05 | 2.9 | |||||
79.7 | 5.76 | 2.3 | |||||
73.2 | 6.25 | 1.7 | |||||
85.9 | 5.34 | 1.8 | |||||
83.3 | 4.98 | 0.6 | |||||
73.6 | 6.39 | 3.1 | |||||
79.2 | 6.22 | 1.2 | |||||
88.1 | 5.1 | 2.1 | |||||
64.5 | 6.49 | 0.5 | |||||
84.1 | 4.86 | 2.9 | |||||
91.2 | 5.1 | 1.6 | |||||
71.8 | 5.98 | 1.5 | |||||
80.6 | 5.02 | 2 | |||||
73.1 | 5.08 | 1.3 | |||||
81 | 5.23 | 1.1 | |||||
73.7 | 6.02 | 2.2 | |||||
82.2 | 5.73 | 1.7 | |||||
74.2 | 5.11 | 0.7 | |||||
75.4 | 5.71 | 0.7 | |||||
81.3 | 5.45 | 2 | |||||
75 | 6.05 | 2.2 |
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started