# Johnson Merger Forecasting Case Study

September 28, 2018

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

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.

help them with this forecast and to determine if this is a good or poor
investment. You will be using StatTools
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.

Table 1. 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 2. Actual and Forecast Revenue for the Johnson
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 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