Build different sheets based on the instruction and given balance sheet.

- Equity Value - Debt Value (Refer to entry on Sheet 3) - Tax Rate (Refer to entry on Sheet 2) - Cost of Debt (Refer to entry on Sheet 3) - Cost of Equity: Gordon Model (Refer to entry on Sheet 4) - Cost of Equity: CAPM (Refer to entry on Sheet 5) - WACC (using Cost of Equity: Gordon Model) - WACC (using Cost of Equity: CAPM) - In a note, answer the following question: Do you have a preference between the two methods for calculating Cost of Equity? Explain your answer in either case. - Sheet 2: Tax Rate - Like we did in class, use at least 3 years of data to estimate an average tax rate. If you recall our class work, Apple has had a fairly steady rate so this shouldn't be an issue. - Sheet 3: Cost of Debt - First estimate Net Financial Debt for year ending 2021 and 2022 (exactly like you did in your EV calculations last week) - Besides the obvious components on the liabilities (CP and Debt), you should also include Other current liabilities and Other noncurrent liabilities for debt here. - Don't include Vendor non-trade receivables here (if you want to know why, read the bottom of the Financial Instruments section of their Notes to Financial Statements), and obviously don't include Deferred revenue here. Both of those should be part of NWC which we're not considering this week. - Don't forget to include both short- and long-term marketable securities as part of excess financial assets. - Estimate the cost of debt as the ratio of net interest expense to net financial debt. - Use the following data: - Total dividends paid in each of the 5 years till Sep 2022 - Net stock repurchase in each of the past 5 years till Sep 2022 - Add two pieces above to get total cash flow to equityholders Estimate the average growth rate in cash flow to equityholders (use arithmetic average) - Get the market value at Sep 2022 end Use Gordon model formula to estimate cost of equity - Sheet 5: CAPM Beta estimate (Refer to sheet 6) Risk-free rate (Use the 1-year Treasury bill rate from the Treasury website) Expected market return or Market risk premium (Use the number from class or any other figure you feel is appropriate - with explanation) - Sheet 6: Beta Estimate Get 5 years of monthly prices for Apple and VFINX (this will be your market proxy) Calculate the returns Estimate beta \begin{tabular}{|c|c|c|c|} \hline 1 & CONSOLIDATED BALANCE SHEETS - USD (\$) \$ in Millions & Sep. 24,2022 & Sep. 25,2021 \\ \hline 2 & Current assets: & & \\ \hline 3 & Cash and cash equivalents & $23,646 & $34,940 \\ \hline 4 & Marketable securities & 24,658 & 27,699 \\ \hline 5 & Accounts receivable, net & 28,184 & 26,278 \\ \hline 6 & Inventories & 4,946 & 6,580 \\ \hline 7 & Vendor non-trade receivables & 32,748 & 25,228 \\ \hline 8 & 0ther current assets & 21,223 & 14,111 \\ \hline 9 & Total current assets & 135,405 & 134,836 \\ \hline 10 & Non-current assets: & & \\ \hline 11 & Marketable securities & 120,805 & 127,877 \\ \hline 12 & Property, plant and equipment, net & 42,117 & 39,440 \\ \hline 13 & 0ther non-current assets & 54,428 & 48,849 \\ \hline 14 & Total non-current assets & 217,350 & 216,166 \\ \hline 15 & Total assets & 352,755 & 351,002 \\ \hline 16 & Current 1iabilities: & & \\ \hline 17 & Accounts payable & 64,115 & 54,763 \\ \hline 18 & 0ther current liabilities & 60,845 & 47,493 \\ \hline 19 & Deferred revenue & 7,912 & 7,612 \\ \hline 20 & Commercial paper & 9,982 & 6,000 \\ \hline 21 & Term debt & 11,128 & 9,613 \\ \hline 22 & Total current liabilities & 153,982 & 125,481 \\ \hline 23 & Non-current liabilities: & & \\ \hline 24 & Term debt & 98,959 & 109,106 \\ \hline 25 & 0ther non-current liabilities & 49,142 & 53,325 \\ \hline 26 & Total non-current liabilities & 148,101 & 162,431 \\ \hline 27 & Total liabilities & 302,083 & 287,912 \\ \hline 28 & Commitments and contingencies & & \\ \hline 29 & Shareholders' equity: & & \\ \hline 30 & Commonstockandadditionalpaid-incapital,$0.00001parvalue:50,400,000sharesauthorized;15,943,425and16,426,786sharesissuedandoutstanding,respectively & 64,849 & 57,365 \\ \hline 31 & Retained earnings/(Accumulated deficit) & (3,068) & 5,562 \\ \hline 32 & Accumulated other comprehensive income/(loss) & (11,109) & 163 \\ \hline 33 & Total shareholders' equity & 50,672 & 63,090 \\ \hline 34 & Total liabilities and shareholders' equity & $352,755 & $351,002 \\ \hline \end{tabular} \begin{tabular}{|c|c|c|c|} \hline \multirow{2}{*}{ CONSOLIDATED STATEMENTS OF COMPREHENSIVE INCOME - USD (\$) \$ in Mil1ions } & \multicolumn{3}{|c|}{12 Months Ended } \\ \hline & Sep. 24,2022 & Sep. 25,2021 & Sep. 26,2020 \\ \hline \multicolumn{4}{|l|}{ Statement of Comprehensive Income [Abstract] } \\ \hline Net income & $99,803 & $94,680 & $57,411 \\ \hline \multicolumn{4}{|l|}{ Other comprehensive income/(loss): } \\ \hline Change in foreign currency translation, net of tax & (1,511) & 501 & 88 \\ \hline \multicolumn{4}{|l|}{ Change in unrealized gains/losses on derivative instruments, net of tax: } \\ \hline Change in fair value of derivative instruments & 3,212 & 32 & 79 \\ \hline Adjustment for net (gains)/losses realized and included in net income & (1,074) & 1,003 & (1,264) \\ \hline Total change in unrealized gains/losses on derivative instruments & 2,138 & 1,035 & (1,185) \\ \hline \multicolumn{4}{|l|}{ Change in unrealized gains/losses on marketable debt securities, net of tax: } \\ \hline Change in fair value of marketable debt securities & (12,104) & (694) & 1,202 \\ \hline Adjustment for net (gains)/losses realized and included in net income & 205 & (273) & (63) \\ \hline Total change in unrealized gains/losses on marketable debt securities & (11,899) & (967) & 1,139 \\ \hline Total other comprehensive income/(loss) & (11,272) & 569 & 42 \\ \hline Total comprehensive income & $88,531 & $95,249 & $57,453 \\ \hline \end{tabular} - Equity Value - Debt Value (Refer to entry on Sheet 3) - Tax Rate (Refer to entry on Sheet 2) - Cost of Debt (Refer to entry on Sheet 3) - Cost of Equity: Gordon Model (Refer to entry on Sheet 4) - Cost of Equity: CAPM (Refer to entry on Sheet 5) - WACC (using Cost of Equity: Gordon Model) - WACC (using Cost of Equity: CAPM) - In a note, answer the following question: Do you have a preference between the two methods for calculating Cost of Equity? Explain your answer in either case. - Sheet 2: Tax Rate - Like we did in class, use at least 3 years of data to estimate an average tax rate. If you recall our class work, Apple has had a fairly steady rate so this shouldn't be an issue. - Sheet 3: Cost of Debt - First estimate Net Financial Debt for year ending 2021 and 2022 (exactly like you did in your EV calculations last week) - Besides the obvious components on the liabilities (CP and Debt), you should also include Other current liabilities and Other noncurrent liabilities for debt here. - Don't include Vendor non-trade receivables here (if you want to know why, read the bottom of the Financial Instruments section of their Notes to Financial Statements), and obviously don't include Deferred revenue here. Both of those should be part of NWC which we're not considering this week. - Don't forget to include both short- and long-term marketable securities as part of excess financial assets. - Estimate the cost of debt as the ratio of net interest expense to net financial debt. - Use the following data: - Total dividends paid in each of the 5 years till Sep 2022 - Net stock repurchase in each of the past 5 years till Sep 2022 - Add two pieces above to get total cash flow to equityholders Estimate the average growth rate in cash flow to equityholders (use arithmetic average) - Get the market value at Sep 2022 end Use Gordon model formula to estimate cost of equity - Sheet 5: CAPM Beta estimate (Refer to sheet 6) Risk-free rate (Use the 1-year Treasury bill rate from the Treasury website) Expected market return or Market risk premium (Use the number from class or any other figure you feel is appropriate - with explanation) - Sheet 6: Beta Estimate Get 5 years of monthly prices for Apple and VFINX (this will be your market proxy) Calculate the returns Estimate beta \begin{tabular}{|c|c|c|c|} \hline 1 & CONSOLIDATED BALANCE SHEETS - USD (\$) \$ in Millions & Sep. 24,2022 & Sep. 25,2021 \\ \hline 2 & Current assets: & & \\ \hline 3 & Cash and cash equivalents & $23,646 & $34,940 \\ \hline 4 & Marketable securities & 24,658 & 27,699 \\ \hline 5 & Accounts receivable, net & 28,184 & 26,278 \\ \hline 6 & Inventories & 4,946 & 6,580 \\ \hline 7 & Vendor non-trade receivables & 32,748 & 25,228 \\ \hline 8 & 0ther current assets & 21,223 & 14,111 \\ \hline 9 & Total current assets & 135,405 & 134,836 \\ \hline 10 & Non-current assets: & & \\ \hline 11 & Marketable securities & 120,805 & 127,877 \\ \hline 12 & Property, plant and equipment, net & 42,117 & 39,440 \\ \hline 13 & 0ther non-current assets & 54,428 & 48,849 \\ \hline 14 & Total non-current assets & 217,350 & 216,166 \\ \hline 15 & Total assets & 352,755 & 351,002 \\ \hline 16 & Current 1iabilities: & & \\ \hline 17 & Accounts payable & 64,115 & 54,763 \\ \hline 18 & 0ther current liabilities & 60,845 & 47,493 \\ \hline 19 & Deferred revenue & 7,912 & 7,612 \\ \hline 20 & Commercial paper & 9,982 & 6,000 \\ \hline 21 & Term debt & 11,128 & 9,613 \\ \hline 22 & Total current liabilities & 153,982 & 125,481 \\ \hline 23 & Non-current liabilities: & & \\ \hline 24 & Term debt & 98,959 & 109,106 \\ \hline 25 & 0ther non-current liabilities & 49,142 & 53,325 \\ \hline 26 & Total non-current liabilities & 148,101 & 162,431 \\ \hline 27 & Total liabilities & 302,083 & 287,912 \\ \hline 28 & Commitments and contingencies & & \\ \hline 29 & Shareholders' equity: & & \\ \hline 30 & Commonstockandadditionalpaid-incapital,$0.00001parvalue:50,400,000sharesauthorized;15,943,425and16,426,786sharesissuedandoutstanding,respectively & 64,849 & 57,365 \\ \hline 31 & Retained earnings/(Accumulated deficit) & (3,068) & 5,562 \\ \hline 32 & Accumulated other comprehensive income/(loss) & (11,109) & 163 \\ \hline 33 & Total shareholders' equity & 50,672 & 63,090 \\ \hline 34 & Total liabilities and shareholders' equity & $352,755 & $351,002 \\ \hline \end{tabular} \begin{tabular}{|c|c|c|c|} \hline \multirow{2}{*}{ CONSOLIDATED STATEMENTS OF COMPREHENSIVE INCOME - USD (\$) \$ in Mil1ions } & \multicolumn{3}{|c|}{12 Months Ended } \\ \hline & Sep. 24,2022 & Sep. 25,2021 & Sep. 26,2020 \\ \hline \multicolumn{4}{|l|}{ Statement of Comprehensive Income [Abstract] } \\ \hline Net income & $99,803 & $94,680 & $57,411 \\ \hline \multicolumn{4}{|l|}{ Other comprehensive income/(loss): } \\ \hline Change in foreign currency translation, net of tax & (1,511) & 501 & 88 \\ \hline \multicolumn{4}{|l|}{ Change in unrealized gains/losses on derivative instruments, net of tax: } \\ \hline Change in fair value of derivative instruments & 3,212 & 32 & 79 \\ \hline Adjustment for net (gains)/losses realized and included in net income & (1,074) & 1,003 & (1,264) \\ \hline Total change in unrealized gains/losses on derivative instruments & 2,138 & 1,035 & (1,185) \\ \hline \multicolumn{4}{|l|}{ Change in unrealized gains/losses on marketable debt securities, net of tax: } \\ \hline Change in fair value of marketable debt securities & (12,104) & (694) & 1,202 \\ \hline Adjustment for net (gains)/losses realized and included in net income & 205 & (273) & (63) \\ \hline Total change in unrealized gains/losses on marketable debt securities & (11,899) & (967) & 1,139 \\ \hline Total other comprehensive income/(loss) & (11,272) & 569 & 42 \\ \hline Total comprehensive income & $88,531 & $95,249 & $57,453 \\ \hline \end{tabular}