Stock Valuation Assignment (Fall 2017 Data)

The purpose of this analysis is to find an intrinsic value for Microsoft (MSFT) using the both the Constant Dividend Discount Model (DDM) and the Non-constant DDM. You will need to (1) estimate Beta in order to calculate the required return for MSFT; (2) estimate dividend growth rate; and (3) estimate future dividends.   

Submit your Excel spreadsheet with all data and formulas so that your answers can be replicated. You may answer the questions on the spreadsheet. HOWEVER, WRAP YOUR TEXT!!! I do NOT want to see text running across 40 columns. Remember, Excel is not a word processor. Do a simple draft print to see if your output is in readable form. Follow instructions as written. NEATNESS AND ORGANIZATION MATTERS!

You are analyzing Microsoft to find an intrinsic value for Microsoft (MSFT) using the both the Constant Dividend Discount Model (DDM) and the Non-constant DDM. I have provided you with an Excel spreadsheet of monthly prices (121 months) from Sept 1, 2007 to Sept 1, 2017). These prices have already been adjusted for dividends. List dates and prices out on your spreadsheet in order to calculate monthly returns.

Using the prices provided, calculate the monthly returns for each of the stocks, where r = (P_t/P_t-1) – 1; which is the same as [(P_t-P_t-1)/ P_t-1] as I covered in the Lecture Video. PLEASE NOTE THAT THE DATA IS LISTED FROM SEPT 2007 TO SEPT 2017! SO BE CAREFUL WITH YOUR RETURN FORMULA! There are 121 months to calculate 120 monthly returns. You may post monthly returns as decimals to 6 places or percentages to 4 places. For example, average return for MSFT can be written as .009999 or .9999%.

(10 points)

At the bottom of the column for each stock calculate the Average Monthly Return (use AVERAGE() function) and the Standard Deviation [use STDEV.P()] population function NOT STDEV() sample function).

As a check, you should find your average returns to be: MSFT = 1.2310% and SPY = .6861%.

(5 points)

Calculate and Interpret the Correlation Coefficient (r_1,2) between Microsoft (MSFT) and S&P 500 Index (SPY). (use CORREL() function). (5 points)

We can estimate the Beta for MSFT over the 120-month period by running a Regression of SPY returns on the x-axis (independent variable) and MSFT returns on the y-axis (dependent variable). The Beta is the SLOPE of the regression. To find Beta use the SLOPE function in Excel. Be careful use RETURNS NOT prices!

How does your estimate compare to the FinanceYahoo.com beta and the Value Line beta? What does Beta represent? (10 points)

Now, let’s check the stability of Beta. You may again use the SLOPE function in Excel, where SPY is independent & MSFT is dependent variable.

Estimate Beta over the first 60 monthly returns (5 years): October 1, 2007 to September 1, 2012.

Estimate Beta over the second 60 monthly returns (5 years):         October 1, 2012 to September 1, 2017.

What is the beta for each period? Is there a substantial difference between the two Betas?

(5 points)

Given the information below, use the CAPM to estimate the required rate of return for MSFT. Round to 2 decimals, e.g., x.xx%, 1.23%

Return on the market portfolio (SPY) R_SPY = 9.25% (based on 25 years of historical data); the risk free rate is R_f = 3.0% (based on L-T inflation rate of 2.0% & real return of 1.0%); USE MSFT beta estimate: b = 0.97

(10 points)

Based on past trends and ValueLine estimate, let’s assume MSFT will pay a dividend of $1.64 in 2018. Therefore, let’s assume that D₁ = $1.64, because it will not be fully paid until the end of Year 2018. Let’s also assume that MSFT will grow its future dividends at a L-T constant rate of g = 6%. Assuming a required rate of return found in (7) above, estimate the current value of MSFT using the Constant Growth DDM.   Assume that D₁ = $1.64

(10 points)

Now, using the Value Line sheet, estimate the average growth rate of dividends for MSFT over the last 10 years, from 2007-2017? Round your growth estimate to 4 decimal places. [Hint: The Growth rate (g) can be calculated as CPT i on your calculator or in Excel as a TVM problem.

(5 points)

Two-stage Non-constant DDM: Now let’s assume that for the next four years MSFT will grow its dividends at the growth rate you estimated in (9) above. Assuming D₁ = $1.64, what are the dividends for: D₂ ; D₃ ; D₄; and D₅ if they grow at the rate estimated in (9)? You may round each dividend estimate to the nearest penny.

(10 points)

Now, let’s assume that the dividend growth reverts back to a L-T sustainable growth rate = 6% after Year 5 to infinity. Estimate is D₆ and P₅.

(5 points)

Use the Non-constant growth DDM from the Stock Video Lecture (at 17:20) to estimate the current value of MSFT using the dividend information you found in (10) & (11) above; assume a L-T sustainable growth rate of g = 6% after Year 5; and the required rate of return found in (7). [HINT: You already have all the data, not much work left here….find the sum of the PV of the cash flows.]

(10 points)

Which of the two models do you think is more reasonable (Constant DDM or Non-constant DDM)? WHY?

(5 points)

What is the current market price of MSFT? Based on your analysis, would you recommend buying this stock at the current market price? Explain why or why not?

(10 points)

Today's buyers allot an ample amount of time for purchases, and most of them are keen to interact with salespeople.

True

False

Sarah is a salesperson who prefers contacting her sales leads before getting any information about them or their companies. Sarah uses _____ as a method of sales prospecting.

Which of the following technologies is a valuable tool for accumulating and updating prospect information on a regular basis?

Company records can be used for:

FitterYou Inc., a company that manufactures health products, hosts an event at a local hotel to generate potential customers. The company invites approximately 120 prospects to the event through direct mails. At the event, the sales representatives of the company give presentations on the various products offered by the company. In this scenario, FitterYou Inc. is using _____ to generate sales leads.

Who among the following would most likely be a sales lead for a company that manufactures toys?

Salespeople who do not regularly prospect are operating under the assumption that:

Which of the following is true of assessment questions?

The types of questions classified by the amount and specificity of information desired and those classified by strategic purpose are mutually exclusive.

True

False

Which of the following is a difference between open-end questions and closed-end questions?

Which of the following statements is true of pictures?

Which of the following can improve sensing skills in listeners?

A written sales proposal is likely to fail when _____.

_____ are referred to as complete self-contained sales presentations on paper, often accompanied by other verbal sales presentations before or after the delivery of these paper presentations.

Which of the following is a function of Section 5 of the sales dialogue template?

Unless all the stages of the ADAPT process are completed, the customer value proposition will not contain enough detail to be useful.

True

False

Problem 11-26 Close or Retain a Store [LO11-2]

Superior Markets, Inc., operates three stores in a large metropolitan area. A segmented absorption costing income statement for the company for the last quarter is given below:

The North Store has consistently shown losses over the past two years. For this reason, management is giving consideration to closing the store. The company has asked you to make a recommendation as to whether the store should be closed or kept open. The following additional information is available for your use:

1. The breakdown of the selling and administrative expenses that are shown above is as follows:

*Allocated on the basis of sales dollars.

*Allocated on the basis of sales dollars.

1. The lease on the building housing the North Store can be broken with no penalty.

2. The fixtures being used in the North Store would be transferred to the other two stores if the North Store were closed.

3. The general manager of the North Store would be retained and transferred to another position in the company if the North Store were closed. She would be filling a position that would otherwise be filled by hiring a new employee at a salary of $13,100 per quarter. The general manager of the North Store would continue to earn her normal salary of $14,100 per quarter. All other managers and employees in the North store would be discharged.

4. The company has one delivery crew that serves all three stores. One delivery person could be discharged if the North Store were closed. This person’s salary is $5,700 per quarter. The delivery equipment would be distributed to the other stores. The equipment does not wear out through use, but does eventually become obsolete.

5. The company pays employment taxes equal to 15% of their employees' salaries.

6. One-third of the insurance in the North Store is on the store’s fixtures.

7. The “General office salaries” and “General office—other” relate to the overall management of Superior Markets, Inc. If the North Store were closed, one person in the general office could be discharged because of the decrease in overall workload. This person’s compensation is $7,050 per quarter.

Required:

1. How much employee salaries will the company avoid if it closes the North Store?

2. How much employment taxes will the company avoid if it closes the North Store?

3. What is the financial advantage (disadvantage) of closing the North Store?

4. Assuming that the North Store's floor space can’t be subleased, would you recommend closing the North Store?

5. Assume that the North Store's floor space can’t be subleased. However, let's introduce three more assumptions. First, assume that if the North Store were closed, one-fourth of its sales would transfer to the East Store, due to strong customer loyalty to Superior Markets. Second, assume that the East Store has enough capacity to handle the increased sales that would arise from closing the North Store. Third, assume that the increased sales in the East Store would yield the same gross margin as a percentage of sales as present sales in the East store. Given these new assumptions, what is the financial advantage (disadvantage) of closing the North Store?

How much employee salaries will the company avoid if it closes the North Store?

How much employment taxes will the company avoid if it closes the North Store?

What is the financial advantage (disadvantage) of closing the North Store? (Enter any "disadvantages" as a negative value.)

Requirement 5

Assume that the North Store's floor space can’t be subleased. However, let's introduce three more assumptions. First, assume that if the North Store were closed, one-fourth of its sales would transfer to the East Store, due to strong customer loyalty to Superior Markets. Second, assume that the East Store has enough capacity to handle the increased sales that would arise from closing the North Store. Third, assume that the increased sales in the East Store would yield the same gross margin as a percentage of sales as present sales in the East store. Given these new assumptions, what is the financial advantage (disadvantage) of closing the North Store? (Enter any "disadvantages" as a negative value.)

Superior Markets, Inc., operates three stores in a large metropolitan area. A segmented absorption costing income statement for the company for the last quarter is given below:

The North Store has consistently shown losses over the past two years. For this reason, management is giving consideration to closing the store. The company has asked you to make a recommendation as to whether the store should be closed or kept open. The following additional information is available for your use:

1. The breakdown of the selling and administrative expenses that are shown above is as follows:

*Allocated on the basis of sales dollars.

*Allocated on the basis of sales dollars.

1. The lease on the building housing the North Store can be broken with no penalty.

2. The fixtures being used in the North Store would be transferred to the other two stores if the North Store were closed.

3. The general manager of the North Store would be retained and transferred to another position in the company if the North Store were closed. She would be filling a position that would otherwise be filled by hiring a new employee at a salary of $10,800 per quarter. The general manager of the North Store would continue to earn her normal salary of $11,800 per quarter. All other managers and employees in the North store would be discharged.

4. The company has one delivery crew that serves all three stores. One delivery person could be discharged if the North Store were closed. This person’s salary is $4,500 per quarter. The delivery equipment would be distributed to the other stores. The equipment does not wear out through use, but does eventually become obsolete.

5. The company pays employment taxes equal to 15% of their employees' salaries.

6. One-third of the insurance in the North Store is on the store’s fixtures.

7. The “General office salaries” and “General office—other” relate to the overall management of Superior Markets, Inc. If the North Store were closed, one person in the general office could be discharged because of the decrease in overall workload. This person’s compensation is $5,900 per quarter.

Required:

1. How much employee salaries will the company avoid if it closes the North Store?

2. How much employment taxes will the company avoid if it closes the North Store?

3. What is the financial advantage (disadvantage) of closing the North Store?

4. Assuming that the North Store's floor space can’t be subleased, would you recommend closing the North Store?

5. Assume that the North Store's floor space can’t be subleased. However, let's introduce three more assumptions. First, assume that if the North Store were closed, one-fourth of its sales would transfer to the East Store, due to strong customer loyalty to Superior Markets. Second, assume that the East Store has enough capacity to handle the increased sales that would arise from closing the North Store. Third, assume that the increased sales in the East Store would yield the same gross margin as a percentage of sales as present sales in the East store. Given these new assumptions, what is the financial advantage (disadvantage) of closing the North Store?

How much employee salaries will the company avoid if it closes the North Store?

How much employment taxes will the company avoid if it closes the North Store?

What actions US Senate has taken for the 2020 market crash, are they working and how do you comment on those actions?

Apply Michael Porter's Five Forces framework to Netflix for the years 1999 (when they started out) and 2020. What are the main differences?

Summarize the monetary policy measures taken by central banks to address the worldwide financial crisis (2008) and the COVID crisis (2019/2020).

. Discuss the salient features of budget 2020-2021 of Pakistan. Also highlight the fiscal measures to improve the social sector development of the country.

Consumers and businesses in Darwin are assuming that the economy may face an inflation by the end of 2020. What could be the consequence of such an assumption?

300 words