Hello,

Please see the attached pdf file for the questions, and use the excel to answer them.I only need some help with Q# 1 (the whole question). I have been trying to figure out what should the capital investment be for year 2013 and 2014? Should it be 1230 as in year 2012? I stopped at this because I didn't want to assume an amount and then I would have to redo the whole question. It seems too long, but it is not. It is just detailing how to answer the questions. Thank you for your!

Question 1 Part (a) PG Capital Expenditure Projections Year Opening Book Value of net PP&E Capital Investment Depreciation Closing Book Value of net PP&E 2012 21300 1230 2130 20400 2013 2014 Part (b) interest rate on short-term debt interest rate on long-term debt interest payments in 2013 0.04 0.055 Part (c) Net Income Statement Year Sales Cost of Sales Depreciation Gross Income Research & Development Sellling, General, Administrative Expenses Operating Income (EBIT) Interest Expense Pre-tax Income Tax Net Income 2012 83680 39190 2130 42360 0 26420 15940 1840 14100 3800 10300 2013 Pard (d) Current Assets Cash Net Receivables Inventory Other Short-Term Assets Current Liability Accounts Payable Short-Term Debt Other Current Liabilities 2012 4440 7070 6700 3700 % of 2012 2012 cost of sales 16210 41.363% =16210/39190 8700 n/a n/a 0 n/a n/a PG Working Capital Requirements as a % of sales and cost of sales Year 2012 Sales 83680 Cost of Sales 39190 Current Assets Cash Net Receivables Inventory Other Short-Term Assets Current Liability Accounts Payable Short-Term Debt Other Current Liabilities PG Working Capital Requirements Year Current Assets Cash Net Receivables Inventory Other Short-Term Assets Current Liability Accounts Payable Short-Term Debt Other Current Liabilities 2013 2014 % of sales 5.306% 4.806% 8.449% 7.949% 8.007% n/a % of cost of sales 41.363% 39.363% n/a n/a 2012 4440 7070 6700 3700 16210 8700 0 Part (e) Stockholders' equity in 2012 Net income in 2013 Dividends in 2013 Stockholders' equity in 2013 % of 2012 sales 5.306% =4440/83680 8.449% =7070/83680 8.007% =6700/83680 n/a n/a 63440 2013 2014 Part (f) Pro-forma Balance Sheet (Asset Side) Cash Net Receivables Inventory Other Short-Term Assets Current Assets 2012 2013 partial 4440 7070 6700 3700 21910 Net Property, Plant, Equipment Goodwill and Intangibles Other Long-Term Assets 20400 84640 5200 Total Assets 2013 132150 Pro-forma Balance Sheet (L+E) Accounts Payable Short-Term Debt Other Current Liabilities 2012 2013 partial 16210 8700 0 Current Liabilities 24910 Long-Term Debt 33100 Deferred Liability Charges 10700 Total Liabilities Stockholders' Equity 68710 63440 Total Liabilities and Equity 132150 EFN Part (g) interest rate on short-term debt interest rate on long-term debt interest payments in 2014 Part (h) EBIT tax rate Depreciation CAPEX 2013 Net Working Capital 2012 Net Working Capital Free Cash Flow Part (i) PG's firm value PG's enterprise value 0.04 0.055 2013 FIN 520 Problem Set 2 Question 1 Today is December 29, 2012. You are a financial manager at PG and want to know projected cash flows of your company for the next two years for decision-making purposes. Below is some financial statement information that is loosely based on Procter & Gamble (PG hereafter) for the year ended December 29, 2012: Income Statement (in millions $) Year Ended December 29, 2012 Sales Cost of Sales Depreciation Gross Income Research and Development Selling, General, Administrative Expenses Operating Income (EBIT) Interest Expense Pre-tax Income Tax Net Income 83,680 39,190 2,130 42,360 0 26,420 15,940 1,840 14,100 3,800 10,300 Balance Sheet (in millions $) Year Ended December 29, 2012 Cash Net Receivables Inventory Other Short-term Assets Current Assets 4,440 7,070 6,700 3,700 21,910 Accounts Payable Short-term Debt Other Current Liabilities 16,210 8,700 0 Current Liabilities 24,910 Net Property, Plant, Equipment Goodwill and Intangibles Other Long-term Assets 20,400 84,640 5,200 Long-term Debt 33,100 Deferred Liability Charges 10,700 Total Liabilities Total Stockholders' Equity Total Liabilities and Equity 68,710 63,440 132,150 Total Assets 132,150 Assume the following: (1) net property, plant, and equipment for the year ended December 29, 2011 was $21,300M; (2) long-term debt for the year ended December 29, 2011 was $32,000M. a) Assume that PG's sales will increase by 0.5% per year for the next two years. To support the projected sales growth, PG will expand its production during this period by using its existing plants more efficiently, rather than building new plants. That is, PG plans to maintain its current net PP&E level for the next two years. Assume that PG depreciates its assets using Declining Balance Method with 10% depreciation rate. That is, PG's assets will depreciate by 10% each year : = & 10% & = & 1 Based on these assumptions, forecast PG's capital expenditures over the next two years. Use the following equation to solve for capital expenditures : & = & 1 + PG CAPEX Projections Year 2012 2013 Opening BV of net PP&E 21300 Capital Investment 1230 Depreciation 2130 Closing BV of net PP&E 20400 (Opening Book Value is the Closing Book Value in the previous year) 2014 b) The interest rate is 4% on PG's short-term debt and 5.5% on PG's long-term debt. Based on these assumptions, calculate PG's interest payments in 2013. Use the following equation to calculate interest payments : = 1, + 1, c) Assume that PG's sales will increase by 0.5% per year for the next two years. Assume also that the ratio of cost of sales to sales and the ratio of selling, general, administrative expenses to sales will remain the same from year 2012 through 2014. PG plans to keep its R&D expenditures at zero. The corporate tax rate of PG is 27%. Based on these assumptions and your answers from part (a) and (b), construct PG's proforma income statement for 2013. Net Income Statement Year Sales Cost of Sales Depreciation Gross Income Research & Development Sellling, General, Admin Expenses Operating Income (EBIT) Interest Expense Pre-tax Income Tax Net Income 2012 83680 39190 2130 42360 0 26420 15940 1840 14100 3800 10300 2013 d) The table below lists PG's current working capital as a fraction of sales and cost of sales. To utilize their working capital more efficiently, PG plans to tighten its credit policy, speed up customer payments and reduce PG's inventory. Specifically, PG plans to reduce the ratios of cash to sales, net receivables to sales, inventory to sales by 0.5% each year for the next two years. For example, cash will be 5.306-0.5=4.806% of 2013 sales in 2013. Likewise, net receivables will be 8.449-0.5=7.949% of 2013 sales in 2013. At the same time, PG will also reduce the ratio of accounts payable to cost of sales by 2% each year for the next two years. For example, accounts payable will be 41.363-2=39.363% of 2013 cost of sales in 2013. Other current items, namely other short-term assets, short-term debt, and other current liabilities, will remain unchanged during the next two years. Based on these projections, calculate PG's working capital requirements through 2014. Current Assets Cash Net Receivables Inventory Other Short-Term Assets 2012 4440 7070 6700 3700 PG WC Requirements Year Current Assets Cash Net Receivables Inventory Other Short-Term Assets Current Liability Accounts Payable Short-Term Debt Other Current Liabilities =4440/83680 =7070/83680 =6700/83680 n/a 2012 16210 8700 0 Current Liability Accounts Payable Short-Term Debt Other Current Liabilities % of 2012 sales 5.306% 8.449% 8.007% n/a % of 2012 cost of sales 41.363% n/a n/a =16210/39190 n/a n/a 2012 2013 2014 4440 7070 6700 3700 3700 3700 16210 8700 0 8700 0 8700 0 e) Assume that PG has a non-flexible dividend policy. That is, PG pays $8 cash dividend per share each year, regardless of its net income. PG has 1000M stock shares outstanding. Calculate stockholders' equity for PG in 2013. Assume that PG will not issue or repurchase stocks in 2013. Use the following equation to calculate stockholders' equity (SE) : = 1 + + f) Based on parts (a) through (e) construct a pro-forma balance sheet for 2013. If total assets are greater than total liabilities plus equity, correct this imbalance by adding to long-term debt. If total assets are less than total liabilities plus equity, correct this imbalance by retiring long-term debt. Assume that goodwill and intangibles, other long-term assets, and deferred liability charges remain unchanged from 2012. As assumed in part (d), other short-term assets, short-term debt, and other current liabilities also remain unchanged. Pro-forma Balance Sheet (Asset Side) Cash Net Receivables Inventory Other Short-Term Assets Current Assets 2012 4440 7070 6700 3700 21910 Net Property, Plant, Equipment Goodwill and Intangibles Other Long-Term Assets 20400 84640 5200 Total Assets 2013 partial 2013 132150 2013 partial 2013 Pro-forma Balance Sheet (L+E) Accounts Payable Short-Term Debt Other Current Liabilities 2012 16210 8700 0 Current Liabilities 24910 Long-Term Debt 33100 Deferred Liability Charges 10700 Total Liabilities Stockholders' Equity 68710 63440 Total Liabilities and Equity 132150 EFN g) The interest rate is 4% on PG's short-term debt and 5.5% on PG's long-term debt. Based on the short-term and long-term debt projections for 2013 from part (f), calculate PG's interest payments in 2014. Use the following equation to calculate interest payments : = 1, + 1, Now, you are ready to calculate PG's pro-forma income statement for 2014. Note that you can also repeat steps (e) and (f) to calculate stockholders' equity and a pro-forma balance sheet for 2014 (luckily, that is not a part of this assignment). As this example shows, you cannot in general calculate, say, pro-forma income statements of next five years all at once. You first calculate an income statement for 2013, and then calculate a balance sheet for 2013 using the income statement for 2013. Next you calculate an income statement for 2014 using the balance sheet for 2013, and then calculate a balance sheet for 2014 using the income statement for 2014, and so on. Hold on though. We are not done with this question yet. Let's now calculate the firm value. h) What is the projected free cash flow (FCF) for December 29, 2013? Use the following equation to solve for FCF: = (1 ) + Make sure to show the individual numbers used in this equation. The free cash flows are the cash flows available for distribution to the firm's investors (stockholders and bondholders). i) Suppose PG's FCF grows at a constant rate of 0.5% after 2013. What is the enterprise value of PG today according to our FCF calculation and the growth assumption? What is the firm value of PG today? Assume that the discount rate for PG's FCF is 10%. = + Question 2 Today is Feb 1, 2015. Suppose you want to create a \"Condor Spread\" option strategy based on Apple call options. The condor spread will involve the following: Buying a call option with strike price $100 Selling a call option with strike price $110 Selling a call option with strike price $120 Buying a call option with strike price $130 You want all of these options to have the same maturity of approximately 1.5 months. The attached excel file reports the quoted prices of Apple equity options that expire on Mar 20, 2015. The \"Ask\" price is the price at which you can buy an option while the \"Bid\" price is the price at which you can sell (i.e. short) an option. The current Apple stock price is $117.16 per share. (You can get the latest equity option quotes from Yahoo! Finance. For example, go to Yahoo! Finance and search for Apple (symbol: AAPL), then click on \"Options\" on the left side of the screen. Choose \"March 20 2015\" from the dropdown menu.) a) Like in class, provide the payoff function for each range of strike prices in which the stock price in 1.5 months could land. (The stock price can land in five ranges: