Johnson Merger Forecasting Case Study Solution

Johnson Merger Forecasting Case Study

Forecasting Case Study

Files Needed:

1. Johnson Merger Forecasting Case Study 2015 (a Word file)

2. Johnson Forecasting Case Study 2015 Raw Data (an Excel file)

Introduction

This individual case study is a forecasting study, you are to analyze the data about the company using various statistical tests you have learned in this course and then forecast the next 8 months of revenue for Johnson multinational corporation. Then you will advise Mr. Watson if he should proceed with the possible purchase of 4 percent of the company for $250 million (US).

NOTE: All monetary values, with the exception of the $250 million just mentioned, are in New Taiwan Dollars or NTDs. The current conversion rate from NTDs to USD are 100,000 NTD = $3,278.37 and you may use this conversion rate in making your recommendation to Mr. Watson about this stock purchase.

The Back Story for the Case Study

Marcus Watson, President and CEO of Watson Investments has been seeking to expand his investments from a Florida-based company to the Far East. He has been interested in Johnson, a Taiwan multi-billion dollar international company that produces consumer electronics, and believes that now is the time to reach out to this company.

Recently, he and his financial vice president have considered making an offer to Johnson to purchase 4% of the company for $250 million (US), but before they decide if this is a good investment or not they needed to understand the revenue of the company over the past several years, and then forecast the possible earning for the remainder of 2015.

Your company has been tasked to help them with this forecast and to determine if this is a good or poor investment. You will be using StatTools and the statistical tests you learned in this class to advise them about this potential investment.

You are supplied with the prior 5 years and 4 months of monthly sales of the company. You are to use this information (NOTE: There are two spreadsheets in the Excel file, one in a single column and one with the same data in rows) to complete the following tasks. Be sure to use the proper spreadsheet for the correct statistical tests.

1. Analyze the historical data using the Column Data and StatTools’ one variable summary and describe the important information that is contained in this data including the mean, median (comparing both), the skewness and Kurtosis and the quartiles and interquartile range. What does this data tell you about the revenue of the company? Are the sales stable, declining or increasing? Does it appear that the revenues are seasonal or not? Why do you believe this?

2. Using the Row Data, create a histograms of the historical data by year and analyze the results. What different picture do these histograms show you? When do the majority of the sales occur?

3. Using the row data create box and whisker plots for all 5.4 years. What do these box and whisker plots (there will be 6 of them) show you about the revenue over the years? Has the revenue remained the same from year to year, or has it changed? Has the revenue mix from quartile to quartile and year to year changed? If so how has it changed?

4. Using the sales in single column and StatTools forecasting functions create the following forecasts for the next 12 months:

a. A moving average forecast with a span of 3 months;

b. A simple exponential smoothing forecast (optimized);

c. A Holt’s double exponential smoothing forecast (optimized); and,

d. Winter’s exponential smoothing forecast (optimized).

1. Compare the mean absolute error, root mean square error, and the mean absolute percent of error for all four of these forecasting techniques – what do these statistics tell you about the forecasts? Which one is the best forecast and why? Use Table 1 to do this comparison and include it in your individual case study report.

2. Compare the forecast lines of the four techniques, what do they tell you about the possible 8 month forecast? Which one appears to be the best forecast and why?

3. Compare the 8 month forecast for the forecast technique you have selected (as the best forecasting technique) to the historical data for the same 8 months during 2014. What does the forecast versus the historical data show you? Is the forecast the same or different from the actual 2014 data? Be specific.

5. Complete the following table (yellow cells) and include it in your report using the forecasting technique that you have selected as the best for the college.

Table 2. Actual and Forecast Revenue for the Johnson Corporation

6. Based on the information and forecast that you have calculated, and any other research that you have done on Johnson, determine if the Florida based company should invest the $250 million (US) in this company. Be specific on your answer, this is not a yes or no answer.

7. Complete the case study and attach your Excel spreadsheet in the assignment drop box by the deadline for your section.