Please see attached Apple case study and the excel file. I did most of the work. I only need help on question #3 (a & b).3. Add items to the statement of cash flows. This spreadsheet should calculate Jensen’s free cash flow. This is equal to cash generated from operations, minus cash needed for +NPV investments. It is necessary to remove investments in marketable securities from cash needed for investments, because this is typically where excess cash is parked.Furthermore, Apple gives employees options, and has no choice but to accept cash fromemployees when they exercise those options. Apple’s dividend will remain as well. A modified version of Jensen’s free cash flow would look like:a. Create a “cash needed for investing” item inside the statement of cash flows. This willequal Cash from Investing, minus Investment in Marketable & Equity Securt.b. Create a Jensen’s free cash flow item in the statement of cash flows. This will equal =cash from operations, + cash from financing activities, - cash needed for investing.You are free to modify this based on your own judgement.
Cover Page Financial Policy at Apple, 2013 Student Supplement Harvard Business School Case 9-214-085 Course Software 9-214-714 This course software was prepared solely as the basis for class discussion. Cases are not intended to serve as endorsements, sources of primary data, or illustrations of effective or ineffective management. Copyright © 2014 President and Fellows of Harvard College. No part of this product may be reproduced, stored in a retrieval system, used in a spreadsheet or transmitted in any form or by any means—electronic, mechanical, photocopying, recording or otherwise—without the permission of Harvard Business School. Balance Sheet Balance Sheet as of: Sept. 25, 2010Sept. 24, 2011Sept. 29, 2012CAGRVertical Analysis ASSETS Cash And Equivalents11,261.09,815.010,746.0-2.3% Short Term Investments14,359.016,137.018,383.013.1% Trading Asset Securities---0.0% Total Cash & ST Investments25,620.025,952.029,129.06.6% 0.0% Accounts Receivable5,510.05,369.010,930.040.8% Other Receivables4,414.06,348.07,762.032.6% Total Receivables9,924.011,717.018,692.037.2% 0.0% Inventory1,051.0776.0791.0-13.2% Deferred Tax Assets, Curr.1,636.02,014.02,583.025.7% Restricted Cash445.0-278.0-37.5% Other Current Assets3,002.04,529.06,180.043.5% Total Current Assets41,678.044,988.057,653.017.6% 0.0% Net Property, Plant & Equipment4,768.07,777.015,452.080.0% 0.0% Long-term Investments25,391.055,618.092,122.090.5%52.3% Goodwill741.0896.01,135.023.8% Other Intangibles342.03,536.04,224.0251.4% Other Long-Term Assets2,263.03,556.05,478.055.6%3.1% Total Assets75,183.0116,371.0176,064.053.0% 0.0% LIABILITIES0.0% Accounts Payable12,015.014,632.021,175.032.8% Accrued Expenses3,641.04,829.06,749.036.1% Currrent Income Taxes Payable658.01,140.01,535.052.7% Unearned Revenue, Current3,647.06,129.07,445.042.9% Other Current Liabilities761.01,240.01,638.046.7% Total Current Liabilities20,722.027,970.038,542.036.4% 0.0% Long-Term Debt---0.0% Unearned Revenue, Non-Current1,139.01,686.02,648.052.5% Deferred Tax Liability, Non-Current4,300.08,159.013,847.079.5% Other Non-Current Liabilities1,231.01,941.02,817.051.3% Total Liabilities27,392.039,756.057,854.045.3% 0.0% Common Stock10,668.013,331.016,422.024.1%0.2318655765 Retained Earnings37,169.062,841.0101,289.065.1% Comprehensive Income and Other(46.0)443.0499.00.0% Total Shareholders' Equity47,791.076,615.0118,210.057.3% 0.0% Total Liabilities And Shareholders' Equity75,183.0116,371.0176,064.053.0% Source: Capital IQ In millions of USD Income Statement Income Statement For the Fiscal Year Ending Sept. 25, 2010Sept. 24, 2011Sept. 29, 2012 Revenue65,225.0108,249.0156,508.0 Cost of Goods Sold39,541.064,431.087,846.0 Gross Profit25,684.043,818.068,662.0 Sales, General, and Administration Expenses5,517.07,599.010,040.0 Research and Development Expenses1,782.02,429.03,381.0 Total Operating Expenses7,299.010,028.013,421.0 Operating Income18,385.033,790.055,241.0 Interest Expense--- Interest and Investment Income311.0519.01,088.0 Net Interest Expense311.0519.01,088.0 Income Tax Expense4,527.08,283.014,030.0 Earnings from Cont. Ops.14,013.025,922.041,733.0 Net Income to Company14,013.025,922.041,733.0 Minority Interest in Earnings--- Net Income14,013.025,922.041,733.0 Net income available to Common Shareholders14,013.025,922.041,733.0 Earnings per Share Basic15.4128.0544.64 Diluted15.1527.6844.15 Weighted Average Shares Outstanding Basic909.5924.3934.8 Diluted924.7936.6945.4 Dividends per Share--$2.65 Payout Ratio %--6.0% EBITDA19,412.035,604.058,518.0 Source: Capital IQ In millions of USD, except per share items. Cash Flows Cash Flow For the Fiscal Year Ending Sept. 25, 2010Sept. 24, 2011Sept. 29, 20122013Vertical analysis Net Income14,013.025,922.041,733.061,101.3100.0% Depreciation & Amortization958.01,622.02,672.03,912.16.4% Amortization of Goodwill and Intangibles69.0192.0605.0885.81.4% Total Depreciation & Amortization1,027.01,814.03,277.04,797.9 Stock-Based Compensation879.01,168.01,740.02,547.54.2% Other Operating Activities1,440.02,868.04,405.06,449.410.6% Change in Accounts Receivable(2,142.0)143.0(5,551.0)(1,869.2) Change In Inventories(596.0)275.0(15.0)(79.1) Change in Accounts Payable6,307.02,515.04,467.02,117.5 Change in Unearned Revenue1,217.01,654.02,824.01,931.2 Change in Other Net Operating Assets(3,550.0)1,170.0(2,024.0)(904.1) Cash from Operations18,595.037,529.050,856.076,092.3 Capital Expenditure(2,005.0)(4,260.0)(8,295.0)(1,545.2) Cash Acquisitions(638.0)(244.0)(350.0)(1,083.7) Divestitures---- Sale (Purchase) of Intangible assets(116.0)(3,192.0)(1,107.0)- Investment in Marketable & Equity Securt.(11,075.0)(32,464.0)(38,427.0)(15,638.1) Net (Inc.) Dec. in Loans Originated/Sold--- Other Investing Activities(20.0)(259.0)(48.0)(929.9) Cash from Investing(13,854.0)(40,419.0)(48,227.0)(19,196.9) Short Term Debt Issued---- Long-Term Debt Issued---- Total Debt Issued---- Short Term Debt Repaid---- Long-Term Debt Repaid---- Total Debt Repaid---- Issuance of Common Stock912.0831.0665.0802.7802.7 Repurchase of Common Stock---- Dividends Paid--(2,488.0)(2,736.8) Other Financing Activities345.0613.0125.0- Cash from Financing1,257.01,444.0(1,698.0)(1,934.1) Net Change in Cash5,998.0(1,446.0)931.054,961.3 Cash Interest PaidNANANANA Cash Taxes Paid2,697.03,338.07,682.012,551.0 Levered Free Cash Flow12,524.620,918.831,224.677,691.7 Unlevered Free Cash Flow12,524.620,918.831,224.6330,650.6 Change in Net Working Capital(1,249.0)(4,270.0)(1,084.0)(39,768.0) Net Debt IssuedNANANANA Source: Capital IQ In millions of USD Annual Summary Annual Summary For the Fiscal Year Ending9/30/009/29/019/28/029/27/039/25/049/24/059/30/069/29/079/27/089/26/099/25/109/24/119/29/12 Total Revenue7,9835,3635,7426,2078,27913,93119,31524,57837,49142,90565,225108,249156,508 Cost of Goods Sold5,8174,1284,1394,4996,0229,88913,71716,42624,29425,68339,54164,43187,846 Sales, General, and Admin.1,2561,1381,1091,2121,4301,8642,4332,9633,7614,1495,5177,59910,040 Gross Profit2,1661,2351,6031,7082,2574,0425,5988,15213,19717,22225,68443,81868,662 EBIT530-33348253361,6432,4534,4078,32711,74018,38533,79055,241 Net Income786-2565692661,3281,9893,4956,1198,23514,01325,92241,733 Cash Flow868185892899342,5352,2205,4709,59610,15918,59537,52950,856 Cash and Marketable Securities4,0274,3364,3374,5665,4648,26110,11015,38624,49033,99251,01181,570121,251 Accounts Receivable9534665657667748951,2521,6372,4223,3615,5105,36910,930 Inventory331145561011652703465094551,051776791 Total Assets6,8036,0216,2986,8158,05011,51617,20525,34736,17147,50175,183116,371176,064 Accounts Payable1,1578019111,1541,4511,7793,3904,9705,5205,60112,01514,63221,175 Long-term Debt3003173160000000000 Total Shareholders' Equity4,1073,9204,0954,2235,0767,4289,98414,53222,29731,64047,79176,615118,210 Source: Capital IQ In millions of USD Quarterly Summary Quarterly Summary For the Fiscal Quarter Ending6/25/119/24/1112/31/113/31/126/30/129/29/1212/29/12 Total Revenue28,57128,27046,33339,18635,02335,96654,512 Cost of Goods Sold16,64916,89025,63020,62220,02921,56533,452 Gross Profit11,92211,38020,70318,56414,99414,40121,060 Sales, General, and Admin.1,9152,0252,6052,3392,5452,5512,840 EBIT9,3798,71017,34015,38411,57310,94417,210 Net Income7,3086,62313,06411,6228,8248,22313,078 Cash Flow11,10810,42917,55413,97710,1899,13623,426 Cash and Marketable Securitiesa76,15681,57097,601110,176117,221121,251137,112 Accounts Receivable6,1025,3698,9307,0427,65710,93011,598 Inventory8897761,2361,1021,1227911,455 Total Assets106,758116,371138,681150,934162,896176,064196,088 Accounts Payable15,27014,63218,22117,01116,80821,17526,398 Long-term Debt0000000 Total Shareholders' Equity69,34376,61590,054102,498111,746118,210127,346 Source: Capital IQ In millions of USD Excess Cash Forecast Key assumptions in Apple Forecast Annual growth rate of sales:10%Interest Rate:5.6% 2012 Actual2012*20132014201520162017 Accounts receivable as % of sales:11.9%11.9%11.9%11.9%11.9%11.9% Inventory as % cost of goods:0.9%0.9%0.9%0.9%0.9%0.9% Cost of goods as % of sales:56.1%56.1%56.1%56.1%56.1%56.1% R&D as % of sales:2.2%2.2%2.2%2.2%2.2%2.2% Sell, gen'l, admin as% of sales:6.4%6.4%6.4%6.4%6.4%6.4% Effective Tax Rate22.9%22.9%22.9%22.9%22.9%22.9% Net PPEN as % of COG17.6%17.6%17.6%17.6%17.6%17.6% Income Statement Net sales$156,508$172,159$189,375$208,312$229,143$252,058 Cost of goods$87,846$96,631$106,294$116,923$128,615$141,477 Research & development$3,381$3,719$4,091$4,500$4,950$5,445 Sell, gen'l, admin$10,040$11,044$12,148$13,363$14,700$16,170 EBIT$55,241$60,765$66,842$73,526$80,878$88,966 Interest expense$1,088$1,197$1,316$1,448$1,593$1,752 Profit before tax$54,153$59,568$65,525$72,078$79,285$87,214 Tax at Effective Rate of 23%$12,420$13,662$15,028$16,531$18,184$20,003 Net income$41,733$45,906$50,497$55,547$61,101$67,211 Times interest earned50.850.850.850.850.850.8 Balance Sheet Total Cash$121,251$145,657$172,504$202,036$234,521$270,254 Required Cash21,911.1214%$24,102.23$26,512.46$29,163.70$32,080.07$35,288.08 Excess Cash (plug)$99,340$121,555$145,992$172,872$202,441$234,966 Accounts receivable$18,692$20,561$22,617$24,879$27,367$30,104 Inventories$791$870$957$1,053$1,158$1,274 Other Current Assets (10.3% of COGS)$9,041$9,945$10,940$12,034$13,237$14,561 Net PPEN$15,452$16,997$18,697$20,567$22,623$24,886 Other (12.3% of COGS)$10,837$11,921$13,113$14,424$15,866$17,453 Total assets$176,064$205,952$238,828$274,992$314,773$358,531 Liabilities and Net Worth Accounts payable (24.1% of COGS)$21,175$23,293$25,622$28,184$31,002$34,103 Accrued expenses (7.7% of COGS)$6,749$7,424$8,166$8,983$9,881$10,869 Other current liabilities (12.1% of COGS)$10,618$11,680$12,848$14,133$15,546$17,100 Other non-current liabilities (22% of COGS)$19,312$21,243$23,368$25,704$28,275$31,102 Total liabilities$57,854$63,639$70,003$77,004$84,704$93,174 Equity$118,210$142,312$168,825$197,988$230,068$265,357 Total $176,064$205,952$238,828$274,992$314,773$358,531 Selected Ratios: Return on equity35.3%32.3%29.9%28.1%26.6%25.3% Sales/total assets88.9%83.6%79.3%75.8%72.8%70.3% Assets/Equity148.9%144.7%141.5%138.9%136.8%135.1% Net income/Revenue26.7%26.7%26.7%26.7%26.7%26.7% Payout ratio2.382.652.893.113.313.50 Accumulated cash$145,657$172,504$202,036$234,521$270