Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

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

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image_2

Step: 3

blur-text-image_3

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Horngrens Accounting Volume 1

Authors: Tracie Miller Nobles, Brenda Mattison, Ella Mae Matsumura

12th Canadian Edition

0136889373, 9780136889373

More Books

Students also viewed these Accounting questions

Question

=+c) State the null and alternative hypotheses.

Answered: 1 week ago

Question

undertake a thematic analysis of your data;

Answered: 1 week ago