Report end excel analysis

Issues and Scope: worth 5 points out of 30

Writing: worth 5 points out of 30

· Table Of Content (TOC)

· Typo free

· Presentation of the summaries of various tests.

· Proper formatting, sentence structure, and English

· Nice easy to read writing style

· References

1. Marks are deducted for, (i) too many typos, (ii) poorly written passages, (iii) poor organization, and (iv) poor documentation – for example, omitting the Regression Output, Supporting calculations, etc.

2. Formatting should be according to APA guidelines.

3. Executive Summary is not part of word count, hence, if included, it is not read.

4. You are allowed two appendices, where you should deposit your calculations, computer output, and original data, etc. Do keep in mind that anything that justifies your conclusion(s) in the main body of the report should be in the main body -- for example, your regression equation with the explanation of its notation should be in the main body. Similarly, if you use p-value to justify a statistical significance, you should provide p-value (in a table for all regression coefficients), in the main body.

5. Exceeding the word count limit -- 1000 words

Pl. pay attention to English, including sentence structure.

We'll use the familiar 5% as significance level for the Assignment I. Also, for the purposes of Assignment 1, we'll not discard any variable from the regressed model, though it may be statistically not significant.

· You need not perform the t-test for significance, pl. use the p-value and its interpretation; it will save you time. (It may help, if you did a quick review of Ch. 4 (T&M), to refresh your understanding of Regression output.)

You are asked to run two regression -- Linear model, and the Log Linear model. You'll see that the explanatory power of both is nearly equal, therefore, state in your Report that you are choosing, Linear model, as this will help you answer optimal price question.

· For computing elasticities, pl. use averages as typical values

· For optimal price, keep in mind that Harry has no control over values of M, N, and P_h, but pl. do not drop these variables from the equation, rather use the average values of these as typical values, and, using the average value for A, you can get an equation with only two variables -- own-price and quantity.

· Once you have found the optimal quantity, Q*, and corresponding price, P*, to find the corresponding advertising spend, say A*, you should go back to your original regression equation, and keep A as the only variable. Since you know the values for all other variables, you can compute A*.

· For all computational work, pl. let EXCEL perform the computations; it would also reduce your headache. (Rounding too early will cause erroneous results, which can be avoided by letting EXCEL do the computations.)

· In your Assignment 1 response pl. ensure that you have:

· clearly answered the questions raised by the CEO and that your answers can be easily found.

· provided all the supporting computations. As you know, most of the tables, figures, and computations should be in Appendices, so that CEO's Assistant can consult them. (Organize your Appendices; you can have up to two Appendices!)

· adopted a writing style that is reader friendly – easy to read and well structured, organized into appropriate sections, and carefully checked for spellings and typos. Pl. note that marks are specifically allocated for writing.

The relevant computer output information should be included in an Appendix and , your EXCEL file containing the regression output should be attached. Thus, there will be two file attachments -- a WORD file (your Report to Harry), and an EXCEL file (your Regression output).

You accepted an offer from MagnaMed, and today is your first day working there as a business analyst. MagnaMed does business by producing and selling a range of medical diagnostic products. Its main product is the TrickKit™, an easy-to-use medical diagnostic tool for Hepatitis B. Trained personnel in physicians’ offices, medical facilities, clinical laboratories, and emergency care situations could use this as a screening assay to produce results in less than a minute. SusaMed is the main competitor of MagnaMed, and it sells a similar product, HepaTest, the only alternative to TrickKit™ in the market.

Last week, MagnaMed’s other business analyst, Sally Litch, didn’t show up for work. Rumour has it that Sally went to Vegas for the weekend and ended up joining Cirque du Soleil, but nobody really knows. The police have been informed.

Before she disappeared, Sally was working on an important and time-sensitive project for MagnaMed’s CEO, Garry Smith. The project involves four interrelated tasks:

1. Estimate the linear and log-linear empirical demand functions for TrickKits™.

2. Interpret the estimated demand functions for TrickKits™.

3. Make pertinent recommendations to senior management based on the empirical demand

functions.

4. Write a short report summarizing the results of the analysis and any recommendations.

Garry asks you to complete the project. He tells you that he’s particularly interested in knowing if the current price of the TrickKit™ is optimal and, if not, whether it should be increased or decreased. He tells you that the current price of a TrickKit™ is $30.00 and that since the production process has been set up, the marginal cost of producing additional kits is essentially zero. Garry is also interested in knowing whether or not the firm’s advertising expenditures

over the last three years have been a good investment and anything else relevant that your analysis uncovers.

You only have a week to complete the analysis, interpret the results, and summarize your findings and recommendations in a brief report. Garry tells you that the main body of the report must be short (a 1000 words at most excluding the title page and any appendices), to the point, and not overly technical. He tells you that his formal knowledge of mathematics, economics, and statistics is somewhat limited, and asks you to keep that in mind as you write the main body of the report. Nevertheless, your conclusions and recommendations must be based on a rigorous analysis of the available data and you should provide a concise summary of any technical details in an appendix. Garry also tells you to be explicit about any important limitations that your analysis might have.

Finally, Garry tells you that he likes his reports to be broken up into sections with sensible and self-explanatory headings because it makes them much easier to read and understand. Garry gives you a copy of an Excel spreadsheet that Sally sent him. It contains data that Sally collected on the variables she thought might have a significant influence on the demand for TrickKits™. It also contains some notes Sally made about the data and how to proceed with the

analysis. The spreadsheet is the only record of Sally’s work on the project.

Garry has complete confidence in Sally’s technical abilities and professional judgment. As such, he tells you to take the notes in Sally’s spreadsheet at face value and to use them as a starting point for your analysis. As Garry puts it, “You’ve only got a week, so there’s no point trying to reinvent the wheel. If Sally thought the data should be treated in a particular way, then that’s what you should do.”