Reference no: EM13926307

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.