Fill This Form To Receive Instant Help
Fill This Form To Receive Instant Help
Homework answers / question archive / MBA700 HOMEWORK 4 Part I: FOREST SWAMP "It don't get any better than this," thought Rick Blackbird as he took another sip of Molson's Golden Ale and watched the sun set through the mangroves on his estate in Forest Swamp
"It don't get any better than this," thought Rick Blackbird as he took another sip of Molson's Golden Ale and watched the sun set through the mangroves on his estate in Forest Swamp. He had built his 1,800-square-foot, one-story, 1.5-bath mansion six years earlier and was convinced it was the best, if not the most handsome, of the investments he had made. It was true that the gators in the swamp had recently dined on his dog, but this was a minor inconvenience. Besides, he had recently switched to cats. His position at Piedmont Business School was secure due to his best?selling organizational behavior texts. Now that he had decided to permanently settle in the community, he was trying to decide whether to move to a larger (and more appropriate) dwelling, to renovate his existing structure and then sell it, or to stay put and enjoy Forest Swamp. Naturally, he needed to learn more about housing values to make an informed decision.
Rick realized he needed some way of determining the values of the houses in his community and, in particular, the value of his own house. Although he could always pay a realtor to do an appraisal, Rick was convinced they frequently overvalued houses in the hope of later obtaining the right to the listing. Because he possessed a keen analytical mind, Rick saw that he was better off trying to develop a statistical model of housing values. A colleague of his, Alan Freebee, mentioned that the problem looked like a natural application of regression analysis. Armed with this idea, Rick went to the county tax office and recorded the relevant data from 40 randomly chosen, recent house closings in the community. He figured that the data measured the hard truth, rather than some realtor's hopeful guess as to what a house might sell for. For the 40 closings, Rick recorded the sales price (in $1000s), the number of square feet in the house (in 100s of square feet), the number of stories, the number of bathrooms, the age of the house, and whether the house was in walking distance of the Piedmont campus. Though he and his lovely wife, Jill, personally preferred to live in Forest Swamp (a short drive from campus), he realized that many people were willing to pay a premium to live closer.
Rick entered the data into Excel and ran a multiple regression of sales price on the five housing characteristics. The partial listing of the data and the regression output are given below. As Rick casually brushed a copperhead off the printout, he realized he had a number of unanswered questions about the regression.
1. What is the value of the coefficient of determination? Is this value significantly greater than zero? Test at the 1 percent level. Support your answer.
2. Which of the calculated slope coefficients are significantly different from zero? Test at the 1 percent level. Support your answer.
3. Obtain a point estimate and approximate 99 percent confidence interval for the value of Rick's house.
4. To finance a new mortgage, Rick would have to sell his existing mansion for at least $80,000. What is the probability that he could do so? Show your work.
5. Building costs in Rick's community currently average about $30 per square foot. Can he make any money if he adds a new family room to the side of his house before he ever sells it? Support your answer.
6. Rick plans to convert his cats' bedroom into a full bathroom. Estimate the change in the selling price of the house if he proceeds with the conversion. Provide a point estimate and a 95 percent confidence interval for the change in price.
7. Rick has been told that, other factors being equal, a house that is older (than another house) should be less valuable (due to normal wear and tear). Does the regression provide convincing evidence to refute this theory? Test at the 1 percent level. Show all relevant calculations, draw a picture, and explain how you reached your conclusion. Give your conclusion in terms of the statement of the problem. What is the prob-value for the test?
8. Rick has been looking at a pretty house within walking distance of campus. Unfortunately, the owner of the house has been asking a price that seems unusually high and has been arguing that homebuyers in the Piedmont community are willing to pay at least $20,000 extra to live within walking distance of campus. Does the regression provide convincing evidence to refute the owner’s argument? Test at the 1 percent level. Show all relevant calculations, draw a picture, and explain how you reached your conclusion. Give your conclusion in terms of the statement of the problem. What is the prob-value for the test?
Alison Rae is owner and operator of Alison’s Restaurant located on West Franklin Street in Chapel Hill. As Alison proudly states, “You can get anything you want at Alison’s Restaurant.” And though it may be true that you can get anything you want at Alison’s Restaurant, the establishment is particularly known for its gourmet French breakfasts. There is no better way to start the morning than with a cup of Alison’s strong black coffee and a delicious buttery croissant. Alison found a French bakery in Montreal that was willing to airmail croissants on a daily basis. The croissants were expensive—they cost her $1.20 each—but she sold them to her restaurant customers for $2.40 apiece. Croissants left over at the end of the day are not very palatable, but a nearby bowling alley was willing to buy any and all leftovers at 40 cents each.
Alison is trying to figure out the optimal number of croissants to stock each day. After observing sales for several weeks, she estimated the following probability distribution for the number of croissants demanded each day:
 |
Although Alison thought there might be an analytic model for determining how many croissants to stock, she knew she could build and run a spreadsheet simulation model more quickly. The Montreal bakery required that in any given week she order the same number of croissants each day, so she decided to simulate a week’s worth of croissant activity.
Alison wishes to determine how many croissants to stock each day to maximize her expected weekly profit.
Your task:
Write a simple Excel model that will calculate the daily profit given any order quantity and any realized daily demand. That is, the order quantity should be entered in one cell, the daily demand should be entered in a second cell, and the model should calculate the daily profit and have it appear in a third cell.
Now suppose the order is 32 croissants per day. Using the spreadsheet you developed to do the daily profit calculations, graph daily profit as a function of daily demand (which varies from 25 to 40). That is, your plot should have daily profit on the y-axis and croissant demand on the x-axis. Turn in a printout of your Excel model and the graph. Just remember that if the demand is less than 32, any leftover croissants are sold at a loss, whereas if demand exceeds 32 you can’t sell what you don’t have.
Here are a few points to remember:
1. This task is mean to be a simple exercise. It shouldn’t take more than 15 minutes.
2. The probability distribution given in the case is irrelevant to this exercise.
3. If you need more than a half-dozen or so cells in Excel, you are overthinking the problem.
4. We will show you how to use a software package called Crystal Ball to analyze Alison’s problem. For the time being we only want you to write that part of the Excel model where the daily net profit is calculated for any order quantity and any daily demand.
