Excel/Regression question

Walker – Spring 2023 Managerial Economics FINAL EXAM Instructions You can use your class notes from this class, the videos I published, your textbook and any outside materials I reference directly. Other sources (on the web or otherwise) are considered plagiarism. I do not want you to consult with your classmates or other individuals. Your work should be your own. I will use the Turnitin software to check all materials submitted against web sources and (artificial intelligence sources). By completing this exam, you are implicitly agreeing to abide by the university honor code. Upload both your final excel file and your word document or pdf as part of your submission, but all work you want considered for evaluation should be included as part of a single word or pdf document. The excel file is only for reference if needed.

DO NOT USE any AI such ChatGPT or any others. Turnitin Software will detect this, and you will get a ZERO. No tolerance policy.

Excel/Regression question 1. (65 points total) In the Microsoft Excel document included with your exam materials you will find a spreadsheet titled “drinkmix” containing sales information about grocery store sales of your company’s product, a powdered drink mix that is added to water. You will also find a codebook with descriptions of each variable and the units it is expressed in. I want to stress that I want professional tables and figures. They should have titles and notes if needed to be able to fully understand their contents. Data should be formatted with intuitive and consistent decimal places. Make tables and figures easy to read before you paste them into the document as a final version. a. (10 points) Prepare a table of summary statistics for all available variables listed. Each variable should have a name and a description. Include mean, standard deviation, minimum, and maximum. Paste that table below. b. (10 points) Estimate a linear regression with sales as a function of the other variables in your data. Create a professional looking table in the style of the example table shown in the Microsoft excel workbook included with the exam. Give it a title, make sure it has consistent decimals, make it look professional. Paste that table below. c. (10 points) For each of the independent variables in the model, interpret the effect on the dependent variable (unit sales) of a 1-unit change in the independent variable taking into account whether that variable is statistically significant. d. (6 points) Given the regression estimates from part b, write the implied demand function for your product here. Include all estimated coefficients that are statistically significant in your model. e. (10 points) Does this firm operate in a competitive market or have some degree of market power (it can choose its price)? Based on your understanding of market characteristics, your personal understanding of powdered drink mixes as a consumer, and what you’ve seen of the data, what type of market is your drink mix company likely to be operating in. Explain your answer in at least two paragraphs. f. (5 points) Write the demand function as only a function of the product’s price, given the regression output from above. Create your own simpler demand function using only the variables that are statistically significant and plug in the mean value for any statistically significant variable that is not ownprice.

Plug the mean into the equation with the coefficients that are statistically significant and add those numbers into the constant, you will end up with a familiar looking demand function that looks like this: Write that function here with the numbers you calculated/used from the regression (points awarded at this step) g. (10 points) Envision that you know your drink mix company has a cost function equation to c(q) = 400 + 0.2q. Now, you will be only using changes in price to calculate optimum price and quantity to maximize profits (for the other statistically significant coefficients, plug in that they are equal to zero) so you will have a demand function like: Q = constant – Coefficient*(price). Start with a price equal to $0.00 and increase the price by $0.05 in each row until you reach $0.80. Using that range of prices, estimate quantity demanded, revenue, cost, and profits (have a column for each). Create a professional looking table in the style of the example table shown in the Microsoft excel workbook included with the exam. Give it a title, make sure it has consistent decimal places and make it look professional. Paste that table below. h. (4 points) What is your profit maximizing price, quantity, and maximum profit given the information from your regression model and table? 2. (15 points) Envision a scenario where you have a strategic opportunity where you could invest a specific amount of money today, say $20,000 for an anticipated increase in profits that will continue each year for the next ten years, say $3,000 each year. A table in Excel showing the NPV of the scenarios in parts a and b will likely help in your explanation, note that I am not stating a specific interest rate. (on purpose). a. Describe the process of how you would use the concept of the time value of money and net present value to consider this decision. How do you know if this is a “good” business decision? Be explicit about your thought process. b. How would changes in the interest rate affect your decision? What if the increased profits didn’t start until year 5 and continue until year 15 instead of year 1 through 10? Explicitly explain how changes in the interest rate and the timing of costs and benefits (sooner or later) would affect the NPV of costs and benefits in the future. 3. (10 points) All products that are produced have risks. You currently have a safety feature included on a product that your firm is making that make them (slightly) safer but decreases profitability. If you stopped adding the feature your company could make an additional $1 million dollars in profit for each 100,000 car seats sold, but it would increase the likelihood of injury by an estimated 1 in 100,000 of infant death. Thus, if you sold approximately 100,000 car seats, that could result in one infant death. For each infant death, there is a 50% chance your company would be sued and your expected value of legal losses are $500,000 for each time you are sued. You have been asked to decide whether the safety feature should be included. What do you think should be done, and why? Write at least two paragraphs defending your position using concepts from the course and strategic decision-making using economics terms.