Do a sensitivity analysis and scenario analysis base on the valuation on Excel ( company Apple). Attached is the work I have done so far included the valuation
Instructions Model NameAPPLE 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 22:24Revenue and Margins HistoryCY Projections CAGRAssumptions/Notes 201720182019202020212022202320242025HistoryFuture Iphone141319164,888142,381137,781140,536.62143,347.35146,214.30149,138.59152,121.360.4%2.0% cost of goods sold86,954101,66488,53685,103 Gross Profit54,36563,22453,84552,67853403.915654471.99391255561.4337902456672.662466044857806.1157153657 %38%38%38%38.2%38%38%38%38%38% Mac2585025,19825,74028,62232,056.6435,903.4440,211.8545,037.2750,441.74-0.2%12.0% cost of goods sold15,90615,53616,00617,679 Gross Profit9,9449,6629,73410,94312181.523213643.30598415280.5027020817114.163026329619167.8625894892 %38%38%38%38.2%38%38%38%38%38% Ipad1922218,38021,28023,72426570.8829759.385633330.51187237330.1732966441809.79409223685.2%12.0% cost of goods sold11,82711,33213,23214,654 Gross Profit7,3957,0488,0489,07010096.934411308.56652812665.5945113614185.465852723215887.72175505 %38%38%38%38.2%38%38%38%38%38% Wearables, Home and Accessories1286317,38124,48230,62034,906.8039,793.7545,364.8851,715.9658,956.1938.0%14.0% cost of goods sold7,91510,71615,22318,913 Gross Profit4,9486,6659,25911,70713264.58415121.6257617238.653366419652.06483769622403.3539149734 %38%38%38%38%38%38%38%38%38% services2998039,74846,29153,76861,295.5269,876.8979,659.6690,812.01103,525.6924.3%14.0% cost of goods sold18,44724,50728,78533,211 Gross Profit11,53315,24117,50620,55723292.297626553.21926430270.6699609634508.563755494439339.7626812636 %38%38%38%38%38%38%38%38%38% Sales Total229,234.0265,595260,174274,515295,366.5318,680.8344,781.2374,034.0406,854.86.5%8.2% Gross Profit88186.0101839.098392.0104956.0112239.3121098.7131016.9142132.9154604.8 %38.5%38.34%37.82%38.23%38%38%38%38%38% Revenue Growth Y/Y ($M)-1036361.0-5421.014341.020851.523314.426100.429252.832820.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 Company229,234.0265,595.0260,174.0274,515.0295,366.5318,680.8344,781.2374,034.0406,854.86.5%8.2% Competitor 1ERROR:#DIV/0!ERROR:#DIV/0! Competitor 2ERROR:#DIV/0!ERROR:#DIV/0! Competitor 3ERROR:#DIV/0!ERROR:#DIV/0! Competitor 4ERROR:#DIV/0!ERROR:#DIV/0! Total Market229,234.0265,595.0260,174.0274,515.0295,366.5318,680.8344,781.2374,034.0406,854.86.5%8.2% Market Share100.0%100.0%100.0%100.0%100.0%100.0%100.0%100.0%100.0% Market Growth36361-5421.014341.020851.523314.426100.429252.832820.8 Market Growth Rate16%-2%6%8%8%8%8%9% Our Share of Market Growth100%100%100%100%100%100%100%100% Expenses Operating Expenses HistoryCY Projections CAGRAssumptions/Notes -3-2-1012345HistoryFuture Revenues229234265595260174274515295366.46318680.8196344781.195608374033.9998376406854.780674058 Research and Development % of Sales5.1%5.4%6.2%6.8%6.8%6.8%6.8%6.8%6.8% R&D expense11581.014236.016217.018752.020084.921670.323445.125434.327666.118.3%8.1% Y/Y Growth 23%14%16%7%8%8%8%9% Marketing % of Sales0.0%0.0%0.0%0.0% Marketing expense0.00.00.00.00.00.00.00.00.0ERROR:#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 Sales7%6%7%7%7%7%7%7%7% Total SG&A15261.016705.018245.019916.020675.722307.724134.726182.428479.89.3%7.4% Y/Y Growth 9%9%9%4%8%8%8%9% Total Operating Expenses26,842.030,941.034,462.038,668.040,760.643,978.047,579.851,616.756,146.013.3%7.7% % of Sales11.7%11.6%13.2%14.1%13.8%13.8%13.8%13.8%13.8% Y/Y Growth 4099.03521.04206.02092.63217.43601.94036.94529.3 Y/Y Growth %15%11%12%5%8%8%8%9% Capital Requirements Property, Plant & Equipment Capital Requirements HistoryCY Projections -3-2-1012345 Revenues (from "Revenues and Margins"229234265595260174274515295366.46318680.8196344781.195608374033.9998376406854.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 PPE38%14.5%13.9%11.5%12%12%12%12%12% Property, Plant & EquipmentHistoryCY Projections Recap2017201820190123450.3760459089 Property Plant and Equipment Beginning Balance27010.075076.090403.095957.0103526.0104399.4112640.1121865.4132205.0 disposals35566.02027.0-4946.0269.0 Capital Expenditures12500.013300.010500.07300.0873.48240.69225.410339.611600.7 Ending Balance75076.090403.095957.0103526.0104399.4112640.1121865.4132205.0143805.8 Accumulated Depreciation Beginning Balance34235.041293.049099.058579.066760.074437.482997.792542.4103184.8 disposal-3099.0-3097.0-3067.0-2875.0 Depreciation10157.010903.012547.011056.07677.48560.39544.710642.411866.3 Ending Balance41293.049099.058579.066760.074437.482997.792542.4103184.8115051.1 PP&E, net of depreciation33783.041304.037378.036766.029962.029642.429323.029020.228754.7 Fin Summary Financial Summary 0.0317% HistoryCY ProjectionsHistoricalFuture in millions201720182019012345CAGRCAGR Income Statement Sales229,234.0265,595.0260,174.0274,515.0295,366.5318,680.8344,781.2374,034.0406,854.86%8% Cost of Goods Sold141,048.0163,756.0161,782.0169,559.0183,127.2197,582.1213,764.3231,901.1252,250.0 Gross Profit88,186.0101,839.098,392.0104,956.0112,239.3121,098.7131,016.9142,132.9154,604.8 %38%38%38%38%38%38%38%38%38% Operating Expenses26842.030941.034462.038668.040760.643978.047579.851616.756146.013%8% Income from Operations61344.070898.063930.066288.071478.777120.883437.090516.298458.9 %26.8%26.7%24.6%24.1%24.2%24.2%24.2%24.2%24.2% Interest Expense2,323.003,240.003,576.002,8734,254.624,212.084,169.964,128.264,086.97 Other income (expense)-133.0-441.0422.08793.61101.00109.27118.54128.94 Profit Before Taxes58888.067217.060776.063,50267317.773009.779376.486506.594500.8-0.0678818079 Income Taxes14486.412300.79663.49,1449424.510221.411112.712110.913230.1 Net Income44401.654916.351112.654,35857893.262788.368263.774395.681270.7 %19.4%20.7%19.6%19.8%19.6%19.7%19.8%19.9%20.0% Balance Sheet Cash20,28925,91348,84438,016113681.7184257.3259070.7338411.4422575.7 Marketable Securities248,606211,187157,054153,814153814.0153814.0153814.0153814.0153814.0no lease because its an off balance sheet item trade Receivables17,87423,18622,92616,12015375.216588.917947.519470.321178.7 vendor non-trade receivables17,79925,80922,87821,32519832.318444.017152.915952.214835.6 Inventory4,8553,9564,1064,0613512.03789.24099.64447.44837.7 Other Current Assets13,93612,08712,35211,26410362.99533.88771.18069.57423.9 Property & Equipment75,07690,40395,957103,526104399.4112640.1121865.4132205.0143805.8 Accumulated Depreciation(41,293)(49,099)(58,579)(66,760)-74437.4-82997.7-92542.4-103184.8-115051.1 Other Non-Current Assets18,17722,28332,97842,52251026.461231.773478.088173.6105808.3 Total Assets375,319365,725338,516323,888397,566.56477,301.32563,656.84657,358.55759,228.63 Accounts Payable49,04955,88846,23642,29645486.449076.853096.357601.262655.6 Accrued Liabilities36,12810,3405,5226,6437088.87648.38274.78976.89764.5 Short Term Debt and Lease Obligations- 0- 0- 0- 00.00.00.00.00.0 Other Current Operating Liabilities11,97744,65143,70047,68047680.047680.047680.047680.047680.0 Long Term Debt and Lease Obligations103,703102,519102,067107,440106365.6105301.9104248.9103206.4102174.40.1-0.004 Other Non-Current Operating Liabilities40,41545,18050,50354,49054490.054490.054490.054490.054490.0-0.0114172203 Contributed Capital (C/S + Add'l PIC)35867.040201.045174.050779.049459.548305.947305.146445.245715.1 Preferred Stock0.00.00.00.00.00.00.00.00.0 Retained Earnings & AOCI98180.066946.045314.014560.086996.2164798.3248561.7338958.8436749.0 Total Liabilities and Equity375319.0365725.0338516.0323888.0397566.5477301.3563656.8657358.5759228.6 Check (s/b 0)0.00.00.00.00.0-0.00.00.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 Income54916.351112.654357.757893.262788.368263.774395.681270.7 Add: D&A10903.012547.011056.07677.48560.39544.710642.411866.3 Capital Expenditures-13300.0-10500.0-7300.0-873.4-8240.6-9225.4-10339.6-11600.7 (Increase) Decrease in Operating Capital9971.0-20683.02455.0-3574.4-7546.2-9269.5-11359.2-13891.4 Operating Cash Flow62490.332476.660568.761122.755561.859313.663339.267644.9 Debt Borrowings (Payments) Dividends12803.013735.014129.014087.014543.015013.815499.816001.516519.53.24%3% Cash Flow76225.346605.674655.775665.770575.674813.379340.784164.3 Key Metrics Y/Y Revenue Growth16%-2%6%8%8%8%8%9% Operating Expense % of Sales11.7%11.6%13.2%14.1%13.8%13.8%13.8%13.8%13.8% Tax Rate25%18%16%14%14%14%14%14%14% Days Sales Outstanding28.531.932.221.419.019.019.019.019.0 Days Sales Inventory12.68.89.38.777777 Accounts Payable % Sales21.4%21.0%17.8%15.4%15.4%15.4%15.4%15.4%15.4% Accrued Expenses % Sales15.8%3.9%2.1%2.4%2.4%2.4%2.4%2.4%2.4% Debt to Total Capital0.43618506830.48896339890.53006673420.62183482950.43804065410.33071581660.26054691270.21122436280.1747650408 Return on Assets11.8%15.0%15.1%16.8%14.6%13.2%12.1%11.3%10.7% Return on Equity33.1%51.3%56.5%83.2%42.4%29.5%23.1%19.3%16.8% Return on Invested Capital19.5%27.6%27.9%32.8%25.3%20.8%17.9%15.9%14.5% Graphs 201720182019202020212022202320242025 Sales229234265595260174274515295366.46318680.8196344781.195608374033.9998376406854.780674058 Y/Y Growth2%16%-2%6%8%8%8%8%9% 201720182019202020212022202320242025 COGS62%62%62%62%62%62%62%62%62% R&D5%5%6%7%7%7%7%7%7% Marketing0%0%0%0%0%0%0%0%0% SG&A7%6%7%7%7%7%7%7%7% OP27%27%25%24%24%24%24%24%24% 100%100%100%100%100%100%100%100%100% Historical CAGRProjected Car 6%8% 201720182019202020212022202320242025 Operating Income6134470898639306628871478.6833277120.758343283437.04933713690516.227960699298458.856923122 OI %27%27%25%24%24%24%24%24%24% 201720182019202020212022202320242025 Market229234265595260174274515295366.46318680.8196344781.195608374033.9998376406854.780674058 Market Share100%100%100%100%100%100%100%100%100% CAGR Historical CAGRProjected Car6.1929492587994961E-28.1868980145123782E-2 Revenue and Y/Y Growth Sales201720182019202020212022202320242025229234265595260174274515295366.46000000002318680.81960000005344781.1956080001374033.9998376001406854.78067405772Y/Y Growth2017201820192020202120222023202420250.020.15861957650261305-2.0410775805267418E-25.5120803769784836E-27.5957452233939932E-27.8933673105605909E-28.1901308151399163E-28.4844546634901347E-28.7748121429356438E-2 Revenue $M Y/Y Growth % Business Model COGS2017201820192020202120222023202420250.615301395081009010.61656281179992090.621822318909652780.617667522721891290.620.620.620000000000000110.620.61999999999999988R&D2017201820192020202120222023202420255.0520428906706681E-25.3600406634161032E-26.233136285716482E-26.8309564140393061E-26.8000000000000005E-26.8000000000000005E-26.8000000000000005E-26.8000000000000005E-26.8000000000000005E-2Marketing201720182019202020212022202320242025000000000SG&A2017201820192020202120222023202420256.6573893924984945E-26.2896515371147807E-27.0126146348213125E-27.2549769593646979E-27.0000000000000007E-27.0000000000000007E-27.0000000000000007E-27.0000000000000007E-27.0000000000000007E-2OP2017201820192020202120222023202420250.267604282087299360.266940266194770240.245720171884969340.241473143544068620.242000000000000020.241999999999999990.241999999999999940.242000000000000050.24200000000000002 Operating Income and Profitability Operating Income20172018201920202021202220232024202561343.9999999999857089863930.0000000000156628871478.68332000001177120.7583432000183437.04933713600690516.22796069923898458.856923121974OI %2017201820192020202120222023202420250.267604282087299360.266940266194770240.245720171884969340.241473143544068620.242000000000000020.241999999999999990.241999999999999940.242000000000000050.24200000000000002 Operating Income $M Operating Income % Market and Market Share Market201720182019202020212022202320242025229234265595260174274515295366.46000000002318680.81960000005344781.1956080001374033.9998376001406854.78067405772Market Share201720182019202020212022202320242025111111111 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 ActualForecast currentt+1t+2t+3t+4t+5 Net sales274,515.0295,366.5318,680.8344,781.2374,034.0406,854.8 Cost of Goods Sold169,559