Need This shown in Excel:

Norwest Property Management (NPM) was founded in 1987 by Henri Rouark. Mr. Rouark had arrived from France several years earlier and had worked in an uncles hotel in San Francisco, where he obtained a firsthand background in property management. Wishing to be his own boss, he founded his own company, Norwest Property Management.

NPM provides management services to homeowners in popular resort areas. The company contracts with home owners to rent their properties to vacationers during times when the homeowners will not be using their homes. The company charges the homeowners a percentage of the rent as their fee, and the homeowner receives the difference between the rent paid and NPMs fee.

In return for the fee, Mr. Rouark and his staff make all arrangements with the renters and collects rents from them. The company arranges for cleaning the property after renters leave. For an additional fee, at a homeowners option, NPM will also provide maintenance services.

Under Mr. Rouarks direction, the company has signed contracts with many owners of second homes in the Lake Tahoe, Russian River, and Grass Valley areas of Northern California; in the areas around Klamath Falls and Bend, Oregon; and on the island of Oahu, Hawaii.

NPM has done an excellent job for both homeowners and renters, and the company has prospered as a result. NPMs annual receipts (in millions of dollars) have grown as follows during the past 8 years:

Year 2009 2008 2007 2006 2005 2004 2003 2002

Receipts ($ million) 74.4 57.1 44.3 33.2 25.3 19.8 15.8 12.6

In March 2010, Mr. Rouark was approached by Ms. Isabel Monroe, representing Goliath Home Services (GHS), about buying NPM. GHS is a successful company that provides similar services in Southern California, Nevada, and Arizona and wants to expand further. The company is well financed and is somewhat larger than NPM. Rouark is receptive to the idea of selling his company, provided he can get a good price for it.

Both parties recognize that Rouarks company is well based and positioned to continue to grow in the future. In fact, GHSs interest is based not only on NPMs financial track record since 1990 but also on NPMs future potential. Therefore, in order to help provide a basis for negotiating a fair purchase price, Monroe has projected the NPMs annual receipts to the end of the current year and the next two that is, to the ends of 2010, 2011, and 2012. In order to be objective and avoid personal bias, Monroe did this by using linear regression analysis.

* * * * * * * * * *

a. Evaluate the coefficients of a linear regression equation (i.e., the intercept and slope) for projecting the trend of NPMs annual receipts for 2002 to 2009. Provide a complete specification for the linear regression model. The specification should include the equation of the model, with the values for the intercept and slope determined from the data, and a definition of the models dependent and independent variables. Also include the models standard error of estimate.

b. Evaluate the coefficient of correlation for the linear regression model.

c. Use the linear regression model determined in part (a) to forecast NPMs annual receipts for 2010, 2011, and 2012.

* * * * * * * * * *

Rouark is not happy with Monroes projections. He tells her, I dont really understand your mathematical analysis, but Im convinced myself that your projected sales for my company for 2010, 2011, and 2012 are much too low.

Monroe counters. Of course, Mr. Rouark, I understand that you might feel that way. After all, youre influenced by your personal bias and your feelings about your company. By using a mathematically objective procedure, however, Ive eliminated all personal bias in my analysis of your data. Theres nothing personal or biased in my analysis.

Rouark persists. But it still doesnt seem right. How can you be sure your mathematical procedure is correct?

Monroe explains. Heres a spreadsheet with my calculations (i.e., your spreadsheet from parts (a), (b), and (c)). In order to ensure that my linear regression equation is correct, Ive even calculated a correlation coefficient for it. This is a standard statistical procedure. As you can see, the correlation coefficient is very nearly one. That proves the model is very nearly perfect as perfect, in fact, as anyone can make a linear regression model for your data. (N.B. Although Monroes argument that a correlation coefficient close to one validates a model, its not a valid test of a models validity for the reasons discussed in the text. However, it can be an effective statistical argument to justify a model when the other party does not understand statistics and incorrectly believes that it is a valid argument.)

But, just maybe youve made an error in your calculations, Rouark suggests.

Ive done all the calculations myself on a spreadsheet, and Ive had them verified by another person in my office, whos an expert statistician and used a highly respected statistical software package, Monroe responds. Im absolutely certain there are no errors in our calculations, but you can check them yourself, or have one of your assistants check them or a consultant, or anyone else.

Just to be sure, Rouark asks one of his assistants to verify Monroes calculations. The assistant does so, and returns shortly to report that the calculations are correct.

See. I told you my analysis is correct, Monroe crows, hiding her irritation at being challenged. Ive studied statistics and I know my business. Ive used statistics in my job for years. Im absolutely certain that the model and its projections are as correct and as accurate as one can possibly make them.

Rouark is still dissatisfied. However, even though hes a savvy and successful businessman, he doesnt have a good enough mathematical background to refute Monroes analysis. Unhappy with the analysis of his business and what he considers a low offer to buy it, he asks Dorothy Lopez, a friend with an MBA, to project his companys past performance to the years 2010, 2011, and 2012. Ms. Lopez tries two other types of models.

* * * * * * * * * *

d. Evaluate the coefficients of a quadratic and an exponential regression equation for fitting to the trend of the data. Provide complete specifications for your models. Use the parameters from the LOGEST output in your specification for the exponential model. Define all variables. Identify the standard error of estimate for each model. Use your models to forecast NPMs annual receipts for 2010, 2011, and 2012. What is the year-to-year percentage increase projected by the exponential model?

1. Insert charts on your spreadsheets that show the error patterns. Follow the format in the text. Which models are valid, and which are not? Justify your conclusions. Its not necessary to repeat the conditions for model validity, which are in the text. Just demonstrate that you know how to apply them. You can respond to this by using simple, direct sentences, such as: The (type) model is (or is not) valid because (complete the sentence by using what you have placed on your worksheet to justify your conclusion). Dont bother discussing conditions that are NOT required for validating a model. Limit any justifications or discussions to whats essential.

* * * * * * * * * *

The purpose of the questions in parts f and g is to see how well you understand your results from the preceding parts and can use them at a bargaining table to get the best price for your side and to rebut the position of the other side. Read the questions carefully and give simple direct responses to each. Limit your responses to whats essential. Theres no need to repeat information youve already provided in parts a to f. Do not raise or wander off into issues that are not essential for your position as the buyer or seller.

* * * * * * * * * *

f. From the standpoint of the buyer (i.e., Monroe), which of the three models (linear, quadratic, or exponential) is most attractive as a basis for negotiating the best price? Why is it most attractive to the buyer? If you were Monroe and the seller did not understand statistics well, what is the most convincing statistical argument you could use to defend your choice of model? Is your argument correct or not? If not correct, why might your argument nevertheless be convincing? If you were Rouark, whats the best statistical argument you can make for rebutting Monroes choice of model? Answer all parts of this question.

g. From the standpoint of the seller (i.e., Rouark), which of the three models (linear, quadratic, or exponential) is most attractive as a basis for negotiating the best price? Why is it most attractive to the seller? If you were Rouark and the buyer did not understand statistics well, what is the most convincing statistical argument you could use to defend your choice of model? Is your argument correct or not? If not correct, why might your argument nevertheless be convincing? If you were Monroe, whats the best statistical argument you can make for rebutting Rouarks choice of models? Answer all parts of this question.

h. Which of the three models should be used as a basis for negotiating a selling price thats fair to both sides? What is the best statistical argument you can make for defending your choice?