2. Below are the income statements and balance sheets of Apple Inc ending fiscal year 2020. You are tasked to determine whether the company's stock price is overvalued or undervalued by conducting a detailed DCF analysis to determine the fair stock price based on fundamentals. In order to do that, you must first forecast the Pro Forma financial statements for the next 5 years and then calculate the free cash flows and terminal value, which you will use the find the enterprise value ending 2020. The stock price can then be calculated based on the enterprise value and the net debt balance. al(6 pts) Proforma income statement: Calculate the Sales growth rate from 2019 to 2020, use this rate for the next five years to calculate the sales from 2021 to 2025. Use the percentage of sales method, calculate the percentages of sales for Cost of Sales and total operating expense from 2018 to 2020. Use the average of the percentages for year 2021 to 2025. Complete other highlighted cells using financial/accounting calculations. Tax rate is 16.24%, calculated as the average of the past 3 years b) (8 pts) Pro forma balance sheet: First calculate the total assets by dividing Sales by Asset Turnover Ratio. Calculate the percentages of total Asset for various line items in 2019 and 2020, use the average percentages of these two years for year 2021 to 2025. Complete any lighted cells. You may refer to "Cash and Cash equivalents" as an example, Use "Shareholder's Equity" as the balancing item, meaning shareholder's equity is the line that makes total assets equal total liabilities and equities in the end. c) (4 pts) Calculate depreciation the same way as you would in part b) using percentage of total assets. This year's Cap EX = This year's Net PP&E-Last year's Net PP&E+This year's depreciation Net Working Capital = Total Current Assets - Total Current Liabilities This Year's Change in NWC - This year's NWC-Last year's NWC d) (6 pts) DCF calculation: Calculate the FCFs for 2021 to 2025. Calculate the terminal value using the long-term growth rate of 5.51%. Discount everything using a WACC of 8.5% to calculate the enterprise value of Apple el (4 pts) Calculate Apple's stock price. To calculate stock price and equity value, the interest-bearing debt should only include long-term debt and short-term debt from the balance sheet in 2020. Excess cash includes cash and cash equivalents. The total numbers of shares at the end of 2020 can be found in the income statement (4 pts) ir Apple's current stock price is $115, is the stock undervalued or overvalued? What should be the expected long-term growth rate by the current investors of Apple? (Hint: use Solver) INCOME STATEMENTS (in Millions) 2018 $ 265,595 12 Months Ended 2019 $ 260,174 Average Sales Growth Forecast 2022 2023 $ 289,400 $ 297.155 2.680% 3.152% (179,590) (184,826) 2.92% 3.03% 109,810 112,329 0 0 F F ADIV/01 NDIV/01 2020 2021 $ 274,515 $ 279,279 1.736% 3.6245 % (169,559) (174,103) 2.68% 3.15% 104,956 105,177 (38,668) (10,799,168,557) 11.79% 11.79% 66,288 803 1,538 67,091 (9,680) -16.24% Sales Soles growth rate Cost of sales % of Sales Gross margin Total operating expenses of Sales Operating income (EBIT) Other income/expensel, net Income before provision for income taxes Provision for income taxes Marginal Tax Rate 2024 $ 306,520 2.916% (190,433) 48.54% 116,087 0 ADIV/0! 2025 $315,457 3.034% 197,993) VALUEI 217,464 0 ADIV/01 32,933 30,680 (163,756) (161,782) 1.74% 3.62% 101,839 98,392 (30,941) (34,462) 11.79% 13963916.69% 70,898 63,930 2,005 1,807 72,903 65,737 (13,372) (10,481) -18.34% -15.94% 1,538 1,539 1,538 1,538 -16.24% -26.24% -16.24% -16.24% $ 59,531 $ 55,256 $ 57,411 Net income Earnings per share: Basic (in dollars per share) Diluted (in dollars per share) Shares used in computing earnings per share: Basic (In shares) Diluted (in shares) $3 $ 2.98 $ 2.99 $2.97 $ 3.31 $ 3.28 19,822 20,000 18,471 18,595 17,352 17,528 2019 2020 2021 2022 2023 2024 2025 SO $ 100,557 29.71% 45,804 $ 90,943 28.08% 37,445 $0 28.89% $0 28.89% $0 28.89% $0 28.89% 28.89% 4,106 4,061 12,352 11,264 CONSOLIDATED BALANCE SHEETS - USD ($) in Millions Current assets: Cash and cash equivalents % of Total Assets Accounts receivable % of Total Assets Inventories % of Total Assets Other current assets % of Total Assets Total current assets Non-current assets: Non current Marketable securities % of Total Assets Property, plant and equipment, net % of Total Assets Other non-current assets % of Total Assets Total non-current assets Total assets Asset turnover ratio: Sales/Total Assets 162,819 143,713 105,341 100,887 37,378 36,766 $0 $0 0 $0 11.44% $0 11.44% $0 11.44% 11.44% 11.44% 32,978 9.74% 175,697 338,516 0.769 42,522 13.13% 180,175 323,888 0.848 0.808 0.808 0.80R 0.808 0.808 46,236 42,296 Current liabilities: Accounts payable % of Total Assets Other current liabilities of Total Assets Short-Term debt 49,222 54,323 10,260 8,773 10,260 8,773 105,718 105,392 91,807 98,667 0 0 0 0 0 Short-Term debt % of Total Assets Total current liabilities Non-current liabilities: Long-Term debt % of Total Assets Other non-current liabilities % of Total Assets Total non-current liabilities Total liabilities Shareholders' equity: Total shareholders' equity Total liabilities and shareholders' equity 0 0 0 0 0 15.87% 15.87% 15.87% 15.87% 15.87% 50,503 14.92% 142,310 248,028 54,490 16.82% 153,157 258,549 90,488 $ 338,516 65,339 $ 323,888 12,547 11,056 Depreciation and amortization % of Total Assets Capital Expenditure (CapEX) Net Working Capital Change in NWC 2020 2021 2022 2023 2024 2025 FCF Calaculation EBIT Tax NOPAT Depreciation Cap Ex Change in NWC FCF to the firm 5.51% Long term growth rate beyond 2025 Terminal value in 2025 FCF includeing terminal Value 8.50% Apple's WACC Enterprise Value using DCF Equity Value Share Price Overvalued or Undervalued ? Long term growth rate if current stock price is $115 2. Below are the income statements and balance sheets of Apple Inc ending fiscal year 2020. You are tasked to determine whether the company's stock price is overvalued or undervalued by conducting a detailed DCF analysis to determine the fair stock price based on fundamentals. In order to do that, you must first forecast the Pro Forma financial statements for the next 5 years and then calculate the free cash flows and terminal value, which you will use the find the enterprise value ending 2020. The stock price can then be calculated based on the enterprise value and the net debt balance. al(6 pts) Proforma income statement: Calculate the Sales growth rate from 2019 to 2020, use this rate for the next five years to calculate the sales from 2021 to 2025. Use the percentage of sales method, calculate the percentages of sales for Cost of Sales and total operating expense from 2018 to 2020. Use the average of the percentages for year 2021 to 2025. Complete other highlighted cells using financial/accounting calculations. Tax rate is 16.24%, calculated as the average of the past 3 years b) (8 pts) Pro forma balance sheet: First calculate the total assets by dividing Sales by Asset Turnover Ratio. Calculate the percentages of total Asset for various line items in 2019 and 2020, use the average percentages of these two years for year 2021 to 2025. Complete any lighted cells. You may refer to "Cash and Cash equivalents" as an example, Use "Shareholder's Equity" as the balancing item, meaning shareholder's equity is the line that makes total assets equal total liabilities and equities in the end. c) (4 pts) Calculate depreciation the same way as you would in part b) using percentage of total assets. This year's Cap EX = This year's Net PP&E-Last year's Net PP&E+This year's depreciation Net Working Capital = Total Current Assets - Total Current Liabilities This Year's Change in NWC - This year's NWC-Last year's NWC d) (6 pts) DCF calculation: Calculate the FCFs for 2021 to 2025. Calculate the terminal value using the long-term growth rate of 5.51%. Discount everything using a WACC of 8.5% to calculate the enterprise value of Apple el (4 pts) Calculate Apple's stock price. To calculate stock price and equity value, the interest-bearing debt should only include long-term debt and short-term debt from the balance sheet in 2020. Excess cash includes cash and cash equivalents. The total numbers of shares at the end of 2020 can be found in the income statement (4 pts) ir Apple's current stock price is $115, is the stock undervalued or overvalued? What should be the expected long-term growth rate by the current investors of Apple? (Hint: use Solver) INCOME STATEMENTS (in Millions) 2018 $ 265,595 12 Months Ended 2019 $ 260,174 Average Sales Growth Forecast 2022 2023 $ 289,400 $ 297.155 2.680% 3.152% (179,590) (184,826) 2.92% 3.03% 109,810 112,329 0 0 F F ADIV/01 NDIV/01 2020 2021 $ 274,515 $ 279,279 1.736% 3.6245 % (169,559) (174,103) 2.68% 3.15% 104,956 105,177 (38,668) (10,799,168,557) 11.79% 11.79% 66,288 803 1,538 67,091 (9,680) -16.24% Sales Soles growth rate Cost of sales % of Sales Gross margin Total operating expenses of Sales Operating income (EBIT) Other income/expensel, net Income before provision for income taxes Provision for income taxes Marginal Tax Rate 2024 $ 306,520 2.916% (190,433) 48.54% 116,087 0 ADIV/0! 2025 $315,457 3.034% 197,993) VALUEI 217,464 0 ADIV/01 32,933 30,680 (163,756) (161,782) 1.74% 3.62% 101,839 98,392 (30,941) (34,462) 11.79% 13963916.69% 70,898 63,930 2,005 1,807 72,903 65,737 (13,372) (10,481) -18.34% -15.94% 1,538 1,539 1,538 1,538 -16.24% -26.24% -16.24% -16.24% $ 59,531 $ 55,256 $ 57,411 Net income Earnings per share: Basic (in dollars per share) Diluted (in dollars per share) Shares used in computing earnings per share: Basic (In shares) Diluted (in shares) $3 $ 2.98 $ 2.99 $2.97 $ 3.31 $ 3.28 19,822 20,000 18,471 18,595 17,352 17,528 2019 2020 2021 2022 2023 2024 2025 SO $ 100,557 29.71% 45,804 $ 90,943 28.08% 37,445 $0 28.89% $0 28.89% $0 28.89% $0 28.89% 28.89% 4,106 4,061 12,352 11,264 CONSOLIDATED BALANCE SHEETS - USD ($) in Millions Current assets: Cash and cash equivalents % of Total Assets Accounts receivable % of Total Assets Inventories % of Total Assets Other current assets % of Total Assets Total current assets Non-current assets: Non current Marketable securities % of Total Assets Property, plant and equipment, net % of Total Assets Other non-current assets % of Total Assets Total non-current assets Total assets Asset turnover ratio: Sales/Total Assets 162,819 143,713 105,341 100,887 37,378 36,766 $0 $0 0 $0 11.44% $0 11.44% $0 11.44% 11.44% 11.44% 32,978 9.74% 175,697 338,516 0.769 42,522 13.13% 180,175 323,888 0.848 0.808 0.808 0.80R 0.808 0.808 46,236 42,296 Current liabilities: Accounts payable % of Total Assets Other current liabilities of Total Assets Short-Term debt 49,222 54,323 10,260 8,773 10,260 8,773 105,718 105,392 91,807 98,667 0 0 0 0 0 Short-Term debt % of Total Assets Total current liabilities Non-current liabilities: Long-Term debt % of Total Assets Other non-current liabilities % of Total Assets Total non-current liabilities Total liabilities Shareholders' equity: Total shareholders' equity Total liabilities and shareholders' equity 0 0 0 0 0 15.87% 15.87% 15.87% 15.87% 15.87% 50,503 14.92% 142,310 248,028 54,490 16.82% 153,157 258,549 90,488 $ 338,516 65,339 $ 323,888 12,547 11,056 Depreciation and amortization % of Total Assets Capital Expenditure (CapEX) Net Working Capital Change in NWC 2020 2021 2022 2023 2024 2025 FCF Calaculation EBIT Tax NOPAT Depreciation Cap Ex Change in NWC FCF to the firm 5.51% Long term growth rate beyond 2025 Terminal value in 2025 FCF includeing terminal Value 8.50% Apple's WACC Enterprise Value using DCF Equity Value Share Price Overvalued or Undervalued ? Long term growth rate if current stock price is $115