Apple
Texas
ProjectionandValuationModel-apple.xlsxInstructions
| Model Name | APPLE INC. | |
| Comments: I will put some comments and suggestions on each tab so you can apply this highly structured model to your company. You may feel free to modify or not even use this model for you Team Project! | Objective: | |
| This model will develop long term projections to support the development of the strategic plans and the evaluation of alternative strategies. it also links to a Discounted Cash Flow valuation model based on free cash flows. | ||
| Presentation Summary | ||
| This model includes a presentation summary that will update with any changes posted to the other worksheets. | ||
| Input | ||
| Both history and future estimates can be input on shaded areas of the worksheets. All other cells represent formulas. | ||
| Code: | Enter Actual Information | |
| Your Assumptions about the future | ||
| Source | ||
| All tabs, other than "Valuation Model" are modifications of the model in | ||
| "Financial Planning and Analysis and Performance Management" by Jack Alexander. The changes are to have revenues based on lines of business rather than products (more of an outside perspective). The Valuation Model is from Prof. Larry. | ||
| Enter Current Year | ||
| Enter Co Name |
Revenue and Margins
| 7/9/21 17:20 | Revenue and Margins | |||||||||||
| History | CY | Projections | CAGR | Assumptions/Notes | ||||||||
| 2017 | 2018 | 2019 | 2020 | 2021 | 2022 | 2023 | 2024 | 2025 | History | Future | ||
| Iphone | 141319 | 164,888 | 142,381 | 137,781 | 140,536.62 | 143,347.35 | 146,214.30 | 149,138.59 | 152,121.36 | 0.4% | 2.0% | |
| cost of goods sold | 86,954 | 101,664 | 88,536 | 85,103 | ||||||||
| Gross Profit | 54,365 | 63,224 | 53,845 | 52,678 | 53403.9156 | 54471.993912 | 55561.43379024 | 56672.6624660448 | 57806.1157153657 | |||
| % | 38% | 38% | 38% | 38.2% | 38% | 38% | 38% | 38% | 38% | |||
| Mac | 25850 | 25,198 | 25,740 | 28,622 | 32,056.64 | 35,903.44 | 40,211.85 | 45,037.27 | 50,441.74 | -0.2% | 12.0% | |
| cost of goods sold | 15,906 | 15,536 | 16,006 | 17,679 | ||||||||
| Gross Profit | 9,944 | 9,662 | 9,734 | 10,943 | 12181.5232 | 13643.305984 | 15280.50270208 | 17114.1630263296 | 19167.8625894892 | |||
| % | 38% | 38% | 38% | 38.2% | 38% | 38% | 38% | 38% | 38% | |||
| Ipad | 19222 | 18,380 | 21,280 | 23,724 | 26570.88 | 29759.3856 | 33330.511872 | 37330.17329664 | 41809.7940922368 | 5.2% | 12.0% | |
| cost of goods sold | 11,827 | 11,332 | 13,232 | 14,654 | ||||||||
| Gross Profit | 7,395 | 7,048 | 8,048 | 9,070 | 10096.9344 | 11308.566528 | 12665.59451136 | 14185.4658527232 | 15887.72175505 | |||
| % | 38% | 38% | 38% | 38.2% | 38% | 38% | 38% | 38% | 38% | |||
| Wearables, Home and Accessories | 12863 | 17,381 | 24,482 | 30,620 | 34,906.80 | 39,793.75 | 45,364.88 | 51,715.96 | 58,956.19 | 38.0% | 14.0% | |
| cost of goods sold | 7,915 | 10,716 | 15,223 | 18,913 | ||||||||
| Gross Profit | 4,948 | 6,665 | 9,259 | 11,707 | 13264.584 | 15121.62576 | 17238.6533664 | 19652.064837696 | 22403.3539149734 | |||
| % | 38% | 38% | 38% | 38% | 38% | 38% | 38% | 38% | 38% | |||
| services | 29980 | 39,748 | 46,291 | 53,768 | 61,295.52 | 69,876.89 | 79,659.66 | 90,812.01 | 103,525.69 | 24.3% | 14.0% | |
| cost of goods sold | 18,447 | 24,507 | 28,785 | 33,211 | ||||||||
| Gross Profit | 11,533 | 15,241 | 17,506 | 20,557 | 23292.2976 | 26553.219264 | 30270.66996096 | 34508.5637554944 | 39339.7626812636 | |||
| % | 38% | 38% | 38% | 38% | 38% | 38% | 38% | 38% | 38% | |||
| Sales Total | 229,234.0 | 265,595 | 260,174 | 274,515 | 295,366.5 | 318,680.8 | 344,781.2 | 374,034.0 | 406,854.8 | 6.5% | 8.2% | |
| Gross Profit | 88186.0 | 101839.0 | 98392.0 | 104956.0 | 112239.3 | 121098.7 | 131016.9 | 142132.9 | 154604.8 | |||
| % | 38.5% | 38.34% | 37.82% | 38.23% | 38% | 38% | 38% | 38% | 38% | |||
| Revenue Growth Y/Y ($M) | -10 | 36361.0 | -5421.0 | 14341.0 | 20851.5 | 23314.4 | 26100.4 | 29252.8 | 32820.8 | |||
| Revenue Growth Y/Y % | 2% | 15.9% | -2.0% | 5.5% | 7.6% | 7.9% | 8.2% | 8.5% | 8.8% | |||
| Market Size and Share (assumes all product lines are in the same industry) | ||||||||||||
| This Company | 229,234.0 | 265,595.0 | 260,174.0 | 274,515.0 | 295,366.5 | 318,680.8 | 344,781.2 | 374,034.0 | 406,854.8 | 6.5% | 8.2% | |
| Competitor 1 | ERROR:#DIV/0! | ERROR:#DIV/0! | ||||||||||
| Competitor 2 | ERROR:#DIV/0! | ERROR:#DIV/0! | ||||||||||
| Competitor 3 | ERROR:#DIV/0! | ERROR:#DIV/0! | ||||||||||
| Competitor 4 | ERROR:#DIV/0! | ERROR:#DIV/0! | ||||||||||
| Total Market | 229,234.0 | 265,595.0 | 260,174.0 | 274,515.0 | 295,366.5 | 318,680.8 | 344,781.2 | 374,034.0 | 406,854.8 | 6.5% | 8.2% | |
| Market Share | 100.0% | 100.0% | 100.0% | 100.0% | 100.0% | 100.0% | 100.0% | 100.0% | 100.0% | |||
| Market Growth | 36361 | -5421.0 | 14341.0 | 20851.5 | 23314.4 | 26100.4 | 29252.8 | 32820.8 | ||||
| Market Growth Rate | 16% | -2% | 6% | 8% | 8% | 8% | 8% | 9% | ||||
| Our Share of Market Growth | 100% | 100% | 100% | 100% | 100% | 100% | 100% | 100% | ||||
Expenses
| Operating Expenses | ||||||||||||
| History | CY | Projections | CAGR | Assumptions/Notes | ||||||||
| -3 | -2 | -1 | 0 | 1 | 2 | 3 | 4 | 5 | History | Future | ||
| Revenues | 229234 | 265595 | 260174 | 274515 | 295366.46 | 318680.8196 | 344781.195608 | 374033.9998376 | 406854.780674058 | |||
| Research and Development | ||||||||||||
| % of Sales | 5.1% | 5.4% | 6.2% | 6.8% | 6.8% | 6.8% | 6.8% | 6.8% | 6.8% | |||
| R&D expense | 11581.0 | 14236.0 | 16217.0 | 18752.0 | 20084.9 | 21670.3 | 23445.1 | 25434.3 | 27666.1 | 18.3% | 8.1% | |
| Y/Y Growth | 23% | 14% | 16% | 7% | 8% | 8% | 8% | 9% | ||||
| Marketing | ||||||||||||
| % of Sales | 0.0% | 0.0% | 0.0% | 0.0% | ||||||||
| Marketing expense | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | ERROR:#DIV/0! | ERROR:#DIV/0! | |
| Y/Y Growth | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | ERROR:#DIV/0! | ||||
| Selling, General and Administrative | ||||||||||||
| % of Sales | 7% | 6% | 7% | 7% | 7% | 7% | 7% | 7% | 7% | |||
| Total SG&A | 15261.0 | 16705.0 | 18245.0 | 19916.0 | 20675.7 | 22307.7 | 24134.7 | 26182.4 | 28479.8 | 9.3% | 7.4% | |
| Y/Y Growth | 9% | 9% | 9% | 4% | 8% | 8% | 8% | 9% | ||||
| Total Operating Expenses | 26,842.0 | 30,941.0 | 34,462.0 | 38,668.0 | 40,760.6 | 43,978.0 | 47,579.8 | 51,616.7 | 56,146.0 | 13.3% | 7.7% | |
| % of Sales | 11.7% | 11.6% | 13.2% | 14.1% | 13.8% | 13.8% | 13.8% | 13.8% | 13.8% | |||
| Y/Y Growth | 4099.0 | 3521.0 | 4206.0 | 2092.6 | 3217.4 | 3601.9 | 4036.9 | 4529.3 | ||||
| Y/Y Growth % | 15% | 11% | 12% | 5% | 8% | 8% | 8% | 9% | ||||
Capital Requirements
| Property, Plant & Equipment | ||||||||||||
| Capital Requirements | ||||||||||||
| History | CY | Projections | ||||||||||
| -3 | -2 | -1 | 0 | 1 | 2 | 3 | 4 | 5 | ||||
| Revenues (from "Revenues and Margins" | 229234 | 265595 | 260174 | 274515 | 295366.46 | 318680.8196 | 344781.195608 | 374033.9998376 | 406854.780674058 | |||
| Projected ending PPE (based on % of sales) | 32.8% | 34.0% | 36.9% | 37.7% | 35.3% | 35.3% | 35.3% | 35.3% | 35.3% | |||
| Projected depreciation ep as % of beginning PPE | 38% | 14.5% | 13.9% | 11.5% | 12% | 12% | 12% | 12% | 12% | |||
| Property, Plant & Equipment | History | CY | Projections | |||||||||
| Recap | 2017 | 2018 | 2019 | 0 | 1 | 2 | 3 | 4 | 5 | 0.3760459089 | ||
| Property Plant and Equipment | ||||||||||||
| Beginning Balance | 27010.0 | 75076.0 | 90403.0 | 95957.0 | 103526.0 | 104399.4 | 112640.1 | 121865.4 | 132205.0 | |||
| disposals | 35566.0 | 2027.0 | -4946.0 | 269.0 | ||||||||
| Capital Expenditures | 12500.0 | 13300.0 | 10500.0 | 7300.0 | 873.4 | 8240.6 | 9225.4 | 10339.6 | 11600.7 | |||
| Ending Balance | 75076.0 | 90403.0 | 95957.0 | 103526.0 | 104399.4 | 112640.1 | 121865.4 | 132205.0 | 143805.8 | |||
| Accumulated Depreciation | ||||||||||||
| Beginning Balance | 34235.0 | 41293.0 | 49099.0 | 58579.0 | 66760.0 | 74437.4 | 82997.7 | 92542.4 | 103184.8 | |||
| disposal | -3099.0 | -3097.0 | -3067.0 | -2875.0 | ||||||||
| Depreciation | 10157.0 | 10903.0 | 12547.0 | 11056.0 | 7677.4 | 8560.3 | 9544.7 | 10642.4 | 11866.3 | |||
| Ending Balance | 41293.0 | 49099.0 | 58579.0 | 66760.0 | 74437.4 | 82997.7 | 92542.4 | 103184.8 | 115051.1 | |||
| PP&E, net of depreciation | 33783.0 | 41304.0 | 37378.0 | 36766.0 | 29962.0 | 29642.4 | 29323.0 | 29020.2 | 28754.7 | |||
Fin Summary
| Financial Summary | |||||||||||||
| 0.0317% | |||||||||||||
| History | CY | Projections | Historical | Future | |||||||||
| in millions | 2017 | 2018 | 2019 | 0 | 1 | 2 | 3 | 4 | 5 | CAGR | CAGR | ||
| Income Statement | |||||||||||||
| Sales | 229,234.0 | 265,595.0 | 260,174.0 | 274,515.0 | 295,366.5 | 318,680.8 | 344,781.2 | 374,034.0 | 406,854.8 | 6% | 8% | ||
| Cost of Goods Sold | 141,048.0 | 163,756.0 | 161,782.0 | 169,559.0 | 183,127.2 | 197,582.1 | 213,764.3 | 231,901.1 | 252,250.0 | ||||
| Gross Profit | 88,186.0 | 101,839.0 | 98,392.0 | 104,956.0 | 112,239.3 | 121,098.7 | 131,016.9 | 142,132.9 | 154,604.8 | ||||
| % | 38% | 38% | 38% | 38% | 38% | 38% | 38% | 38% | 38% | ||||
| Operating Expenses | 26842.0 | 30941.0 | 34462.0 | 38668.0 | 40760.6 | 43978.0 | 47579.8 | 51616.7 | 56146.0 | 13% | 8% | ||
| Income from Operations | 61344.0 | 70898.0 | 63930.0 | 66288.0 | 71478.7 | 77120.8 | 83437.0 | 90516.2 | 98458.9 | ||||
| % | 26.8% | 26.7% | 24.6% | 24.1% | 24.2% | 24.2% | 24.2% | 24.2% | 24.2% | ||||
| Interest Expense | 2,323.00 | 3,240.00 | 3,576.00 | 2,873 | 4,254.62 | 4,212.08 | 4,169.96 | 4,128.26 | 4,086.97 | ||||
| Other income (expense) | -133.0 | -441.0 | 422.0 | 87 | 93.61 | 101.00 | 109.27 | 118.54 | 128.94 | ||||
| Profit Before Taxes | 58888.0 | 67217.0 | 60776.0 | 63,502 | 67317.7 | 73009.7 | 79376.4 | 86506.5 | 94500.8 | -0.0678818079 | |||
| Income Taxes | 14486.4 | 12300.7 | 9663.4 | 9,144 | 9424.5 | 10221.4 | 11112.7 | 12110.9 | 13230.1 | ||||
| Net Income | 44401.6 | 54916.3 | 51112.6 | 54,358 | 57893.2 | 62788.3 | 68263.7 | 74395.6 | 81270.7 | ||||
| % | 19.4% | 20.7% | 19.6% | 19.8% | 19.6% | 19.7% | 19.8% | 19.9% | 20.0% | ||||
| Balance Sheet | |||||||||||||
| Cash | 20,289 | 25,913 | 48,844 | 38,016 | 113681.7 | 184257.3 | 259070.7 | 338411.4 | 422575.7 | ||||
| Marketable Securities | 248,606 | 211,187 | 157,054 | 153,814 | 153814.0 | 153814.0 | 153814.0 | 153814.0 | 153814.0 | no lease because its an off balance sheet item | |||
| trade Receivables | 17,874 | 23,186 | 22,926 | 16,120 | 15375.2 | 16588.9 | 17947.5 | 19470.3 | 21178.7 | ||||
| vendor non-trade receivables | 17,799 | 25,809 | 22,878 | 21,325 | 19832.3 | 18444.0 | 17152.9 | 15952.2 | 14835.6 | ||||
| Inventory | 4,855 | 3,956 | 4,106 | 4,061 | 3512.0 | 3789.2 | 4099.6 | 4447.4 | 4837.7 | ||||
| Other Current Assets | 13,936 | 12,087 | 12,352 | 11,264 | 10362.9 | 9533.8 | 8771.1 | 8069.5 | 7423.9 | ||||
| Property & Equipment | 75,076 | 90,403 | 95,957 | 103,526 | 104399.4 | 112640.1 | 121865.4 | 132205.0 | 143805.8 | ||||
| Accumulated Depreciation | (41,293) | (49,099) | (58,579) | (66,760) | -74437.4 | -82997.7 | -92542.4 | -103184.8 | -115051.1 | ||||
| Other Non-Current Assets | 18,177 | 22,283 | 32,978 | 42,522 | 51026.4 | 61231.7 | 73478.0 | 88173.6 | 105808.3 | ||||
| Total Assets | 375,319 | 365,725 | 338,516 | 323,888 | 397,566.56 | 477,301.32 | 563,656.84 | 657,358.55 | 759,228.63 | ||||
| Accounts Payable | 49,049 | 55,888 | 46,236 | 42,296 | 45486.4 | 49076.8 | 53096.3 | 57601.2 | 62655.6 | ||||
| Accrued Liabilities | 36,128 | 10,340 | 5,522 | 6,643 | 7088.8 | 7648.3 | 8274.7 | 8976.8 | 9764.5 | ||||
| Short Term Debt and Lease Obligations | - 0 | - 0 | - 0 | - 0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | ||||
| Other Current Operating Liabilities | 11,977 | 44,651 | 43,700 | 47,680 | 47680.0 | 47680.0 | 47680.0 | 47680.0 | 47680.0 | ||||
| Long Term Debt and Lease Obligations | 103,703 | 102,519 | 102,067 | 107,440 | 106365.6 | 105301.9 | 104248.9 | 103206.4 | 102174.4 | 0.1 | -0.004 | ||
| Other Non-Current Operating Liabilities | 40,415 | 45,180 | 50,503 | 54,490 | 54490.0 | 54490.0 | 54490.0 | 54490.0 | 54490.0 | -0.0114172203 | |||
| Contributed Capital (C/S + Add'l PIC) | 35867.0 | 40201.0 | 45174.0 | 50779.0 | 49459.5 | 48305.9 | 47305.1 | 46445.2 | 45715.1 | ||||
| Preferred Stock | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | ||||
| Retained Earnings & AOCI | 98180.0 | 66946.0 | 45314.0 | 14560.0 | 86996.2 | 164798.3 | 248561.7 | 338958.8 | 436749.0 | ||||
| Total Liabilities and Equity | 375319.0 | 365725.0 | 338516.0 | 323888.0 | 397566.5 | 477301.3 | 563656.8 | 657358.5 | 759228.6 | ||||
| Check (s/b 0) | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | -0.0 | 0.0 | 0.0 | -0.0 | ||||
| Operating Capital | -96663.0 | -106634.0 | -85951.0 | -88406.0 | -84831.6 | -77285.4 | -68015.9 | -56656.8 | -42765.4 | ||||
| Cash Flow | |||||||||||||
| Net Income | 54916.3 | 51112.6 | 54357.7 | 57893.2 | 62788.3 | 68263.7 | 74395.6 | 81270.7 | |||||
| Add: D&A | 10903.0 | 12547.0 | 11056.0 | 7677.4 | 8560.3 | 9544.7 | 10642.4 | 11866.3 | |||||
| Capital Expenditures | -13300.0 | -10500.0 | -7300.0 | -873.4 | -8240.6 | -9225.4 | -10339.6 | -11600.7 | |||||
| (Increase) Decrease in Operating Capital | 9971.0 | -20683.0 | 2455.0 | -3574.4 | -7546.2 | -9269.5 | -11359.2 | -13891.4 | |||||
| Operating Cash Flow | 62490.3 | 32476.6 | 60568.7 | 61122.7 | 55561.8 | 59313.6 | 63339.2 | 67644.9 | |||||
| Debt Borrowings (Payments) | |||||||||||||
| Dividends | 12803.0 | 13735.0 | 14129.0 | 14087.0 | 14543.0 | 15013.8 | 15499.8 | 16001.5 | 16519.5 | 3.24% | 3% | ||
| Cash Flow | 76225.3 | 46605.6 | 74655.7 | 75665.7 | 70575.6 | 74813.3 | 79340.7 | 84164.3 | |||||
| Key Metrics | |||||||||||||
| Y/Y Revenue Growth | 16% | -2% | 6% | 8% | 8% | 8% | 8% | 9% | |||||
| Operating Expense % of Sales | 11.7% | 11.6% | 13.2% | 14.1% | 13.8% | 13.8% | 13.8% | 13.8% | 13.8% | ||||
| Tax Rate | 25% | 18% | 16% | 14% | 14% | 14% | 14% | 14% | 14% | ||||
| Days Sales Outstanding | 28.5 | 31.9 | 32.2 | 21.4 | 19.0 | 19.0 | 19.0 | 19.0 | 19.0 | ||||
| Days Sales Inventory | 12.6 | 8.8 | 9.3 | 8.7 | 7 | 7 | 7 | 7 | 7 | ||||
| Accounts Payable % Sales | 21.4% | 21.0% | 17.8% | 15.4% | 15.4% | 15.4% | 15.4% | 15.4% | 15.4% | ||||
| Accrued Expenses % Sales | 15.8% | 3.9% | 2.1% | 2.4% | 2.4% | 2.4% | 2.4% | 2.4% | 2.4% | ||||
| Debt to Total Capital | 0.4361850683 | 0.4889633989 | 0.5300667342 | 0.6218348295 | 0.4380406541 | 0.3307158166 | 0.2605469127 | 0.2112243628 | 0.1747650408 | ||||
| Return on Assets | 11.8% | 15.0% | 15.1% | 16.8% | 14.6% | 13.2% | 12.1% | 11.3% | 10.7% | ||||
| Return on Equity | 33.1% | 51.3% | 56.5% | 83.2% | 42.4% | 29.5% | 23.1% | 19.3% | 16.8% | ||||
| Return on Invested Capital | 19.5% | 27.6% | 27.9% | 32.8% | 25.3% | 20.8% | 17.9% | 15.9% | 14.5% | ||||
Graphs
| 2017 | 2018 | 2019 | 2020 | 2021 | 2022 | 2023 | 2024 | 2025 | ||||||||||||||||
| Sales | 229234 | 265595 | 260174 | 274515 | 295366.46 | 318680.8196 | 344781.195608 | 374033.9998376 | 406854.780674058 | |||||||||||||||
| Y/Y Growth | 2% | 16% | -2% | 6% | 8% | 8% | 8% | 8% | 9% | |||||||||||||||
| 2017 | 2018 | 2019 | 2020 | 2021 | 2022 | 2023 | 2024 | 2025 | ||||||||||||||||
| COGS | 62% | 62% | 62% | 62% | 62% | 62% | 62% | 62% | 62% | |||||||||||||||
| R&D | 5% | 5% | 6% | 7% | 7% | 7% | 7% | 7% | 7% | |||||||||||||||
| Marketing | 0% | 0% | 0% | 0% | 0% | 0% | 0% | 0% | 0% | |||||||||||||||
| SG&A | 7% | 6% | 7% | 7% | 7% | 7% | 7% | 7% | 7% | |||||||||||||||
| OP | 27% | 27% | 25% | 24% | 24% | 24% | 24% | 24% | 24% | |||||||||||||||
| 100% | 100% | 100% | 100% | 100% | 100% | 100% | 100% | 100% | ||||||||||||||||
| Historical CAGR | Projected Car | |||||||||||||||||||||||
| 6% | 8% | |||||||||||||||||||||||
| 2017 | 2018 | 2019 | 2020 | 2021 | 2022 | 2023 | 2024 | 2025 | ||||||||||||||||
| Operating Income | 61344 | 70898 | 63930 | 66288 | 71478.68332 | 77120.7583432 | 83437.049337136 | 90516.2279606992 | 98458.856923122 | |||||||||||||||
| OI % | 27% | 27% | 25% | 24% | 24% | 24% | 24% | 24% | 24% | |||||||||||||||
| 2017 | 2018 | 2019 | 2020 | 2021 | 2022 | 2023 | 2024 | 2025 | ||||||||||||||||
| Market | 229234 | 265595 | 260174 | 274515 | 295366.46 | 318680.8196 | 344781.195608 | 374033.9998376 | 406854.780674058 | |||||||||||||||
| Market Share | 100% | 100% | 100% | 100% | 100% | 100% | 100% | 100% | 100% |
CAGR
Historical CAGR Projected Car 6.1929492587994961E-2 8.1868980145123782E-2
Revenue and Y/Y Growth
Sales 2017 2018 2019 2020 2021 2022 2023 2024 2025 229234 265595 260174 274515 295366.46000000002 318680.81960000005 344781.1956080001 374033.9998376001 406854.78067405772 Y/Y Growth 2017 2018 2019 2020 2021 2022 2023 2024 2025 0.02 0.15861957650261305 -2.0410775805267418E-2 5.5120803769784836E-2 7.5957452233939932E-2 7.8933673105605909E-2 8.1901308151399163E-2 8.4844546634901347E-2 8.7748121429356438E-2
Revenue $M
Y/Y Growth %
Business Model
COGS 2017 2018 2019 2020 2021 2022 2023 2024 2025 0.61530139508100901 0.6165628117999209 0.62182231890965278 0.61766752272189129 0.62 0.62 0.62000000000000011 0.62 0.61999999999999988 R & D 2017 2018 2019 2020 2021 2022 2023 2024 2025 5.0520428906706681E-2 5.3600406634161032E-2 6.233136285716482E-2 6.8309564140393061E-2 6.8000000000000005E-2 6.8000000000000005E-2 6.8000000000000005E-2 6.8000000000000005E-2 6.8000000000000005E-2 Marketing 2017 2018 2019 2020 2021 2022 2023 2024 2025 0 0 0 0 0 0 0 0 0 SG & A 2017 2018 2019 2020 2021 2022 2023 2024 2025 6.6573893924984945E-2 6.2896515371147807E-2 7.0126146348213125E-2 7.2549769593646979E-2 7.0000000000000007E-2 7.0000000000000007E-2 7.0000000000000007E-2 7.0000000000000007E-2 7.0000000000000007E-2 OP 2017 2018 2019 2020 2021 2022 2023 2024 2025 0.26760428208729936 0.26694026619477024 0.24572017188496934 0.24147314354406862 0.24200000000000002 0.24199999999999999 0.24199999999999994 0.24200000000000005 0.24200000000000002
Operating Income and Profitability
Operating Income 2017 2018 2019 2020 2021 2022 2023 2024 2025 61343.999999999985 70898 63930.000000000015 66288 71478.683320000011 77120.75834320001 83437.049337136006 90516.227960699238 98458.856923121974 OI % 2017 2018 2019 2020 2021 2022 2023 2024 2025 0.26760428208729936 0.26694026619477024 0.24572017188496934 0.24147314354406862 0.24200000000000002 0.24199999999999999 0.24199999999999994 0.24200000000000005 0.24200000000000002
Operating Income $M
Operating Income %
Market and Market Share
Market 2017 2018 2019 2020 2021 2022 2023 2024 2025 229234 265595 260174 274515 295366.46000000002 318680.81960000005 344781.1956080001 374033.9998376001 406854.78067405772 Market Share 2017 2018 2019 2020 2021 2022 2023 2024 2025 1 1 1 1 1 1 1 1 1
Market Size $M
Market Share
Critical Assumptions Significant growth over horizon of plan, primarily due to introduction of new products Gross margins decline from 50% to 44% due to lower margins on the new products Strong balance sheet and cash flow maintained over plan horizon
Valuation
| Free Cash Flow DCF Valuation | |||||||
| in millions | |||||||
| Actual | Forecast | ||||||
| current | t+1 | t+2 | t+3 | t+4 | t+5 | ||
| Net sales | 274,515.0 | 295,366.5 | 318,680.8 | 344,781.2 | 374,034.0 | 406,854.8 | |
| Cost of Goods Sold | 169,559.0 | 183,127.2 | 197,582.1 | 213,764.3 | 231,901.1 | 252,250.0 | |
| Operating costs (excl. depr.) | 27,612.0 | 33,083.2 | 35,417.7 | 38,035.1 | 40,974.3 | 44,279.7 | |
| Depreciation | 11,056.0 | 7,677.4 | 8,560.3 | 9,544.7 | 10,642.4 | 11,866.3 | |
| EBIT | 66,288.0 | 71,478.7 | 77,120.8 | 83,437.0 | 90,516.2 | 98,458.9 | |
| Income tax (EBIT x tax%) | 9,545.5 | 10,007.0 | 10,796.9 | 11,681.2 | 12,672.3 | 13,784.2 | |
| NOPAT | 56,742.5 | 61,471.7 | 66,323.9 | 71,755.9 | 77,844.0 | 84,674.6 | |
| Cash | 38,016.0 | 113,681.7 | 184,257.3 | 259,070.7 | 338,411.4 | 422,575.7 | |
| Marketable sec | 153,814.0 | 153,814.0 | 153,814.0 | 153,814.0 | 153,814.0 | 153,814.0 | |
| Accounts receivable | 16,120.0 | 15,375.2 | 16,588.9 | 17,947.5 | 19,470.3 | 21,178.7 | |
| Inventories | 4,061.0 | 3,512.0 | 3,789.2 | 4,099.6 | 4,447.4 | 4,837.7 | |
| Other current operating assets | 11,264.0 | 10,362.9 | 9,533.8 | 8,771.1 | 8,069.5 | 7,423.9 | |
| Net fixed assets | 36,766.0 | 29,962.0 | 29,642.4 | 29,323.0 | 29,020.2 | 28,754.7 | |
| Other non-current operating assets | 42,522.0 | 51,026.4 | 61,231.7 | 73,478.0 | 88,173.6 | 105,808.3 | |
| total assets | 302,563.0 | 377,734.3 | 458,857.3 | 546,503.9 | 641,406.3 | 744,393.1 | |
| Accts. payable, accruals and other operating current liabilities | 96,619.0 | 100,255.2 | 104,405.2 | 109,051.1 | 114,258.1 | 120,100.2 | |
| short term debt | - 0 | - 0 | - 0 | - 0 | - 0 | - 0 | |
| Long Term debt | 107,440.0 | 106,365.6 | 105,301.9 | 104,248.9 | 103,206.4 | 102,174.4 | |
| Other operating non-current liabilities | 54,490.0 | 54,490.0 | 54,490.0 | 54,490.0 | 54,490.0 | 54,490.0 | |
| total liabilities | 258,549.0 | 261,110.8 | 264,197.1 | 267,790.0 | 271,954.5 | 276,764.5 | |
| common stock (100 sh outstanding) | 50,779.0 | ||||||
| preferred stock | - 0 | ||||||
| retained earnings | 14,560.0 | 116,623.5 | 194,660.2 | 278,713.9 | 369,451.9 | 467,628.6 | |
| total equity | 65,339.0 | 116,623.5 | 194,660.2 | 278,713.9 | 369,451.9 | 467,628.6 | |
| total lia and equity | 323,888.0 | 377,734.3 | 458,857.3 | 546,503.9 | 641,406.3 | 744,393.1 | |
| Valuation | |||||||
| current | t+1 | t+2 | t+3 | t+4 | t+5 | ||
| Net Operating Working Capital | (65,174.0) | (125,495.1) | (128,983.2) | (132,722.8) | (136,760.9) | (141,149.8) | |
| other noncurrent op. lia | (54,490.0) | (54,490.0) | (54,490.0) | (54,490.0) | (54,490.0) | (54,490.0) | |
| Net Fixed Assets | 36,766.0 | 29,962.0 | 29,642.4 | 29,323.0 | 29,020.2 | 28,754.7 | |
| other long term assets | 42,522.0 | 51,026.4 | 61,231.7 | 73,478.0 | 88,173.6 | 105,808.3 | |
| Net Operating Capital | (40,376.0) | (98,996.7) | (92,599.2) | (84,411.8) | (74,057.1) | (61,076.8) | |
| Free Cash Flow | |||||||
| NOPAT | 56,742.5 | 61,471.7 | 66,323.9 | 71,755.9 | 77,844.0 | 84,674.6 | |
| - Incr. in Net Op Cap | 58,620.7 | (6,397.5) | (8,187.4) | (10,354.7) | (12,980.3) | ||
| FCF | 120,092.3 | 59,926.4 | 63,568.5 | 67,489.2 | 71,694.3 | ||
| NOPAT | 56,742.5 | 61,471.7 | 66,323.9 | 71,755.9 | 77,844.0 | 84,674.6 | |
| ROIC | (1.4) | (0.6) | (0.7) | (0.9) | (1.1) | (1.4) | |
| t+1 | t+2 | t+3 | t+4 | t+5 | |||
| Free Cash Flows | 120,092.3 | 59,926.4 | 63,568.5 | 67,489.2 | 71,694.3 | ||
| WACC = | 8.0% tc={44CD2139-77AB-A049-BB4A-1D8B54760107}: [Threaded comment] Your version of Excel allows you to read this threaded comment; however, any edits to it will get removed if the file is opened in a newer version of Excel. Learn more: https://go.microsoft.com/fwlink/?linkid=870924 Comment: https://finbox.com/NASDAQGS:AAPL/models/wacc |
||||||
| l-t growth rate= | 6.4% tc={4AE9051E-F274-2746-BF03-4157CF06EF1D}: [Threaded comment] Your version of Excel allows you to read this threaded comment; however, any edits to it will get removed if the file is opened in a newer version of Excel. Learn more: https://go.microsoft.com/fwlink/?linkid=870924 Comment: https://www.bea.gov/data/gdp/gdp-state |
||||||
| HV = (FCF end yr)*(1+g) / (WACC - g) = | 4,767,672.7 | ||||||
| FCF + HV | 120,092.3 | 59,926.4 | 63,568.5 | 67,489.2 | 4,839,367.0 | ||
| Value of Operations | |||||||
| NPV | 3,556,235.0 | ||||||
| + cash and ST Investments | 191,830.0 | ||||||
| Est Intrinsic val | 3,748,065.0 | ||||||
| - debt | (107,440.0) | debt = all short and long term loans, bonds and accrued interest | |||||
| - preferred stock | - 0 | you may use the market value of all outstanding prfd stock = pref stock price x # of prf stock outstanding | |||||
| Intrinsic val of equity | 3,640,625.0 | ||||||
| number of sh | 17,352.0 | use the outstanding shares (available on a financial info website (e.g. Yahoo Finance) or the 10-K | use the outstanding shares (available on a financial info website (e.g. Yahoo Finance) or the 10-K | ||||
| intrinsic stock price | 209.81 | ||||||
