Forecasting

profileXoxoxoo
2022F_MFIN1756_FPA_ClassExercise_Session3.ForecastingFS_withAppleExampleforFinalProject_Q11.xlsx
Home>Business & Finance homework help>Accounting homework help>Forecasting

APPL Forecast. FP&A

Final Project. Forecasted Financial Statements and Comparative Analysis Instruction. (Remove this part once your work is done.)
Proejct Company: Apple Inc. (1) Describe all the justifications and explanations of the requirements on the Word.
Financial Analyst: Paul Ahn, CPA (2) Research to justify all the hard inputs. Explain with WHY it should be and WHERE you get the info by pasting the URL link as an evidence on Word.
(3) If calculated, then explan with HOW you compute the outcomes on Word.
Part I. Assumption Table ($ million, %) * Tips! To get a higher grade, use as many independent assumptions on accounts in each quarter as possible with research support,
rather than just using the same % of sales throughout the forecasted quarters.
1. Forecasting Assumptions. 2022 Q3 Actual 2022 FQ4 2023 FQ1 2023 FQ2 2023 FQ3 Forecasted financial statements must be reasonable and realistic along with contemporary events and issues of the firm.
Comprehensive Income Statement items: All the I/S items must be dealt in the Assumption part. All the I/S items must be distinguished as % of Sales or trend methods and be justified.
Sales forecasted ($ million) 82,959 88,576 129,803 96,409 86,473 Sales forecast must be linked to a sales forecasting model and be justified.
Cost of Sales (% of sales) -56.7% 57.0% 58.0% 59.0%
R&D Exp (% of sales) -8.2% -8.5% -8.5% -9.0% -9.0%
SG&A Exp (% of sales) -3.9% -4.0% -4.5% -4.5% -4.0%
Depr. & Amort. (% of fixed assets) 6.4% 6.4% 6.4% 6.4% 6.4%
Other Income (% of sales) 0.9% 1.0% 1.0% 1.1% 1.1%
Tax rate (% of income before tax) -15.7% -15.7% -15.7% -15.7% -15.7%
Dividend (% of net Income, payout ratio) -19.3% -20.0% -15.0% -25.0% -27.0%
Stock Repurchase Program (% of net income) -113.1% 90.0% -80.0% 70.0% 60.0%
* Include or delete the I/S items according to the chosen company's I/S
Balance Sheet accounts: All the B/S accounts with hard inputs or trend methods must be dealt in the Assumption part. All the B/S accounts must be distinguished as % of Sales or trend methods and be justified.
Current Marketable Securities ($ million) 25% 26% 27% 28% 29%
Non-current Marketable Securities ($ million) 158% 155% 150% 145% 140%
Property, plant and equipment, net 40,335 43,968 44,621 275 45,929 At least one B/S account must be forecasted by Trend Method and linked to the Assumption part.
Commercial paper (% annual interest rate) 2.0% 2.5% 2.5% 3.0% 3.0%
Short-term debt (% annual interest rate) 2.0% 2.5% 2.5% 3.0% 3.0%
Long-term debt (% annual interest rate) 3.0% 3.5% 3.5% 4.0% 4.0%
* Include or delete the B/S accounts according to the chosen company's B/S
Part II. Financial Planning with Forecasted Financial Statements ($ million)
1. Income Statement for the quarter ended % of sales 2022 Q3 Actual 2022 FQ4 2023 FQ1 2023 FQ2 2023 FQ3
Total net sales 100% 82,959 How gets this forecasted sales, which forecasting model, why this model, is this forecasted sales justifiable in 4 quarters
Less Cost of sales -57% (47,074) still the same % of sales? Then why? could be changed? Then why?
Gross margin 43% 35,885
Less Operating expenses:
Research and development -8.2% (6,797) still the same % of sales? Then why? could be changed? Then why?
Selling, general, and administrative -3.9% (3,266) still the same % of sales? Then why? could be changed? Then why?
Depreciation & Amortization -3.3% (2,746) must be extracted if I/S doesn't present
Total operating expenses -15.4% (12,809) still the same % of sales? Then why? could be changed? Then why?
Operating Income 28% 23,076
Other Income (Expense, excluding interest) 0.9% 709 still the same % of sales? Then why? could be changed? Then why?
Interest expense -0.9% (719) must extract if I/S doesn't present, must link to all the interest bearing debts
Income before tax 28% 23,066
Less Provision for Income tax -4% (3,624) still the same % of sales? Then why? could be changed? Then why?
Net Income 23% 19,442
(Dividend) -5% (3,760) still the same % of sales? Then why? could be changed? Then why?
(Stock Repurchased) -27% (21,991) still the same % of sales? Then why? could be changed? Then why?
2. Balance sheet (B/S), as of quarter end % of sales 2022 Q3 Actual 2022 FQ4 2023 FQ1 2023 FQ2 2023 FQ3
Current assets: For the simplicity, plesase assume that the company does not change % of sales on each assets other than Cash.
Cash and cash equivalents 33% $ 27,502 must indentify if there is excess cash.
Marketable securities 25% 20,729 - 0 - 0 - 0 - 0
Accounts receivable, net 26% 21,803 - 0 - 0 - 0 - 0
Inventories 7% 5,433 - 0 - 0 - 0 - 0
Vendor non-trade receivables 25% 20,439 - 0 - 0 - 0 - 0
Other current assets 20% 16,386 - 0 - 0 - 0 - 0
Total current assets 135% 112,292 - 0 - 0 - 0 - 0
Non-current assets:
Marketable securities 158% 131,077 - 0 - 0 - 0 - 0
Property, plant and equipment, net 49% 40,335 43,968 44,621 275 45,929
Other non-current assets 63% 52,605 - 0 - 0 - 0 - 0
Total non-current assets 270% 224,017 43,968 44,621 275 45,929
Total assets 405% $ 336,309
Current liabilities: For the simplicity, plesase assume that the company does not change % of sales on each liability other than AFN debt
Accounts payable 58% $ 48,343 - 0 - 0 - 0 - 0
Other current liabilities 59% 48,811 - 0 - 0 - 0 - 0
Deferred revenue 9% 7,728 - 0 - 0 - 0 - 0
Commercial paper (interest bearing, 2%) 13.24% 10,982 Use any kind of short term debt as additional financing needed (AFN), Research all the borrowing cost of debt and determine what % can be applicable
Term debt (interest bearing, 3%) 17% 14,009 - 0 - 0 - 0 - 0 Research all the borrowing cost of debt and determine what % can be applicable
Total current liabilities 157% 129,873 - 0 - 0 - 0 - 0
Non-current liabilities:
Term debt (interest bearing, 3%) 114% 94,700 94,700 94,700 94,700 94,700 Research all the borrowing cost of debt and determine what % can be applicable
Other non-current liabilities 65% 53,629 53,629 53,629 53,629 53,629
Total non-current liabilities 179% 148,329 148,329 148,329 148,329 148,329
Total liabilities 335% 278,202 148,329 148,329 148,329 148,329
Shareholder's Equity: For the simplicity, plesase assume that the company does not issue equity during the forcased period
Common stock and additional paid-in capital 75% 62,115 62,115 62,115 62,115 62,115
Retained earnings 6% 5,289 5,289 5,289 5,289 5,289
Accumulated other comprehensive income/(loss) -11.21% (9,297) (9,297) (9,297) (9,297) (9,297)
Total shareholders' equity 70% 58,107 58,107 58,107 58,107 58,107
Total liabilities and shareholders' equity 405% $ 336,309
3. Statement of Cash Flows, for the quarter ended 2022 FQ4 2023 FQ1 2023 FQ2 2023 FQ3 Analyze Operating, Investing, and Financing activities in terms of cash.
Operating:
Net Income - 0 - 0 - 0 - 0
Depreciation & Amortization - 0 - 0 - 0 - 0
Change in Marketable securities 20,729 - 0 - 0 - 0
Change in Accts. Receivable 21,803 - 0 - 0 - 0
Change in Inventories 5,433 - 0 - 0 - 0
Change in Vendor non-trade receivables 20,439 - 0 - 0 - 0
Change in other current assets 16,386 - 0 - 0 - 0
Change in Accts. Payable (48,343) - 0 - 0 - 0
Change in other current liabilities (48,811) - 0 - 0 - 0
Change in Deferred revenue (7,728) - 0 - 0 - 0
Cash Flow from Operations (20,092) - 0 - 0 - 0
Investing:
Change in Marketable securities 131,077 - 0 - 0 - 0
Change in Gross Fixed Assets (3,633) (653) 44,346 (45,654)
Change in Other non curent assets 52,605 - 0 - 0 - 0
Cash Flow from Investing 180,049 (653) 44,346 (45,654)
Financing:
Commercial paper (10,982) - 0 - 0 - 0
Short Term debt (14,009) - 0 - 0 - 0
Long Term debt - 0 - 0 - 0 - 0
Other non-current liabilities - 0 - 0 - 0 - 0
Dividend paid - 0 - 0 - 0 - 0
Stock repurchased - 0 - 0 - 0 - 0
Cash Flow from Financing (24,991) - 0 - 0 - 0
Net Quarterly Cash Flow 134,966 (653) 44,346 (45,654)
Beginning Cash 27,502 - 0 - 0 - 0
Ending Cash 162,468 (653) 44,346 (45,654)
Part III. Compariative Analysis with Ratios.
1. Liquidity (Safety) 2022 Q3 Actual 2022 FQ4 2023 FQ1 2023 FQ2 2023 FQ3 Analyze financial statements with ratios in terms of Liquidity. How the ratios wiil be changed? And Why?
a. Current ratio 0.86 ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
b. Quick ratio 0.82 ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
c. Accounts Receivable Turnover 3.80 ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
d. Days in Receivable 23.92 ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
2. Productivity (Efficiency) Analyze financial statements with ratios in terms of Efficiency. How the ratios wiil be changed? And Why?
a. Inventory Turnover 8.66 ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
b. Days in Inventory 10.53 ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
c. Total Asset Turnover 0.25 ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
d. Total Fixed Asset Turnover 2.06 - 0 - 0 - 0 - 0
3. Profitability (Performance) Analyze financial statements with ratios in terms of Performance. How the ratios wiil be changed? And Why?
a. Operating profit margin (%) 27.82% ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
b. Return on Asset (%) 5.78% ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
c. Return on Sales, net profit margin (%) 23.44% ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
d. Return on Equity (%) 33.46% 0.00% 0.00% 0.00% 0.00%
4. Insolvency (Leverage) Analyze financial statements with ratios in terms of Leverage How the ratios wiil be changed? And Why?
a. Debt Ratio 0.83 ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
b. Asset to Equity (Equity Multiplier) 5.79 - 0 - 0 - 0 - 0
c. Current Liabilities to Total Debt Ratio 0.47 - 0 - 0 - 0 - 0
d. Interest Coverage 32.09 ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
5. DuPont Analysis. (ROE break-down) Analyze financial statements with ratios in terms of Profitability, Productivity, Leveage. How the ratios wiil be changed? And Why?
a. Return on Sales, net profit margin (%) 23.44% ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
b. Asset Turnover 24.67% ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
c. Asset to Equity 5.79 - 0 - 0 - 0 - 0
d. Return on Equity (%) 33.46% ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0! ERROR:#DIV/0!
6. Additional Ratios (At least 4 more)
a.
b.
c.
d.

APPL Q Rev.

Apple Quarterly Sales (Actual)
Date Q Sales Y Sales Apple Quarterly Sales Dec 2014 to Jun 2022
12/27/14 74,599
3/28/15 58,010
6/27/15 49,605
9/26/15 51,501 233,715
12/26/15 75,872
3/26/16 50,557
6/25/16 42,358
9/24/16 46,852 215,639
12/31/16 78,351
4/1/17 52,896
7/1/17 45,408
9/30/17 52,579 229,234
12/30/17 88,293
3/31/18 61,137
6/30/18 53,265
9/29/18 62,900 265,595
12/29/18 84,310
3/30/19 58,015
6/29/19 53,809
9/28/19 64,040 260,174
12/28/19 91,819
3/28/20 58,313
6/27/20 59,685
9/26/20 64,698 274,515
12/26/20 111,439
3/27/21 89,584
6/26/21 81,434
9/25/21 83,360 365,817
12/25/21 123,945
3/26/22 97,278
6/25/22 82,959
*All figures in millions of dollars
Apple Quarterly Sales Dec 2014 to Jun 2022

Q Sales 42000 42091 42182 42273 42364 42455 42546 42637 42735 42826 42917 43008 43099 43190 43281 43372 43463 43554 43645 43736 43827 43918 44009 44100 44191 44282 44373 44464 44555 44646 44737 74599 58010 49605 51501 75872 50557 42358 46852 78351 52896 45408 52579 88293 61137 53265 62900 84310 58015 53809 64040 91819 58313 59685 64698 111439 89584 81434 83360 123945 97278 82959

Millions of Dollars

Apple Yearly Sales

Y Sales 42273 42637 43008 43372 43736 44100 44464 233715 215639 229234 265595 260174 274515 365817

% Sales vs. Trend

Forecasting Variable Costs (% of Sales Method) & Fixed Investment or Fixed Expense (Trend Method)
Date Y Sales Q Sales Cost of Sales net PPE
actual 12/27/14 74,599 44,858 20,392
3/28/15 58,010 34,354 20,151
6/27/15 49,605 29,924 21,149
9/26/15 233,715 51,501 30,953 27,010
12/26/15 75,872 45,449 22,300
3/26/16 50,557 30,636 23,203
6/25/16 42,358 26,252 25,448
9/24/16 215,639 46,852 29,030 33,783
12/31/16 78,351 48,175 26,510
4/1/17 52,896 32,305 27,163
7/1/17 45,408 27,920 29,286
9/30/17 229,234 52,579 32,648 41,304
12/30/17 88,293 54,381 33,679
3/31/18 61,137 37,715 35,077
6/30/18 53,265 32,844 38,117
9/29/18 265,595 62,900 38,816 37,378
12/29/18 84,310 52,279 39,597
3/30/19 58,015 36,194 38,746
6/29/19 53,809 33,582 37,636
9/28/19 260,174 64,040 39,726 36,766
12/28/19 91,819 56,602 37,031
3/28/20 58,313 35,943 35,889
6/27/20 59,685 37,005 35,687
9/26/20 274,515 64,698 40,009 39,440
12/26/20 111,439 67,111 37,933
3/27/21 89,584 51,505 37,815
6/26/21 81,434 46,179 38,615
9/25/21 365,817 83,360 48,186 42,117
12/25/21 123,945 69,702 39,245
3/26/22 97,278 54,719 39,304
6/25/22 82,959 47,074 40,335
Forcast 9/25/22 43,968 =TREND(F5:F35,B5:B35,B36:B39,TRUE)
12/25/22 44,621
3/26/23 45,275
6/25/23 45,929
*All figures in millions of dollars
\

Cost of Sales

74599 58010 49605 51501 75872 50557 42358 46852 78351 52896 45408 52579 88293 61137 53265 62900 84310 58015 53809 64040 91819 58313 59685 64698 111439 89584 81434 833 60 123945 97278 82959 44858 34354 29924 30953 45449 30636 26252 29030 48175 32305 27920 32648 54381 37715 32844 38816 52279 36194 33582 39726 56602 35943 37005 40009 67111 51505 46179 48186 69702 54719 47074

Q Sales 42000 42091 42182 42273 42364 42455 42546 42637 42735 42826 42917 43008 43099 43190 43281 43372 43463 43554 43645 43736 43827 43918 44009 44100 44191 44282 44373 44464 44555 44646 44737 74599 58010 49605 51501 75872 50557 42358 46852 78351 52896 45408 52579 88293 61137 53265 62900 84310 58015 53809 64040 91819 58313 5968 5 64698 111439 89584 81434 83360 123945 97278 82959 Cost of Sales 42000 42091 42182 42273 42364 42455 42546 42637 42735 42826 42917 43008 43099 43190 43281 43372 43463 43554 43645 43736 43827 43918 44009 44100 44191 44282 44373 44464 44555 44646 44737 44858 34354 29924 30953 45449 30636 26252 29030 48175 32305 27920 32648 54381 37715 32844 38816 52279 36194 33582 39726 56602 35943 37005 40009 67111 51505 46179 48186 69702 54719 47074

74599 58010 49605 51501 75872 50557 42358 46852 78351 52896 45408 52579 88293 61137 53265 62900 84310 58015 53809 64040 91819 58313 59685 64698 111439 89584 81434 83360 123945 97278 82959 0 20392 20151 21149 27010 22300 23203 25448 33783 26510 27163 29286 41304 33679 35077 38117 37378 39597 38746 37636 36766 37031 35889 35687 39440 37933 37815 38615 42117 39245 39304 40335

74599 58010 49605 51501 75872 50557 42358 46852 78351 52896 45408 52579 88293 61137 53265 62900 84310 58015 53809 64040 91819 58313 59685 64698 111439 89584 81434 83360 123945 97278 82959 0 20392 20151 21149 27010 22300 23203 25448 33783 26510 27163 29286 41304 33679 35077 38117 37378 39597 38746 37636 36766 37031 35889 35687 39440 37933 37815 38615 42117 39245 39304 40335 43967.669513600762 44621.43307855376 45275.196643506701 45928.960208459641

CMA

Apple Quarter Sales Forecasting: Trend Method (Central Moving Average)
: A wrong ST financial planning application for a firm with Seasonality
Date Q Sales CMA4
Actual 12/27/14 74,599 58,588
3/28/15 58,010 57,815
6/27/15 49,605 55,978 =AVERAGE(AVERAGE(C8:C11),AVERAGE(C9:C12))
9/26/15 51,501 54,491
12/26/15 75,872 54,220
3/26/16 50,557 54,822
6/25/16 42,358 55,496
9/24/16 46,852 56,593
12/31/16 78,351 58,551
4/1/17 52,896 60,824
7/1/17 45,408 62,836
9/30/17 52,579 65,109
12/30/17 88,293 65,901
3/31/18 61,137 65,013
6/30/18 53,265 64,691
9/29/18 62,900 64,901
12/29/18 84,310 65,982
3/30/19 58,015 66,958
6/29/19 53,809 67,730
9/28/19 64,040 68,547
12/28/19 91,819 71,081
3/28/20 58,313 77,443
6/27/20 59,685 84,070
9/26/20 64,698 89,122
12/26/20 111,439 93,018
3/27/21 89,584 95,543
6/26/21 81,434 96,695
9/25/21 83,360 97,821
12/25/21 123,945 94,786
3/26/22 97,278 90,348
6/25/22 82,959 91,368
Forecast 9/25/22 90,843 93,531
12/25/22 92,186 94,877
3/26/23 93,529 95,887
6/25/23 94,872 96,562
9/25/23 96,229
12/25/23 97,572
=TREND(C6:C36,B6:B36,B37:B42,TRUE)
*All figures in millions of dollars

42000 42091 42182 42273 42364 42455 42546 42637 42735 42826 42917 43008 43099 43190 43281 43372 43463 43554 43645 43736 43827 43918 44009 44100 44191 44282 44373 44464 44555 44646 44737 44829 44920 45011 45102 45194 45285 74599 58010 49605 51501 75872 50557 42358 46852 78351 52896 45408 52579 88293 61137 53265 62900 84310 58015 53809 64040 91819 58 313 59685 64698 111439 89584 81434 83360 123945 97278 82959 90843.4221936994 92186.146758819465 93528.871323939646 94871.59588905971 96229.075669181184 97571.800234301249 42000 42091 42182 42273 42364 42455 42546 42637 42735 42826 42917 43008 43099 43190 43281 43372 43463 43554 43645 43736 43827 43918 44009 44100 44191 44282 44373 44464 44555 44646 44737 44829 44920 45011 45102 45194 45285 58587.875 57815.375 55977.875 54490.875 54219.625 54821.875 55495.5 56592.625 58551.25 60824.125 62836.375 65108.625 65900.875 65012.75 64690.5 64901 65982.125 66958 67729.75 68546.5 71081.25 77442.625 84070.125 89121.5 93017.5 95542.5 96694.875 97820.927774212425 94786.498893277283 90348.001153622172 91368.434555247091 93530.715725814778 94877.129094685224 95887.246521650581 96562.297607960965

Forecast Factors

Apple Quarter Sales Forecasting: CMA4, Seasonal Index, & Irregularity
Data Seasonal Index
Date Fiscal QTR Sales CMA4 Raw Seasonals Seasonal Index Irregular Sales Reconstruction Quarter Raw Factors Normalized Seaonal Index
12/27/14 1 74,599 =AVERAGE(AVERAGE(D5:D8),AVERAGE(D6:D9)) 1 1.348 1.355
3/28/15 2 58,010 =D7/E7 =VLOOKUP(C7,$K$5:$M$8,3,FALSE) 2 0.925 1.054
6/27/15 3 49,605 58,588 0.847 0.956 3 0.816 0.956
9/26/15 4 51,501 57,815 0.891 1.000 4 0.892 1.000
12/26/15 1 75,872 55,978 1.355 1.355 =AVERAGEIF(C7:C33,K5,F7:F33) =L5/AVERAGE(L5:L8)
3/26/16 2 50,557 54,491 0.928 1.054 =AVERAGEIF(C8:C34,K6,F8:F34) =L6/AVERAGE(L6:L9)
6/25/16 3 42,358 54,220 0.781 0.956 =AVERAGEIF(C9:C35,K7,F9:F35) =L7/AVERAGE(L7:L10)
9/24/16 4 46,852 54,822 0.855 1.000 =AVERAGEIF(C10:C36,K8,F10:F36) =L8/AVERAGE(L8:L11)
12/31/16 1 78,351 55,496 1.412 1.355
4/1/17 2 52,896 56,593 0.935 1.054
7/1/17 3 45,408 58,551 0.776 0.956
9/30/17 4 52,579 60,824 0.864 1.000
12/30/17 1 88,293 62,836 1.405 1.355
3/31/18 2 61,137 65,109 0.939 1.054
6/30/18 3 53,265 65,901 0.808 0.956
9/29/18 4 62,900 65,013 0.968 1.000
12/29/18 1 84,310 64,691 1.303 1.355
3/30/19 2 58,015 64,901 0.894 1.054
6/29/19 3 53,809 65,982 0.816 0.956
9/28/19 4 64,040 66,958 0.956 1.000
12/28/19 1 91,819 67,730 1.356 1.355
3/28/20 2 58,313 68,547 0.851 1.054
6/27/20 3 59,685 71,081 0.840 0.956
9/26/20 4 64,698 77,443 0.835 1.000
12/26/20 1 111,439 84,070 1.326 1.355
3/27/21 2 89,584 89,122 1.005 1.054
6/26/21 3 81,434 93,018 0.875 0.956
9/25/21 4 83,360 95,543 0.872 1.000
12/25/21 1 123,945 96,695 1.282 1.355
3/26/22 2 97,278 ERROR:#N/A ERROR:#N/A
6/25/22 3 82,959
*All figures in millions of dollars

Trend

Trend 58587.875 57815.375 55977.875 54490.875 54219.625 54821.875 55495.5 56592.625 58551.25 60824.125 62836.375 65108.625 65900.875 65012.75 64690.5 64901 65982.125 66958 67729.75 68546.5 71081.25 77442.625 84070.125 89121.5 93017.5 95542.5 96694.875

Seasonality

0.95557104466632425 1 1.3546954543048817 1.0541838754858242 0.95557104466632425 1 1.3546954543048817 1.0541838754858242 0.95557104466632425 1 1.3546954543048817 1.0541838754858242 0.95557104466632425 1 1.3546954543048817 1.0541838754858242 0.95557104466632425 1 1.3546954543048817 1.0541838754858242 0.95557104466632425 1 1.3546954543048817 1.0541838754858242 0.95557104466632425 1 1.3546954543048817

Irregular

Apple Quarterly Sales

42182 42273 42364 42455 42546 42637 42735 42826 42917 43008 43099 43190 43281 43372 43463 43554 43645 43736 43827 43918 44009 44100 44191 44282 44373 44464 44555

Expo Smoothing

Apple Q Sales Forecasting: Exponential Smoothing Method
Date Sales Exp Smoothing Sensitivity Factors & Forecast Error
Actual 12/27/14 74,599 74,599 =C5 Alpha 65,535.0000
3/28/15 58,010 74,599 =D5+$G$5*(C5-D5) Mean Squared Error (MSE) 20,106,628,786,425,560,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000 =SUMXMY2(C5:C35,D5:D35)/COUNT(D5:D35)
6/27/15 49,605 (1,087,085,516) =D6+$G$5*(C6-D6) Mean Absolute % Error (MAPE) 30699473420530280000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000.00% =AVERAGE(ABS(C5:C35-D5:D35)/C5:C35)
9/26/15 51,501 71,244,313,069,219 =D7+$G$5*(C7-D7)
12/26/15 75,872 (4,668,924,809,303,079,900) =D8+$G$5*(C8-D8)
3/26/16 50,557 305,973,318,452,873,060,000,000 =D9+$G$5*(C9-D9)
6/25/16 42,358 (20,051,655,451,490,585,000,000,000,000) =D10+$G$5*(C10-D10)
9/24/16 46,852 1,314,065,188,357,984,000,000,000,000,000,000 =D11+$G$5*(C11-D11)
12/31/16 78,351 (86,115,948,053,852,120,000,000,000,000,000,000,000) =D12+$G$5*(C12-D12)
4/1/17 52,896 5,643,522,539,761,146,000,000,000,000,000,000,000,000,000 =D13+$G$5*(C13-D13)
7/1/17 45,408 (369,842,606,120,707,000,000,000,000,000,000,000,000,000,000,000) =D14+$G$5*(C14-D14)
9/30/17 52,579 24,237,265,349,514,413,000,000,000,000,000,000,000,000,000,000,000,000 =D15+$G$5*(C15-D15)
12/30/17 88,293 (1,588,364,947,415,077,300,000,000,000,000,000,000,000,000,000,000,000,000,000) =D16+$G$5*(C16-D16)
3/31/18 61,137 104,091,908,463,899,680,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000 =D17+$G$5*(C17-D17)
6/30/18 53,265 (6,821,559,129,273,202,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000) =D18+$G$5*(C18-D18)
9/29/18 62,900 447,044,055,977,790,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000 =D19+$G$5*(C19-D19)
12/29/18 84,310 (29,296,585,164,448,486,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000) =D20+$G$5*(C20-D20)
3/30/19 58,015 1,919,922,412,166,967,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000 =D21+$G$5*(C21-D21)
6/29/19 53,809 (125,820,195,358,950,050,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000) =D22+$G$5*(C22-D22)
9/28/19 64,040 8,245,500,682,653,433,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000 =D23+$G$5*(C23-D23)
12/28/19 91,819 (540,360,641,737,010,100,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000) =D24+$G$5*(C24-D24)
3/28/20 58,313 35,411,994,295,593,215,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000 =D25+$G$5*(C25-D25)
6/27/20 59,685 (2,320,689,634,167,405,600,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000) =D26+$G$5*(C26-D26)
9/26/20 64,698 152,084,074,485,526,750,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000 =D27+$G$5*(C27-D27)
12/26/20 111,439 (9,966,677,737,334,510,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000) =D28+$G$5*(C28-D28)
3/27/21 89,584 653,156,258,838,479,800,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000 =D29+$G$5*(C29-D29)
6/26/21 81,434 (42,803,942,266,720,930,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000) =D30+$G$5*(C30-D30)
9/25/21 83,360 2,805,113,552,507,289,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000 =D31+$G$5*(C31-D31)
12/25/21 123,945 (183,830,311,550,012,630,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000) =D32+$G$5*(C32-D32)
3/26/22 97,278 12,047,135,637,118,528,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000 =D33+$G$5*(C33-D33)
6/25/22 82,959 (789,496,986,842,925,800,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000) =D34+$G$5*(C34-D34)
Forecast 9/22/22 51,738,895,535,764,290,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000 =D35+$G$5*(C35-D35)
All figures in millions of dollars

Linear Trend Expo

Apple Q Sales Forecasting: Linear Trend Exponential Smoothing Method
Sensitivity Factors & Forecast Error
Alpha 0.1692
Beta 0.2878
Mean Squared Error (MSE) 322,786,038 =SUMXMY2(C13:C42,F13:F42)/COUNT(C13:C42)
Mean Absolute % Error (MAPE) 19.58% =AVERAGE(ABS(C13:C42-F13:F42)/C13:C42)
Sales Forecasting
Date Sales Level (simple) Trend (change) Forecast
Actual 12/27/14 74,599 74,599 =C12
3/28/15 58,010 71,792 (808) 74,599 =$D$5*C13+(1-$D$5)*(D12+E12) =$D$6*(D13-D12)+(1-$D$6)*E12 =SUM(D12:E12)
6/27/15 49,605 67,366 (1,849) 70,984 =$D$5*C14+(1-$D$5)*(D13+E13) =$D$6*(D14-D13)+(1-$D$6)*E13 =SUM(D13:E13)
9/26/15 51,501 63,145 (2,532) 65,517 =$D$5*C15+(1-$D$5)*(D14+E14) =$D$6*(D15-D14)+(1-$D$6)*E14 =SUM(D14:E14)
12/26/15 75,872 63,196 (1,789) 60,614 =$D$5*C16+(1-$D$5)*(D15+E15) =$D$6*(D16-D15)+(1-$D$6)*E15 =SUM(D15:E15)
3/26/16 50,557 59,571 (2,317) 61,407
6/25/16 42,358 54,733 (3,043) 57,254
9/24/16 46,852 50,872 (3,278) 51,691
12/31/16 78,351 52,798 (1,780) 47,594
4/1/17 52,896 51,336 (1,689) 51,018
7/1/17 45,408 48,930 (1,895) 49,647
9/30/17 52,579 47,973 (1,625) 47,034
12/30/17 88,293 53,445 418 46,347
3/31/18 61,137 55,094 772 53,863
6/30/18 53,265 55,426 645 55,866
9/29/18 62,900 57,227 978 56,071
12/29/18 84,310 62,622 2,249 58,205
3/30/19 58,015 63,711 1,915 64,871
6/29/19 53,809 63,627 1,340 65,627
9/28/19 64,040 64,810 1,295 64,967
12/28/19 91,819 70,456 2,547 66,105
3/28/20 58,313 70,517 1,832 73,003
6/27/20 59,685 70,206 1,215 72,349
9/26/20 64,698 70,283 887 71,421
12/26/20 111,439 77,985 2,849 71,171
3/27/21 89,584 82,314 3,275 80,833
6/26/21 81,434 84,886 3,073 85,589 =$D$5*C38+(1-$D$5)*(D37+E37) =$D$6*(D38-D37)+(1-$D$6)*E37 =SUM(D37:E37)
9/25/21 83,360 87,180 2,849 87,958 =$D$5*C39+(1-$D$5)*(D38+E38) =$D$6*(D39-D38)+(1-$D$6)*E38 =SUM(D38:E38)
12/25/21 123,945 95,768 4,500 90,029 =$D$5*C40+(1-$D$5)*(D39+E39) =$D$6*(D40-D39)+(1-$D$6)*E39 =SUM(D39:E39)
3/26/22 97,278 99,762 4,355 100,268 =$D$5*C41+(1-$D$5)*(D40+E40) =$D$6*(D41-D40)+(1-$D$6)*E40 =SUM(D40:E40)
6/25/22 82,959 100,537 3,324 104,117 =$D$5*C42+(1-$D$5)*(D41+E41) =$D$6*(D42-D41)+(1-$D$6)*E41 =SUM(D41:E41)
Forecast 9/22/22 103,861 =$D$42+$E$42*(ROW()-ROW($F$42))
12/22/22 107,185 =$D$42+$E$42*(ROW()-ROW($F$42))
3/23/23 110,510 =$D$42+$E$42*(ROW()-ROW($F$42))
6/23/23 113,834 =$D$42+$E$42*(ROW()-ROW($F$42))

Sales 42000 42091 42182 42273 423 64 42455 42546 42637 42735 42826 42917 43008 43099 43190 43281 43372 43463 43554 43645 43736 43827 43918 44009 44100 44191 44282 44373 44464 44555 44646 44737 44826 44917 45008 45100 74599 58010 49605 51501 75872 50557 42358 46852 78351 52896 45408 52579 88293 61137 53265 62900 84310 58015 53809 64040 91819 58313 59685 64698 111439 89584 81434 83360 123945 97278 82959 Forecast 42000 42091 42182 42273 42364 42455 42546 42637 42735 42826 42917 43008 43099 43190 43281 43372 43463 43554 43645 43736 43827 43918 44009 44100 44191 44282 44373 44464 44555 44646 44737 44826 44917 45008 45100 74599 70983.956483573915 65517.139839175426 60613.568154022476 61406.810150574034 57253.743950072298 51690.563597551765 47593.538878501335 51017.873937786986 49646.898095115917 47034.38464156929 46347.423574007138 53862.937475636136 55865.828620690867 56071.079944820689 58204.572854923026 64871.363201288768 65626.627864231763 64966.809057887229 66104.728147990609 73003.081678067989 72348.98930013481 71420.949675525946 71170.780024034902 80833.400972725372 85588.995159982369 87958.418057933319 90028.845137300654 100268.33261053542 104117.08912589778 103861.12057128936 107185.39385326623 110509.6671352431 113833.94041721997

HWAS Model

Apple Q Sales Forecasting: Holt-Winters Additive Seasonal (HWAS) Model
Sensitivity Factors & Forecast Error
Alpha 0.5995
Beta 0.0648
Gamma 1.0000
Mean Squared Error (MSE) 44,486,505 =SUMXMY2(C17:C43,G17:G43)/COUNT(C17:C43)
Mean Absolute % Error (MAPE) 6.64% =AVERAGE(ABS(C17:C43-G17:G43)/C17:C43)
Sales Forecasting
Date Sales Level (simple) Trend (change) Seasonal Forecast
Actual 12/27/14 74,599 16,170 =C13-$D$16
3/28/15 58,010 (419) =C14-$D$16
6/27/15 49,605 (8,824) =C15-$D$16
9/26/15 51,501 58,429 - 0 (6,928) =AVERAGE(C13:C16) =C16-$D$16
12/26/15 75,872 59,192 49 16,680 74,599 =$D$5*(C17-F13)+(1-$D$5)*(D16+E16) =$D$6*(D17-D16)+(1-$D$6)*E16 =$D$7*(C17-D17)+(1-$D$7)*F13 =SUM(D16:E16)+F13
3/26/16 50,557 54,286 (272) (3,729) 58,823 =$D$5*(C18-F14)+(1-$D$5)*(D17+E17) =$D$6*(D18-D17)+(1-$D$6)*E17 =$D$7*(C18-D18)+(1-$D$7)*F14 =SUM(D17:E17)+F14
6/25/16 42,358 52,316 (382) (9,958) 45,191 =$D$5*(C19-F15)+(1-$D$5)*(D18+E18) =$D$6*(D19-D18)+(1-$D$6)*E18 =$D$7*(C19-D19)+(1-$D$7)*F15 =SUM(D18:E18)+F15
9/24/16 46,852 53,041 (310) (6,189) 45,007
12/31/16 78,351 58,090 37 20,261 69,411
4/1/17 52,896 57,227 (21) (4,331) 54,398
7/1/17 45,408 56,103 (93) (10,695) 47,247
9/30/17 52,579 57,664 15 (5,085) 49,822
12/30/17 88,293 63,885 417 24,408 77,939
3/31/18 61,137 65,001 462 (3,864) 59,971
6/30/18 53,265 64,562 404 (11,297) 54,768
9/29/18 62,900 66,775 521 (3,875) 59,881
12/29/18 84,310 62,864 234 21,446 91,704
3/30/19 58,015 62,367 186 (4,352) 59,233
6/29/19 53,809 64,084 285 (10,275) 51,256
9/28/19 64,040 66,495 423 (2,455) 60,494
12/28/19 91,819 68,989 557 22,830 88,365
3/28/20 58,313 65,421 290 (7,108) 65,195
6/27/20 59,685 68,258 455 (8,573) 55,437
9/26/20 64,698 67,778 395 (3,080) 66,258
12/26/20 111,439 80,424 1,188 31,015 91,002
3/27/21 89,584 90,652 1,774 (1,068) 74,504
6/26/21 81,434 90,976 1,680 (9,542) 83,853
9/25/21 83,360 88,929 1,438 (5,569) 89,576 =$D$5*(C40-F36)+(1-$D$5)*(D39+E39) =$D$6*(D40-D39)+(1-$D$6)*E39 =$D$7*(C40-D40)+(1-$D$7)*F36 =SUM(D39:E39)+F36
12/25/21 123,945 91,904 1,538 32,041 121,383 =$D$5*(C41-F37)+(1-$D$5)*(D40+E40) =$D$6*(D41-D40)+(1-$D$6)*E40 =$D$7*(C41-D41)+(1-$D$7)*F37 =SUM(D40:E40)+F37
3/26/22 97,278 96,382 1,728 896 92,373 =$D$5*(C42-F38)+(1-$D$5)*(D41+E41) =$D$6*(D42-D41)+(1-$D$6)*E41 =$D$7*(C42-D42)+(1-$D$7)*F38 =SUM(D41:E41)+F38
6/25/22 82,959 94,748 1,511 (11,789) 88,568 =$D$5*(C43-F39)+(1-$D$5)*(D42+E42) =$D$6*(D43-D42)+(1-$D$6)*E42 =$D$7*(C43-D43)+(1-$D$7)*F39 =SUM(D42:E42)+F39
Forecast 9/22/22 90,689 =$D$43+$E$43*(ROW()-ROW($G$43))+F40
12/22/22 129,810 =$D$43+$E$43*(ROW()-ROW($G$43))+F41
3/23/23 100,175 =$D$43+$E$43*(ROW()-ROW($G$43))+F42
6/23/23 89,001 =$D$43+$E$43*(ROW()-ROW($G$43))+F43

42000 42091 42182 42273 42364 42455 42546 42637 42735 42826 42917 43008 43099 43190 43281 43372 43463 43554 43645 43736 43827 43918 44009 44100 44191 44282 44373 44464 44555 44646 44737 44826 44917 45008 45100 74599 58010 49605 51501 75872 50557 42358 46852 78351 52896 45408 52579 88293 61137 53265 62900 84310 58015 53809 64040 91819 58313 59685 64698 111439 89584 81434 83360 123945 97278 82959 0 74599 58822.578292398044 45190.949600872365 45007.080207518346 69411.020193717515 54398.295390970779 47247.465210228584 49821.780610317968 77938.78823666423 59970.965682977141 54767.72799555755 59881.048836202055 91703.933028899279 59233.398788120336 51256.281261963144 60493.702228277507 88364.592740847904 65194.544623065434 55436.61492269054 66258.11297809855 91002.296094007994 74503.938311746082 83852.997477223107 89575.834558080198 121382.99315373965 92373.40569936308 88568.414087296202 90688.690879234273 129809.93058376887 100175.33463812681 89001.253483702705

HWMS Model

Apple Q Sales Forecasting: Holt-Winters Multiplicative Seasonal (HWMS) Model
Sensitivity Factors & Forecast Error
Alpha 0.9364
Beta 0.0339
Gamma 0.9853
Mean Squared Error (MSE) 47,341,137 =SUMXMY2(C17:C43,G17:G43)/COUNT(C17:C43)
Mean Absolute % Error (MAPE) 7.78% =AVERAGE(ABS(C17:C43-G17:G43)/C17:C43)
Sales Forecasting
Date Sales Level (simple) Trend (change) Seasonal Forecast
Actual 12/27/14 74,599 1.28 =C13/$D$16
3/28/15 58,010 0.99 =C14/$D$16
6/27/15 49,605 0.85 =C15/$D$16
9/26/15 51,501 58,429 - 0 0.88 =AVERAGE(C13:C16) =C16/$D$16
12/26/15 75,872 59,362 32 1.28 74,599 =$E$5*(C17/F13)+(1-$E$5)*(D16+E16) =$E$6*(D17-D16)+(1-$E$6)*E16 =$E$7*(C17/D17)+(1-$E$7)*F13 =SUM(D16:E16)*F13
3/26/16 50,557 51,461 (238) 0.98 58,968 =$E$5*(C18/F14)+(1-$E$5)*(D17+E17) =$E$6*(D18-D17)+(1-$E$6)*E17 =$E$7*(C18/D18)+(1-$E$7)*F14 =SUM(D17:E17)*F14
6/25/16 42,358 49,977 (280) 0.85 43,488
9/24/16 46,852 52,935 (170) 0.89 43,805
12/31/16 78,351 60,760 101 1.29 67,438
4/1/17 52,896 54,280 (122) 0.97 59,802
7/1/17 45,408 53,612 (141) 0.85 45,903
9/30/17 52,579 59,031 48 0.89 47,324
12/30/17 88,293 67,881 347 1.30 76,174
3/31/18 61,137 63,079 172 0.97 66,496
6/30/18 53,265 62,911 161 0.85 53,573
9/29/18 62,900 70,145 401 0.90 56,172
12/29/18 84,310 65,191 219 1.29 91,747
3/30/19 58,015 60,206 42 0.96 63,401
6/29/19 53,809 63,343 147 0.85 51,011
9/28/19 64,040 70,919 400 0.90 56,927
12/28/19 91,819 71,012 389 1.29 92,242
3/28/20 58,313 61,203 43 0.95 68,808
6/27/20 59,685 69,690 330 0.86 52,025
9/26/20 64,698 71,551 382 0.90 63,221
12/26/20 111,439 85,279 835 1.31 93,010
3/27/21 89,584 93,506 1,086 0.96 82,061
6/26/21 81,434 95,064 1,102 0.86 81,002
9/25/21 83,360 92,444 975 0.90 86,953
12/25/21 123,945 94,772 1,021 1.31 122,058 =$E$5*(C41/F37)+(1-$E$5)*(D40+E40) =$E$6*(D41-D40)+(1-$E$6)*E40 =$E$7*(C41/D41)+(1-$E$7)*F37 =SUM(D40:E40)*F37
3/26/22 97,278 101,179 1,204 0.96 91,768 =$E$5*(C42/F38)+(1-$E$5)*(D41+E41) =$E$6*(D42-D41)+(1-$E$6)*E41 =$E$7*(C42/D42)+(1-$E$7)*F38 =SUM(D41:E41)*F38
6/25/22 82,959 97,197 1,028 0.85 87,703 =$E$5*(C43/F39)+(1-$E$5)*(D42+E42) =$E$6*(D43-D42)+(1-$E$6)*E42 =$E$7*(C43/D43)+(1-$E$7)*F39 =SUM(D42:E42)*F39
Forecast 9/22/22 88,576 =($D$43+$E$43*(ROW()-ROW($G$43)))*F40
12/22/22 129,803 =($D$43+$E$43*(ROW()-ROW($G$43)))*F41
3/23/23 96,409 =($D$43+$E$43*(ROW()-ROW($G$43)))*F42
6/23/23 86,473 =($D$43+$E$43*(ROW()-ROW($G$43)))*F43

Sales 42000 42091 42182 42273 42364 42455 42546 42637 42735 42826 42917 43008 43099 43190 43281 43372 43463 43554 43645 43736 43827 43918 44009 44100 44191 44282 44373 44464 44555 44646 44737 44826 44917 45008 45100 74599 58010 49605 51501 75872 50557 42358 46852 78351 52896 45408 52579 88293 61137 53265 62900 84310 58015 53809 64040 91819 58313 59685 64698 111439 89584 81434 83360 123945 97278 82959 Forecast 42000 42091 42182 42273 42364 42455 42546 42637 42735 42826 42917 43008 43099 43190 43281 43372 43463 43554 43645 43736 43827 43918 44009 44100 44191 44282 44373 44464 44555 44646 44737 44826 44917 45008 45100 74599 58968.419021043854 43487.616669651339 43804.897112982922 67438.068612107396 59801.676957074706 45902.687636832066 47324.086010583436 76173.75698000718 66495.987626776623 53572.922845306719 56172.4 27092952326 91746.97856944926 63401.156718602637 51011.366584151896 56926.96677434694 92242.047658378186 68808.152238792434 52025.263019116908 63221.323569402019 93009.538245881413 82060.991646480848 81001.960361608377 86953.267495525899 122057.78024872173 91768.067477119694 87703.219733479753 88576.09240498404 129803.09683370905 96409.19814057344 86473.03059496879

ETS

Error Trend Seasonal (ETS) for Scenario Analysis
Date Sales Forecast Lower Confidence Bound Upper Confidence Bound
12/27/14 74,599
3/28/15 58,010
6/27/15 49,605
9/26/15 51,501
12/26/15 75,872
3/26/16 50,557
6/25/16 42,358
9/24/16 46,852
12/31/16 78,351
4/1/17 52,896
7/1/17 45,408
9/30/17 52,579
12/30/17 88,293
3/31/18 61,137
6/30/18 53,265
9/29/18 62,900
12/29/18 84,310
3/30/19 58,015
6/29/19 53,809
9/28/19 64,040
12/28/19 91,819
3/28/20 58,313
6/27/20 59,685
9/26/20 64,698
12/26/20 111,439
3/27/21 89,584
6/26/21 81,434
9/25/21 83,360
12/25/21 123,945
3/26/22 97,278
6/25/22 82,959 82,959 82,959 82,959 =[@Sales] =[@Forecast] =[@[Lower Confidence Bound]]
9/22/22 88,321 75,305 101,338 =FORECAST.ETS(B36,$C$5:$C$35,$B$5:$B$35,1,1) =D36-FORECAST.ETS.CONFINT(B36,$C$5:$C$35,$B$5:$B$35,0.95,1,1) =D36+FORECAST.ETS.CONFINT(B36,$C$5:$C$35,$B$5:$B$35,0.95,1,1)
12/22/22 118,828 104,069 133,587 =FORECAST.ETS(B37,$C$5:$C$35,$B$5:$B$35,1,1) =D37-FORECAST.ETS.CONFINT(B37,$C$5:$C$35,$B$5:$B$35,0.95,1,1) =D37+FORECAST.ETS.CONFINT(B37,$C$5:$C$35,$B$5:$B$35,0.95,1,1)
3/23/23 96,794 80,588 112,999 =FORECAST.ETS(B38,$C$5:$C$35,$B$5:$B$35,1,1) =D38-FORECAST.ETS.CONFINT(B38,$C$5:$C$35,$B$5:$B$35,0.95,1,1) =D38+FORECAST.ETS.CONFINT(B38,$C$5:$C$35,$B$5:$B$35,0.95,1,1)
6/23/23 88,949 71,424 106,473 =FORECAST.ETS(B39,$C$5:$C$35,$B$5:$B$35,1,1) =D39-FORECAST.ETS.CONFINT(B39,$C$5:$C$35,$B$5:$B$35,0.95,1,1) =D39+FORECAST.ETS.CONFINT(B39,$C$5:$C$35,$B$5:$B$35,0.95,1,1)
Sales 74599 58010 49605 51501 75872 50557 42358 46852 78351 52896 45408 52579 88293 61137 53265 62900 84310 58015 53809 64040 9181 9 58313 59685 64698 111439 89584 81434 83360 123945 97278 82959 Forecast 42000 42091 42182 42273 42364 42455 42546 42637 42735 42826 42917 43008 43099 43190 43281 43372 43463 43554 43645 43736 43827 43918 44009 44100 44 191 44282 44373 44464 44555 44646 44737 44826 44917 45008 45100 82959 88321.122937770415 118828.28933290365 96793.64791414066 88948.679194156328 Lower Confidence Bound 42000 42091 42182 42273 42 364 42455 42546 42637 42735 42826 42917 43008 43099 43190 43281 43372 43463 43554 43645 43736 43827 43918 44009 44100 44191 44282 44373 44464 44555 44646 44737 44826 44917 45008 45100 82959 75304.535237100557 104069.41589158826 80588.483267744887 71424.011144039454 Upper Confidence Bound 42000 42091 42182 42273 42364 42455 42546 42637 42735 42826 42917 43008 43099 43190 43281 43372 43463 43554 43645 43736 43827 43918 44009 44100 44191 44282 44373 44464 44555 44646 44737 44826 44917 45008 45100 82959 101337.71063844027 133587.16277421903 112998.81256053643 106473.3472442732

Regression

Regression Forecasting Model
Date Period Q1 Q2 Q3 Sales Fit
Actual 12/27/14 1 0 0 0 74,599 ERROR:#N/A
3/28/15 2 1 0 0 58,010
6/27/15 3 0 1 0 49,605
9/26/15 4 0 0 1 51,501
12/26/15 5 0 0 0 75,872
3/26/16 6 1 0 0 50,557
6/25/16 7 0 1 0 42,358
9/24/16 8 0 0 1 46,852
12/31/16 9 0 0 0 78,351
4/1/17 10 1 0 0 52,896
7/1/17 11 0 1 0 45,408
9/30/17 12 0 0 1 52,579
12/30/17 13 0 0 0 88,293
3/31/18 14 1 0 0 61,137
6/30/18 15 0 1 0 53,265
9/29/18 16 0 0 1 62,900
12/29/18 17 0 0 0 84,310
3/30/19 18 1 0 0 58,015
6/29/19 19 0 1 0 53,809
9/28/19 20 0 0 1 64,040
12/28/19 21 0 0 0 91,819
3/28/20 22 1 0 0 58,313
6/27/20 23 0 1 0 59,685
9/26/20 24 0 0 1 64,698
12/26/20 25 0 0 0 111,439
3/27/21 26 1 0 0 89,584
6/26/21 27 0 1 0 81,434
9/25/21 28 0 0 1 83,360
12/25/21 29 0 0 0 123,945
3/26/22 30 1 0 0 97,278
6/25/22 31 0 1 0 82,959
Forecast 9/22/22 32 0 0 1
12/22/22 33 0 0 0
3/23/23 34 1 0 0
6/23/23 35 0 1 0
*Q1,2,3,4 is not fiscal quarter of Apple, but the general quarters in annual.
*All figures in millions of dollars

image1.wmf

image2.wmf

image3.wmf

image4.wmf

image5.wmf