Statistics Forecasting Case Study

attached is useful data set on Lenovo investor relations website. It includes sales for each quarter for the past 6 years. I did some analysis in Excel and the data set has trend and seasonal pattern. I calculated seasonal indexes,i need help to deseasonalize the data, compute trend and write estimated regression equation. Also please do forecast for the next time periods. Additionally, please forecast the past sales using the regression and compute forecast accuracy. Next, please use alternative approach and forecast sales using regression with dummy variables.

 

Consolidated Income Statement – Quarterly (unaudited)

Q1 2013-14 Q4 2012-13 Q3 2012-13 Q2 2012-13 Q1 2012-13 Q4 2011-12 Q3 2011-12 Q2 2011-12 Q1 2011-12 Q4 2010-11 Q3 2010-11 Q2 2010-11 Q1 2010-11 Q4 2009-10 Q3 2009-10* Q2 2009-10* Q1 2009-10* Q4 2008-09* Q3 2008-09* Q2 2008-09* Q1 2008-09* Q4 2007-08 Q3 2007-08 Q2 2007-08 Q1 2007-08

For the quarter

ended Jun 30, 2013

For the quarter

ended Mar31, 2013

For the quarter

ended Dec 31, 2012

For the quarter

ended Sep 30, 2012

For the quarter

ended Jun 30, 2012

For the quarter

ended Mar 31, 2012

For the quarter

ended Dec 31, 2011

For the quarter

ended Sep 30, 2011

For the quarter

ended Jun 30, 2011

For the quarter

ended Mar 31, 2011

For the quarter

ended Dec 31, 2010

For the quarter

ended Sep 30, 2010

For the quarter

ended Jun 30, 2010

For the quarter

ended Mar 31, 2010

For the quarter ended

Dec 31, 2009

For the quarter ended

Sept 30, 2009

For the quarter ended

Jun 30, 2009

For the quarter ended

Mar 31, 2009

For the quarter ended

Dec 31, 2008

For the quarter ended

Sept 30, 2008

For the quarter ended

Jun 30, 2008

For the quarter ended

Mar 31, 2008

For the quarter ended

Dec 31, 2007

For the quarter ended

Sept 30, 2007

For the quarter ended

Jun 30, 2007

US$’000 US$’000 US$’000 US$’000 US$’000 US$’000 US$’000 US$’000 US$’000 US$’000 US$’000 US$’000 US$’000 US$’000 US$’000 US$’000 US$’000 US$’000 US$’000 US$’000 US$’000 US$’000 US$’000 US$’000 US$’000

Continuing operations

Revenue 8,787,098 7,832,358 9,358,746 8,672,601 8,009,696 7,496,278 8,371,858 7,786,386 5,919,916 4,879,385 5,808,331 5,759,983 5,146,672 4,317,392 4,759,414 4,087,231 3,440,778 2,770,785 3,591,287 4,326,225 4,212,634 3,734,203 4,493,881 4,310,323 3,813,096

Cost of sales (7,595,707) (6,780,400) (8,169,408) (7,533,154) (6,963,396) (6,690,546) (7,417,698) (6,838,554) (5,181,419) (4,279,800) (5,159,944) (5,166,997) (4,623,676) (3,868,129) (4,228,963) (3,654,563) (3,063,566) (2,508,440) (3,226,265) (3,757,443) (3,611,587) (3,174,966) (3,812,943) (3,665,683) (3,247,931)
────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ──────────

Gross profit 1,191,391 1,051,958 1,189,338 1,139,447 1,046,300 805,732 954,160 947,832 738,497 599,585 648,387 592,986 522,996 449,263 530,451 432,668 377,212 262,345 365,022 568,782 601,047 559,237 680,938 644,640 565,165

Other (loss) / income – net 106 – 19,954 269 (316) – 1,199 – – 130 116 84 89 0 43,433 38,166 1,527 – 41 (138) 1,026 6,225 8,894 28 2,114

Selling and distribution expenses (438,303) (417,331) (535,584) (489,285) (445,901) (424,332) (458,441) (458,489) (349,546) (281,314) (291,271) (243,854) (222,016) (212,915) (225,037) (212,014) (189,422) (184,661) (232,837) (270,702) (250,251) (262,587) (282,808) (303,892) (254,426)

Administrative expenses (346,475) (315,895) (315,201) (287,644) (281,101) (189,319) (169,908) (197,351) (173,716) (194,167) (173,986) (191,083) (160,472) (155,269) (134,845) (136,940) (139,191) (143,494) (152,309) (157,764) (174,336) (152,818) (171,422) (142,923) (128,739)

Research and development expenses (170,319) (173,290) (162,432) (152,824) (135,441) (139,296) (117,722) (118,795) (77,521) (91,613) (75,784) (66,458) (69,558) (56,521) (54,418) (54,184) (49,220) (73,651) (45,160) (47,086) (54,113) (65,256) (58,096) (57,589) (48,818)

Other operating (expenses) / income- net (34,527) 23,585 47,448 (4,332) (1,717) 48,681 (15,853) (7,108) (14,650) 36,810 17,128 15,221 10,268 2,997 (59,751) 10,712 11,984 (123,516) (31,849) (57,757) 9,561 9,959 17,576 (20,341) (46,017)
────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ──────────

Operating profit 201,873 169,027 243,523 205,631 181,824 101,466 193,465 166,089 123,064 69,431 124,590 106,896 81,307 27,555 99,833 78,408 12,890 (262,977) (97,092) 35,335 132,934 94,760 195,082 119,923 89,279

Finance income 12,495 11,758 11,128 10,365 11,630 10,987 12,568 10,206 8,932 6,842 6,797 6,406 4,882 5,372 7,459 4,126 3,420 6,689 14,854 18,624 19,810 16,448 15,191 14,484 5,925

Finance costs (14,747) (15,177) (8,051) (11,517) (8,124) (10,365) (13,580) (11,343) (8,196) (15,899) (10,817) (11,622) (10,837) (12,841) (13,698) (17,666) (18,676) (11,757) (14,349) (15,099) (15,268) (7,763) (12,997) (9,572) (8,034)

Share of losses of associates and jointly

controlled entities 15,808 162 (144) (654) (82) (351) (103) (90) (307) (329) 177 (120) 47 (105) 156 121 (51) (20) 690 255 (574) (136) 201 65 (6)
────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ──────────

Profit before taxation 215,429 165,770 246,456 203,825 185,248 101,737 192,350 164,862 123,493 60,045 120,747 101,560 75,399 19,981 93,750 64,989 (2,417) (268,065) (95,897) 39,115 136,902 103,309 197,477 124,900 87,164

Taxation (45,536) (41,571) (46,386) (40,938) (40,812) (34,163) (37,975) (20,191) (14,698) (17,906) (21,097) (24,973) (20,539) (7,207) (14,229) (11,907) (13,592) 4,460 (822) (15,673) (26,409) 1,666 (16,155) (17,533) (15,591)
────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ──────────

Profit from continuing operations 169,893 124,199 200,070 162,887 144,436 67,574 154,375 144,671 108,795 42,139 99,650 76,587 54,860 12,774 79,521 53,082 (16,009) (263,605) (96,719) 23,442 110,493 104,975 181,322 107,367 71,573

Discontinued operations

Profit/(loss) from discontinued operations – – – – – – – – – – – – – – – – – – – – – 35,524 (8,758) (2,112) (4,734)
────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ──────────

Profit for the period 169,893 124,199 200,070 162,887 144,436 67,574 154,375 144,671 108,795 42,139 99,650 76,587 54,860 12,774 79,521 53,082 (16,009) (263,605) (96,719) 23,442 110,493 140,499 172,564 105,255 66,839
══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════

Profit attributable to:

Equity holders of the Company 173,932 126,864 204,870 162,047 141,367 66,817 153,459 143,919 108,796 42,138 99,650 76,586 54,860 12,774 79,521 53,082 (16,009) (263,608) (96,719) 23,442 110,493 140,421 171,748 105,255 66,839

Non-controlling interests -4,039 -2,665 -4,800 840 3,069 757 916 752 (1) 1 1 – – – – – 3 – – – 78 816 – –
────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ──────────

169,893 124,199 200,070 162,887 144,436 67,574 154,375 144,671 108,795 42,139 99,650 76,587 54,860 12,774 79,521 53,082 (16,009) (263,605) (96,719) 23,442 110,493 140,499 172,564 105,255 66,839
══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════

Earnings per share attributed to equity holders of

the company (US cents)

Basic

– Continuing operations 1.67 1.22 1.99 1.58 1.37 0.65 1.50 1.41 1.11 0.43 1.03 0.81 0.57 0.14 0.86 0.59 (0.18) (2.98) (1.09) 0.27 1.25 1.16 2.03 1.24 0.84

– Discontinued operations – – – – – – – – – – – – – – – – – – – – – 0.40 (0.10) (0.02) (0.06)
────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ──────────

1.67 1.22 1.99 1.58 1.35 0.65 1.50 1.41 1.11 0.43 1.03 0.81 0.57 0.14 0.86 0.59 (0.18) (2.98) (1.09) 0.27 1.25 1.56 1.93 1.22 0.78
══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════

Diluted

– Continuing operations 1.65 1.20 1.96 1.55 1.33 0.63 1.46 1.38 1.08 0.42 0.98 0.76 0.54 0.13 0.79 0.55 (0.18) (2.98) (1.09) 0.25 1.15 1.09 1.85 1.14 0.79

– Discontinued operations – – – – – – – – – – – – – – – – – – – – – 0.35 (0.09) (0.02) (0.05)
────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ────────── ──────────

1.65 1.20 1.96 1.55 1.33 0.63 1.46 1.38 1.08 0.42 0.98 0.76 0.54 0.13 0.79 0.55 (0.18) (2.98) (1.09) 0.25 1.15 1.44 1.76 1.12 0.74
══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════ ══════════

*Management reviewed the overhead expenses included as part of the COGS and noted it is more appropriate to include these expenses as part of the administrative expenses. FY1213 result has been restated accordingly