Files Needed:
1. HCT Merger Forecasting Case Study 2015 (a Word file)
2. HCT Forecasting Case Study Spring 2015 Raw Data (an Excel file)
Introduction:
This is the second of the major reports that you will completing this term. This individual case study is a forecasting study, you are to analyze the data about the company using the various statistical tests you have previously learned in this course and then forecast the next 8 months of revenue for the HCT multinational corporation (https://www.htc.com/us/). Then you will advise Mr. Wallbanger 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. Wallbanger about this stock purchase.
The Back Story for the Case Study:
Marcus Wallbanger, President and CEO of Wallbanger Investments has been seeking to expand his investments from a Prescott Florida-based company to the Far East. He has been interested in HCT, 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 HCT 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 to do this comparison and include it in your individual case study report.
Table: Comparison of the Forecasting Techniques
Moving Exponential Holts Winters
MAE
RMSE
MAPE
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: Actual and Forecast Revenue for the HCT Corporation
Monthly Consolidated Revenues (In NT$ million)
2015 2014 2013 2012 2011 2010
January 12,275 9,671 15,536 16,615 35,014 11,171
February 9,226 7,225 11,370 20,294 32,106 10,280
March 20,023 16,225 15,882 30,880 37,036 16,496
April 13,542 22,079 19,591 31,032 38,729 18,147
May 21,065 29,001 30,004 40,621 18,822
June 21,917 22,075 30,004 45,049 23,991
July 10,605 15,728 25,025 45,112 24,611
August 14,541 13,168 24,019 45,322 24,179
September 16,718 18,151 21,133 45,388 27,058
October 15,751 14,995 17,214 44,114 32,434
November 16,930 15,472 21,230 30,942 38,484
December 15,185 12,433 21,569 26,363 33,087
Total 55,067 187,911 203,403 289,020 465,795 278,760
NOTE the monetary values in this case study are in New Taiwan Dollars: 100,000 NTD = $3,278.37 (USD) as of 5-15-15.
6. Based on the information and forecast that you have calculated, and any other research that you have done on HCT, determine if the Prescott company should invest the $250 million (US) in this company. Be specific on your answer, this is not a yes or no answer.
7. Write up your case study results using the examples of case studies provided, and include the individual case study grading rubric and the required first page for your report. Consult the various chats concerning the individual case study for what the individual case study report is to include and how it is to be formatted.
8. Complete the case study and attach your Excel spreadsheet in the assignment drop box by the deadline for your section.